SIT103 Database and Information Retrieval Assignment 2: SQL Queries

This document presents a comprehensive solution to a database assignment (SIT103) focusing on relational database concepts and SQL queries. It begins by defining a relational schema with tables for Department, Dept_Locations, Employee, Project, Dependent, and Works_On, outlining primary and foreign key relationships, and specifying NOT NULL constraints for each column. Following the schema, the solution provides the SQL CREATE TABLE statements for each table, defining data types and constraints. Subsequently, it includes INSERT statements to populate these tables with sample data, demonstrating the population of various attributes for each entity. The core of the solution lies in a series of SQL queries (Query a to Query l) designed to retrieve specific information from the database, such as employee details, supervisor relationships, project information, dependent counts, and department managers. Each query is presented with its corresponding SQL code, showcasing various SQL features like JOIN operations, aggregation, and filtering with WHERE and HAVING clauses. The queries cover a range of database operations, including selecting, joining, grouping, and filtering data, providing a practical demonstration of SQL usage in a relational database context. The solution is supported by references to Guru99 and Brombarg for database normalization concepts.

SIT103- Database and Information Retrieval
2018 – Assignment 2
Student ID:
Tutor:
5/20/2018

Report
Relational Schema
Table Name Primary Key Foreign Key NOT NULL
Columns
Department FldDNo
Dept_Locations FldDNo, FldLocation FOREIGN KEY (FldDNo)
REFERENCES
Department(FldDNo)
Employee FldSsn FOREIGN KEY (FldDNo)
REFERENCES
Department(FldDNo)
FldSuper_ssn
Project FldPnumber FOREIGN KEY (DNum,
Plocation)
REFERENCES
FldDept_Locations(FldDNo,
FldLocation)
Dependent FldEssn,
FldDependent_Name
FOREIGN KEY (FldEssn)
REFERENCES
Employee(FldSsn)
Works_On FldEssn, FldPno FOREIGN KEY (FldPno)
REFERENCES
Project(FldPnumber),
FOREIGN KEY (FldEssn)
REFERENCES
Employee(FldSsn)
Hours
(Guru99 2016)
(Brombarg P. n.d.)

Create Tables
Department Table
CREATE TABLE Department
( FldDNo NUMBER NOT NULL,
FldDName VARCHAR2(20) NOT NULL,
FldMgr_ssn Number NOT NULL,
FldMgr_start_date date NOT NULL,
CONSTRAINT "DEPT_PK" PRIMARY KEY (FldDNo)
);

Dept_Locations Table
CREATE TABLE Dept_Locations
( FldDNo NUMBER NOT NULL,
FldLocation VARCHAR2(30) NOT NULL,
CONSTRAINT "DEPTLOC_PK" PRIMARY KEY (FldDNo, FldLocation),
CONSTRAINT fk_depart
FOREIGN KEY (FldDNo)
REFERENCES Department(FldDNo)
);
Employee Table
CREATE TABLE Employee
( FldFname VARCHAR2(30) NOT NULL,
FldMinit VARCHAR2(1) NOT NULL,
FldLname VARCHAR2(30) NOT NULL,
FldSsn Number NOT NULL,
FldBDate Date NOT NULL ,

FldAddress VARCHAR2(50) NOT NULL ,
FldSex VARCHAR2(1) NOT NULL ,
FldSalary Number NOT NULL ,
FldSuper_ssn Number NULL,
FldDNo Number NOT NULL,
CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY (FldSsn),
CONSTRAINT fk_department
FOREIGN KEY (FldDNo)
REFERENCES Department(FldDNo)
);
Project Table
CREATE TABLE Project
( FldPnumber NUMBER NOT NULL,
FldPname VARCHAR2(30) NOT NULL,
FldPlocation VARCHAR2(30) NOT NULL,
FldDNum NUMBER NOT NULL,
CONSTRAINT "PROJ_PK" PRIMARY KEY (FldPnumber),
CONSTRAINT fk_deptLocation

FOREIGN KEY (FldDNum, FldPlocation)
REFERENCES Dept_Locations(FldDNo, FldLocation)
);
Dependent Table
CREATE TABLE Dependent
( FldEssn NUMBER NOT NULL,
FldDependent_Name VARCHAR2(30) NOT NULL,
FldSex VARCHAR2(1) NOT NULL,
FldBDate Date NOT NULL ,
FldRelationship VARCHAR2(30) NOT NULL,
CONSTRAINT "DEPEND_PK" PRIMARY KEY (FldEssn, FldDependent_Name),
CONSTRAINT fk_empdepend
FOREIGN KEY (FldEssn)
REFERENCES Employee(FldSsn)
);

