1 / 17

Relational Model

Relational Model. Stores data as tables Each column contains values about the same attribute Each column has a distinct name Each row contains values for a given entity Each row is distinct Order of columns (and rows) is immaterial Cells are single valued. Terminology. Relationships.

maurinej
Download Presentation

Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Model • Stores data as tables • Each column contains values about the same attribute • Each column has a distinct name • Each row contains values for a given entity • Each row is distinct • Order of columns (and rows) is immaterial • Cells are single valued

  2. Terminology

  3. Relationships • An association between entities • Unary (recursive) • Binary • One to Many or Many to Many • Ternary • Quaternary

  4. Binary Relationships • One to one • Each row in a table has at most one matching row in another table • One to many • Many to many • A row in the first table matches many rows in the second and a row in the second matches many rows in the first

  5. Keys • Superkey • an attribute or a set of attributes that uniquely identifies a row within a table • Candidate • unique and irreducible identifier of a row, for all time (minimal superkey) • Primary • chosen candidate key for a table • Alternate

  6. Keys (continued) • Secondary • an attribute used for sorting and retrieval • Composite • a key made of more than one attribute • Foreign • used to designate relationships

  7. Database Design Language (DBDL) • Include all attributes (separated by commas) within parentheses with the name of the table outside the parentheses • Underline the primary keys • List any alternate, secondary and foreign keys identified by the letters AK, SK and FK respectively • Foreign keys are followed by an arrow pointing to the relation identified by the foreign key

  8. Representing Relationships • One to one • create a foreign key • One to many • place primary key of parent in child table • Many to many • Break into two one to many relationships

  9. Student # Student # Course # Name Course # Name Major Grade Instructor Student Grade Course

  10. Entity Relationship Design • Determine potential entities (rectangles) • Identify relationships (diamonds) between entities • Identify the attributes (ovals) of the entities • (and the attributes for any M:N relationships) • Identify the primary keys

  11. Entity-Relationship Diagram First

  12. Symbols for Columns

  13. Integrity rules • Entity integrity • No column that is part of the primary key may accept null values. • Referential integrity • If table A contains a foreign key that matches the primary key of table B, then values of this foreign key either must match the value of the primary key for the same row in table B or must be null.

  14. Integrity Services • Domain integrity • ensures that values assigned to a field are in that field’s domain • Relation integrity • ensures that related fields are in the same level of update

  15. Referential Constraints • Cascading Updates Option If the value of the primary key in the primary (referenced) table is changed, then all values of the appropriate foreign key in the related (referencing) table are automatically changed to the new value • Cascading Deletes Option If you delete a record in the primary (referenced) table, then all records in the related (referencing) table that have matching foreign key values will also be deleted

  16. Main/Subforms • First define the 1:N relationship between the tables • Create a main form for data from the primary table • Create a subform for the data from the related table (Similarly you can display records from the related table as a sub-datasheet in the primary table’s datasheet)

  17. Assignment 2 • MS Access 2000 • Page AC 4.33 • #6-9 (first set up a relationship between the tables)

More Related