CSC3400 | DATABASE SYSTEMS ASSIGN1 CSC850 | DATABASE

Question 1.                                      8 marks

Consider the following:

D₁     =     fStudent1; Student2; Student3; Student4g

D₂     =     f20; 18; 34; 27g

D₃     =     fundergraduate; postgraduateg

• = f(x; y; z) j x 2 D₁; y 2 D₂; z 2 D₃; and y   30g

• Create a Relation Schema for S given above. Write out both the full and the abbreviated schemas as shown on Slide of the lecture notes in Module 3.

• Using a table as a physical representation, create a relation for S as defined above. The table should include ALL the possible tuples for S and has column headings that correspond to the attributes of the relation.

• What is the cardinality of S, the table you have constructed in Ques-tion 1(b)?

• What is the degree of S?

                                                                                                           

Question 2.                                                            12 marks

Consider the following relational schema:

Student(stdNo, firstName, lastName, eMail)

Course(code, name, credits)

WhenOffered(course, semester, year, examiner)

Staff(staffID, name, birth-date, department)

Transcript(student, course, semes, year, grade)

This is a schema for a database maintained by a small college to keep track of students, courses, etc. Students have their own unique student ID (which may only contain numbers) and can be contacted via their own unique college email address. There are no two courses having the same code or name. A course is offered at most once per semester (where 1 semester 3), but can be offered in several semesters or in different years. A course offer is examined by a specific member of staff.

The examiner can vary over different semesters, and can examine more than one course in a given semester. A staff belongs to one and only one department at the university. A student enrolled in a specific course offer obtains a grade for that course. A student can take a same course multiple times (e.g., fail for a few time and finally pass it), but cannot take the course twice in a single semester of a given year. Students’ and staff’s names are generally NOT unique.

The college typically uses the database to create a list of course names, when they are offered, and the name of the examiner. It also emails reports for each student with their name, the names of courses they have taken and in which semester, the name of their examiner, and the grade they obtained.

In the above relations, we have the following correspondence between attributes (but not limited to): stdNo$student, examiner$ staffID and code$course.

For the following questions, do not use assumptions that are not sup-ported by the description given above. Use the Forum on the course web site to discuss the problem setting if you need clarification.

• Identify all Candidate Keys for each relation.

• Choose the Primary Key for each relation.

• Identify all Foreign Keys for each relation. Use the following notation for each Foreign Key:

FK TableName(Attributes) References TableName(Attributes).

• Consider the following instance for Transcript:

student	course	semes	year	grade
1	CSC2401	1	2010	NULL
1	CSC2401	2	2010	B
2	CSC3400	NULL	2011	A
3	CSC3403	3	2011	F
4	NULL	NULL	2011	C
Jeff	CSC2406	1	2011	HD
NULL	CSC8407	2	2011	NULL

Note: Please do not use functional dependencies to answer Questions (a), (b) and (c). You need to identify keys based upon the description given above only.

Indicate which row(s) break the relational integrity rules, and why (there may be more than 1 reason per row).