Question

1.Consider the following (normalized) relational model (primary keys

are underlined, foreign keys are in italics). EMPLOYEE(SSN, ENAME, EADDRESS, SEX,

DATE_OF_BIRTH, SUPERVISOR, DNR)
S U P E R V I S O R : foreign key refers to SSN in EMPLOYEE,

NULL value allowed

D N R : foreign key refers to DNR in DEPARTMENT, NULL value not allowed

DEPARTMENT(DNR, DNAME, DLOCATION, MGNR) MGNR: foreign key refers to SSN in EMPLOYEE, NULL

value not allowed
PROJECT(PNR, PNAME, PDURATION, DNR)

DNR: foreign key refers to DNR in DEPARTMENT, NULL value not allowed

WORKS_ON(SSN, PNR, HOURS)
SSN: foreign key refers to SSN in EMPLOYEE, NULL

value not allowed

PNR: foreign key refers to PNR in PROJECT, NULL value not allowed

Which statement is correct?
a. According to the model, a supervisor cannot supervise more than

one employee.

b. According to the model, an employee can manage multiple departments.

c. According to the model, an employee can work in multiple departments.

d. According to the model, an employee should always work on projects assigned to his/her department.

2.

Consider a data model for the Olympics storing information about countries and athletes. There is a 1:N relationship type between country and athlete and an athlete always has to belong to exactly one country. A relational data model containing only one table leads to:

a. Unnecessary replication of data about athletes.
b. Unnecessary replication of data about countries.
c. Unnecessary replication of data about athletes and countries.

d. No unnecessary replication of data.

3.

Consider the following (normalized) relational model (primary keys underlined; foreign keys in italics):

EMPLOYEE (SSN, ENAME, EADDRESS, SEX, DATE_OF_BIRTH, SUPERVISOR, DNR)

SUPERVISOR: foreign key, refers to SSN in EMPLOYEE, NULL value allowed

DNR: foreign key, refers to DNR in DEPARTMENT, NULL value not allowed

DEPARTMENT (DNR, DNAME, DLOCATION, MGNR) MGNR: foreign key, refers to SSN in EMPLOYEE, NULL

value not allowed
PROJECT (PNR, PNAME, PDURATION, DNR)

DNR: foreign key, refers to DNR in DEPARTMENT, NULL value not allowed

WORKS_ON (SSN, PNR, HOURS)
SSN: foreign key, refers to SSN in EMPLOYEE, NULL

value not allowed

PNR: foreign key, refers to PNR in PROJECT, NULL value not allowed

Which statement is not correct?

a. A department always has exactly one manager.
b. Every employee must always be supervised by exactly one other

employee.
c. Every project is always assigned to exactly one department.

d. According to the model, an employee can work in another department than he/she manages.

4.

Consider the following relational model (primary keys are underlined, foreign keys in italics):

STUDENT (student number, student name, street name, street number, zip code, city)

ENROLLED (student number, course number) COURSE (course number, course name) PROFESSOR (professor number, professor name) TEACHES (course number, professor number) Which statement is correct?

a. The model does not allow a course to be taught by multiple professors.

b. The model can be further normalized.
c. The model does not allow a professor to teach multiple courses.

d. The model does not allow a course to be followed by multiple students.

Answer 1

1) option b is correct.

Because mngr is the foreign key to the employee table. So that can repeat . Only thing is it shouldn't be null and should have a matching row in employee table.

2) Option b is correct. Since it is 1 to many relationship between country and athlete country details keep repeating.

Example for India there can be 5 athlets . So in these 5 rows athlets values are unique but country information will be repeating

3)option c is incorrect. Because supervisor is the foreign key to the employee table. there is no restriction that the supervisor should not be equal to his ssn number. So an employee can be the supervisor of himself.

4)There are no italics or underlines.

From my assumption that course number is the primary key of course and it is the foreign key to other tables. The ta

The table can be further normalized.