Project Management Database Queries

The provided content is about a database management system that involves various tables and queries. It includes the creation of two tables, Project_Hware and Project_Sware, with specific constraints and data inserted into them. The assignment also provides several SQL queries to retrieve information from these tables, including lists of employees assigned to projects, projects assigned to employees, projects completed before a given date, staff skilled in web development, projects requiring encryption software and Imagines equipment, project allocation form, number of people performing each type of job, and list of hardware not used in any project.

database coursework
Student name:
5 November 2016

Contents
NORMALIZATION...................................................................................................................................2
1NF....................................................................................................................................................2
2NF....................................................................................................................................................2
3NF....................................................................................................................................................2
ASSUMPTIONS.......................................................................................................................................3
ER DIAGRAM..........................................................................................................................................4
CREATE TABLE COMMANDS AND INSERT TABLE COMMANDS..............................................................5
SQL QUERIES........................................................................................................................................10
1 | P a g e

NORMALIZATION
1NF
The initial table structure without normalization is:
Project (Project_id, Project Name, Project_Type, Start Date, expect_finish_date, End Date, Staff_ID,
Name, Job_Type, Job_Grade, skill_name, Hardware_name, Software_name)
After applying 1NF and creating tables with atomic values are as follows:
Project (Project_id, Project Name, Project_Type, Start Date, expect_finish_date, End Date, Staff_ID,
Name, Job_Type, Job_Grade)
Staff_skill (Staff_ID, Name, Job_Type, Job_Grade, skill_name)
Project_hware (Project_id, Project Name, Project_Type, Start Date, expect_finish_date, End Date,
Hardware_name)
Project_sware (Project_id, Project Name, Project_Type, Start Date, expect_finish_date, End Date,
Software_name)
2NF
In 2NF we remove the partial dependency, create primary and foreign keys and the table structure is
as follows:
Project (Project_id, Project Name, Project_Type, Start Date, expect_finish_date, End Date)
Staff (Staff_ID, Name, Job_Type, Job_Grade, Project_id)
Staff_skill(Staff_ID, Name, Job_Type, Job_Grade, skill_name)
Project_hware (Project_id, Hardware_ID, Hardware_name)
Project_sware (Project_id, Software_ID, Software-name)
3NF
In 3NF we remove the transitive dependency and the table structure is as follows:
Project (Project_id, Project Name, Project_Type_id, Start Date, expect_finish_date, End Date)
Project_Type (ptype_id, ptype_name)
Job_type (type_id, type_name)
Staff (Staff_ID, Name, Job_Type_id, Job_Grade)
Staff_skill (Staff_ID, skill_name)
Project_Staff (Staff_ID, Project_id)
Project_hware (Project_id, Hardware_ID)
2 | P a g e

Hardware (Hardware_ID, Hardware_name)
Project_sware (Project_id, Software_ID)
Software (Software_ID, Software-name)
NOTE
UNDERLINE: primary key
ITALICS: foreign key
ASSUMPTIONS
We have assumed the following things in the scenario:
1. Each staff possess has one or more skills
2. Project has one or more staff working on it.
3. A staff person works on one or more projects.
4. Project requires one or more hardware
5. Project requires one or more software
6. Each project has a type
7. Each job has a type
8. Each staff member has a unique id
9. Each hardware has a unique id
10. Each software has a unique id
11. Each project has a unique id
3 | P a g e

ER DIAGRAM
4 | P a g e

CREATE TABLE COMMANDS AND INSERT TABLE
COMMANDS
create table Project_Type
(
ptype_id integer primary key,
ptype_name varchar(50)
);
insert all
into project_type(ptype_id, ptype_name) values (1, 'Web')
into project_type(ptype_id, ptype_name) values (2, 'Maintenance')
into project_type(ptype_id, ptype_name) values (3, 'App')
into project_type(ptype_id, ptype_name) values (4, 'Testing')
select * from dual;
create table project
(
Project_id varchar(10) primary key,
Project_Name varchar(100),
Project_Type_id integer,
Start_Date date,
End_Date date,
expect_finish_date date,
constraint p_fk foreign key(Project_Type_id) references Project_Type(ptype_id)
);
insert all
into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date, expect_finish_date)
values ('B1049', 'Health System', 1, '01-06-2016', '01-09-2016', '02-09-2016')
5 | P a g e

