Tom Kelliher, CS 318
Jan. 30, 2002
Read 4.3
PHP lab.
SQL data definition language I.
Views used to constrict the data available to a user. Enhance security, privacy, simplicity.
Additionally, the relational model has well-defined, powerful mathematically-defined operators, enabling analysis and optimization of queries.
Type constraint example:
Database schema, database instance.
Some constraints:
Intra-relational. Key constraint. Name another ``key.'' Static.
Static constraints define legal instances.
Inter-relational. Foreign key constraint. Static.
Dynamic constraint.
Dynamic constraints define transitions between legal instances.
Semantic constraint. Implement business rules.
As opposed to structural constraints, as in some of the former
constraints.
(Which?)
consists of a subset, , of attributes of S with the property that an instance, s, of S satisfies if it does not contain a pair of distinct tuples whose values agree on all the attributes of . Also, we assume no proper subset of is a key constraint.
Often, the candidate keys are expressed as ICs.
Transcript references Course. How do you guarantee that a transcript row refers to an actual Course row?
Example of referential integrity: foreign key constraint.
1-1 relationship between the attributes and the corresponding attributes have the same values.
For it to be a foreign key, ``at most'' must be ``exactly.''
Suppose that S and T are relation schemas, is a list of attributes in S, and is a key constraint in T. There is a 1-1 correspondence between attributes in and . We say that relation instances and satisfy the foreign key constraint references and that is a foreign key if and only if for every tuple there is a tuple that has the same values over the attributes of as does s over the corresponding attributes of .
Foreign keys are a specific type of inclusion dependency.