rea model n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
REA Model PowerPoint Presentation
Download Presentation
REA Model

Loading in 2 Seconds...

  share
play fullscreen
1 / 14
kenna

REA Model - PowerPoint PPT Presentation

206 Views
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. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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