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;

Project Table
begin
insert into Project (Pnumber, Pname, Plocation, DNum)
values(1, 'ProductX', 'Bellaire', 5);
insert into Project (Pnumber, Pname, Plocation, DNum)
values(2, 'ProductY', 'Sugarland', 5);
insert into Project (Pnumber, Pname, Plocation, DNum)
values(3, 'ProductZ', 'Houston', 5);
insert into Project (Pnumber, Pname, Plocation, DNum)
values(10, 'Computerization', 'Stafford', 4);
insert into Project (Pnumber, Pname, Plocation, DNum)
values(20, 'Reorganization', 'Houston', 1);
insert into Project (Pnumber, Pname, Plocation, DNum)
values(30, 'Newbenefits', 'Stafford', 4);
end;

Dependent Table
begin
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (333445555, 'Alice', 'F', to_date('1986-04-05','YYYY-MM-DD'), 'Daughter');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (333445555, 'Theodore', 'M', to_date('1983-10-25','YYYY-MM-DD'), 'Son');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (333445555, 'Joy', 'F', to_date('1958-05-03','YYYY-MM-DD'), 'Daughter');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (987654321, 'Abner', 'M', to_date('1942-02-28','YYYY-MM-DD'), 'Spouse');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)

values (123456789, 'Michael', 'M', to_date('1988-01-04','YYYY-MM-DD'), 'Son');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (123456789, 'Alice', 'F', to_date('1988-12-30','YYYY-MM-DD'), 'Daughter');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (123456789, 'Elizabeth', 'F', to_date('1967-05-05','YYYY-MM-DD'), 'Spouse');
insert into Dependent (Essn , Dependent_Name , Sex ,BDate , Relationship)
values (123456789, 'Noah', 'M', to_date('2005-05-05','YYYY-MM-DD'), 'Son');
end;
Works_on Table

begin
insert into works_on (Essn, Pno, Hours)
values (123456789, 1, 32.5);
insert into works_on (Essn, Pno, Hours)
values (123456789, 2, 7.5);
insert into works_on (Essn, Pno, Hours)
values (666884444, 3, 40.0);
insert into works_on (Essn, Pno, Hours)
values (453453453, 1, 20.0);
insert into works_on (Essn, Pno, Hours)
values (453453453, 2, 20.0);
insert into works_on (Essn, Pno, Hours)
values (333445555, 2, 10.0);
insert into works_on (Essn, Pno, Hours)
values (333445555, 3, 10.0);
insert into works_on (Essn, Pno, Hours)
values (333445555, 10, 10.0);
insert into works_on (Essn, Pno, Hours)
values (333445555, 20, 10.0);
insert into works_on (Essn, Pno, Hours)
values (999887777, 30, 30.0);
insert into works_on (Essn, Pno, Hours)
values (999887777, 10, 10.0);
insert into works_on (Essn, Pno, Hours)
values (987987987, 10, 35.0);
insert into works_on (Essn, Pno, Hours)
values (987987987, 30, 5.0);
insert into works_on (Essn, Pno, Hours)

values (987654321, 30, 20.0);
insert into works_on (Essn, Pno, Hours)
values (987654321, 20, 15.0);
end;
insert into works_on (Essn, Pno, Hours)
values (888665555, 20,0 );

Queries
Query a
select Fname, Lname, address, DNo from employee;
Query b
Select concat (concat(a.Fname, ' ') , a.LName) as EmployeeName, a.DNo, concat (concat(b.Fname, ' ') ,
b.LName) as Supervisor from employee a left outer join employee b
on a.super_ssn=b.ssn;

Query c
select e.ssn, e.fname, e.lname, e.address, e.sex, e.salary, e.dno, p.pname from employee e inner join
department d on e.dno=d.dno
inner join dept_locations dl on dl.dno=d.dno inner join Project p on dl.dno=p.dnum and
dl.location=p.plocation where pnumber=3;
Query d
select e.ssn, concat (concat(e.Fname, ' ') , e.LName) as EmployeeName, count (d.essn) from Employee e
inner join dependent d on e.ssn=d.Essn
group by e.Fname, e.Lname, e.ssn;
Query e

select e.ssn, concat (concat(e.Fname, ' ') , e.LName) as EmployeeName, count (w.essn) No_Of_Projects
from Employee e inner join works_on w on e.ssn=w.Essn group by e.Fname, e.Lname, e.ssn having count
(w.essn)>1;
Query f
Select * from (Select ssn, Fname, Lname, salary from employee where ssn in (select super_ssn from
employee) order by salary desc) emp where rownum=1 ;
Query g
select essn, pno, count(Hours) as TotalHours from works_on group by essn, pno order by essn;

Query h
select * from Project where Dnum=5;
Query i

select p.dnum, count(pnumber) NoOfProjects, d.mgr_ssn from Project p inner join Department d on
p.dnum=d.dno group by p.dnum, d.mgr_ssn;
Query j
select dependent.essn, employee.fname, employee.lname,
TRUNC(TRUNC(MONTHS_BETWEEN(current_date, dependent.bdate)) / 12) ChildAge from dependent
inner join employee on employee.ssn=dependent.essn
where TRUNC(TRUNC(MONTHS_BETWEEN(current_date, dependent.bdate)) / 12) < 15;
Query k
select distinct employee.ssn, employee.fname, employee.lname from employee, department where ssn
in (select mgr_ssn from department)
and TRUNC(TRUNC(MONTHS_BETWEEN(current_date, department.mgr_start_date)) / 12) > 20;

Query l
Question
Display all employees with their project names.
Query

select distinct e.ssn, concat (concat(e.Fname, ' ') , e.LName) as EmployeeName, Pname from
Employee e inner join works_on w on e.ssn=w.Essn inner join project p on w.pno=p.pnumber;
References
Easy Computer Academy (2017), Database Normalization Explained in Simple English, Retrieved from
https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-
english/

BigennersBook (2017), Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database: Foreign Keys,
Retrieved from https://beginnersbook.com/2015/05/normalization-in-dbms/