05 Feb Convert the ERD to tables and

MIS 4300: Assignment 1

1. Upload the assignment to eLearning before the deadline. No email submissions allowed.

1. The solution for the assignment should be in a word document.

1. Both the Word and Visio files need to be named “ITS 4300 Assignment 1 – FirstName LastName”.

1. Mention your name and netid in the Word document.

1. Please make sure the primary keys, foreign keys are mentioned clearly. Any ambiguity could lead to you losing points.

Question 1: (40 points)

The following is the business environment of Enterprise Car Rental. A car is manufactured by one manufacturer only. A manufacturer manufactured at least one of Enterprise’s cars. A car could have had many maintenance events (but a brand-new car may not have had any, yet.) A car may not have been rented to any customers (again, the case of a brand-new car) or to many customers. A customer may have rented many cars from Enterprise, and to be in Enterprise’s business environment must have rented at least one. Please draw the ERD for the above business. Include any and all attributes you think are required such as Customer Details, etc.

Convert the ERD to tables and write CREATE table statements including all constraints.

Note: If you are making any assumptions, do mention them explicitly.

Grading Criteria (partial marking will be there):

15 points for all correct create table statements

13 points for all correct relationships

7 points to all correct primary keys

5 points if it has all entities

Question 2 : (60 points)

The UT system is trying to create and convert their student records into digital form. The idea behind this database is to list all students in UT system colleges/universities with their Name, major, age during enrollment, address of residence and the corresponding school and university details.

There are no students who attend 2 colleges with in UT system at the same time, but there are many students who did their under-grad and grad with in UT systems. However, the variables Start_date and End_date is to mark when they went to which school. Branch_ID is not a unique value, same Branch_Id may represent another Branch in another University and a student can belong to only one branch in a college at a time.

To help understand UT systems data, an excel worksheet (ut_students_assignment_1.xls) of current data fields they keep track of are provided. Look at the given MS Excel worksheet to see what kind of data they collect and record for each student enrollment.

Create tables for this database in 3NF form. Please note,

a) Look at the data in the excel sheet provided and determine the functional dependencies in the data. (20 Points)

b) Please provide detailed explanation about how you would proceed and split the data into multiple tables so that resulting tables do not contain redundancy so that it is in 3NF form. List each resulting table with its primary key(s), foreign key(s) properly identified. Remember to use the relational schema representation we used in class to show the list of tables, foreign keys.(40)

Sheet1
Stud_SSN Branch_ID Branch_Name Branch_school Stud_Name Stud_Age Stud_Street Stud_Location Stud_Zip School_City School_State School_Zip School_Code Start_Date End_Date
2146589647 20 ITM JSOM,UTD Joseph Marks 20 Jupiter Rd Allen,TX 75002 Richardson TX 75080 4243 1/1/17 12/1/17
2456987412 21 BA JSOM,UTD Mary Barnhill 21 Campbell Rd Frisco,TX 75070 Richardson TX 75080 4243 1/1/17 12/1/17
4579625879 22 CSC ERIK,UTD Elizabeth Smith 24 Coit Rd Dallas,TX 75253 Richardson TX 75080 4243 1/1/17 12/1/17
4123658749 21 MECH ERIK,UTD Martin Jones 19 Pilot Dr Plano,TX 75025 Richardson TX 75080 4243 1/1/17 12/1/17
4578562145 38 NEURO SOBBS,UTD Albert Lau 22 Spring Valley Rd Richardson,TX 75080 Richardson TX 75080 4243 1/1/17 12/1/17
2146589635 20 ITM JSOM,UTD John Lewis 21 Pilot Dr Plano,TX 75025 Richardson TX 75080 4243 1/1/17 12/1/17
2146589635 38 MIS McComb, UTA John Lewis 21 Pilot Dr Plano,TX