Database Question Students can lease University flats and some of the details of

Question

Database Question

Students can lease University flats and some of the details of the leases held by students for places in University flats are shown in the table below. A place number uniquely identifies each single room in all flats and is used when leasing a room to student. Make suitable assumptions.

a. Identify the functional dependencies that exist between the columns
b. Identify primary key and alternate key if exists
c. Describe why the table is not in 3NF
d. Provide examples of update anomalies (insert, delete, update) and explain them.

placeNo fName Name |startDate finishDate flatNo address 78 aceNofName Nae startDate leaseNobannerld 10003 B017706 10259B017706 88 10364 B01339989 10566 B012124 102 11067 B034511 88 11169 B01339978 ane watt 01/09/201006/30/2011 F5614 High Street, Paisley jane tomjones01/09/201106/30/2012 F78 karen black 01/09/2011 06/30/2012 F79 steven smith 01/09/201206/30/2013F78 tom B017706 0109/2011 0630/2012 11 Story Rd, Paisley 11 Story Rd, Paisley 120 Lady Lane, Paisley 11 Story Rd, Paisle 34 High Street, Paisle watt 01/09/201106/30/2012 F78 karen black 01/092011 602012 ones 0 01/09/2012 06/30/2013 F56

Explanation / Answer

a) Funcational dependencies
leaseNo - student name (fname and lname)
leaseno - startdate
leaseno - finishdate
leaseno - flatno, placeno, address
fname - leanseno (identify number of leases of student and there by lease dates)

b) primary key is leaseno since it will be unique and not null and is indepent.

c) It will not be a 3NF because it not using any referencial integrity, example,

if student name changes in college records, this table would not be updated with

current name. Simple terms, no relationships to student details (name and id)

are provided, table only provides old/static information from date of entry.

d) If all action (update, insert, delete) are based on primary key, then no

anomalies would be there, however, if they are based on other columns then

anomalies will surly exits.

1. Insert and Update statement only checks for leaseno (which is primary key)

while performing action, it would not take into account if startdate and finish

date are overlapping for same student in another record example: by error one

can enter jane in lease no 10003 startdate as 01/09/201 and finish date as

06/30/2015 at the same time and another can enter same student for different

dates.

2. Inser and Update statement only checks for leaseno and not current occupancy,

by error placeno 78 could be assigned to two students on an overlapping date.

3. Fname and Lname could be same for few students, resulting in not being to

update/delete data for correct student it is intended to - because it is not in

3NF.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.