1 / 11

Database Relationships

Database Relationships. Types of Relationships. One to one Person to Driver’s License, Country to President, Person to Social Security Number (in theory) One to many Mother to Child, Division to Department, Supervisor to Employees, Owner to Car, Customer to Invoice Many to many

cabbott
Télécharger la présentation

Database Relationships

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. Database Relationships

  2. Types of Relationships • One to one • Person to Driver’s License, Country to President, Person to Social Security Number (in theory) • One to many • Mother to Child, Division to Department, Supervisor to Employees, Owner to Car, Customer to Invoice • Many to many • Parent to child, student to professor, nurse to patient, Invoice to Inventory Part, student to class

  3. In Theory and Practice • In theory, a DBMS could represent any of the relationships shown • In practice, many-to-many relationships are never done directly • Instead it is broken down into two one-to-many relationships (easier to implement in DBMS, easier to understand)

  4. Students and Classes Many to many one student has many (>= 0) classes one class has many (>= 0) students Students Classes

  5. Changing Many-Many to 1-Many • Students to Classes is many-to-many • To change it to two one-to-many relationships, One table is just Students, one table is just Classes, then a third table gives the relationship “enrolled” and ties the two other tables together. The relationships between the two original tables and the Enrolled table are one-to-many.

  6. Two One-to-Many Relationships

  7. To make a relationship between tables • Start by clicking on the Database Tools menu on the Ribbon

  8. Add the tables that you want

  9. Adding relationships • Decide which fields in which tables you want to relate • Drag the arrows from one of the fields you want to relate to the other – not just the TABLES but the specific fields

  10. Referential Integrity • “References have integrity” (what does this mean?) • When you state the relationships that must hold between the tables in a database, the DBMS enforces those relationships • If a table is on the “many” end of a one-to-many relationship, when you enter a record in the table, the DBMS checks to see that there does exist ONE record in the other table to match

  11. Referential Integrity violation

More Related