Database Coursework: Normalization and SQL Queries

This document presents a database coursework solution focusing on database normalization, SQL queries, and table creation. It begins with the initial table structure and progresses through 1NF, 2NF, and 3NF normalization steps, detailing the changes made at each stage. The document also includes assumptions made about the database scenario, an ER diagram, and the SQL commands for creating tables and inserting data. Furthermore, it provides a series of SQL queries to retrieve specific information from the database, along with their corresponding outputs. The coursework covers various aspects of database design and implementation, offering a comprehensive solution for the given scenario.

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