Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Given two relation schema with referential integrity constraints as follows, Sch

ID: 3731659 • Letter: G

Question

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;


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

Solution:

C option is correct

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))

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Given this table data, Estimate the following simple regression model: Return =
Question #3219311
Given this table: Find: A) Puck 2, after collision v x (in cm/s). B) Puck 2, aft
Question #1283833
Given this xml file, please help me complete the following: The root element is
Question #664761
Given this xml file, please help me complete the following: The root element is
Question #664784
Given this xml file, please help me complete the following: This XML document co
Question #664760
Given this xml file, please help me complete the following: This XML document co
Question #664785
Given this, what advantage might be offered by having a code with 64 codons? Giv
Question #174302
Given three Cartesian points (2,2), (4,5), (7,1), find the coefficients {a, b, c
Question #3402863
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Given two reference variables t1 and t2, if t1 = t2 is true, t1 .equals(t2) must
Next
Given two relational schemas R(ABC) and S(ABC), let r(R) and s(S) be the relatio

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