1 / 24

The Relational Database Model – some relations you might want to avoid!!!

The Relational Database Model – some relations you might want to avoid!!!. Our HERO!!!. Logical vs. Physical. Relational Database Designer focuses on logical representation rather than physical Use of table advantageous Structural and data independence

oralee
Download Presentation

The Relational Database Model – some relations you might want to avoid!!!

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. The Relational Database Model – some relations you might want to avoid!!!

  2. Our HERO!!!

  3. Logical vs. Physical • Relational Database • Designer focuses on logical representation rather than physical • Use of table advantageous • Structural and data independence • Related records stored in independent tables • Logical simplicity • Allows for more effective design strategies

  4. Logical View of Data • Entities and Attributes • Entity is a person, place, event, or thing about which data is collected • Attributes are characteristics of the entity • Tables • Holds related entities or entity set • Also called relations • Comprised of rows and columns

  5. Table Characteristics • Two-dimensional structure with rows and columns • Rows (tuples) represent single entity • Columns represent attributes • Row/column intersection represents single value • Tables must have an attribute to uniquely identify each row • Column values all have same data format • Each column has range of values called attribute domain • Order of the rows and columns is immaterial to the DBMS

  6. Keys • One or more attributes that determine other attributes • Key attribute • Composite key • Full functional dependence • Entity integrity • Uniqueness • No ‘null’ value in key

  7. Keys

  8. Example Tables Figure 2.1

  9. Comparison Operators … A and B - Intersect A OR B –UNION: all of A (including yellow, gray and purple) all of B (including aqua, and purple and gray) C and B

  10. Simple Relational Database Figure 2.2

  11. Integrity Rules • Entity integrity • Ensures all entities are unique • Each entity has unique key • Referential integrity • Foreign key must have null value or match primary key values • Makes it impossible to delete row whose primary key has mandatory matching foreign key values in another table

  12. Relationships within Relational Database • Relationship classifications • 1:1 • 1:M • M:N • E-R Model • ERD Maps E-R model • Chen • Crow’s Feet

  13. ERD Symbols • Rectangles represent entities • “1” side of relationship • Number 1 in Chen Model • Bar crossing line in Crow’s Feet Model • “Many” relationships • Letter “M” and “N” in Chen Model • Three pronged “Crow’s foot” in Crow’s Feet Model

  14. Define Relationship • Determine relationship using this terminology: (i.e. relationship between student and dorm rooms) • 1 of A is related to X (1 or many) of B • i.e. 1 student is assigned to 1 dorm room • 1 of B is related to X (1 or many) of A • i.e. 1 dorm room is assigned to many students • The decision will be as follows: • 1:1 • 1 of A is related to 1 of B • 1 of B is related to 1 of A • 1:M • 1 of A is related to many of B • 1 of B is related to 1 of A • M:N • 1 of A is related to many of B • 1 of B is related to many of A

  15. Relationship Resolution 1 to 1 (1:1) • Assumed that the entity is just another attribute for that table. • Add entity as another attribute to existing table

  16. Relationship Resolution 1 to Many (1:M) • The primary key of the one side is duplicated as the foreign key on the many side. • RULE!!!! foreign key ALWAYS goes on Many side. • Names of the primary key and the foreign key do not need to match - only the data type needs to be the same. • Of course, the values of the data stored in the field must match as well or there can not be a join.

  17. Relationship Resolution M:N • Resolve the M:N relationship into two 1:M relationships • Create an associative entity (AKA composite entity or bridge entity) with primary keys (PK) of two entities as foreign keys (FK) • Associative entity is many side of both 1:M relationships. • FK ALWAYS goes on many side of relationship -> Associative entity ALWAYS many side of the relationship • If combination of 2 FKs unique, can use as PK of the associative entity. • In this case, since PK be composed of 2 PKs, called composite key. • If combination of 2 FKs NOT unique, leave 2 FKs in associative entity. Create new PK for associative entity.

  18. Comparison of Modeling Techniques 1:1 relationship Steering Wheel Steering Wheel Steering Wheel Car Car Car 1 1 1 1 1 1

  19. Comparison of Modeling Techniques 1:M relationship Tire Tire Tire Car Car Car 1 M ∞ 1 1

  20. Example 1:M Relationship Figure 2.20

  21. Comparison of Modeling Techniques M:N relationship (yes it seems it should be M:M but…) Student Student Student Class Class Class M N ∞ ∞

  22. Example M:N Relationship Figure 2.24

  23. Data Redundancy Revisited • Foreign keys can reduce redundancy • Some redundancy is desirable • Called controlled redundancy • Speed • Information requirements

  24. Indexes • Points to location • Makes retrieval of data faster Figure 2.31

More Related