SIT103 Database and Information Retrieval Assignment 2 - Trimester 1

This document provides a solution to SIT103 Database and Information Retrieval Assignment 2, focusing on relational database design and SQL queries. It includes the identification of primary keys, foreign keys, and NOT NULL constraints within a given database schema. The solution demonstrates the creation of tables in Oracle, complete with constraints, followed by the insertion of data values into these tables. Furthermore, the document presents a series of SQL queries designed to retrieve and manipulate data, covering various aspects such as employee information, departmental details, project assignments, and dependent records. The assignment provides a comprehensive overview of database design principles and SQL query techniques.

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

Dependent Table
CREATE TABLE Dependent
( Essn NUMBER NOT NULL,
Dependent_Name VARCHAR2(30) NOT NULL,
Sex VARCHAR2(1) NOT NULL,
BDate Date NOT NULL ,
Relationship VARCHAR2(30) NOT NULL,
CONSTRAINT "DEPENDENT_PK" PRIMARY KEY (Essn, Dependent_Name),
CONSTRAINT fk_empdep
FOREIGN KEY (Essn)
REFERENCES Employee(Ssn)
);

Works_on Table
CREATE TABLE Works_On
( Essn NUMBER NOT NULL,
Pno NUMBER NOT NULL,
Hours NUMBER(3,1) NULL,
CONSTRAINT "WORK_PK" PRIMARY KEY (Essn, Pno),
CONSTRAINT fk_project
FOREIGN KEY (Pno)
REFERENCES Project(Pnumber),
CONSTRAINT fk_empworks
FOREIGN KEY (Essn)
REFERENCES Employee(Ssn) );

Insert Statements
Department Table
insert into Department (DNo, DName, Mgr_ssn, Mgr_start_date)
values (1, 'HeadQuarters', 888665555, to_date('1981-06-19','YYYY-MM-DD'));
insert into Department (DNo, DName, Mgr_ssn, Mgr_start_date)
values (4, 'Administration', 987654321, to_date('1995-01-01','YYYY-MM-DD'));
insert into Department (DNo, DName, Mgr_ssn, Mgr_start_date)
values (5, 'Research', 333445555, to_date('1988-05-22','YYYY-MM-DD'));

Employee Table
begin
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('John', 'B', 'Smith', 123456789, to_date('1965-01-09','YYYY-MM-DD'), '731 Fondren, Houston, TX',
'M', 30000, 333445555, 5);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Franklin', 'T', 'Wong', 333445555, to_date('1955-12-08','YYYY-MM-DD'), '638 Voss, Houston, TX',
'M', 40000, 888665555, 5);

insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Alicia', 'J', 'Zalaya', 999887777, to_date('1968-01-19','YYYY-MM-DD'), '3321 Castle, Spring, TX',
'F', 25000, 987654321, 4);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Jennifer', 'S', 'Wallace', 987654321, to_date('1941-06-20','YYYY-MM-DD'), '291 Berry, Bellaire,
TX', 'F', 43000, 888665555, 4);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Ramesh', 'K', 'Narayan', 666884444, to_date('1962-09-15','YYYY-MM-DD'), '975 Fire Oak,
Humble, TX', 'M', 38000, 333445555, 5);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Joyce', 'A', 'English', 453453453, to_date('1972-07-31','YYYY-MM-DD'), '5631 Rice, Houston, TX',
'F', 25000, 333445555, 5);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('Ahmad', 'V', 'Jabbar', 987987987, to_date('1969-03-29','YYYY-MM-DD'), '980 Dallas, Houston,
TX', 'M', 25000, 987654321, 4);
insert into Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
values ('James', 'E', 'Borg', 888665555, to_date('1937-11-10','YYYY-MM-DD'), '450 Stone, Houston, TX',
'M', 55000, '', 1);
end;
Dept_Locations Table

begin
insert into Dept_Locations(DNo, Location )
values (1, 'Houston');
insert into Dept_Locations(DNo, Location )
values (4, 'Stafford');
insert into Dept_Locations(DNo, Location )
values (5, 'Bellaire');
insert into Dept_Locations(DNo, Location )
values (5, 'Sugarland');
insert into Dept_Locations(DNo, Location )
values (5, 'Houston');
end;