$7$COM$1082$ Multi$-$User Database Systems Case Study A database is to be constructed for the Hatfield Apple Tree Suppliers $($HATS$).$ HATS sells 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 and the price of the tree. Varieties have different attributes such as the colour 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 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 unready to dispatch until a particular date and this needs to be recorded. The system generates an ID for each tree. Customers select individual trees from the HATS stock, listed on the database, these are delivered at a particular time of year but can be ordered at any time. Apart from the total cost, the date and time each order was made should also be recorded so that they can be serviced in the ordered sequence, a unique ID is assigned to each order. A customer can select any number of trees on one order, up to a maximum of $5.$ A customer can place a number of orders. Customer registration details are held on the database for ease if they re$-$order Hatfield Apple Tree Suppliers in the future. The customer is asked to supply their email address, delivery address, including postcode, phone number and the system records the date they register and generates an ID for each customer. 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 a description of the tree characteristics this rootstock provides $($further details at www$.$rhs$.$org.uk$/$advice$/$profile$?$pid$=359)$ M$27$ Extremely dwarfing, height to $1\mathrm{.}5$m M$9$ Dwarfing, height to $2\mathrm{.}7$m M$26$ Dwarfing, height to $3\mathrm{.}6$m MM$106$ Semi$-$dwarfing, height to $4$m MM$111$ Vigorous, height to $4\mathrm{.}5$m M$25$ Very vigorous, height to $6$m Variety: Each variety has a name and is described by the following: o the pollination group: $(1($early$)$ to $8($late$)$ o the fruiting season: Early, Mid or Late o whether the fruit is suitable for cooking, or eating $($or both$)$ o the size of the fruit $($Large$,$ Medium, or Small$)$ o the majority colour of the fruit o the yield, the amount of apples produced $($Heavy$,$ Average, Light$)$ Your Tasks are: $1.$ Design the database using a logical entity$-$relationship $($ER$)$ diagram in UML notation. The multiplicity of relationships should be shown in the diagram. A list of the main attributes of each entity and relationship must be provided separately. State the membership class $($participation$)$ of the appropriate entity types. Underline all primary key attributes. $2.$ Convert your ER diagram to a relational database schema $($i$.$e$.,$ a set of relations$/$tables$)$ in your answer document. Ensure the relations are fully normalised. Underline primary keys. $3.$ Implement this database in an Oracle database. Create the tables that you produced in $(2).$ Give appropriate data types to the attributes. Create a proper primary key $($and foreign key constraints if necessary$)$ for each table. Insert some records into each table. You must make up at least $10$ customers, $5$ rootstocks, $5$ varieties, $15$ trees. You should also have more than $5$ orders in the database. $4.$ In your Oracle database, design the following queries: a$)$ A customer has an existing tree that has a "late" fruiting season, they wish to complement this with an early fruiting variety. Provide a list of all apple varieties which fruit early. The report should give the name of the Variety, the fruit colour and the yield. b$)$ Provide a customer with a list of all trees HATS have in stock, that grow on M$9$ rootstock. Order the output starting with the most expensive. The report should provide the Name of the Variety, the price and the Tree$\_$ID$.$ c$)$ Write a query that returns the number of trees placed on any order. d$)$ List all orders in which more than one type of tree has been requested. The report should contain the Order$\_$ID and how many different tree types were ordered. Provide meaningful column headings and display the report by most types first. e$)$ List all trees which consist of varieties that have a pollinate group of $1.$ List the Variety name and tree number. f$)$ List all customers who have placed more than one order. The list should be a single column with a meaningful heading More than $1$ order placed and ordered alphabetically as presented. The format of the names should be Family name, Given name. The output will have the following format: $---------------------------------$ More than $1$ order placed Smith, John $---------------------------------$ Please note: You may make any assumption you deem necessary $($e$.$g$.$ regarding the choice of key attributes, etc.$).$ However, you must justify your decisions, which should not be in conflict with the original requirement descriptions.