Logical Data Modeling

1. Logical Data Modeling

2. Logical Data Modeling • The process of arranging the entities and attributes of the conceptual data model (ERD) of the business environment into the tables and columns of a relational database structure to serve that business in an information system • The goal is to model tables that properly reflect the organization’s business environment, showing the linkages between related data via the use of primary keys and foreign keys (see next slide)

3. Relational Keys in Tables • Primary Key (PK)(analogous to entity identifier) • A column (or columns) whose value uniquely identifies or differentiates each row in a table(e.g., EmployeeID) • Requiredforeverytableinarelationaldatabase • Composite Key (CPK) - a primary key made up of more than one column (e.g., FirstName + MiddleName + LastName) • Foreign Key (FK) • Acolumninonetablethatservesastheprimary key of another table in the same database (thus serving as a link between the two tables)

4. Foreign Keys Foreign keys reference a related table throughthe primary key of that related table FK PK

5. Foreign Keys • A foreign key in one table serves as a primary key in another table • This is a crucial concept for relational data-bases, because the foreign key is the means by which tables are linked together to repre-sent unary, binary, ternary, etc. relationships • The foreign key column in one table must have the same domain of values as the primary key column in the linked table • Two columns have the same domain of values if the columns have values of the same type (e.g., integer numbers; see previous slide)

6. Referential Integrity • A relational database constraint that involves the circumstance of referring to a specific data row in one table in the database based on the value stored in a column in another table • This constraint states that a foreign key value can not be stored in a table unless a matching value can be found in the primary key of the related table • The next slide shows how referential integrity affects the table relationship shown on slide 4

7. Referential Integrity • For every value of a foreign key there must be a primary key with that value • Example: For every value of CustomerID in the Order table there must be a matching value of CustomerID in the Customer table • The primary key must exist before the foreign key can be defined • Thus: Create and populate the Customer table before the Order table

8. Converting an ERD intoa Relational Schema • Each entity, attribute, and relationship that is present on the ERD that was developed to model a business situation must be converted to the appropriate structure required by a relational database design • A set of rules exist that specify each of the conversions that are required • If the ERD used is correct and complete, and if the conversion rules are properly used, a set of well-structured database tables will result

9. Converting an ERD intoa Relational Schema • Before beginning coverage of the conversion rules, let’s take a quick look(see next two slides)at the appearance of an ERD compared to the relational schema that results from it • Note the appearance of the arrows present on the relational schema ─ these will be explained later in these slides • Note also on the schema that the spaces have been removed from the names used for the database tables and columns ─ this is done to accommodate the requirements of the DBMS

10. Sample ERD

11. Sample Relational Schema The arrows shown on this diagram that are used to show the linkages between the tables are called referential integrity arrows. They are used to connect the FK of one table to the PK of another table.

12. Transforming ERDs into Schemas 1. Map Regular Entities to Tables • Composite Attributes: Use only their simple, component attributes • Multivalued Attributes: Become a separate table with a foreign key taken from the table for the original entity • Derived Attributes: Are not included in a relational schema (since, by definition, they represent data that are not stored, only calculated as needed)

13. Mapping a Composite Attribute (a) CUSTOMER entity with Address composite attribute (b) Resulting Customer table with Address details only

14. Mapping a Multivalued Attribute (a) EMPLOYEE entity with Skill multivalued attribute (b) Two resulting tables Note the composite PK in this table

15. Transforming ERDs into Schemas 2. Map Dependent (Weak) Entities • Becomes a separate table with a foreign key taken from the primary key of the table for the strong entity • Primary key is composed of the partial identifier of the dependent entity plus the primary key from the table for the strong entity (thus, creating a composite PK)

16. Mapping a Dependent Entity – Example 1 (a) Dependent (Weak) entity CHILD (b) Tables resulting from mapping entities Note the composite PK in Child table

17. Mapping a Dependent Entity – Example 2 (a) Dependent (Weak) entity TEAM (b) Tables resulting from mapping entities Note the FK in Player table

18. Transforming ERDs into Schemas 3. Map Binary Relationships • One-to-Many - Primary key on the one side becomes a foreign key on the many side • Many-to-Many - Create a newtable; the primary key of the new table is typically a CPK comprised of (at least) the primary keys of the two entities involved in the relationship • One-to-One - Primary key on the mandatory side becomes a foreign key on the optional side (if optionalities are asymmetric)

19. Mapping a Binary 1:M Relationship (a) Relationship between CUSTOMER and ORDER (1:M) (b) Two resulting tables

20. Mapping a Binary M:M Relationship (a) Relationship between ORDER and PRODUCT (M:M) (b) Three resulting tables

21. Mapping a Binary 1:1 Relationship (a) Relationship between NURSE and CARE CENTER (1:1) Note the asymmetric optionalities (b) Two resulting tables Note the optional use of a synonym for the FK

22. Transforming ERDs into Schemas 4. Map Associative Entities • Identifier Not Assigned • Default primary key for the table formed for the associative entity is typically a composite PK composed of (at least) the primary keys of the two entities • Identifier Assigned • May use if one exists that is natural and familiar to end-users • Must use if the composite PK can not be made unique by adding intersection data

23. Mapping an Associative Entity with Identifier not Assigned (a) Order Line as associative entity (b) Three resulting tables Note the PK of the associative table Note similarity of this situation to the M:M relationship shown on slide 20

24. Mapping an Associative Entity with an Identifier (a) Associative entity (ASSIGNMENT) (b) Three resulting tables Note the PK of the associative table

25. Transforming ERDs into Schemas 5. Map Unary (Recursive) Relationships • One-to-Many: Recursive foreign key in the same table (also true for unary One-to-One) • Many-to-Many (e.g., bill of materials): Two tables result: • One for the entity type • One for an associative relation in which the primary key has two fields, both taken from the identifier of the original entity

26. Mapping a Unary 1:M Relationship (a) EMPLOYEE entity with unary relationship (1:M) (b) Resulting Employee table with recursive foreign key Note mandatory use of synonym for FK

27. Mapping a Unary 1:M Relationship (c) Example data for Employee table PK FK • Requires a column in the table to act as a recursive foreign key referencing the primary key of the table

28. Mapping a Unary M:M Relationship (a) “Bill-of-Materials” relationship Note that if Quantity is always 1 this attribute may be omitted (as is done in the example on the next two slides (b) Two resulting tables Note compositePK, two FKs referencing the same PK, and mandatory use of synonym

29. Mapping a Unary M:M Relationship (c) Diagram of relationships of example items with one another Consider this Product, for example: • Both individual tools and sets of tools are sold • M:M relationship exists among the products

30. Mapping a Unary M:M Relationship (d) Example data for Item and Component tables PK FK FK

31. Transforming ERDs into Schemas 6. Map Ternary (and n-ary) Relationships • One table for each original entity and one for the common relationship (associative entity) (i.e., a ternary relationship maps to a total of four tables) • Table representing the associative entity has foreign keys to each entity in the relationship • PK of the table formed for the associative entity is typically a composite PK composed of (at least) the primary keys of the three entities

32. Mapping a Ternary Relationship (a) Ternary relationship as associative entity (b) Four resulting tables Note composite PK of associative relation (linking table) Remember that the CPK must represent a unique set of values

33. Validating the Number of Tables • One simple check that can be performed to make sure that your relational schema con-tains all of the tables that it should have, based on correctly converting the ERD from which the schema originates, is to add up the number of the following structures on the ERD: • Entities (regular, associative, and dependent) • M:M relationships • Multivalued attributes • The number of tables in the schema should match the sum of the numbers of these items