31271 Lecture Notes - Lecture 4: Subtyping, Referential Integrity, Unique Key

76 views7 pages
4 Jun 2018
School
Course
Professor
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
Unlock document

This preview shows pages 1-2 of the document.
Unlock all 7 pages and 3 million more documents.

Already have an account? Log in
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
Unlock document

This preview shows pages 1-2 of the document.
Unlock all 7 pages and 3 million more documents.

Already have an account? Log in

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.

Get access

Grade+20% off
$8 USD/m$10 USD/m
Billed $96 USD annually
Grade+
Homework Help
Study Guides
Textbook Solutions
Class Notes
Textbook Notes
Booster Class
40 Verified Answers
Class+
$8 USD/m
Billed $96 USD annually
Class+
Homework Help
Study Guides
Textbook Solutions
Class Notes
Textbook Notes
Booster Class
30 Verified Answers

Related Documents