Project Allocation Report

The provided content includes the creation of two tables, Project_Hardware and Project_Software, which store information about projects and their corresponding hardware and software. The content also includes several SQL queries that demonstrate various database operations such as selecting data based on specific conditions, grouping results by a common attribute, and counting the number of occurrences of each job type. Additionally, there are two forms: the Project Allocation form (Fig. 1) and an unknown form.

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 into project_type(ptype_id, ptype_name) values (1, 'Web');
insert into project_type(ptype_id, ptype_name) values (2, 'Maintenance');
insert into project_type(ptype_id, ptype_name) values (3, 'App');
insert into project_type(ptype_id, ptype_name) values (4, 'Testing');
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 into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1049', 'Health System', 1, '2016-06-01', '2016-09-01', '2016-09-02');
insert into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1050', 'insurance System', 2, '2016-01-01', '2016-03-03', '2016-03-06');
5 | P a g e

insert into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1051', 'university System', 3, '2016-05-01', '2016-06-01', '2016-06-05');
create table Job_type
(
type_id varchar(5) primary key,
type_name varchar(50)
);
insert into job_type(type_id, type_name) values ('JT1', 'Imaging');
insert into job_type(type_id, type_name) values ('JT2', 'Web Developer');
insert into job_type(type_id, type_name) values ('JT3', 'Maintenance');
insert into job_type(type_id, type_name) values ('JT4', 'Training');
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)
);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1014E', 'John Baker', 'JT1', 3);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('230R1', 'Marian Dowd', 'JT2', 1);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0P625', 'Jennifer Tuff', 'JT3', 2);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('4T371', 'Paul Downing', 'JT4',
3 );
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1044E', 'Johnny Woods', 'JT1',
3);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('233R1', 'Mia S.', 'JT2', 1);
6 | P a g e

insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0Q625', 'Jenny Duff', 'JT3', 2);
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('6T371', 'Pal Sterling', 'JT4', 3 );
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 INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1014E', 'DATA IMAGING' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('230R1', WEB DEVELOPMENT' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0P625', 'OFFLINE DEVELOPMENT' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('4T371', 'C++' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1044E', 'BLACK BOX' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('233R1', 'HTML' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0Q625', 'SQL' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('6T371', 'NOSQL' );
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)
);
7 | P a g e

INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1014E', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('230R1', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0P625', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('4T371', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1044E', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('233R1', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0Q625', 'B1051' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1051' );
create table Software
(
Software_ID integer primary key,
Software_name varchar(50)
);
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1001, 'Encryption Protocol
Suite');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1002, 'Autodesk Maya');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1003, 'Rational rose');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1004, 'Sql Server');
create table Hardware
(
Hardware_ID integer primary key,
Hardware_name varchar(50)
);
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (101, 'Games Monitor');
8 | P a g e

INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (102, 'Web Cam');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (103, 'Scanner');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (104, 'External Drive');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (105, 'Hard Disk 5GB');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (106, 'Flat Screen Monitor');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (107, 'Imagines tool');
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),
constraint ph_fk2 foreign key(project_ID) references project(project_ID)
);
insert into Project_hware(project_id, hardware_id) values('B1049', 101);
insert into Project_hware(project_id, hardware_id) values('B1049', 102);
insert into Project_hware(project_id, hardware_id) values('B1049', 103);
insert into Project_hware(project_id, hardware_id) values('B1049', 104);
insert into Project_hware(project_id, hardware_id) values('B1050', 101);
insert into Project_hware(project_id, hardware_id) values('B1050', 102);
insert into Project_hware(project_id, hardware_id) values('B1051', 101);
insert into Project_hware(project_id, hardware_id) values('B1051', 104);
insert into Project_hware(project_id, hardware_id) values('B1051', 102);
insert into Project_hware(project_id, hardware_id) values('B1051', 103);
insert into Project_hware(project_id, hardware_id) values('B1050', 107);
insert into Project_hware(project_id, hardware_id) values('B1051', 106);
create table Project_sware
9 | P a g e

(
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 into Project_sware(project_id, software_id) values('B1049', 1001);
insert into Project_sware(project_id, software_id) values('B1049', 1002);
insert into Project_sware(project_id, software_id) values('B1049', 1003);
insert into Project_sware(project_id, software_id) values('B1051', 1001);
insert into Project_sware(project_id, software_id) values('B1051', 1002);
insert into Project_sware(project_id, software_id) values('B1050', 1001);
insert into Project_sware(project_id, software_id) values('B1050', 1004);
SQL QUERIES
1. A list of employees assigned to a particular project.
SQL: select staff_id from project_staff where project_id = 'B1049'
OUTPUT:
10 | 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.
11 | P a g e

SQL: select * from project where end_date < expect_finish_date;
OUTPUT:
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:
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:
12 | P a g e

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 'hardware', sw.software_name as 'software'
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 = 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:
13 | P a g e

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:
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:
14 | P a g e