into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date, expect_finish_date)
values ('B1050', 'insurance System', 2, '01-01-2016', '03-03-2016', '06-03-2016')
into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date, expect_finish_date)
values ('B1051', 'university System', 3, '01-05-2016', '01-06-2016', '05-06-2016')
select * from dual;
create table Job_type
(
type_id varchar(5) primary key,
type_name varchar(50)
);
insert all
into job_type(type_id, type_name) values ('JT1', 'Imaging')
into job_type(type_id, type_name) values ('JT2', 'Web Developer')
into job_type(type_id, type_name) values ('JT3', 'Maintenance')
into job_type(type_id, type_name) values ('JT4', 'Training')
select * from dual;
create table Staff
(
Staff_ID varchar(10) primary key,
Name varchar(50),
Job_Type_id varchar(5),
Job_Grade integer,
constraint s_fk foreign key(job_type_id) references Job_type(type_id)
);
6 | P a g e

insert all
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1014E', 'John Baker', 'JT1', 3)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('230R1', 'Marian Dowd', 'JT2', 1)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0P625', 'Jennifer Tuff', 'JT3', 2)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('4T371', 'Paul Downing', 'JT4', 3 )
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1044E', 'Johnny Woods', 'JT1', 3)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('233R1', 'Mia S.', 'JT2', 1)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0Q625', 'Jenny Duff', 'JT3', 2)
into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('6T371', 'Pal Sterling', 'JT4', 3 )
select * from dual;
create table Staff_skill
(
Staff_ID varchar(10),
skill_name varchar(50),
primary key(staff_id, skill_name),
constraint ss_fk foreign key(Staff_ID) references staff(Staff_ID)
);
insert all
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1014E', 'DATA IMAGING' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('230R1', 'WEB DEVELOPMENT' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0P625', 'VB.NET' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('4T371', 'C++' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1044E', 'BLACK BOX' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('233R1', 'WEB DEVELOPMENT' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0Q625', 'SQL' )
INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('6T371', 'NOSQL' )
select * from dual;
7 | P a g e

