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 organizations 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)
• Required for every table in a relational database
• Composite Key (CPK) - a primary key made up of
  more than one column (e.g., FirstName
  MiddleName LastName)
• 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)

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, lets 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 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)

19
Mapping a Binary 1M Relationship
(a) Relationship between CUSTOMER and ORDER (1M)
(b) Two resulting tables
20
Mapping a Binary MM Relationship
(a) Relationship between ORDER and PRODUCT (MM)
(b) Three resulting tables
21
Mapping a Binary 11 Relationship
(a) Relationship between NURSE and CARE CENTER
(11)
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 MM
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 1M Relationship
(a) EMPLOYEE entity with unary relationship (1M)
(b) Resulting Employee table with recursive
foreign key
Note mandatory use of synonym for FK
27
Mapping a Unary 1M Relationship
(c) Example data for Employee table
FK
PK

• Requires a column in the table to act as a
  recursive foreign key referencing the primary key
  of the table

28
Mapping a Unary MM 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
29
Mapping a Unary MM 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
• MM relationship exists among the products

30
Mapping a Unary MM Relationship
(d) Example data for Item and Component tables
FK
FK
PK
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)
• MM relationships
• Multivalued attributes
• The number of tables in the schema should match
  the sum of the numbers of these items