1

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

2
Contributors
1
Reply
27
Views
2 Years
Discussion Span
Last Post by AleMonteiro
0

Hi Nyck, I think your schema it's quite good.

Just some remarks: In the table(s) 'Parts Used/Specified' you don't need to have the Part_Name because you already have the Part_id.

Another thing is that you have two tables for used parts (specified and additional). Instead of having two tables for that, you could have only one table with a bit flag 'Specified'.

I'd like to say that I focused on your schema and description. I did not fully analyse the raw data.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.