Works_on Table
CREATE TABLE Works_On
( FldEssn NUMBER NOT NULL,
FldPno NUMBER NOT NULL,
FldHours NUMBER(3,1) NOT NULL,
CONSTRAINT "WORKS_PK" PRIMARY KEY (FldEssn, FldPno),
CONSTRAINT fk_proj
FOREIGN KEY (FldPno)
REFERENCES Project(FldPnumber),
CONSTRAINT fk_empwork
FOREIGN KEY (FldEssn)
REFERENCES Employee(FldSsn)
);

Insert Statements
Department Table
begin
insert into Department (FldDNo, FldDName, FldMgr_ssn, FldMgr_start_date)
values (1, 'HeadQuarters', 888665555, to_date('1981-06-19','YYYY-MM-DD'));
insert into Department (FldDNo, FldDName, FldMgr_ssn, FldMgr_start_date)
values (4, 'Administration', 987654321, to_date('1995-01-01','YYYY-MM-DD'));
insert into Department (FldDNo, FldDName, FldMgr_ssn, FldMgr_start_date)
values (5, 'Research', 333445555, to_date('1988-05-22','YYYY-MM-DD'));
end;

Employee Table
begin
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('John', 'B', 'Smith', 123456789, to_date('1965-01-09','YYYY-MM-DD'), '731 Fondren, Houston, TX',
'M', 30000, 333445555, 5);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('Franklin', 'T', 'Wong', 333445555, to_date('1955-12-08','YYYY-MM-DD'), '638 Voss, Houston, TX',
'M', 40000, 888665555, 5);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('Alicia', 'J', 'Zalaya', 999887777, to_date('1968-01-19','YYYY-MM-DD'), '3321 Castle, Spring, TX',
'F', 25000, 987654321, 4);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('Jennifer', 'S', 'Wallace', 987654321, to_date('1941-06-20','YYYY-MM-DD'), '291 Berry, Bellaire,
TX', 'F', 43000, 888665555, 4);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)

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 (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('Joyce', 'A', 'English', 453453453, to_date('1972-07-31','YYYY-MM-DD'), '5631 Rice, Houston, TX',
'F', 25000, 333445555, 5);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
values ('Ahmad', 'V', 'Jabbar', 987987987, to_date('1969-03-29','YYYY-MM-DD'), '980 Dallas, Houston,
TX', 'M', 25000, 987654321, 4);
insert into Employee (FldFname, FldMinit, FldLname, FldSsn, FldBdate, FldAddress, FldSex, FldSalary,
FldSuper_ssn, FldDno)
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(FldDNo, FldLocation)
values (1, 'Houston');
insert into Dept_Locations(FldDNo, FldLocation)
values (4, 'Stafford');
insert into Dept_Locations(FldDNo, FldLocation)
values (5, 'Bellaire');
insert into Dept_Locations(FldDNo, FldLocation)
values (5, 'Sugarland');
insert into Dept_Locations(FldDNo, FldLocation)
values (5, 'Houston');
end;

Project Table
begin
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(1, 'ProductX', 'Bellaire', 5);
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(2, 'ProductY', 'Sugarland', 5);
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(3, 'ProductZ', 'Houston', 5);
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(10, 'Computerization', 'Stafford', 4);
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(20, 'Reorganization', 'Houston', 1);
insert into Project (FldPnumber, FldPname, FldPlocation, FldDNum)
values(30, 'Newbenefits', 'Stafford', 4);
end;