Project Management Database Queries
Added on 2019-09-16
18 Pages2348 Words274 Views
|
|
|
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
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents