Ask a question to Desklib · AI bot
Normalization- Databse Design
Added on -2019-09-16
| 18 pages
| 2348 words
| 274 views
Trusted by 2+ million users,
1000+ happy students everyday
Student name:5 November 2016DATABASE COURSEWORK
ContentsNORMALIZATION...................................................................................................................................21NF....................................................................................................................................................22NF....................................................................................................................................................23NF....................................................................................................................................................2ASSUMPTIONS.......................................................................................................................................3ER DIAGRAM..........................................................................................................................................4CREATE TABLE COMMANDS AND INSERT TABLE COMMANDS..............................................................5SQL QUERIES........................................................................................................................................101 | P a g e
NORMALIZATION1NFThe 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)2NFIn 2NF we remove the partial dependency, create primary and foreign keys and the table structure isas 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)3NFIn 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)NOTEUNDERLINE: primary keyITALICS: foreign keyASSUMPTIONSWe have assumed the following things in the scenario:1.Each staff possess has one or more skills2.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 hardware5.Project requires one or more software6.Each project has a type7.Each job has a type8.Each staff member has a unique id9.Each hardware has a unique id10.Each software has a unique id11.Each project has a unique id3 | P a g e
Found this document preview useful?
You are reading a preview
Upload your documents to download
or
Become a Desklib member to get accesss
Paid Plans
Free Plan
Single Unlock
Monthly Plan
Yearly Plan