Relational Data Structure, Tables, Inserts and Queries

SIT103 Database and Information Retrieval
Assignment 2
5/19/2018
Student ID:
Module Tutor:

Relational Data Structure
Table Name Primary Key Foreign Key NOT NULL Constraints
Department DNo NOT NULL
Dept_Locations DNo, Location FOREIGN KEY (DNo)
REFERENCES
Department(DNo)
NOT NULL
Employee Ssn FOREIGN KEY (DNo)
REFERENCES
Department(DNo)
NOT NULL
Project Pnumber FOREIGN KEY (DNum,
Plocation)
REFERENCES
Dept_Locations(DNo,
Location)
NOT NULL
Dependent Essn, Dependent_Name FOREIGN KEY (Essn)
REFERENCES
Employee(Ssn)
NOT NULL
Works_On Essn, Pno FOREIGN KEY (Pno)
REFERENCES
Project(Pnumber),
FOREIGN KEY (Essn)
REFERENCES
Employee(Ssn)
NOT NULL
(Easy Computer Academy 2017)
(BigennersBook 2017)

Create Tables
Department Table
CREATE TABLE Department
( DNo NUMBER NOT NULL,
DName VARCHAR2(30) NOT NULL,
Mgr_ssn Number NOT NULL,
Mgr_start_date date NOT NULL,
CONSTRAINT "DEP_PK" PRIMARY KEY (DNo)
);

Dept_Locations Table
CREATE TABLE Dept_Locations
( DNo NUMBER NOT NULL,
Location VARCHAR2(30) NOT NULL,
CONSTRAINT "DEPLOC_PK" PRIMARY KEY (DNo, Location),
CONSTRAINT fk_dept
FOREIGN KEY (DNo)
REFERENCES Department(DNo)
);

Employee Table
CREATE TABLE Employee
( Fname VARCHAR2(30) NOT NULL,
Minit VARCHAR2(1) NOT NULL,
Lname VARCHAR2(30) NOT NULL,
Ssn Number NOT NULL,
BDate Date NOT NULL ,
Address VARCHAR2(50) NOT NULL ,
Sex VARCHAR2(1) NOT NULL ,
Salary Number NOT NULL ,
Super_ssn Number NULL,
DNo Number NOT NULL,
CONSTRAINT "EMP_PK" PRIMARY KEY (Ssn),
CONSTRAINT fk_departments
FOREIGN KEY (DNo)
REFERENCES Department(DNo)
);

Project Table
CREATE TABLE Project
( Pnumber NUMBER NOT NULL,
Pname VARCHAR2(30) NOT NULL,
Plocation VARCHAR2(30) NOT NULL,
DNum NUMBER NOT NULL,
CONSTRAINT "PROJECT_PK" PRIMARY KEY (Pnumber),
CONSTRAINT fk_deptLoc
FOREIGN KEY (DNum, Plocation)
REFERENCES Dept_Locations(DNo, Location)
);