Database Systems (Kifer, Bernstein, Lewis)

From Colettapedia
(Redirected from Relational Database Terms)
Jump to navigation Jump to search

Chapter 2

  • Atomicity - The system must ensure that the transaction either runs to completion, or if it does not complete, has no effect at all.
  • Isolation - concurrent vs. serial transaction processing. Time interleaving of tranactions results in lost update.

Chapter 3

  • indices - auxiliary data structures that enable applications to retrieve records based on the value of a search key.
  • physical schema vs. conceptual schema vs. external schema: External schema, also known as the user abstraction level, or the view abstraction level, is used to customize the conceptual schema to the needs of various classes of users.
  • conceptual data independence - Applications tailored to the needs of specific user groups can be designed to use the external schemas appropriate for these groups.
  • data model - e.g., The Relational Model; consists of a set of concepts and languages for describing
    1. Conceptual and external schemas. A schema specifies the structure of the data stored in the database. Schemas are described using a data definition language.
    2. Constraints ...
    3. Operations on data
  • relation = table
    • relation instance = a table with rows and named columns. A set of unique tuples. Rows are called tuples because they will all have the same number of columns.
    • cardinality = number of tuples in a relation
    • attributes = names of the columns
    • attribute domain - set of all the values in the tuple for a given attribute.
    • data atomicity - you can't go to a value in a row and do a dot notation function call on it like an object oriented database.
  • relation schema - name of the relation, attribute names and their data types either built in or defined, and integrity constraints.
  • key constraint, key, minimality property, uniqueness property.
  • superkey = a key, plus some more attributes. like a key without the minimality condition.
  • Candidate Key = one of several possible keys for a relation
  • primary key - a key that represents a unique tuple, a minimal superkey
  • Referential Integrity (Referential Constraint) - The requirement that referenced tuples must exist. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key.
  • Foreign key - When a value references the primary key in some other table.
  • referential constraint - between two tables. restrict this column's values to consist only of a primary key of the parent table.
    • ex., EMPLOYEE(MngrId) references EMPLOYEE(Id);
      • see how they don't have to have the same name?
    • also referential constraint: TRANSCRIPT(CrsCode,Semester) references TEACHING(CrsCode,Semester)
  • CREATE TABLE blah( .... PRIMARY KEY (blah1), UNIQUE (blah4, blah7))
  • NULL = the placeholder until the value becomes available. In CREATE TABLE stmt, you can specify and attribute to be NOT NULL.

Semantic Constraints

  • semantic constraint - more a business or convention rather than a structural constraint as described by the preceding defined terms.
    • Use the CHECK( bool ), attached to the CREATE TABLE stmt, or when you CREATE ASSERTION.
    • CHECK ( Grade IN ('A', 'B', 'C', 'D', 'F' ) )
    • CHECK ( StuID > 0 AND StuId < 10000000 )
    • CHECK ( MngrSalary > Salary ) MngrSalary and Salary being attributes in the relation.
    • CHECK clause always returns true for empty relation. "The condition is supposed to be satisfied by every tuple in the relation, and not the relation itself"
    • CREATE ASSERTION ThouShaltNotFireEveryone ( CHECK ( 0 < SELECT COUNT(*) FROM Employee ) )
    • CREATE ASSERTION ThouShaltNotOutearnYourBoss ( CHECK ( NOT EXISTS ( SELECT * FROM Employee, Manager WHERE Employee.Salary > Manager.Salary AND Employee.MngrId = Manager.Id))

User Defined Domains

  • CREATE DOMAIN Grades CHAR(1) CHECK ( VALUE IN ('A', 'B', 'C', 'D', 'F', 'I' ) )

Foreign-Key Constraints

  • CREATE TABLE Teaching ( ... FOREIGN KEY (CrsCode) REFERENCES Course, FOREIGN KEY (ProfId) REFERENCES Professor (Id) )
    • if the attribute names are the same you don't have to state them twice in defining foreign key.
  • Chicken and egg problem - postpone the introduction of teh foreign-key constraint until after teh other relation has been defined:
    • ALTER TABLE Employee ADD CONSTRAINT EmpDeptConstr FOREIGN KEY (DepartmentId) REFERENCES Department (DeptId)
  • You may need to use transactions to initially defer constraint checking

Reactive Constraints

  • reactive constraint = When you want other remedial action to take place when a constraint is violated, other than abort the whole thing.
  • use triggers which are fired. declare when creating table and defining foreign keys. ("ON DELETE NO ACTION", "ON DELETE SET NULL")