Final Project Report

Video Rental System

Objective:

Business Rules:

  • A store can have one or more employees.
  • An employee may or may not manages one store.

 

  • A employee may or may not have one or more employee job history.

 

  • A employee is assigned an user id for login.
  • An user id associated with one and only one employee.

 

  • A employee may or may not creates one or more customers.
  • A customer can be created by one and only one employee.

 

  • A employee may or may not change one or more rental item types.
  • A rental item type can be changed by one and only one employee.

 

  • A rental item type may or may not have one or more rental items.
  • A rental item can be of one and only one rental item type.
  • A vendor may or may not placed one or more purchase orders.

 

  • A Purchase order can contains one or more Purchase order detail lines.
  • A Customer rental table resolves the many to many relationships between the Customer and Rental item table. 

  

  • A rental item may or may not be rented by one or more customers.
  • A customer may or may not rent one or more rental items.

 

  • And Customer rental history table also resolves the many to many relationships between the Customer and Rental item. 

 

  • A customer may or may not have one or more rental items.
  • A rental item may or may not have one or more customers.

        

ER Diagram:

                        

Data Dictionary:

Join now!

Normalization:

Before Normalization the “CUSTOMER” table contained the “RENTAL RETURN DATE” AND “ITEM RENT PAY”. Since the Customer can rent more than one item this caused data anomalies.  There were multiple entries for one customer.

Note: All the table attributes are not included for the ease of description. The required and important attributes are used to show the normalization.

The dependencies before normalization

First Normal Form:

CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)

ITEM_ID – ( ITEM_SHORT_DESC,ITEM_NAME)

CUS_ID+ITEM_ID  – (CUS_ADDRESS, CUS_PHONE, CUS_NAME, ITEM_RENT_PRICE, ITEM_NAME, RENTAL_DATE, RENTAL_DUE_DATE)

Second Normal form:

CUS_ID – (CUS_ADDRESS, CUS_PHONE, CUS_NAME)

...

This is a preview of the whole essay