RELATIONAL DATABASE

QUESTION

Question A.1 (2 marks)

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.2 (6 marks)

. Gardeners and landscapers are employed on contract with specific skills. Skills are described with a skill_id – a uniquely assigned number, a skill description and their qualifications. The skill description must also be uniquely stored in the database. The system needs to be able to list the skills for each contractor as shown below:

John Smith, Garden design, Cold climate gardens, rock gardens, Cert. Horticulture;

Jane Doe, Landscape design, Feature design, Garden planning, Adv. Dip. Horticulture – Landscaping;

Attributes to be included in the relational tables are: contractor_id, contractor_fname, contractor_lname, contractor_skill_id, contractor_qual_id, skill_id, skill_description, skill_acquired_date, qual_name.

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.

Attributes to be included 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

Part B Normalization (20 marks)

 Table 1: Plan Report Table

Table 1 shows a table called Plan Report for Regional Gardens. Answer the following questions about this table:

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:

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 C ERD and RDM (45 marks)
Represent the following problem  by means of:
1. An Entity Relationship model (25 marks), and
2. A Relational Data Model (20 marks).

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.
The garden design and consultancy service gathers a number of facts from their clients. These details include the details of the client’s block of land, its features, structures and vegetation. The designer then determines what the client is looking for in their garden plan. This could include features, such as paths, water features, rock gardens, etc. They should also be able to plan for structures, such as patios, terraces, fences, walls, etc. Both features and structures should be able to have their final size and shape adjusted when added to the garden plan. The plan should also be able to access the list of plants and shrubs that are usually available from the Regional Gardens Nursery.

Tasks:

1. Draw an ERD for the Regional Gardens Nursery database.
2. Develop an RDM for the Regional Gardens Nursery database.
Notes:
Your ERD should describe how you see the design and implementation of this database. Use a drawing tool to produce the ERD. Information about drawing tools is available on the Subject Interact Resources site.

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)

State any assumptions that you make. However any assumptions that are made need to be consistent with the assignment problem.
Rationale

This assignment has been designed for students to model data, and construct entity-relationship diagrams for a particular scenario.

Marking criteria

These criteria will be used to evaluate Assignment 1:

Part A:

• ·         Appropriate entities are identified;
• ·         The entity-relationship diagram is consistent with the relational tables;
• ·         The primary keys are unique;
• ·         The model eliminates redundancy and insertion and deletion anomalies;
• ·         Foreign keys are identified and correct;
• ·         Alternate keys are identified and correct;
• ·         Attributes are in correct tables.

Part B:

• ·         Appropriate entities are identified;
• ·         The dependency diagram is consistent with the relational tables;
• ·         The primary keys are unique;
• ·         The model eliminates redundancy and insertion and deletion anomalies;
• ·         Attributes are in correct tables.

Part C:

• ·         Appropriate entities are identified;
• ·         The entity-relationship diagram is consistent with the relational tables;
• ·         The primary keys are unique;
• ·         The model eliminates redundancy and insertion and deletion anomalies;
• ·         Foreign keys are identified and correct;
• ·         Alternate keys are identified and correct;
• ·         Attributes are in correct tables.

Marking criteria

Approximate value of marks subtracted for the following errors in Part A:

• ·         Missing a table from an M:N relationship – 1 mark each
• ·         Wrong or missing primary key – 1 mark each
• ·          Relational tables not matching ERD 1 mark each
• ·         No mention of foreign key 1 mark each
• ·         Missing or incorrect placement of attribute 1 mark each

Approximate value of marks subtracted for the following errors in Part B:

• ·         Missing a dependency in a relationship – 1 mark each
• ·         Wrong or missing primary key – 1 mark each
• ·          Relational tables not matching dependency diagram 1 mark each
• ·         Missing or incorrect placement of attribute 1 mark each

Approximate value of marks subtracted for the following errors in Part C:

• ·         Missing a table from an M:N relationship 3 marks each
• ·         Wrong primary key 2 marks each
• ·         Relational tables not matching ERD 1 mark each
• ·         No mention of foreign key 1 mark each
• ·         Missing or incorrect placement of attribute

JC86

But you can order it from our service and receive complete high-quality custom paper.  Our service offers “Information Technology”  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”