Question

Consider the following relations for course-enrollment database in a university:

STUDENT(S-ID,S-Name, Department, Birth-date)

COURSE(C-ID, C-Name, Department)

ENROLL(S-ID, C-ID, Grade)

TEXTBOOK(B-ISBN, B-Title, Publisher, Author)

BOOK-ADOPTION(C-ID, B-ISBN)

(a) Draw the database relational schema and show the primary keys and referential integrity constraints on the schema.

(b) How many superkeys does the relation TEXTBOOK have? List ALL of them.

(c) Now assume each COURSE has distinct C-Name.

(i) If C-ID is a primary key, what are the candidate keys and the unique keys of the relation COURSE?

(ii) How many superkeys does the relation COURSE have? List ALL of them.

Answer 1

(a) relation schema:

(b)

TEXTBOOK(B-ISBN, B-Title, Publisher, Author)

below are super keys as these keys can uniquely identify each row

{B-ISBN}
{B-Title}
{B-ISBN, B-Title}
{B-Title, Author}
{B-Title, Publisher}
{B-ISBN,Author}
{B-ISBN,Publisher}
{B-ISBN, B-Title, Author}
{B-ISBN, B-Title, Publisher}
{B-ISBN, B-Title, Publisher, Author}

(c) if C-ID is the primary key, then {C-ID}, {C-NAME} are candidate key without having any redundant attributes.

Unique key would be {C-ID}

(d) -

COURSE(C-ID, C-Name, Department), below are super keys

{C-ID}
{C-Name}
{C-ID, C-Name}
{C-ID, Department}
{C-Name, Department}
{C-ID, C-Name, Department}