Database Systems (Kifer, Bernstein, Lewis)
Jump to navigation
Jump to search
Contents
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
- Conceptual and external schemas. A schema specifies the structure of the data stored in the database. Schemas are described using a data definition language.
- Constraints ...
- 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)
- ex., EMPLOYEE(MngrId) references EMPLOYEE(Id);
- 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")