Data Modeling and Database Design

DATA MODELING AND
DATABASE DESIGN
Part 3
Objectives
• Translate Logical to Physical design
• Steps for converting (mapping) Resolved
ERD to set of Table Instance Charts
• Table Instance Charts symbols.
• Some mapping examples
Database Design -
from Logical to Physical
• Map entities and attributes to tables and
columns respectively.
• Naming conventions should be consistent.
• Consider primary, unique and foreign keys
and check constraints as well
• Additional requirements
– Design the indexes
– Establish view definitions
– Plan the physical storage space
Steps to convert your Resolved
ERD to TIC set
• Map ENTITIES to TABLES
• Map ATTRIBUTES to COLUMNS
• Map * to NOT NULL constraints
• Map UIDs to PRIMARY KEY constraints
• Map (#) Secondary UIDs to NOT NULL and
UNIQUE Constraints
• Map RELATIONSHIPS to FKs
Table Instance Chart Symbols
• PK - Primary Key
• FK - Foreign Key
• FK1, FK1 - Composite Foreign Keys
• FK1, FK2 - Two foreign keys related to two
separate tables
• NN - Not Null
• U - Unique
• U1, U1 - Two columns unique in
combination with each other

• U1, U2 - Two unique (and indepenent) columns
Creating a TIC: Example
Create a Table Instance Chart (TIC) from
the Logical Schema (Resolved ERD)
EMPLOYEE_NEW_FORCE Table
Columns Column emp_no position salary Comm

name
Keys Key type PK,FK
Nulls/ NN NN
Unique
Reference FK table EMPLOYEE
FK emp_no
column
Datatype NUM CHAR NUM
Max 7 25 11,2
length NUM
Validate < 10000 4,2< 1
Steps for creating TIC
1: Map Entities to Tables
• Create a frame for each TIC
– Table name
– Column names
– Key types
– Not Null and Unique references
– Foreign key information
– Column datatype and maximum length
_ Validation (Check)
– Sample data
• Put the table name at the top of the chart.
Step 2: Map the Attributes to
Columns
• Map each attribute from the entity to a
column name in the table.
• Mandatory columns get a NN constraint
• Add sample data for each column (usually
three rows)
• Do not use SQL reserved words for
column names.
Step 3: Map the Unique
Identifiers to Primary Keys
• Map the UID of the entity as the
primary key in the table.
• Do NOT mark it as NN and U
• If there is an alternate UID, label it as
NN and U,but choose only one for a
PK.
• Add a FK column for each UID that is
through a relationship (over UID bar)
Step 4: Map Relationships to
Foreign Keys
• For One-to-Many relationships
– Take the PK at the One end and create a
column under same or different name in the
other table at the Many end.
– Mark it as a FK.
• For mandatory relationships, be sure to
label the NN reference.
Step 4: Map Relationships
to Foreign Keys --- continued
• For One-to-One relationships, label the
U reference.
– If the relationship is optional in both
directions, place the FK in the table at either
end of the relationship.
– If the relationship is mandatory, place the
unique FK in the table at the mandatory end.
• For mandatory relationships, be sure to
label the NN reference.
MAPPING: Example
performed EMPLOYEE_ for
by ACTIVITY
* per_diem_rate
performs requires
EMPLOYEE_NEW_FORCE ACTIVITY
# emp_no # activity_id
* position *
o job_description description
* salary o start_date
o commission o end_date
* internal
Entities to tables
EMPLOYEE_NEW_FORCE ACTIVITY
EMPLOYEE_ACTIVITY
ACTIVITY
Attributes to Columns
Column Name id description start_date end_date internal
Key
Type
Nulls/
Uniques
FK Ref
Table
FK Column
Datatype
MAX Length
Validation
UID to PK and * to NOT NULL
ACTIVITY
Key
Type PK
Nulls/
Uniques NN NN
FK Ref
Table
FK Column
Datatype
MAX Length
Validation
If Entity on Many side, then add FK Column
ACTIVITY
Key
Type PK
Nulls/
Uniques NN NN NN
FK Ref
Table
FK Column
Datatype
MAX Length
Validation
We do NOT have here a FK Column !

Validation (Check) on Columns with Limits
ACTIVITY
Key
Type PK
Nulls/
NN NN NN
Uniques
FK Ref
Table
FK Column
Datatype
MAX Length
Validation >= sysdate {Y,N}
LINK - INTERSECTION
ENTITIES
for EMPLOYEE_ for
ACTIVITY
PK and NN
EMPLOYEE_ACTIVITY
Column Name emp_no activity_id per_diem_rate
Key
Type PK PK
Nulls/
Uniques NN
FK Ref
Table
FK ref
Column
Datatype
MAX
Length
Foreign
EMPLOYEE_ACTIVITY
Keys
Column Name emp_no activity_id per_diem_rate
Key
Type PK, FK1 PK, FK2
Nulls/
Uniques NN
FK Ref . EMPLOYEE_ ACTIVITY

table NEW_FORCE
FK ref
Column emp_no activity_id
Datatype Number Number Number

MAX
Length 6 6 7,2
Foreign Key Mapping Rules
• Map the Relationship to a foreign key.
• One to One optional in both directions - take the PK from
either table and make it a FK in the other table.
• One to One mandatory in one direction, take the PK from
the optional end and make it a FK in the entity at the
mandatory end.
• One to Many - Take the PK at the one end and make it a
FK in the entity at the many end.
• Do not forget to label mandatory relations with NN

Data Modeling and Database Design

Uploaded by

Copyright:

Available Formats

Data Modeling and Database Design

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Modeling and Database Design

Uploaded by

Copyright:

Available Formats

DATA MODELING AND

combination with each other

Columns Column emp_no position salary Comm

We do NOT have here a FK Column !

FK Ref . EMPLOYEE_ ACTIVITY

Datatype Number Number Number

You might also like