2014– 2015 2nd Semester

ITIS314/ITBIS395 – Lab

University of Bahrain College of Information Technology Department of Information System

Tutorial 2 –Logical Design and Physical Design Scenario: Our company sales software at an affordable price. Our internal system, which we need to develop, will consist of a customer database, software CD database; which will consist of CD build numbers, pricing, licenses allowed, and other important sources of information depicted in the diagram below. We will also have an employee, order, and order detail table. A customer will call and order software. The customer will automatically be generated a unique customer ID by the system. The employee will enter the first name, last name, address, city, state and zip code for the customer so that his\her order can be shipped. Next, the employee will enter the order information which also has an auto-generated special ID. The date, total, and employee ID are items store in the order table. Once the order has been placed, the order detail will be available and can serve as a receipt for the customer. This receipt will be included in the package sent to the customer. The order detail will include the order id, number of CD’s ordered, and the price per CD. Customer Cust_ID Cust_FName Cust_LName Cust_Address Cust_City Cust_State Cust_Zip Software_CD CD_ID CD_CDBuildNumber CD_SoftwareVersion CD_PricePerCD CD_NumOfLicenses Employee Empl_ID Empl_FName Empl_LName Empl_Address Empl_City Empl_State Empl_Zip Order Order_ID Order_Date Order_Total OrderDetail Order_ID PD_NumOfCDs CD_PricePerCD

Prepared By: Amna Khalifa & Hajar Khalifa

2014– 2015 2nd Semester

ITIS314/ITBIS395 – Lab

Transform the ERD into a set of 3NF? Transform the Logical Design to Physical Design Logical Design

Prepared By: Amna Khalifa & Hajar Khalifa

2014– 2015 2nd Semester

ITIS314/ITBIS395 – Lab

Prepared By: Amna Khalifa & Hajar Khalifa

2014– 2015 2nd Semester

ITIS314/ITBIS395 – Lab

Physical Design

Customer (Cust_ID, Cust_LName, Cust_FName,Cust_Address, Cust_City, Cust_State, Cust_Zip)

Software_CD (CD_ID, CD_BuildNumber, CD_SoftwareVersion, CD_PricePerCD, CD_NumOfLicenses)

Employee (Empl_ID, Empl_Fname, Empl_LName, Empl_Address, Empl_City, Empl_State, Empl_Zip)

Order (Order_ID, Order_Date, Cust_ID, Order_Total, Empl_ID)

OrderDetail (Order_ID, CD_ID, OD_NumOfCDs, CD_PricePerCD)

Prepared By: Amna Khalifa & Hajar Khalifa

2014– 2015 2nd Semester

ITIS314/ITBIS395 – Lab

Tables & Relationships

Prepared By: Amna Khalifa & Hajar Khalifa