List of Hardware Used in Project SQl

Added on - 16 Sep 2019

  • 15

    pages

  • 2423

    words

  • 59

    views

  • 0

    downloads

Showing pages 1 to 4 of 15 pages
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
desklib-logo
You’re reading a preview
card-image

To View Complete Document

Become a Desklib Library Member.
Subscribe to our plans

Unlock This Document