Relational Data Model I
Tom Kelliher, CS 318
Jan. 30, 2002
- Schemas and abstraction.
- Relational model.
- Integrity constraints.
SQL data definition language I.
- Schema --- description of the layout of data.
- Database schemas:
- Physical: bits, bytes, and files level.
- Conceptual: The database at the DBMS level.
- View: Generally, constrictions of the conceptual schema.
Supported by the DBMS.
- Why abstractions?
Views used to constrict the data available to a user. Enhance security,
- A data model consists of:
- Conceptual and view schemas.
- Constraints --- conditions which the data must satisfy.
- Data operations --- queries, inserts, deletes, updates.
- We've talked about relation before.
Additionally, the relational model has well-defined, powerful
mathematically-defined operators, enabling analysis and optimization of
- Schema (S) vs. instance (s).
- Data atomicity --- smallest unit of data the DBMS is aware of.
- Example: The string type, even though strings decompose into
- What we're getting at: set-valued attributes such as ``hobbies.''
- Solution: object-relational databases. (PostgreSQl.)
- A relation schema consists of:
- Relation name --- unique across database.
- Attributes and associated domains.
- Integrity constraints --- an instance must satisfy these to be
Type constraint example:
- Column naming: there must be a one-to-one mapping between
columns in an instance and attributes in a schema.
- Domain constraints: The values in a particular column of an
instance must belong to the domain of the corresponding schema
- Relational database --- collection of relations.
Database schema, database instance.
- Consider the relation schemas:
- All course codes must be unique in the Course relation.
Intra-relational. Key constraint. Name another ``key.''
Static constraints define legal instances.
- The course code in a transcript tuple must match a course code in
a course tuple.
Inter-relational. Foreign key constraint. Static.
- A grade of ``A'' may not be changed to ``I.''
Dynamic constraints define transitions between legal instances.
- A student may not take more than 21 credits per semester.
Semantic constraint. Implement business rules.
As opposed to structural constraints, as in some of the former
- Key constraint definition:
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.
- What is the key for the Transcript table?
- A relation may have several keys, as we already saw.
- Candidate keys: set of possible keys.
Often, the candidate keys are expressed as ICs.
- Primary key. Table may be indexed on this key.
- Tuples in one relation commonly reference tuples in another
Transcript references Course. How do you guarantee that a transcript row
refers to an actual Course row?
- Referential integrity: the referenced tuple must exist.
Example of referential integrity: foreign key constraint.
- Foreign key constraint:
- is a candidate key for Table 2.
- Foreign key of Table 1 references the given candidate key
of Table 2.
1-1 relationship between the attributes and the corresponding attributes
have the same values.
- Since the foreign key references a candidate key, at most one row
of Table 2 is associated with a row of Table 1.
For it to be a foreign key, ``at most'' must be ``exactly.''
- Transcript(CrsCode) is a foreign key of Course: Transcript(CrsCode)
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 .
- If the attribute set in Table 2 were not a candidate key, we would
have an inclusion dependency.
Foreign keys are a specific type of inclusion dependency.
Thomas P. Kelliher
Tue Jan 29 13:05:35 EST 2002