Student(stdNo, firstName, lastName, eMail) Course(code, name, credits) WhenOffer
ID: 3728790 • Letter: S
Question
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): stdNostudent, examiner staffID and codecourse. For the following questions, do not use assumptions that are not supported by the description given above. Use the Forum on the course web site to discuss the problem setting if you need clarification.
(a) Identify all Candidate Keys for each relation.
(b) Choose the Primary Key for each relation.
(c) Identify all Foreign Keys for each relation. Use the following notation for each Foreign Key: FK TableName(Attributes) References TableName(Attributes).
(d) 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 Indicate which row(s) break the relational integrity rules, and why (there may be more than 1 reason per row). 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
USQ UConnect I UConnect Course: Advanced Relat- xDcsc340QS1 Assign1.pdfx Consider Tne Following x x COhttps://usqstudydeskusq.eduì u/ma/pluginfile.php/1563431/modr 51 Assign1.pdf 12 marks Consider the following rclational schema StedNo, listName, la-Na, eMail) Courselcode, name, crodits) WhenOWered couse, semester, year, examiner) StastalD, ame, birth-date, department) Transcript(studeut, course, semes, yr, grade) This is a sellema for database maintained by a amall eallete to key, traek of students, coarses, cte. Students have their own unique student ID (which may only contain umbers) and can be contacted via their own uniqe vollege emall acdes There ae no two couses having the same eode or ame. A course is offered at most once per semesterwhere 1 semester 3], but can be offered in several notes or in cliffrent years. A course offer i®ethmined by a speciie member of talf. The examiner can vary over different semesters, nd can examine more than one course in a given semester. A staff bolongs to oce and only one department at the univesity. A student enroled in a specific course offer obtals a grade for that coureA studet can take a same course amltiple times e.g., nil for a few time and finally pases i), but cannot take the coune twice in single mester of s given year. St'xlents' and staff's natnes are geterally Norunique. The collesge typically uses the database to create a list of course names, when they are offered, and the name of the exaine t aso emails repoets for cach sudent with their name, the ames voarses they have taken and in which semester, the name of their examiner, and the rale they obtained In the abve relatiois, we have the following correspondence betueen attributes (but not limited to): stdNostudent, examiner++ stufflD and codec aeourse. For the folowing questions, do not use assumptions that are not sup portod by the deseripticn given above. Use the Forumon the course web site to diseuss the problem setting if you need clarification (a) Identfy all Candidate Keys for each relation. b) Chocse the Primnry Key for each relation c)Identify all Forcign Keys for each relation. Use the following notation for each Foreign Key FK TableN(Attributes) Referenecs TabeName Attributes) d) Conse the instance for 201O 20 integrity rul, and why (theeExplanation / Answer
a) Candidate Keys
Student(stdNo, firstName, lastName, eMail) -----> stdNo,eMail
Course(code, name, credits) ------> code,name
WhenOffered(course, semester, year, examiner) ----> course,semester,year
Staff(staffID, name, birth-date, department) ------> staffID
Transcript(student, course, semes, year, grade) -----> student,course,semes,year
a) Primary Keys
Student(stdNo, firstName, lastName, eMail) -----> stdNo
Course(code, name, credits) ------> code
WhenOffered(course, semester, year, examiner) ----> (course,semester,year) CompositeKey
Staff(staffID, name, birth-date, department) ------> staffID
Transcript(student, course, semes, year, grade) -----> (student,course,semes,year) Composite Key
c
FK Student(stdNo) References Transcript(student)
FK Course(code) References Transcript(course)
FK WhenOffered(semester) References Transcript(semes)
FK WhenOffered(year) References Transcript(year)
d. row(s) which break the relational integrity rules
2 CSC3400 NULL 2011 A ----- semes cannot be NULL
4 NULL NULL 2011 C ------------ course cannot be NULL,semes cannot be NULL
Jeff CSC2406 1 2011 HD ---------- student id cannot be string should be number
NULL CSC8407 2 2011 NULL ------- student cannot be NULL
Do ask if there is any query. Please upvote if it is helpful.