Assignment 2 consists of two tasks as laid out in the following
Specification
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 )
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
2. 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
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
We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.
Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.
Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.
Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.
Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.
Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.
We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.
Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.
You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.
Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.
Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.
You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.
You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.
Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.
We create perfect papers according to the guidelines.
We seamlessly edit out errors from your papers.
We thoroughly read your final draft to identify errors.
Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!
Dedication. Quality. Commitment. Punctuality
Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.
We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.
We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.
We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.
We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.