Data Base Design Steps 1 2 Define Entity
Data Base Design Steps 1. 2. Define Entity Classes (Relations/Tables) and Keys. Define Relationships between Entity Classes. Normalization Steps • Eliminate all M: M relationships • Assure all fields are dependent only on the Primary Key • Remove all calculated fields 3. Define Fields in the Relation. 4. Using the Data Definition Language, create the data base.
Video Rental Example – Step 1 l Tracking the rental of videos in a store l Step 1. Define the Entity Classes + Primary Key l Customer ¡ ( ID, Name, Address, Phone #, # of Videos Rented) l Videos ¡ ( ID, Type, Days Rented, Rental Price, Distributor, Phone #)
Video Rental Example – Step 2 l Step 2 - Define Relationships between Entity Classes ¡ Entity Relationship Diagrams (ERD) Video Customer Required Many Optional One
Video Rental Example – Step 2 a. l Normalization ¡a. Eliminate all M: M relationships Customer Video Rental Customer ID Video ID Date Rented Date Returned Video Rental Composite Primary Key Video
Video Rental Example – Step 2 b. l Normalization ¡ b. Assure all fields are dependent on the Primary Key Customer ¡ ( ID, Name, Address, Phone #, # of Videos Rented) Videos ¡ ( ID, Type, Days Rented, Rental Price, Distributor, Phone # ) Distributor ¡ ( ID, Distributor Name, Phone #) Customer Video Rental Video Distributor
Video Rental Example – Step 2 c. l Normalization ¡c. Remove all calculated fields l Customer ¡ ( ID, Name, Address, Phone #, # of Videos Rented) Field can be calculated Customer Video Rental Video Distributor
Video Rental Example – Step 3 l Step 3 - Define Fields in the Relation ¡ Numeric ¡ Alphanumeric ¡ Currency ¡ Length - Required or Optional - Default Value - Pull Down List - Indexed
Video Rental Example – Step 4 l Step 4 - Using the Data Definition Language, create the data base.
- Slides: 8