1 / 14

REA Model

REA Model. Relational Database Design: Converting Conceptual Models to Relational Databases. Relational Database Model. Some principles of the relational model Entity Integrity A primary key in a table must not contain a null value

kenna
Download Presentation

REA 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. REA Model Relational Database Design: Converting Conceptual Models to Relational Databases

  2. Relational Database Model • Some principles of the relational model • Entity Integrity • A primary key in a table must not contain a null value • Guarantees uniqueness of entities and enables proper referencing of primary key values by foreign key values • Referential Integrity • A value for a foreign key in a table must either • Be null (blank) • Match exactly a value for the primary key in the table from which it was posted • One Fact, One Place • Fact = a pairing of a candidate key attribute value with another attribute value

  3. Referential Integrity Example Not in Salesperson Table

  4. Converting Conceptual to Relational • Participation Cardinalities communicate some of the information regarding redundancy and load • Provide us with info needed for establishing the relationships between entities • Relationship table • Foreign key posting

  5. Relationship Conversion • Maximum Cardinalities • The general rule is to post into a “1” entity table • This avoids “repeating groups” redundancy • You can NEVER post into an “N” entity • This causes “repeating groups” redundancy • Minimum Cardinalities • The general rule is to post into a “1” (mandatory) entity table • This avoids null values in the foreign key column

  6. Relationship Conversion • Step 2: Create a separate table to represent each many-to-many relationship in the conceptual model, I.e., for the following participation cardinality patterns (0,N)-(0,N) (0,N)-(1,N) (1,N)-(0,N) (1,N)-(1,N) • You must create a separate table to represent the relationship • The primary keys of the related entity tables are posted into the relationship table to form its primary key. This kind of primary key is called a composite or concatenated primary key • There are no exceptions to this rule!!! • If you post a foreign key in either direction, redundancy will be a problem for many-to-many relationships

  7. Two guidelines will produce the same result. • Put the primary key of the event with the minimum of one (sales) as a foreign key in the event with the minimum of zero (receive cash); or • Put the primary key of the event that occurs first (sales) as a foreign key in the event that occurs second (receive cash).

  8. Example: Many-Many Relationships

  9. Example: Many-Many Relationship Relationship Table

  10. SaleID CustID City Amount Date Name Sale Customer (0,N) (1,1) Is-to S-ID* Cust-ID Name Address S1, S3 C1 Heather Walnut Creek S2 C2 Steven Cincinnati Example 1: Posting into a (1,N)

  11. SaleID CR-ID Amount Amount Date Date Sale Cash Receipt (1,1) (1,1) yields Example: (1,1)-(1,1) Could do either one but because the entities represent sequential events, authors follow the practice of placing the primary key of the event that occurs first (sale) as a foreign key in the event that occurs second (cash receipt).

  12. SaleID CR-ID Amount Amount Date Date Sale Cash Receipt (0,1) (1,1) yields Example 2: Posting into a (1,1)-(0,1) This eliminates null values

  13. Relationship Attribute Placement • If relationship becomes a separate table, then relationship attributes are placed in that table Relationship Attribute Relationship Table

  14. Relationship Attribute Placement Relationship Attribute

More Related