Transforming E-R Models into Relations

1
TransformingE-R Modelsinto Relations
Though this be madness, yet there is method in
it. Shakespeare
2
Class Outline

• What is the primary key-foreign key basis of a
  relational database?
• How is an Entity-Relationship model converted to
  relational tables? Specifically, how are these
  converted
• 11, 1M, MN relationships binary relationships
• ternary or recursive relationships
• optional/ mandatory relationships
• generalized hierarchies
• weak entities
• What are common data types provided by databases?
• Describe some guidelines for designing tables.
• Describe common database design flaws.

3
Steps to E-R Transformation

• 1. Identify entities
• 2. Identify relationships
• 3. Determine relationship type
• 4. Determine level of participation
• 5. Assign an identifier for each entity
• 6. Draw completed E-R diagram
• 7. Deduce a set of preliminary skeleton tables
  along with a proposed primary key for each table
  (using cases provided)
• 8. Develop a list of all attributes of interest
  (not already listed and systematically assign
  each to a table in such a way to achieve a 3NF
  design (i.e., no repeating groups, no partial
  dependencies, and no transitive dependencies)

4
Primary Key

• The basis of a relational database is the
  ability to link instances from different entities
  if they share a common attribute (primary key,
  foreign key).
• Each entity must have a primary key - an
  attribute that contains a unique value for each
  instance. The primary key cannot be null. The
  primary key is selected from all possible
  identifiers or candidate keys.

• A primary key is a determinant of all other
  attributes in a given row (e.g., knowing a
  supplier number allows us to look up all of the
  other characteristics of that supplier (name,
  phone , etc.)

5
Requirements for a Relationship between tables

• (a) Primary key - every row of a table must have
  a unique identifier which cannot include null
  entries
• (b) Foreign key- an entry that must match a
  primary key value in a table to which it is
  related may be null

Dependent Table
Parent Table
primary key
foreign key
primary key
6
Case 1. 11 relationship, both entities mandatory

• CASE 1a
• When the relationship type of a binary
  relationship is 11 with the level of
  participation of both entities mandatory,
  generally only one table is required.
• The primary key of this table can be the entity
  key from either entity (usually the strong
  entitys primary key).
• If the weaker entity is not part of any other
  relationships, it is probably an attribute of the
  stronger entity.

EMPLOYEE
JOB-DESCRIPTION
1
1
has a
EMPLOYEE (EMP_NUM, EMP_LNAME,, JOB_DESC)
7
Case 1. 11 relationship, both entities mandatory

• CASE 1b
• When the relationship type of a binary
  relationship is 11 with the level of
  participation of both entities mandatory, two
  tables may be created if they are indeed two
  different entities
• Place the foreign key (not null) in the strong
  entity (most frequently accessed)

PLUMBER
BUILDING
1
1
assigned
8
Case 2. 11 relationship, one entity mandatory

• When the relationship type of a binary
  relationship is 11 with the level of
  participation of one entity mandatory and the
  other optional, two tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• Additionally, the primary key from the entity
  with mandatory participation must be added as a
  foreign key (not null) to the corresponding table
  of the entity with optional participation.

1
1
EMPLOYEE
AUTO
has
EMPLOYEE (EMP_ID, EMP_LNAME, EMP_PHONE,) AUTO
(LIC_NUM, SERIAL_NUM, MAKE, MODEL,, , EMP_ID)
9
Case 3. 11 relationship, both entities optional

• When the relationship type of a binary
  relationship is 11 with the level of
  participation of both entities optional, two
  tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• Place the foreign key (null allowed) in the
  strong entity (most frequently accessed) .

TRAINER
1
1
has
EXERCISER
EXERCISER (EXERCISER_ID, EXERCISER_LNAME,
TRAINER_ID) TRAINER (TRAINER_ID, TRAINER_LNAME,
...)
10
Case 4. 1M relationship, both entities mandatory

• When the relationship type of a binary
  relationship is 1M with level of participation
  of both sides mandatory, two tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• The table corresponding to the M-side, will have
  among its attributes, the foreign key (not null)
  corresponding to the 1-side of the entity.

1
M
EMPLOYEE
PRODUCT
checks
EMPLOYEE (EMP_ID, EMP_DEPT, ) PRODUCT (PROD_ID,
PROD_NAME, PROD_FIBRE, EMP_ID... )
11
Case 5. 1M relationship,1-entity mandatory,
M-entity optional

• When the relationship type of a binary
  relationship is 1M with the level of
  participation of the entity on the M-side
  optional, two tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• The primary key from the entity on the 1-side
  must be added as a foreign key (not null) in the
  corresponding table of the entity on the M-side.

1
M
MACHINE
PARTS
contains
MACHINE (MACH_ID, MACH_NAME, MACH_DEPT, ...) PART
(PART_ID, PART_NAME, PART_CATEGORY, , MACH_ID)
12
Case 6. 1M relationship,1-entity optional,
M-entity mandatory

• When the relationship type of a binary
  relationship is 1M with the level of
  participation of the entity on the 1-side
  optional, two tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• The primary key from the entity on the 1-side
  must be added as a foreign key (null allowed) in
  the corresponding table of the entity on the
  M-side.

1
M
BAND
MUSICIAN
accepts
BAND (BAND_ID, BAND_NAME, MUSIC_TYPE...) MUSICIAN
(MUSICIAN_ID, MUSICIAN_INSTRUMENT, BAND_ID)
13
Case 7. 1M relationship, both entities optional

• When the relationship type of a binary
  relationship is 1M with the level of
  participation of both entities optional, two
  tables are required.
• There must be one table for each entity, and each
  entity must have a corresponding primary key.
• The primary key from the entity on the 1-side
  must be added as a foreign key (null allowed) in
  the corresponding table of the entity on the
  M-side.

M
1
has
PHYSIOTHERAPIST
CLIENTS
PHYSIOTHERAPIST (PT_ID, PT_LNAME, ...) CLIENT
(CLIENT_ID, CLIENT_LNAME, CLIENT_OHIP, PT_ID)
14
Case 8. MN Relationships

• When the relationship type of a binary
  relationship is MN three tables are required
  one for each entity, with the entity key from
  each entity serving as the primary key for the
  corresponding table, and one for the
  relationship.
• The table corresponding to the relationship
  (intersection table) will have among its
  attributes the foreign keys (not null) from each
  entity. The combination of foreign keys may be
  the composite primary key for the relationship
  table.

PATIENT
MEDICATION
prescribed
M
N
PATIENT (PATIENT_ID, PATIENT_LNAME,
PATIENT_PHYSICIAN,...) DRUG (DRUG_ID, DRUG_NAME,
DRUG_MANUFACTURER, ...) PRESCRIPTION (PATIENT_ID,
DRUG_ID, DOSAGE, DATE)
NOTE The relationship may have its own
attributes.
15
Example of decomposing entitieswith a binary MN
relationship

• StudentsClasses have an MN relationship,
  therefore, decompose to three tables.

Bridge table
16
Case 9. Decomposing Weak Entities

• When the relationship type of a binary
  relationship is 1M between an entity and its
  weak entity, two tables are required one for
  each entity, with the entity key from each entity
  serving as the primary key for the corresponding
  table.
• Additionally, the entity that has a dependency on
  the existence of another entity has a primary key
  that is partially or totally derived from the
  parent entity of the relationship.
• Weak entities must be deleted when the strong
  entity is deleted.

HOSPITAL (HOSP_ID, HOSP_NAME, HOSP_ADDRESS,
...) UNIT (HOSP_ID, UNIT_NAME, HEAD_NURSE, ...)
17
Considerations in a Generalized Hierarchy
Entity CLIENT contains ClientNumber ClientName Add
ress AmountDue SocialInsuranceNumber TaxIdentifica
tionNumber ContactPerson Phone
Problem Too many NULL values Solution
Separate into CLIENT entity plus several subtypes
18
Case 10. Decomposing aGeneralization Hierarchy

• To transform a subtype relationship, create a
  table for the parent entity and each of the child
  entities or subtypes
• Move the associated attributes from the parent
  entity into the child table to which they
  correspond
• From the parent entity take the entity key and
  add it as the primary key to the corresponding
  table for each child entity
• In the event a table corresponding to a child
  entity already has a primary key then simply add
  the entity key from the parent entity as an
  attribute of the table corresponding to the child
  entity

CLIENT (CLIENT_ID, AMOUNT_DUE, ) INDIVIDUAL_CLIEN
T (CLIENT_ID, SIN, ) CORPORATE_CLIENT(CLIENT_ID,
GST, )
19
Transforming Recursive Relationships

• 11 - create a foreign key field (duplicate
  values not allowed) that contains the domain of
  primary key

• 1M - create a foreign key field (duplicate
  values allowed) that contains the domain of
  primary key

20
Transforming MN Recursive Relationships

• MN - create a second relation that contains two
  foreign keys one for each side of the
  relationship

21
Decomposing Ternary relationships

• When a relationship is three-way (ternary) four
  preliminary tables are required one for each
  entity, with the entity key from each entity
  serving as the primary key for the corresponding
  table, and one for the relationship.
• The table corresponding to the relationship will
  have among its attributes the entity keys from
  each entity
• Similarly, when a relationship is N-way, N1
  preliminary tables are required.

22
Library Database Example
AUTHOR
BOOK
N
M
M
1
23
University Example
M
N
takes
COURSE
STUDENT
M
M
advises
taught by
N
FACULTY
1
24
Characteristics of Fields

• Each field within a table must have a unique name
  (avoid spaces and special characters).
• Data within a field must be of the same data
  type. The following are common data types
• character (text or string)
• memo (large character field)
• integer (whole numbers for calculations)
• number (values with decimals for calculations)
• currency (formatted number)
• logical or Boolean (true/false 0,-1 yes/no)
• date/ time (use computers internal
  calendar/clock)
• graphic (picture)

25
Guidelines for Ideal Table Design

• Each table should represents a single theme or
  subject or entity or transaction
• Tables should include primary keys that uniquely
  identify each record of each table
• Avoid the use of smart keys that attempt to embed
  meaning into primary keys (keys should be
  meaningless)
• A primary key should be a unique, random or
  sequential collection of alphabetic, numeric or
  alphanumeric characters
• The domain of primary keys should be large enough
  to accommodate the identification of unique rows
  for the entire potential universe of records
• Use the suffix ID in constructing primary keys to
  ensure they are readily identifiable
• Tables should not contain any of the following
  multipart fields, multivalued fields, calculated
  or derived fields or unnecessary duplicate fields
• There should be a minimum amount of redundant data

26
Common Errors in Database Design

• Flat file database
• Too much data
• Compound fields
• Missing keys
• Bad keys
• Missing relationships
• Unnecessary relationships
• Incorrect relationships

• Duplicate field names
• Cryptic field or table names
• Referential integrity
• Database Security
• Missing or incorrect business rules
• Missing or incorrect constraints

John Paul Ashenfelter, Common Database
Mistakes, May 26, 1999, lthttp//webreview.com/wr/
pub/1999/03/26/feature/index3.htmlgt (Oct 10,
1999).
27
The Well-Structured Database

• E-R modeling is top-down method of designing
• Transforming an E-R model does not guarantee the
  best design (e.g., E-R model could be way off)
• Best to transform E-R model and then check the
  design according to the Cases of normalization
• Normalization is bottom-up method of designing a
  database
• Use both approaches to develop a well-structured
  database