Problem 1. Consider the following relational database: student ( student_number

Question

Problem 1. Consider the following relational database:

student ( student_number , name )

course ( course_number , year , semester , classroom )

take ( ? ? ? )

(a) (10 points) Identify an appropriate primary key for each of the student and course relations. Assume that (i) each student is assigned a unique number, (ii) for each combination of year and semester (e.g., 2018 Fall), each course is assigned a unique course number, and (iii) a course with a certain course number (e.g., CSI 508) may have been offered multiple times (e.g., 2017 Spring and 2017 Fall). Explain why the primary key that you have chosen for the course relation is a candidate key.

(b) (10 points) Given your choice of primary keys for the student and course relations, identify appropriate attributes for the take relation. This take relation must store data regarding the courses that each student has taken. Assume that a student may have taken a course (e.g., CSI 508) multiple times (e.g., 2017 Spring and 2017 Fall). Also, identify an appropriate primary key for the take relation.

(c) (10 points) Given your choice of primary keys, identify all of the foreign keys. For each foreign key, specify the referencing and referenced relations.

(d) (10 points) Describe a situation where an attempt to update the take relation violates a foreign key constraint. Explain why your answer is correct.

Explanation / Answer

Answer is as follows :

a)

According to given scenario Student_Number is Primary key for student table becasue other attribute name can not be defined as primary key because name of two students can be same that can't show the uniqueness. So only primary key for student table is Student_number1.

And for other table Course_Number is primary key for Course table that shows the uniqueness of table. Year and Semester can be same for multiple courses.

And also Course_Number is candidate key of course table. Candidate key is set of one or more attributes that show the uniqueness of given table. So here is only one attribute that show uniqueness of course table. So Course_Number is candidate key of relation Course.

b)

According to given information the attribute of Take relation are (Student_Number, Course_Number, Year, Semester)

Where Student_Number and Course_Number are both combinely treated as Primary key. You can say that (Student_Number, Course_Number) is candidate key of relation take.

c)

There are two foreign keys in the relation taken i.e. Student_Number references to relation student and Course_Number references to course relation.

d)

When you are going to update the table take, the record would be change only in take table and it's referenced table can't be updated. So there is no concept of Foreign key works. Becasue if there is foreign key elements, so main relation must contian the attribute of defined relation.

For example let you update the student_number (14853) of take relation to (14526) and updated student number is not present in relation student. Than it show wrong results.

So it is violation of Foreign Key Constraint.

if there is any query please ask in comments.....

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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