Assignment 1 (15%)
Due date: 9.30am Friday, April 26th 2013
AIMS AND OBJECTIVES:
? to represent a problem description given in natural language as an (Enhanced) EntityRelationship model;
? to convert the ER/EER model into a relational data model;
? to apply Normalization techniques (Bottom-Up approach) on the user-defined forms; ? to implement a relational database system (using ORACLE11g).
This is an individual Assignment. You are not permitted to work as a group when writing this assignment.
Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is of your own. The Department of Computer Science and Computer Engineering treats plagiarism very seriously. When it is detected, penalties are strictly imposed.
No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See the departmental Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.
All 4 tasks are to be submitted in hard-copy format to the relevant submission box on the first floor of the Beth Gleeson Building by 9.30am Friday April 26th, 2013.
Students are referred to the Department of Computer Science and Computer Engineering`s Handbook and policy documents with regard to plagiarism and assignment return, and also to the document on ‘Academic Misconduct’ distributed in the first lecture and attached to the syllabus.
‘Eat Well’ Food Store
‘Eat Well’ is a restaurant which serves fresh food for healthy living. It serves not only customers in the restaurant but also caters for small events and occasions.
The company currently records its data manually, keeping records in hardcopy, with individual files being held for different events/occasions. This process is slow, laborious and not very secure. As repeating fields and human error are being attributed to most of the errors occurring, this reflects badly on the company’s image. The company would therefore like to turn its manually kept information into a computerized database to help automate processes. In addition, at this point in time due to the large amount of manual transactions, there is a large cost associated with ordering extra quantities of stock. The manager would like to reduce the losses and cut down unwanted costs and expenses by keeping a stock count for each item, thereby maintaining up to date information and reducing expenditure.
To do this effectively the manager has decided to automate all administrative duties in relation to the process of recording the catering events and occasions. To request catering a person or business approaches ‘Eat Well’ with the event/occasion details. The manager of ‘Eat Well’ liaises with every new client to find out the relevant details. This information is captured on an ‘Eat Well’ Client Form (Appendix 1). The information captured in this form includes the client name (personal/company/school etc), client address, contact telephone number, and name of the person in charge (if any).
When a client wants to place an order for a particular event, he/she can simply fax a form with the details of the order. This form is called ‘Eat Well’ Client Order Form (Appendix 2). The information captured in this form includes: the number of sandwich platters ordered, the type of ingredients for the sandwiches, and the client name, address, contact telephone number, and name of the person in charge. The manager accepts the order and verifies it by making sure that the client details matches his client list records. Depending on stock availability the order is fulfilled. Each platter ordered normally consists of different types of sandwiches. The ingredients for these sandwiches can be categorized into cold meats (such as chicken, salami, tuna, etc.), different types of breads, salad toppings, cheeses, and sauces. A client (except for ‘schools’ as described below) can benefit from a discount of 5% of the total amount if either this client is a regular customer or this client places a bulk order of at least $60. Once the whole order is completed, the manager sends an invoice to the client.
The manager also wants to keep track of stock level so that an order is placed with the supplier in case there is shortage of stock. The supplier’s details consist of name, address, contact number, the ingredients that they supply and the price. Note that the same item can be supplied by different suppliers, and an order may include one or more items from the same supplier.
Due to their increasing popularity, ‘Eat Well’ food store has also started taking some school contracts. Working parents often find it difficult to prepare healthy lunches for their children, so their teachers can order food for them from ‘Eat Well’ store at a discounted price. This information is captured in the ‘Eat Well’ School Order Form (Appendix 3). This School Order Form is different from the previous Client Order Form, since ‘Eat Well’ provides special selections for school children. Each school has a teacher in charge of filling in this form. Pupils’ preferences are supplied to the teacher by their parents. Information supplied on this form includes the style of sandwich ordered, the quantity, the total price and the teacher in charge of ordering. Each sandwich combo comes with a piece of fruit and a 300ml carton of milk. The food is generally ordered in the morning, at least a couple of hours before lunch time, this may vary from school to school. The current tax exemption for schools is 6% of the total amount purchased and is subject to change almost every year. Payment can be made at the time of collection or an invoice can be sent to the school.
Your task is to design and implement a database system which handles the catering services of ‘Eat Well’ food store.
1. You are required to develop an EER model for the above problem description. The model should contain all necessary information such as entities, attributes, primary keys, relationships (including specialization/generalization if any), cardinalities, and participation (including (min, max)). Any assumptions should also be clearly stated. If assumptions are made, it is important to make sure that the assumptions reflect the real practice in a catering service food store database system and do not contradict with the problem description above.
2. Transform the ER/EER model to the Relational tables, making sure you show all the steps. The final set of tables should contain necessary information such as table names, attribute names, primary keys and foreign keys.
3. a) Implement the tables in the ORACLE Relational DBMS. When creating tables make sure you choose appropriate data types for the attributes and specify any null/not null or other constraints whenever applicable.
b) Write one insert statement for each of your tables using realistic data. Make sure you take into consideration all the necessary constraints.
4. Apply Normalization Techniques (Bottom-Up approach) on Appendices 1,2 and 3 and compare the final tables with the tables you have generated for Task 2. You need to discuss the differences between the results of the two approaches.
? Your (Enhanced) Entity-Relationship Model for the proposed database (Task 1);
? The transformation steps from your EER Model to your final tables (Task 2); Make sure to show each step of the transformation, and the final transformation tables;
? The DDL implementation for the ‘Eat Well’ Food Store’s’ tables from Task 2 (create table statements), and the required insert statements (Task 3a and 3b respectively).
? The steps and final results of applying Normalization technique on the user-defined forms (in the appendices) and some discussions on the result (Task 4).