1.Given two relation schema with referential integrity constraints as follows, S

Question

1.Given two relation schema with referential integrity constraints as follows,

Schema:
EMPLOYEE(SSN, NAME, AGE, SALARY, SUPERSSN, DNO)
DEPARTMENT(DNUMBER, DNAME, MGRSSN, MGRSTARTDATE)

Referential Integrity Constraints:
EMPLOYEE.SUPERSSN references to EMPLOYEE.SSN
EMPLOYEE.DNUMBER references to DEPARTMENT.DNUMBER
DEPARTMENT.MGRSSN references to EMPLOYEE.SSN

Please select the correct set of SQL statements to create these two database tables and enforce these referential integrity constraints correctly.
===========================================================================================================================
A:
1. Create table EMPLOYEE:
create table EMPLOYEE
(name varchar2(19) not null,
ssn char (9),
bdate date,
sex char(3),
salary number(8,2),
superssn char(9),
dno varchar(8),
constraint empPK primary key (ssn),
constraint empsuperFRK foreign key (superssn)
references employee (ssn) disable)

2. Create table DEPARTMENT:

create table DEPARTMENT
(dname varchar2(15) not null,
dnumber varchar(8),
mgrssn char(9),
mgrstartdate date,
constraint departPK primary key (dnumber),
constraint departUK unique (dname),
constraint departFRK foreign key (mgrssn)
references employee (ssn) disable)

3. Add constraint to EMPLOYEE table
alter table employee
add (constraint empdnoFRK foreign key (dno)
references department(dnumber))

4. Enable the constraints:
alter table EMPLOYEE enable constraint empsuperFRK;
alter table DEPARTMENT enable constraint departFRK;

--------------------------------------------------------------------------------------------------------

B:
1. Create table DEPARTMENT:
create table DEPARTMENT
(dname varchar2(15) not null,
dnumber varchar(8),
mgrssn char(9),
mgrstartdate date,
constraint departPK primary key (dnumber),
constraint departUK unique (dname))

2. Create table EMPLOYEE:

create table EMPLOYEE
(name varchar2(19) not null,
ssn char (9),
bdate date,
sex char(3),
salary number(8,2),
superssn char(9),
dno varchar(8),
constraint empPK primary key (ssn),
constraint empsuperFRK foreign key (superssn)
references employee (ssn),
constraint empdnoFRK foreign key (dno)
references department(dnumber) disable)
3. Add constraint to DEPARTMENT table
alter table DEPARTMENT
add (constraint departFRK foreign key (mgrssn)
references employee(ssn))
4. Enable the constraints:
alter table EMPLOYEE enable constraint empsuperFRK;
alter table EMPLOYEE enable constraint empdnoFRK;

-------------------------------------------------------------------------------------

C:
1. Create table DEPARTMENT:
create table DEPARTMENT
(dname varchar2(15) not null,
dnumber varchar(8),
mgrssn char(9),
mgrstartdate date,
constraint departPK primary key (dnumber),
constraint departUK unique (dname) disable)
2. Create table EMPLOYEE:
create table EMPLOYEE
(name varchar2(19) not null,
ssn char (9),
bdate date,
sex char(3),
salary number(8,2),
superssn char(9),
dno varchar(8),
constraint empPK primary key (ssn),
constraint empsuperFRK foreign key (superssn)
references employee (ssn));

3. Add constraint to DEPARTMENT table
alter table DEPARTMENT
add (constraint departFRK foreign key (mgrssn)
references employee(ssn))

4. Add constraint to EMPLOYEE table
alter table EMPLOYEE
add (constraint empdnoFRK foreign key (dno)
references department(dnumber))

----------------------------------------------------------------------------------------------

D:
1. Create table DEPARTMENT:
create table DEPARTMENT
(dname varchar2(15) not null,
dnumber varchar(8),
mgrssn char(9),
mgrstartdate date,
constraint departPK primary key (dnumber),
constraint departUK unique (dname),
constraint departFRK foreign key (mgrssn)
references employee (ssn) disable)

2. Create table EMPLOYEE:

create table EMPLOYEE
(name varchar2(19) not null,
ssn char (9),
bdate date,
sex char(3),
salary number(8,2),
superssn char(9),
dno varchar(8),
constraint empPK primary key (ssn),
constraint empsuperFRK foreign key (superssn)
references employee (ssn),
constraint empdnoFRK foreign key (dnodisable)

3. Add constraint to EMPLOYEE table
alter table employee
add (constraint empdnoFRK foreign key (dno)
references department(dnumber))
4. Enable the constraints:
alter table EMPLOYEE enable constraint empsuperFRK;
alter table DEPARTMENT enable constraint departFRK;

Explanation / Answer

- The correct answer for the above example is A.

-As the proper tables are created as given schema.

-For Referential Integrity Constraints:
EMPLOYEE.SUPERSSN references to EMPLOYEE.SSN

-superssn is declared foreign key which -> to ssn of employee table itself.

EMPLOYEE.DNUMBER references to DEPARTMENT.DNUMBER

-it is mentioned in the constraints.

DEPARTMENT.MGRSSN references to EMPLOYEE.SSN

-mgrssn is declred as foreign key and ssn declared as primary key to depict the referential integrity in constraint.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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