210 likes | 314 Views
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.
E N D
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 emp_no PK,FK EMPLOYEE emp_no NUM 7 Column name Key type Nulls/ Unique FK table FK column Datatype Max length Validate position NN CHAR 25 salary NN NUM 11,2 Comm NUM 4,2 Columns Keys Reference < 1 < 10000
Steps for creating TIC1: 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 • 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.
performed by EMPLOYEE_ ACTIVITY * per_diem_rate for MAPPING: Example requires performs EMPLOYEE_NEW_FORCE ACTIVITY # emp_no * position o job_description * salary o commission # activity_id * description o start_date o end_date * internal
EMPLOYEE_NEW_FORCE ACTIVITY Entities to tables EMPLOYEE_ACTIVITY
ACTIVITY Column Name id description start_date end_date internal Key Type Nulls/ Uniques FK Ref Table FK Column Datatype MAX Length Validation Attributes to Columns
ACTIVITY Column Name id description start_date end_date internal Key Type Nulls/ Uniques FK Ref Table FK Column Datatype MAX Length Validation PK UID to PK and * to NOT NULL NN NN NN
ACTIVITY Column Name id description start_date end_date internal Key Type Nulls/ Uniques FK Ref Table FK Column Datatype MAX Length Validation PK If Entity on Many side, then add FK Column NN NN NN We do NOT have here a FK Column !
ACTIVITY Column Name id description start_date end_date internal Key Type Nulls/ Uniques FK Ref Table FK Column Datatype MAX Length Validation PK Validation (Check) on Columns with Limits NN NN NN {Y,N} >= sysdate
for EMPLOYEE_ ACTIVITY for LINK - INTERSECTION ENTITIES
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 PK and NN
EMPLOYEE_ACTIVITY 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 Keys
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