create table Project_Staff
(
Staff_ID varchar(10),
Project_id varchar(10),
primary key(staff_id, project_id),
constraint ps_fk1 foreign key(Staff_ID) references staff(Staff_ID),
constraint ps_fk2 foreign key(project_ID) references project(project_ID)
);
insert all
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1014E', 'B1049' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('230R1', 'B1049' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0P625', 'B1049' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('4T371', 'B1049' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1044E', 'B1050' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('233R1', 'B1050' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0Q625', 'B1051' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1050' )
INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1051' )
select * from dual;
create table Software
(
Software_ID integer primary key,
Software_name varchar(50)
);
8 | P a g e

insert all
INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1001, 'Encryption Protocol Suite')
INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1002, 'Autodesk Maya')
INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1003, 'Rational Rose')
INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1004, 'Sql Server')
select * from dual;
create table Hardware
(
Hardware_ID integer primary key,
Hardware_name varchar(50)
);
insert all
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (101, 'Imagines equipment’)
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (102, 'Web Cam')
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (103, 'Scanner')
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (104, 'External Drive')
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (105, 'Hard Disk 5GB')
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (106, 'Flat Screen Monitor')
INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (107, 'Laser Printer')
select * from dual;
create table Project_hware
(
Project_id varchar(10),
Hardware_ID integer,
primary key(project_id, hardware_id),
constraint ph_fk1 foreign key(hardware_ID) references hardware(hardware_ID),
9 | P a g e

constraint ph_fk2 foreign key(project_ID) references project(project_ID)
);
insert all
into Project_hware(project_id, hardware_id) values('B1049', 101)
into Project_hware(project_id, hardware_id) values('B1049', 102)
into Project_hware(project_id, hardware_id) values('B1049', 103)
into Project_hware(project_id, hardware_id) values('B1049', 104)
into Project_hware(project_id, hardware_id) values('B1050', 101)
into Project_hware(project_id, hardware_id) values('B1050', 102)
into Project_hware(project_id, hardware_id) values('B1051', 101)
into Project_hware(project_id, hardware_id) values('B1051', 104)
into Project_hware(project_id, hardware_id) values('B1051', 102)
into Project_hware(project_id, hardware_id) values('B1051', 103)
into Project_hware(project_id, hardware_id) values('B1050', 107)
into Project_hware(project_id, hardware_id) values('B1051', 106)
select * from dual;
create table Project_sware
(
Project_id varchar(10),
Software_ID integer,
primary key(project_id, software_id),
constraint psw_fk1 foreign key(software_ID) references software(software_ID),
constraint psw_fk2 foreign key(project_ID) references project(project_ID)
);
insert all
into Project_sware(project_id, software_id) values('B1049', 1001)
into Project_sware(project_id, software_id) values('B1049', 1002)
10 | P a g e

into Project_sware(project_id, software_id) values('B1049', 1003)
into Project_sware(project_id, software_id) values('B1051', 1001)
into Project_sware(project_id, software_id) values('B1051', 1002)
into Project_sware(project_id, software_id) values('B1050', 1003)
into Project_sware(project_id, software_id) values('B1050', 1004)
select * from dual;
SQL QUERIES
1. A list of employees assigned to a particular project.
SQL: select staff_id from project_staff where project_id = 'B1049';
OUTPUT:
11 | P a g e

2. A list of projects which an individual employee is assigned to.
SQL: select project_id from project_staff where staff_id = '1014E';
OUTPUT:
3. A list of projects which complete before a given date.
SQL: select * from project where end_date < expect_finish_date;
OUTPUT:
12 | P a g e

4. A list of staff who could work on a web development project.
SQL: select s.* from staff s, staff_skill ss where ss.staff_id = s.staff_id and ss.skill_name like
'%WEB DEVELOPMENT%' ;
OUTPUT:
13 | P a g e

5. A list of projects that require encryption software and Imagines equipment.
SQL: select p.project_id from project p, project_hware ph, project_sware ps, software s,
hardware h where ps.software_id = s.software_id and ph.hardware_id = h.hardware_id and
p.project_id = ph.project_id and p.project_id = ps.project_id and h.hardware_name like
'%Imagines%' and s.software_name like '%Encryption%'
OUTPUT:
6. The Project Allocation form (Fig. 1) which appears in the case study.
SQL: select p.project_id as project_# , p.project_name as project_name, pt.ptype_name as
project_type, p.start_Date as start_date, p.end_Date as end_date,
s.staff_id as staff_#, s.name as Staff_name, jt.type_name as job_type, s.job_grade as
job_grade, h.hardware_name as hardwares, sw.software_name as softwares from project p,
project_type pt, staff s, project_staff ps, job_type jt, project_sware psw, project_hware phw,
software sw, hardware h where p.project_type_id = pt.ptype_id and p.project_id =
ps.project_id and s.staff_id = ps.staff_id and jt.type_id = s.job_type_id and psw.software_id
14 | P a g e

= sw.software_id and phw.hardware_id = h.hardware_id and psw.project_id = p.project_id
and phw.project_id = p.project_id and p.project_id = 'B1049';
OUTPUT:
7. Number People performing each type of job
SQL: select count(s.staff_id) as number_of_people , j.type_name as jobs from staff s,
Job_type j where j.type_id = s. Job_Type_id group by j.type_name;
OUTPUT:
15 | P a g e

8. List of hardware that have not been used in any project
SQL: select * from hardware where hardware_id NOT IN (select distinct hardware_id from
project_hware);
OUTPUT:
16 | P a g e

17 | P a g e