Answer yes or no to the following functional dependency


Problem 1- 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?

4). Does E→B?

5). Does C→D?

6). Does E→D

7). Does A+C→D?

Problem 2: 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: 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: 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

                      StudentId→SFName, SLName

                      TutorAffiliation→Taffiphone

Problem 5: 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: ReservationId→Rdate, 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.

Solution Preview :

Prepared by a verified Expert
Database Management System: Answer yes or no to the following functional dependency
Reference No:- TGS01119441

Now Priced at $35 (50% Discount)

Recommended (99%)

Rated (4.3/5)