DATABASE SCENARIO NORMALIZATION - ER DIAGRAM
1
Normalization 3NF
1. DESIGN PARTS SPECIFIED IN DESIGN PARTS PARTS USED AS SPECIFIED IN DESIGN
2. ------ ------------------------- -------------- ---------------------------------
3. Design_id(pk) Design_id(fk) Part_id(pk) Job_id(fk)
4. Design_name Part_id(fk) Part_name Part_id(fk)
5. Part_name Part_name
6. Quantity Quantity
7.
8.
9. JOB TYPE JOB PROPERTY ADDITIONAL PARTS USED ON JOB
10. -------- --------------- -------------- ----------------------------
11. Job_type_id(pk) Job_id(pk) Property_id(pk) Job_id(fk)
12. Job_type Job_type_id(fk) Address Part_id(fk)
13. property_id(fk) Part_name
14. Quantity
15.
16. STAFF STAFF ON JOB
17. -------------- -------------
18. Staff_number(pk) Job_id(fk)
19. Name Staff_number(fk)
My ER Diagram
1. ------------ 1 1..* ------------------ 0..* 1 ------------
2. | DESIGN |-------------------| PARTS SPECIFIED|-------------------| PARTs |
3. | | | IN DESIGN | | |
4. ------------ ------------------ ------------
5. | | 1
6. 1..* | |
7. | | 0..*
8. | ------------------------ 1 1..*-----------------
9. | |PARTs USED AS SPECIFIED|--------------|ADDITIONAL PARTS|
10. | | IN DESIGN | | USED ON JOB |
11. | ------------------------ -----------------
12. | | 1..*
13. | |
14. 1 | | 1
15. ------------ 1 0..* ------------ 0..* 1 -------------
16. | JOB |-------------------------------------------------------| JOB |----------------------------| PROPERTY |
17. | TYPE | | | | |
18. ------------ ------------- 1 --------------
19. |
20. |
21. | 0..*
22. ----------------
23. | JOB |
24. | STAFF |
25. ----------------
26. | 0..*
27. |
28. | 1
29. ------------- | STAFF |
30. | |
31. -------------
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
1. **Document 1 - Initial Job Sheet at outset of a job**
2.
3.
4.
5.
6. **Job ID** **PropertyId ** **Address** **Job Type** **Design** **Parts specified in Design**
7. --------------------------------------------------------------------------------------------------------------------------------
8. 2 234 23The Elms, Andover, Hants. Summer House Summer House Type2 2 x Long side wall fittings
9. 1 x Back wall fittings
10. 1 x front wall fittings
11. 8 x window fittings
12. --------------------------------------------------------------------------------------------------------------------------------
13. 6 343 Rapid House Gardens, Essex Summer House Summer House Type8 2 x short side wall fittings
14. 1 x Back wall fittings
15. 1 x front wall fittings
16. 6 x window fittings
17. --------------------------------------------------------------------------------------------------------------------------------
18. 7 343 99 Neat Street, Chesire. Utility Shed Shed Type 1 Standard Shed
19. --- ----------------------------------------------------------------------------------------------------------------------------
20.
21.
22.
23.
24.
25. ** Document 2 - Staff on Job Record**
26.
27.
28.
29.
30. **Job ID** **Staff** **Number Name**
31. --------------------------------------------
32. 2 S1 Isla St Cloud
33. --------------------------------------------
34. 2 S8 Zak Dodd
35. --------------------------------------------
36. 2 S2 Marco Yad
37. --------------------------------------------
38. 6 S11 Sally Lam
39. --------------------------------------------
40. 6 S1 Isla St Cloud
41. --------------------------------------------
42. 7 S8 Zak Dodd
43. --------------------------------------------
44. 7 S1 Isla St Cloud
45. --------------------------------------------
46.
47.
48.
49.
50.
51.
52. **Document 3 - Final Job Record**
53.
54.
55.
56.
57. **Job ID** **Property Id** **Design** **Parts used as specified in Design** **Additional Parts used on job**
58. ---------------------------------------------------------------------------------------------------------------------------------
59. 2 234 Summer House Type 2 2 x Long side wall fittings 1 x Base Board
60. 1 x Back wall fittings 12 x filters for water pump
61. 1 x front wall fittings 1 x additional window frame
62. 8 x window fittings
63. ---------------------------------------------------------------------------------------------------------------------------------
64. 6 343 Summer House Type 8 2 x short side wall fittings 1 x additional window frame
65. 1 x Back wall fittings
66. 1 x front wall fittings
67. 6 x window fittings
68. ---------------------------------------------------------------------------------------------------------------------------------
69. 7 343 Shed Type 1 Standard Shed pack 1 x door pelmet
70.
71.
72. ---------------------------------------------------------------------------------------------------------------------------------
Task 1 – initial design
Produce an entity relationship model for the proposed database system for the house.
Produce a data dictionary for the entity relationship model.
Task 2 – data and queries.
Create all of the tables in the entity relationship model using SQL. You should provide screen dumps to show that the create statements have worked.
Populate all database tables with appropriate data. You should provide screen dumps to show that your INSERT statements have worked.
Query
Write a query that selects all of the jobs and the staffs that have worked on them. Write a query that selects the parts that are actually used on job including those specified as standard in the design for that particular job type.
Write a query that selects all the properties and jobs that have taken place at those properties.
Write a query that shows all of the jobs that have taken place for a particular design.
Task 3 - derived data
The company has said they would like to extend the database to include costing information. All parts will be allocated a price and all members of staff will be allocated a flat rate for working on a job. Show how you would extend the database. Using the extended database write a query that selects all the relevant data for a job. This should include the property, job type, design, parts, staff and cost.
Task 4 – Evaluation
Give an evaluate of how the work you have done has met the requirements of the firm.