1 / 21

Database Design Mapping from Logical to Physical: Steps and Symbols

Learn how to convert a Resolved ERD to Table Instance Charts, mapping entities to tables and attributes to columns. Understand key constraints, naming conventions, and additional requirements.

hgeraldine
Download Presentation

Database Design Mapping from Logical to Physical: Steps and Symbols

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATA MODELING AND DATABASE DESIGN Part 3

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

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

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

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

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

  7. 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 _ Validation (Check) • Sample data • Put the table name at the top of the chart.

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

  9. 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)

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

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

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

  13. EMPLOYEE_NEW_FORCE ACTIVITY Entities to tables EMPLOYEE_ACTIVITY

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

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

  16. 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 !

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

  18. for EMPLOYEE_ ACTIVITY for LINK - INTERSECTION ENTITIES

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

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

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

More Related