Convert ERD to Relational Data Model
Make a Relational Data Model to implement a business state that is explained by using an Entity Relationship Diagram and text. You are to use only the attributes listed in each question. You might add associative tables, if the implementation needs it.
Each of the RDM explanations is to be in text format as shown:
Table_Name( attribute_1, attribute_2, attribute_3, … ,attribute_x)
Primary Key (attribute_1, attribute_2)
Foreign Key attribute_4 References Table_Name_2 (repeat for each foreign key)
Alternate Key (attribute_5, attribute_6) (repeat for each alternate key)
Question 1: Bluegum Childcare has numerous nurseries, comprising Wagga Wagga, Bathurst, Albury, Orange, and Mudgee. Each nursery is known by its campus code, example: WW, B, A, O and M correspondingly. Additionally a unique nursery id is assigned to each nursery. Staff members are generally situated at one nursery, however might move to the other nursery on occasion.
Attributes to be comprised in the relational tables are nursery_name, nursery_id, nursery_address, nursery_code, staff_id, staff_fname, staff_lname, staff_employ_date.
Question 2: Handymen are employed on contract with specific skills. Skills are explained with a skill_id – a uniquely assigned number, a skill explanation and their qualifications. The skill description should as well be uniquely stored in the database. The system requires being able to list the skills for each contractor as illustrated below:
John Smith, Garden design, lawn mowing, plumbing, Cert. Plumber;
Jane Doe, Painting, carpentry, electrical wiring, Adv. Dip. Industrial Electronics;
Attributes to be comprised in the relational tables are: contractor_id, contractor_fname, contractor_lname, qual_id, skill_id, skill_description, skill_acquired_date, qual_name.
Question 3: Contractors can work on many projects at any one time. One staff member supervises each project. Contractors can stop and restart work on a project and the system must record the details of starting and stopping work on the plan for each contractor.
Attributes to be comprised are staff_id, staff_fname, staff_lname, contractor_id, contractor_fname, contractor_lname, plan_id, plan_name, contractor_work_start_date, contractor_work_end_date