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