Logical Data Modeling Logical Data Modeling The process

Logical Data Modeling

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

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. , Employee. ID) Required for every table in a relational database Composite Key (CPK) - a primary key made up of more than one column (e. g. , First. Name + Middle. Name + Last. Name) § Foreign Key (FK) • A column in one table that serves as the primary key of another table in the same database (thus serving as a link between the two tables) 3

Foreign Keys Foreign keys reference a related table through the primary key of that related table PK Order. ID Date FK Customer. ID 02345 9/25/07 2689 Customer. ID Last. Name First. Name Street City State Zip 02346 9/25/07 1001 Snerd Mortimer Gen. Delivery Tampa FL 33625 02347 9/26/07 2689 1233 Fogg Bob 567 Fogg Lane 02348 Omaha NE 681041327 9/27/07 1327 Amos Famous 2 Cookie Ct. Miami FL 33109 1551 Targa Maxine 67 Fast Lane Clinton NJ 08809 2002 George Scott 56 Neat St. Boulder CO 80301 2689 Guy Nice 290 Pleasant St. Tampa FL 33617 2966 Smith Bob 76 Quaker Path Aurora NY 13026 3014 Smith James 234 Bayview 33617 Tampa FL 4

Foreign Keys § A foreign key in one table serves as a primary key in another table § This is a crucial concept for relational databases, because the foreign key is the means by which tables are linked together to represent 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) 5

Referential Integrity § A relational database constraint that involves the circumstance of referring to a specific data row in one table in the databased 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 6

Referential Integrity § For every value of a foreign key there must be a primary key with that value • Example: For every value of Customer. ID in the Order table there must be a matching value of Customer. ID 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 7

Converting an ERD into a 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 8

Converting an ERD into a 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 9

Sample ERD 10

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

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

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

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

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

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

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

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 new table; 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) 18

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

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

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 21

Transforming ERDs into Schemas 4. Map Associative Entities • Identifier Not Assigned w 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 w May use if one exists that is natural and familiar to end-users w Must use if the composite PK can not be 22 made unique by adding intersection data

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 23

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

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: w One for the entity type w One for an associative relation in which the primary key has two fields, both taken from the identifier of the original entity 25

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 26

Mapping a Unary 1: M Relationship (c) Example data for Employee table PK Employee. ID FK First. Name Last. Name Date. Of. Birth Manager. ID 137 John Doe 03/15/1980 142 Mary Brown 05/16/1982 137 170 George Turner 11/04/1969 137 186 Stephen Smith 09/17/1978 142 198 Amanda Walters 12/17/1984 170 204 Ernest Hodges 08/29/1972 137 267 Michael Rogers 01/02/1985 170 285 Juan Rodriguez 10/10/1968 137 323 Kevin Mc. Fadden 11/11/1977 142 361 Charles Robideaux 02/28/1980 142 § Requires a column in the table to act as a recursive foreign key referencing the primary key of the table 27

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 composite PK, two FKs referencing the same PK, and mandatory use of synonym 28

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 29

Mapping a Unary M: M Relationship (d) Example data for Item and Component tables PK FK Item. No Name Selling Price FK Item. No Component. No 11 Wrench Model A 12. 50 43 11 14 Wrench Model B 13. 75 43 14 17 Wrench Model C 11. 62 44 11 19 Wrench Model D 15. 80 44 17 22 Hammer Model A 17. 50 44 19 24 Hammer Model B 18. 00 48 22 28 Hammer Model C 19. 95 48 24 31 Drill Model A 31. 25 48 28 35 Drill Model B 38. 50 53 43 43 Deluxe Wrench Set 23. 95 53 48 44 Master Wrench Set 35. 00 53 31 48 Deluxe Hammer Set 51. 00 56 44 53 Supreme Tool Set 100. 00 56 48 56 Grand Tool Set 109. 95 56 35 30

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 31

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 32

Validating the Number of Tables § One simple check that can be performed to make sure that your relational schema contains 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 33