31271 Lecture Notes - Lecture 4: Subtyping, Referential Integrity, Unique Key
Lecture 4: Relational Model
Components of Relational Model
• Data Structure:
o Tables (relations), rows, columns
• Data manipulation:
o Powerful SQL operations for retrieving and modifying data
• Data Integrity:
o Mechanisms for implementing business rules that maintain integrity of
manipulated data. If there is a business rule, it needs to be
correct/relevant for the business
Relations
• A relation is a named, two-dimensional table of data.
• The rows of this table correspond to instances, with the columns corresponding
to an attribute or field.
• The relation table itself corresponds to entity types, and with many-to-many
relationship types.
• Each row must be unique otherwise there is data redundancy.
• The order of the rows and columns must be irrelevant for a table to qualify as a
relation.
Key Fields
• Primary keys are unique identifiers of the relation, guaranteeing that all the
rows are unique.
• Foreign keys are identifiers that enable a dependent relation to refer to its
parent relation. Effectively, it is an attribute in a relation which serves as the
primary key of another relation.
• Keys can be simple or composite. They are usually used as indexes to speed up
the response to user queries.
Constraints
• Domain Constraints – allowable values for an attribute. All about the type of data
stored in a database.
• Referential Integrity – no primary key attribute may be null. All primary key
fields MUST have data. Any foreign key value on the many side, must match a
primary key value in the relation of the one side.
o There are a set of delete rules that support the referential integrity rule:
▪ Restrict – don’t allow the deletion of the parent side if related
rows exist in dependent side.
▪ Cascade – automatically delete the dependent side rows that
correspond with the parent side row to be deleted.
▪ Set-to-Null – set the foreign key in the dependent side to null if
deleting from the parent side.
• The foreign key is allowed to be set to null, unlike the
primary key.
• Set-to-Null is not allowed for weak and associated
entities.
• Action Assertions – Business Rules.
Transforming EER Diagrams into Relations
Regular Entities
Simple Attributes
• ER attributes map directly onto the relation.
find more resources at oneclass.com
find more resources at oneclass.com
Composite Attributes
• Use only their simple component values
Multivalued Attribute
• Becomes a separate relation with a foreign key taken from the superior identity.
• There is a one to many relationship between the original entity and the new
relation.
Weak Entities
• Becomes a separate relation with a foreign key taken from the superior entity.
• The primary key is composed of:
o Partial identifier of the weak entity.
o Primary key of the identifying relation (strong entity)
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Components of relational model: data structure, tables (relations), rows, columns, data manipulation, powerful sql operations for retrieving and modifying data, data integrity, mechanisms for implementing business rules that maintain integrity of manipulated data. If there is a business rule, it needs to be correct/relevant for the business. Key fields: primary keys are unique identifiers of the relation, guaranteeing that all the rows are unique, foreign keys are identifiers that enable a dependent relation to refer to its parent relation. Effectively, it is an attribute in a relation which serves as the primary key of another relation: keys can be simple or composite. They are usually used as indexes to speed up the response to user queries. Constraints stored in a database. fields must have data. Any foreign key value on the many side, must match a primary key value in the relation of the one side: domain constraints allowable values for an attribute.