1) Consider the following relation (i.e., table) created for a class list: (Stud
ID: 3710999 • Letter: 1
Question
1) Consider the following relation (i.e., table) created for a class list:
(Student#, class#, student-lastname, student-firstname, student-major, section#,
meeting-time, meeting-room)
a) Suppose we wish to add a new class time and room (i.e., a new section). Is this
possible? Why or why not? If not, what is necessary to make it possible?
b) Suppose all students drop a particular class from their schedules (perhaps they have
heard that I will be teaching the class). What effect will this have on the data stored
in the class list relation?
c) Is the class list relation in 1st, 2nd, and 3rd normal form?
2) Consider the following two relations:
(Department#, dept-name, location, professor#)
(Professor#, prof-name, address, phone)
Professor# is a foreign key in the department relation and links the two tables together.
a)What is wrong with these relations (i.e., what problems will be caused) and how can the
tables be fixed?
Explanation / Answer
Ans 1a) It's not possible to add new class time and room only. This is because of the reason that student No., class & section are mandatory columns. so, In order to add this, we have to make sure will will insert these fields also.
1b) In such a case, Roll No. i.e. Student# will not be in a sequential order. They will be intermittent gaps in between the records. Also, All the student Information will get lost while dropping a single class. In order to save this, we can normalize the tables so that student info does not get lost.
1c) The list relation is in 1st normal form since there are partial dependencies in this table.
2a) Since Professor# is a foreign key in the department relation which is the primary key in the Professor table, there will be problems but these can be rectified easily.
i) While inserting the row in the Department table having the value for professor column in Department table, we have to make sure that corresponding entry will be there in Professor table.
ii) While deleting the row from the professor table, we have to make sure that corresponsing cell in the Department table must be handled. eg. we must enter NULL value in that cell or corresponsding row must be deleted.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.