Objective

Design a database solution to a concisely defined requirement. $($P$7,$ PLO$3)$

Instruction

This is an individual$-$based Assignment

Coursework Details

Assessment Brief

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, which determine the size of the tree when mature. Varieties have different attributes such as color and size of fruit, when they need to be pollinated, when they 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. Growing from seed both takes longer and cannot guarantee the variety is maintained. A tree may also be unavailable to dispatch until a particular date, as it needs more growing time, and this needs to be recorded.

The company grows batches of trees with the same features. Customers select trees from the HATS stock batches and can order a quantity of each, as listed on a catalog of all tree batches, these are delivered at a particular time of year, but can be ordered at any time. 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$.$ 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 directly at the time of ordering yet, but a query will be able to provide information as to whether enough stock is held to service all requests, a manual response can then be used to inform customers of trees that are unavailable. Additional Information:

$-$ Rootstock:

Types of rootstock on which new apple trees are grafted to produce different sizes of full grown tree, these are specified rootstock types $($further details at www$.$rhs$.$org.uk$/$advice$/$profile$?$pid$=359)$

M$27-$ Extremely dwarfing $-$ Good for small gardens with fertile soil, fruits after $\backslash (\backslash$mathbf$\{2\}\backslash )$ years

M$9-$ Dwarfing $-$ Good for small gardens, fruits after $2-3$ years

M$26-$ Dwarfing $-$ Good for small containers, fruits after $\backslash (2-3\backslash )$ years

MM$106-$ Semi$-$dwarfing $-$ All forms, tolerant of range of soils fruits after $3-4$ years

MMII$1-$ Vigorous $-$ Standards and semi$-$standards, tolerant of range of soils fruits after $3-4$ years

M$25-$ Very vigorous $-$ Standards, tolerant of range of soils fruits after $5-6$ years

$-$ Variety

$-$ 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$)$ For Assignment $1,$ you need to do the following:

Write out the Relational Schema for each of the $6$ 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

$]$ Submission

All coursework should be submitted before or on the dateline, based on the provided guidelines and formats.

Dateline

All submissions are due on $[25$ November $2024].$ Late submissions will not be accepted. All submissions need to be presented individually in $[$Date for the presentation $($if related$].$

Deliverables

All submissions will be through the SEGi Blackboard. Any submission outside of the SEGi Blackboard will NOT be considered.

$[1.$ Format:$**$

$-$ Your report should be in Microsoft Word or PDF format.

$-$ Use a standard font $($e$.$g$.,$ Times New Roman, Arial$)$ in size $12.$

$-$ Double$-$space your document with $1-$inch margins on all sides.

$2.$ File Naming:$**$

$-$ Name your file in the following format: 'LastName$\_$FirstName$\_$DB$\_$Assignment.pdf$\text{'}($e$.$g$.,$

'Doe$\_$John$\_$DB$\_$Assignment.