Hatfield Apple Tree Suppliers

Hatfield Apple Tree Suppliers (HATS) sell a wide variety of apple trees of differing varieties. The plants are of different ages and grown on different rootstock types, the rootstock type determines the general size of the tree when mature (see details below). Varieties have different attributes such as colour and size of fruit, at what time of the year they need to be pollinated, when they produce fruit and whether they are a cooking variety, an eating variety or both. All new apple trees are grown by grafting part of an existing variety of tree to a rootstock. This is the only way to maintain the variety. A tree may also be unready to dispatch until a particular date and this needs to be recorded.

The company has become very successful and has moved to a larger site, this means that rather than growing individual trees and recording them with individual identification numbers, it grows batches of trees on the same day grafted from the same variety and rootstock. Customers select trees from the HATS stock batches and can order a quantity of each, as listed on the database. The date and time each order was made should also be recorded, so that they can be serviced in ordered sequence. A customer can select any number of trees without limitation on one order. A customer can also place any number of orders. Customer registration details are held on the database for ease if they re-order in the future. The customer is asked to supply their delivery address, including postcode, phone number and the system records the date they register.

The system is unable to manage stock control at the time of ordering yet, but a query will provide information as to whether enough stock is held to service all requests, so this does not need to be reflected in the E-R diagram.

Additional Information:

· Rootstock:

Types of rootstock on which new apple trees are grafted to produce different sizes of full grown tree, these are specified types as listed below with description of the tree characteristics this rootstock provides (further details at

www.rhs.org.uk/advice/profile?pid=359

)

M27 – Extremely dwarfing, height to 1.5m

M9 – Dwarfing, height to 2.7m

M26 – Dwarfing, height to 3.6m

MM106 – Semi-dwarfing, height to 4m

MM111 – Vigorous, height to 4.5m

M25 – Very vigorous, height to 6m

· Each variety has a name and has the following attributes:

· the pollination group: (1 (early) to 8 (late)

· the fruiting season: Early, Mid or Late

· whether the fruit is suitable for cooking, or eating (or both)

· the size of the fruit ( Large, Medium or Small )

· the majority colour of the fruit

· the yield, the amount of apples produced ( Heavy, Average, Light )

Assignment 1
For assignment 1, you are required to undertake the following:
· Evaluate the specification provided above
· Construct an Entity-Relationship (E-R) Diagram using the Crow’s Foot notation that reflects this specification
· Consider all relevant entities identified in the specification
· Identify all relevant attributes
· Consider for of all relationships, their:
· Connectivity
· Names and Direction
· Participation
· Cardinality
· Indicate all Primary and Foreign Keys (creating Foreign Key attributes if necessary)
· Resolve any M-M relationships with the use of appropriate link/hidden entities
· Draw your final E-R Diagram using a suitable graphics package such as draw.io or lucidchart.com
· Export your final diagram in format
The E-R Diagram must confirm to the format example below.

Created on lucidchart.com

Created on draw.io

Submission Instructions
· UPLOAD the PDF to CANVAS
· Any assumptions you make must be provided as comments associated with your submission, either on the diagram or in a separate text file.
Marking Schema
1 mark per correct identification of entity including link/hidden entity
2 marks per correct attribute list
1 mark for each correct Primary Key identified
2 marks for each correct Foreign Key identified
1 mark for each correct relationship connection
2 marks for each correct connectivity
2.5 marks for each correct participation
12 additional marks available for detailing upto 3 assumptions which affects which
· relationship connection
· cardinality
· participation
· attribute list
is chosen, this must include a reason why.
MAX TOTAL MARKS = 100