Hi,
Please I'm new in database and I need your help, I'm working on my database project and before to continue I would like to be sure that my NORMALIZATION 3NF and my ER Diagram are correct.
Normalization 3NF
DESIGN PARTS SPECIFIED IN DESIGN PARTS PARTS USED AS SPECIFIED IN DESIGN
------ ------------------------- -------------- ---------------------------------
Design_id(pk) Design_id(fk) Part_id(pk) Job_id(fk)
Design_name Part_id(fk) Part_name Part_id(fk)
Part_name Part_name
Quantity Quantity
JOB TYPE JOB PROPERTY ADDITIONAL PARTS USED ON JOB
-------- --------------- -------------- ----------------------------
Job_type_id(pk) Job_id(pk) Property_id(pk) Job_id(fk)
Job_type Job_type_id(fk) Address Part_id(fk)
property_id(fk) Part_name
Quantity
STAFF STAFF ON JOB
-------------- -------------
Staff_number(pk) Job_id(fk)
Name Staff_number(fk)
My ER Diagram
------------ 1 1..* ------------------ 0..* 1 ------------
| DESIGN |-------------------| PARTS SPECIFIED|-------------------| PARTs |
| | | IN DESIGN | | |
------------ ------------------ ------------
| | 1
1..* | |
| | 0..*
| ------------------------ 1 1..*-----------------
| |PARTs USED AS SPECIFIED|--------------|ADDITIONAL PARTS|
| | IN DESIGN | | USED ON JOB |
| ------------------------ -----------------
| | 1..*
| |
1 | | 1
------------ 1 0..* ------------ 0..* 1 -------------
| JOB |-------------------------------------------------------| JOB |----------------------------| PROPERTY |
| TYPE | | | | |
------------ ------------- 1 --------------
|
|
| 0..*
----------------
| JOB |
| STAFF |
----------------
| 0..*
|
| 1
------------- | STAFF |
| |
-------------
The Scenario
Wyndham Summer Houses are a construction company based in US. The company specialize in building summer houses and outbuildings such as sheds. They want you to design and implement a database that meets the requirements for their data. These requirements are specified in this scenario and the examples of paper documents kept by the company that are shown below.
Wyndham Summer Houses organise their data around the concept of a ‘job’. A job is specified as being for a particular property; but note that a property might have more than one job over time.
A job is also categorized by job type which is linked to a particular design. The detailed architectural plans for each design are NOT to be stored on this database. Instead a design would be linked to a number of parts and there should be a reference to a detailed plan kept as a separate document. Moreover, a particular design will specify the parts to be used for that design. A separate record is kept of the actual parts used on that job, which will include any additional parts used beyond those specified in the design. This is shown in the sample documents below. Finally, there should also be a record kept of the members of staff who work on a job.
Please Note: The data shown in the assignment is not necessarily normalised, and that it is the candidate’s task to organize the data in the most optimal way possible. For example, the paper records shown below will not necessarily map directly to data base tables. The candidate is expected to use these tables as a starting point for their own normalisation and optimisation of the Lawson Summer Houses data.
Below is a sample of the paper records currently kept by Lawson Summer Houses
**Document 1 – Initial Job Sheet at outset of a job**
**Job ID** **PropertyId ** **Address** **Job Type** **Design** **Parts specified in Design**
--------------------------------------------------------------------------------------------------------------------------------
2 234 23The Elms, Andover, Hants. Summer House Summer House Type2 2 x Long side wall fittings
1 x Back wall fittings
1 x front wall fittings
8 x window fittings
--------------------------------------------------------------------------------------------------------------------------------
6 343 Rapid House Gardens, Essex Summer House Summer House Type8 2 x short side wall fittings
1 x Back wall fittings
1 x front wall fittings
6 x window fittings
--------------------------------------------------------------------------------------------------------------------------------
7 343 99 Neat Street, Chesire. Utility Shed Shed Type 1 Standard Shed
--- ----------------------------------------------------------------------------------------------------------------------------
** Document 2 – Staff on Job Record**
**Job ID** **Staff** **Number Name**
--------------------------------------------
2 S1 Isla St Cloud
--------------------------------------------
2 S8 Zak Dodd
--------------------------------------------
2 S2 Marco Yad
--------------------------------------------
6 S11 Sally Lam
--------------------------------------------
6 S1 Isla St Cloud
--------------------------------------------
7 S8 Zak Dodd
--------------------------------------------
7 S1 Isla St Cloud
--------------------------------------------
**Document 3 – Final Job Record**
**Job ID** **Property Id** **Design** **Parts used as specified in Design** **Additional Parts used on job**
---------------------------------------------------------------------------------------------------------------------------------
2 234 Summer House Type 2 2 x Long side wall fittings 1 x Base Board
1 x Back wall fittings 12 x filters for water pump
1 x front wall fittings 1 x additional window frame
8 x window fittings
---------------------------------------------------------------------------------------------------------------------------------
6 343 Summer House Type 8 2 x short side wall fittings 1 x additional window frame
1 x Back wall fittings
1 x front wall fittings
6 x window fittings
---------------------------------------------------------------------------------------------------------------------------------
7 343 Shed Type 1 Standard Shed pack 1 x door pelmet
---------------------------------------------------------------------------------------------------------------------------------
I will be very greatful if you can help me , please its important
