Management Information System ( ISMG)
Submit a hard copy in class and an eCopy to Canvas. Graded on 50 point scale then scaled down to out of 10 points.
Problem 1 (14 Points) Answer Yes or No to the following Functional Dependency questions. Base your answers on the data shown in Table X below. There are no further rows in this table. Provide reasoning VERY briefly.
X:
A | B | C | D | E |
a1 | b1 | c1 | d1 | 2013 |
a1 | b1 | c2 | 2013 | |
a2 | b2 | c1 | d1 | 2014 |
a3 | b2 | c2 | 2014 | |
a3 | b3 | c1 | d1 | 2014 |
a5 | b3 | c2 | 2014 |
1). Does A→B?
2). Does B→A?
3) Does B→E?
Hint: consider null value as a regular value.
4).Does E→B?
5). Does C→D?
6). Does E→D
7). Does A+C→D?
Problem 2 (5 Points): Table “Complaints” records complaints related information. Given the functional dependencies identified below, identify the primary key. Do not assume any further functional dependencies.
Complaints: (Complaint#, ComplaintReason, CustID, CustName, CustAddress, ReviewDept, AffiliateSite)
All Functional Dependencies:
Complaint# ComplaintReason
CustID CustName, CustAddress
ComplaintReason ReviewDept
Problem 3 (5 Points): Normalize the following table into first normal form. The table uses one row to record information about each student. A student may take one or more electives. This table is not in 1NF. Normalize this table so it is in 1NF. In your answer, list all columns of the new table, the primary key, and all data, in a table format.
SID | LName | FName | Electives |
100345 | Ford | George | Geometry, Dance |
200898 | Gibbs | Mary | Orchestra |
300987 | Jordan | Jeff | Orchestra, Creative Writing, Dance |
Problem 4 (20 Points): Determine if each of the following tables satisfies 2NF. If the table is already in 2NF, say so. If the table violates 2NF, say so and produce the normalized tables. Briefly describe the normalization process. Please see solutions document to the exercises. Be sure to identify the primary key for each table you write. The primary key and additional functional dependencies are already identified.
A: Fundraising: (Event#, SubEvent, Date, OrganizerName, Beneficiary)
Additional FDs: Event# Date, OrganizerName
B: FileDownload: (SessionId, DownLoadId, FileName, SessionStDateTime, SessionEndDateTime)
Additional FDs: SessionId SessionStDateTime, SessionEndDateTime
C. Sale: (Date, Customer#, Product#, Vendor#, Vendor-City)
Additional FD: Vendor#Vendor-City
D. BookInfo: (BookTitle, DateofPrint, #copiesPrinted, #copiesSold, Price, MajorSponsorName)
Additional FD: None
E. Tutoring: (TutorId, StudentID, TutorFName, TutorLName, Sfname, SLName, TutorAffiliation, TaffiPhone, #ofTimes, TotalFee)
Additional FD: TutorId#TutorFName, TutorLName, TutorAffiliation
StudentIdSFName, SLName
TutorAffiliationTaffiphone
Problem 5 (6 Points): Normalize the following table into 2NF and display the final tables with data in them. Take the primary key and functional dependencies as given. Do not assume further funtional dependencies.
TravelItinerary: (ReservationId, TravelerID, LegNum, FlightNo, FlightDate, ReservationDate, ReservationTotal)
Additional FDs: ReservationIdRdate, Total
Reservation
Id |
Traveler
Id |
Leg
Num |
FlightNo | FlightDate | Reservation
Date |
Reservation
Total |
100230 | 10 | 1 | UA3551 | 3/5/2002 | 1/20/2015 | $320 |
100230 | 10 | 2 | UA723 | 3/8/2016 | 1/20/2015 | $320 |
100231 | 11 | 1 | UA1754 | 3/6/2015 | 2/5/2015 | $460 |
100231 | 11 | 2 | UA3382 | 3/6/2015 | 2/5/2015 | $460 |
100231 | 11 | 3 | UA589 | 3/9/2015 | 2/5/2015 | $460 |
100231 | 11 | 4 | UA1655 | 3/9/2015 | 2/5/2015 | $460 |
100232 | 12 | 1 | UA1764 | 3/7/2015 | 2/10/2015 | $500 |
100232 | 12 | 2 | UA719 | 3/7/2015 | 2/10/2015 | $500 |
100232 | 12 | 3 | UA589 | 3/10/2015 | 2/10/2015 | $500 |
100232 | 12 | 4 | UA1085 | 3/10/2015 | 2/10/2015 | $500 |
1). Normalize the table by listing the 2NF tables (without data).
2). Display the final tables with data in them.
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp Order Paper Now