Data Models and Relational Databases

1. Data Models and Relational Databases Chapter 2

2. Learning Objectives • Identify primary and foreign keys for each entity and relevant relationships in the data model, • Create tables that are properly linked with foreign keys or through relationship table(s), • Examine a table design for any anomalies, and • Normalize a table to the third normal form.

3. The Database Development Process • Begins with enterprise modeling • Setting the range and general contents of organizational databases • Follows with conceptual data modeling • Analyzing overall entities’ requirements based on transaction cycles • Involves with logical database design • Transforming the conceptual data model into a logical data model using relational data models

4. Enterprise Modeling

5. Conceptual Data Modeling

6. Complete REA Diagram for Cherokee’s Revenue Cycle Resource Events Agents

7. Structure of a Relational Database • The primary construct is called a relation or a table. • Constructed with rows and columns much like a spreadsheet. • Each table represents either an entity or a relationship between entities. • Tables must be properly linked to make a relational database. • The columns in a table are called fields. • Fields represent the attributes or characteristics of the entity or relationship. • The rows in a table are called records or tuples. • Records represent all the specific data values that are associated with one instance.

8. Primary and Foreign Keys Primary key • The attributes (column) or combination of attributes (multiple columns) that uniquely identifies a specific row in a table. Foreign key • An attribute appearing in one table that is a primary key in another table. • Foreign key is used to link tables

9. Basic Requirements of Tables • The Entity Integrity Rule • The Referential Integrity Rule • Each attribute must be uniquely named. • Values of a specific attribute must be of the same type. • Each attribute (column) of a record (row) must be single-valued.

10. Steps of Implementing an REA Diagram in a Relational Database • Step 1: Create a table for each entity • Step 2: Create a table for each many-to-many relationship • Step 3: Examine tables with a one-to-one relationship • Step 4: Identify the attributes and assign the primary key for each table (note that a relationship table uses two columns as the primary key – concatenated key) • Step 5: Assign non-key attributes to tables • Step 6: Implement relationships using foreign keys

11. Implementing an REA Diagram in a Relational Database • Implement relationships using foreign keys • One-to-One (1:1): Include the primary key of the first entity as the foreign key in the second entity (Note: it does not matter which primary key becomes the foreign key). • One-to-Many (1:N or N:1): Place the primary key of the entity with the maximum cardinality of N as a foreign key in the entity that has a maximum cardinality of 1

12. Revised REA Data Model

13. Database Anomalies and Normalization • If a table is not well-designed, problems (anomalies) arise. • Three types of anomalies: • Update anomaly causes problems when every occurrence of a data item is not updated. • Insert anomaly causes difficulties (sometimes impossibility) to insert new data into a table without violating the basic integrity rules. • Delete anomaly involves unintended loss of data that occurs when deleting a record in a table.

14. Suppose Ted Sanderson changes his phone number. You need to make the change two times. If you fail to change it in both places or change it incorrectly in one place, then the records for Ted will be inconsistent. • This problem is referred to as an update anomaly.

15. What is the primary key for this table? • What if there is a new product to add but no customers have purchased it yet? Can we add the new product? Why or why not? • This problem is referred to as an insert anomaly.

16. Assume that Bird has filed bankruptcy and you decide to delete her record from the table. If Bird is the only customer that purchased item P-7119, deleting Bird’s record will also delete any information pertaining to P-7119. If there are any other requests for this product in the future, the product info will not be available. • This problem is referred to as a delete anomaly.

17. Normalization • One solution to avoid anomalies is to normalize the table by separating the table into a set of smaller tables. • Normalization is a formal process to determine which attributes should be grouped together in a table. It validates the logical design to avoid any violation of database requirements. • Each entity is stored in a table and we use foreign keys or relationship tables to link the entities together.

18. Steps in Normalization • Step 1: Remove any repeating groups (First Normal Form) • Step 2: Remove any partial dependencies (Second Normal Form) • Step 3: Remove any transitive dependencies (Third Normal Form)

19. Example:Partial Data in a Vendor Table

20. Remove repeating groups (1st NF) Vendor Table Inventory Table

21. The data exists in separate tables Vendor Table Inventory Table

22. We must link the separate tables • Using foreign keys, or • Using a relationship table • if it is M:N relationship • using a concatenated key (two columns together as the primary key) Vendor-Item Table

23. End of Chapter 2