Draw dependency diagram for the table shown table

Return date: 13-Apr-2012
Submission method options
Alternative submission method

Task
Part A Convert ERD to Relational Data Model (15 marks) Part A has three questions. Answer each question independently.

Regional Gardens has many nurseries, including Wagga Wagga, Bathurst, Albury, Orange, and Dubbo. Each nursery is known by its campus code, eg WW, B, A, O and D respectively. Additionally a unique nursery id is assigned to each nursery. Staff members are usually located at one nursery, but may move to another nursery on occasion.

Attributes to be included in the relational tables are nursery_name, nursery_id, nursery_address, nursery_code, staff_id, staff_fname, staff_lname, staff_employ_date.

Question A.3 (7 marks)
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.

1. Draw a dependency diagram for the table shown in Table 1. This diagram should show all the dependencies that exist in this table. (3 marks)
2. What is the normal form of this table? (1 mark)
3. The Grade Report table has to be decomposed into a set of 3NF tables. Draw the dependency diagrams for each table in this set, using only the attributes that are provided. (8 marks) 4. Draw the relational data model for the set of 3NF tables and include all necessary attributes and integrity constraints. (8 marks)

The RDM for Q4 is to be in the format:

Regional Gardens Case Study

Regional Gardens Ltd is a company that runs a number of related gardening enterprises. It has a large display garden that it opens for public inspection a number of times a year. The company also owns the Regional Gardens Nursery which sells plants and garden supplies to the public as well as providing garden advice, design and consultancy services. The directors of Regional Gardens Ltd have decided that they need to modernise their business and finally install a database system to keep track of the design and consultancy aspects of their nursery business. You have been engaged to design the database for their garden design business.

Your relational data model has to document your ERD so that it is ready for implementation. The relational data model needs to be documented in text format as described in the following format:

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)

Part A: