Database Coursework: Normalization, ER Diagram, and SQL Queries

This database coursework solution covers the principles of database normalization, including 1NF, 2NF, and 3NF, to optimize data structure and reduce redundancy. It includes an Entity-Relationship (ER) diagram illustrating the database schema, detailing entities, attributes, and relationships. The assignment provides SQL CREATE TABLE commands and INSERT statements to populate the database with sample data, followed by a series of SQL queries to retrieve and manipulate the data. The queries demonstrate various functionalities, such as selecting staff assigned to a project, projects completed before a specific date, and staff with particular skills. The solution also presents a project allocation form and queries to count job types and identify unused hardware. This assignment is contributed by a student to be published on the website Desklib. Desklib is a platform which provides all the necessary AI based study tools for students.

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