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 Tasks

For Referral Assignment 2, you need to do the following:

1. Write out the Relational Schema for each of the 7 entities detailed

• These should use the format as defined in this example (not taken from the specification) underlining the primary key attributes and marking the foreign key attributes with an asterisk.

    Employee ( Employee_Number, Name, Address, Department*)

• Use the same names for the Relational Schema statements as are specified in the E-R Diagram.

• Do NOT add or remove any attributes, specifically, do NOT replace composite Primary Keys with a Simple Primary Key

• Using your Oracle account, create the table structures as reflected in the diagram and your Relational Schema

• Again, use the same names for the CREATE TABLE statements as are specified in the E-R Diagram these should not clash with any existing tables you have created so far, however, if you have created your own tables with the same name, rename them prior to starting this task.

• Ensure that you create and code the following:

• All attributes, labelled exactly as specified in the E-R diagram
• Suitable datatypes with sensible sizes and lengths, and reflecting the nominal or quantitative nature of the attribute
• All PRIMARY KEY and FOREIGN KEY constraints
• Use NOT NULL constraints where appropriate
• Provide CHECK constraints where appropriate
• There are no UNIQUE constraints expected

Figure 1 – Finalised E-R Diagram

Submission Requirements

Upload a PDF document to CANVAS, it should include:

• A title line including “Assignment 2” followed by your ORACLE USERNAME
• Your 7 Relational Schema statements
• Screenshots of the CREATE TABLE  SQL commands you used to create the final version of each of the 7 tables

Having created the tables in your ORACLE account, please ensure you do not drop them from your ORACLE account until the end of the course.

Marking Schema

Relational Schema:

2 marks per correctly defined relational schema, maintaining entity and attribute names and utilising underscore and asterisk notation                                                                                                                                                 

Create Table statements (considering each table)

1 mark for conforming to table and attribute names                                                                                      

2 mark for each correct Primary Key specification                                                                                        

3 marks for each correct Foreign Key specification                                                                                       

4 marks for sensible datatype definitions

Create Table statements (considering all tables)                                                                                          

19 marks for sensible use of NOT NULL and CHECK CONSTRAINTS

If you fail to record your username on the submission document, 5 marks will be deducted from your total.

If the tables do not exist in your ORACLE Account, NO marks will be awarded for the CREATE TABLE element of the assignment.                                                                                                                                                                   

                                                                                                                                          MAX TOTAL MARKS = 100