2. (11 marks) Consider the relations Students, Faculty, Courses, Rooms, Enrolled

Question

2. (11 marks) Consider the relations Students, Faculty, Courses, Rooms, Enrolled, Teaches, and MeetsIn defined below. .Students(srid: string, name: string, login: string. age: integer. gpa: real) . Faculty(fid: string. fname: string, sal: real) . Courses(cid: string, cname: string, credits: integer) Rooms(rno: integer, address: string, capacity: integer) . Enrolled(sid: string, cid: string, grade: string) Teaches(fid: string, cid: string) . Meetsln(cid: string, rno: integer, time: string) a) (5 marks: 4 x 0.5+ 1 x 3) Identify a primary key for each relation. For each key, briefly b) (4 marks) Given your choice of primary keys in (a), define four referential integrity con- state the assumptions or conditions under which each key would be valid. straints. State the appropriate primary to foreign key references, and describe in one sen- tence why your referential integrity constraint is necessary. ) (2 marks) Give an example of a domain constraint and a tuple constraint over any of the above relations. Again, provide a one sentence justification for the constraint. gradebook db A Chiaki-Nanami-d... jpg

Explanation / Answer

(a) Following are the Primary key for each relation:

Students:- sid

Assumptions and conditons :-

1.sid must never be null

2.sid must uniquely identify each record in table i.e. there sholudn`t be repetition of values in sid attribute

Faculty: fid

Assumptions and conditons :-

1.fid must never be null

2.fid must uniquely identify each record in table i.e. there sholudn`t be repetition of values in fid attribute

Course: cid

Assumptions and conditons :-

1.cid must never be null

2.cid must uniquely identify each record in table i.e. there sholudn`t be repetition of values in cid attribute

Rooms:rno

Assumptions and conditons :-

1.rno attribute will contain only interger values

2.rno must never be null

3.rno must uniquely identify each record in table i.e. there sholudn`t be repetition of values in rno attribute

AND there will be no primary key in Enrolled, Teaches, MeetsIn relation as all the attributes in these relations have the attributes which are primary key of another relation and more than one tuple in the relation can have same attribute values in these relations which violates the conditon of the primary key to be unique So,There is no primary key in above mentioned three relations i.e Enrolled, Teaches, MeetsIn.

(b)

Referential Integrity Constraint :- Referencing attribute must be the subset of reffered attribute.

1.rno in MeetsIn relation as students and faculty meets in a room so room number(rno) must be from available rooms from relation(table) rooms

similarly you can justify Referential Integrity Constraint for

2.sid in Enrolled table

3.cid in teaches table

4.fid in teaches table

(c)

Domain constraint

Rule:1 Value must be an atomic value from t he set of defined values

2 Domain constraint specified using data types

So, Attribute rno in relation is having Domain constraint as it must be having values of integer type i.e. can not hold annother character than numbers

2 tuple constraint

in teaches relation there are 2 attribute fid and cid So,Every faculty teaches a unique course so it is the example of tuple constraint.

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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