Hello, Expert reply only. There\'s two files with data here. Please provide me w

Question

Hello,

Expert reply only.

There's two files with data here.

Please provide me with the SQL query for Oracle to create a table for the data below in oracle. Please provide step by step for each to be able to run in Oracle DBMS.

What is the Primary Key and Secondary Key for each, and how did you determine PK and FK.

EMPLOYEE FNAME | MNT | LNAME | SSN BDATE 123456789 1965-01-09 333445555 1955-12-08 Zelaya 999887777 1968-01-19 Walace 987654321 1941-06-20 Narayan666884444 1962-09-15 453453453 972-07-31 987987987 1969-03-29 888665555 1937-11-10 ADDRESS SEX SALARY I SUPERSSN DNO 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX 333445555 888665555 5 987654321 4 8886655554 333445555 5 333445555 5 987654321 Franklin Alicia Jenniter Ramesh Bellaire, TX Ahmad E Borg DEPT LOCATIONS DNUMBE Stafford DNUMBER MGRSSN 333445555 987654321 888665555 DEPARTMENT DNAME MGRSTARTDATE Sugarland Administration Headquarters 1995-01-01 1981-06-19

Explanation / Answer

Answer)

Create table scripts for the above design:

create table for Employee - primary key is SSN, foreign key is DNO

create table EMPLOYEE (FNAME varchar(255), MNIT varchar(2),

LNAME varchar(255), SSN int NOT NULL PRIMARY KEY, BDATE date,

ADDRESS varchar(255), SEX varchar(2),

SALARY int, SUPERSSN int,

DNO int ,

CONSTRAINT FK_EMPLOYEE FOREIGN KEY (DNO)

REFERENCES DEPARTMENT(DNUMBER)

);

  

create table for Department - primary key is DNUMBER, foreign key is MGRSSN

create table DEPARTMENT(

DNAME varchar(25), DNUMBER int NOT NULL PRIMARY KEY,

MGRSSN int,

MGRSTARTDATE date,

CONSTRAINT FK_DEPT FOREIGN KEY (MGRSSN)

REFERENCES EMPLOYEE(SSN)

);

create table for DEPT_LOCATIONS - primary key is DNUMBER,DLOCATION foreign key is DNUMBER

create table DEPT_LOCATIONS (

DNUMBER int,

DLOCATION varchar(20),

CONSTRAINT DL PRIMARY KEY (DNUMBER,DLOCATION),

CONSTRAINT FK_LOC FOREIGN KEY (DNUMBER)

REFERENCES DEPARTMENT(DNUMBER)

);

create table for WORKS_ON - primary key is ESSN,PNO foreign key are ESSN,PNO

create table WORKS_ON(

ESSN int,

PNO int,

HOURS int,

CONSTRAINT WORKS PRIMARY KEY (ESSN,PNO),

CONSTRAINT FK_WORKS FOREIGN KEY (ESSN)

REFERENCES EMPLOYEE(SSN),

CONSTRAINT FK_WORKSON FOREIGN KEY (PNO)

REFERENCES PROJECT(PNUMBER)

);

create table for PROJECT - primary key is PNUMBER foreign key is DNUM

create table PROJECT(

PNAME varchar(255),

PNUMBER int NOT NULL PRIMARY KEY,

PLOCATION varchar(255),

DNUM int,

CONSTRAINT FK_PROJECT FOREIGN KEY (DNUM)

REFERENCES DEPARTMENT(DNUMBER)

);

create table for DEPENDENT - primary key is ESSN,DEPENDENT_NAME foreign key is ESSN

create table DEPENDENT(

ESSN int,

DEPENDENT_NAME varchar(255),

SEX varchar(2),

BDATE date,

RELATIONSHIP varchar(20),

CONSTRAINT DEPENDENTV PRIMARY KEY (ESSN,DEPENDENT_NAME),

CONSTRAINT FK_DEPENDENT FOREIGN KEY (ESSN)

REFERENCES EMPLOYEE(SSN)

);

The primary keys are provided in the table design.

The foreign keys are the primary keys in other table referenced for integrity constraint checks.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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