Database Written Assessment

Database Written Assessment :

1. HURDLE TASK – Question Answer session – this task that must be passed to pass the whole Assignment.
2. Answer all questions. Answers to all questions must be supplied in electronic form 
3. Submit the Word document TWICE on Moodle– Submit the document on Turnitin link and on the Moodle Assignment link. 
4. All components of the assignment must be submitted as one MS Word document, including the ER diagram and code (If you are using Visio or any other tool export the image and insert it into MS Word). 
5. Use the all digits of your student id as part of entity, attribute and constraint name. e.g.

entityNamexxxx where xxxx are the digits of your user id. 

• Please incorporate your complete student id in the ER diagram e.g. in the middle of the diagram. For examples 

• List any assumptions you have made and incorporate them in ER Diagram.  
• For Task 4 – Save all the SQL code in one txt/sql file and submit it with the submission.  
• For Task 4 – The word document must contain for each question
  • Question No
  • SQL Code
  • Screen shot of execution of SQL code on SAS Studio (including your login name/id of SAS or

Oracle)

• The screen shot must show your login identity either in Oracle or SAS and the clock. 
  • NO SQL CODE NO MARKS
  • NO SCREEN SHOTS NO MARKS

1. For Task 4
   1. Take screen shots, copy, and paste screen shots into MS word file as evidence successful operation of the SQL Statement
   1. The screen shot MUST show your login identity either in Oracle or SAS and the clock. 

1. For Task 4, 
   1. Use Oracle/SAS Studio to write SQL code without errors, according to the instructions given in task4
   1. Screen shot of all SQL code with evidence should be in MS Word file.
2. All work should be submitted to Moodle. Your submission to must contain one files MS Word contains a cover sheet with your student Id and Name, your student Id and Name in footer,  the question, code in text form and screenshot of execution and result on Oracle or SAS interface. 
3. For Task 4 – SQL Code in Text form – Your SAS PROC SQL  or Oracle SQL code must run without errors apart from those errors resulting from table and drop sequence commands for the tables and sequences that have not as yet been created. 5 marks will be deducted for each error.

Database Written Assessment Submission Instructions:

• Submit the Word document TWICE on Moodle– Submit the document on Turnitin link and on Assignment link
• All work should be submitted to Moodle. Your submission to must contain one files MS Word contains a cover sheet with your student Id and Name, your student Id and Name in footer,  the question, code in text form and screenshot of execution and result on Oracle or SAS interface.  
• The text in MS Word document should be the SQL code that SHOULD written in Oracle or SAS PROC SQL format that runs on Oracle or SAS Studio User Interface with out error

Database Requirements

HappyDiet Customer Ordering Application (Database)

HappyDiet is a privately held home delivered diet meals company, which began trading 20 years ago in Melbourne. It provides calorie controlled meals to people wanting to lose weight or just interested in a healthy diet. The company grew rapidly in its first decade, and it now has 2000 customers a week in Victoria area though it is now wanting to expand to NSW and Queensland.   HappyDiet accepts orders from customers by phone and delivers the orders in iced Styrofoam boxes 5 days a week. In Victoria, HappyDiet employs approximately 12 delivery personnel, 20 chef’s assistants’ assistants, 6 chefs, and 10 office and clerical staff. The management team includes another six people, mainly company owners. 

Currently the menu is available on the internet though customers submit orders by telephone only. For each order, the customer has to specify their card number and expiration date etc. Orders are logged into a computer as they are received.    

Each day the computer generates the total of each meal that needs to be prepared for delivery the next day.  This is sent to the kitchen for preparation. At the same time, the computer generates the list containing each customer’s order along with the customer’s address.  At the end of the day, the food to be delivered the next day comes from the kitchen and is put in the “for delivery” cool room.  At the beginning of each day the order for each customer patient is collected from the “for delivery” cool room and packed with ice in one Styrofoam box along with the customer’s name and address.  When all of the cases have been assembled, they are loaded onto trucks. Each truck has a designated area, and the driver is given the list of addresses to deliver.  

Order entry, billing, and inventory management procedures are a hodgepodge of manual and computer-assisted methods. Currently, the company uses a combination of Excel spreadsheets, and antiquated custom-developed billing software running on personal computers. The system has become increasingly unwieldy.  

Management has placed a high priority on developing a Web-based application to automate the customer ordering process. It is envisaged that customers would register with their email address and would choose a password. They would also include their credit card details which would be used to process payment. These details would have tight security. 

Once registered, a customer can log on and order their meals for the week. After logging on, they would firstly specify the date on which they want delivery. Once they specified the date, the system would ask them to specify the “weekly plan” which they wish to use for that week. 

A plan consists of 

1. The number of calories per day which can be either 1200, 1500 or 1800.
2. The number of meals per day 2 or 3. This can be (breakfast, lunch, dinner), (breakfast, lunch), (lunch, dinner). All breakfasts in a plan are the same price, all lunches in a plan are the same

price, all dinners in a plan are the same price. 

• For each plan the total price is specified by the system. 

An example of the available options is shown below. 

HappyDiet Weekly Plan

7 days, 1800 calories, breakfast, lunch, dinner $190  7 days, 1800 calories, breakfast, lunch $120  7 days, 1800 calories, lunch, dinner $140

7 days, 1500 calories, breakfast, lunch, dinner $158  7 days, 1500 calories, breakfast, lunch $100  7 days, 1500 calories, lunch, dinner $117

7 days, 1200 calories, breakfast, lunch, dinner $127  7 days, 1200 calories, breakfast, lunch $80  7 days, 1200 calories, lunch, dinner $93

Once a customer has chosen their “weekly plan”, the system takes them the choice of meals. The order in which they would choose would be breakfasts (if specified), lunches (if specified) and then dinners. Each day has 4 breakfast options. If a user has specified breakfasts, the user chooses one of the 4 breakfasts for that day. The system then takes them to the next day’s breakfast options. This is repeated 7 times for 7 days meal plan.

If the customer specifies lunches, the same processDatabase Written Assessment is followed except that it is lunches rather than dinners that are specified. For dinners and lunches, the customer is given a choice of categories e.g. Asian, Pasta, Fish, Poultry, Beef. The customer can choose a category and then choose one or several dinners from each category up to a total of 7 (for 7 days meal plan).  

Once completed the customer accepts all the plan, their credit card is automatically charged. On the bank’s approval, a receipt is emailed to the customer.  Management regularly changes the menu available for each meal. Thus, the system has to allow for easy updates of menus. When a delivery is made to the customer’s doorstep, the customer is notified via an SMS. 

Database Tasks (Total marks – 90)

Task 1 – ER Diagram (30 marks)

1. Identify the business rules given in the HappyDiet case study and given screenshots of Menu. 
   1. Identify all the business rule as a foundation to create ER Diagram
   1. Any assumptions made must be stated 

• Create an ER Diagram using Crow’s foot notation to represent the Database Written Assessment problem domain. (Use any suitable diagramming tool e.g., Visio/Word) 
  • Only maximum cardinalities are required Each entity needs to show the entity name, primary key , foreign key and at least 4 more non-key attributes
  • All M:N relationships are to be resolved into 1:M/1:M relationships  iv. Please add your Student Id as part of entity name and attributes
• Please add your complete student id as part of complete ER diagram
• MARKS WILL BE DEDUCTED IF STUDENT ID IS NOT ADDED AS PART OF (1) Entity Name, (2) Attribute and (3) COMPLETE ER DIAGRAM

Task 2 – Relational Data logical model  – RDM (10 marks)

Convert the above ER Diagram into a Relational Data logical model. 

1. All primary keys must be underlined (define Primary Key or Composite Primary key in a single line)
2. All foreign keys must be stated with bold and underlined format (Repeat each Foreign Key definition

in separate lines)

1. NO MARKS WILL BE GIVEN IF FOLLOWING FORMAT IS NOT FOLLOWED

 Task 3 – Data Dictionary (10 marks)

Create a data dictionary for the above relational data model using the following sample format. All data items must have suitable data types.

Task 4 – SQL Code (40 marks) – Sample Answer is provided at the end of the Task 4

Based on the specification you have provided in the data dictionary. Create ONE SCRIPT for i, ii and iii.

1. Write the Oracle SQL code (in ONE script) to implement the relational data logical model. [10 marks

1. Define primary keys, foreign keys, NOT NULL, CHECK and UNIQUE constraints in the CREATE TABLE statement ONLY. You must use appropriate names for your constraints. [Note: a foreign key constraint requires the existence of the referenced table]. (10 marks)  

1. (Oracle) – Choose an appropriate column to use the DEFAULT clause. Explain what is meant by

DEFAULT and why this column is suitable to take such values. (5 marks)  

OR

(SAS) – What is view in Database Written Assessment database? Create a view to display the category that contains “GREEN APPLES”.  iv. Use appropriate INSERT INTO statements to populate each table. (at least 3 rows per table) (5 marks)  

• Use DROP TABLE statements for all tables in your schema so that the database can be deleted and then created again every time the schema script is run (Please do not use CASCADE).  (5 marks)  

• Demonstrate your knowledge of the ALTER TABLE statement by using three different examples in your script and explain briefly reason of writing this alter table command. (5 marks)

• (Oracle)This section requires some additional research. Ideally, your script would include a SEQUENCE. Using appropriate resources investigate the SQL sequence feature and use it to insert a value of the surrogate key such as Category ID or Meal Type ID. Display your knowledge of the use of sequences by the use of NEXTVAL. (HINT: NextVal should be used in the INSERT INTO statement) (10

marks)

OR

(SAS) This section requires some additional research. List all the DONOR NAME AND FOOD CATEGORIES THEY ARE DENOTING IN  by using PROC SQL Select statement and Macro variable

assignment in SAS. Hint: Use PROC SQL with into and quit.  

	Task 4 – Sample Answer (Each answer should contain question no, question, code in editable form, screen
	shot of code execution and result)
Task 4 – Question 2 – Create a category table in the Oracle database with a primary key
Code CREATE TABLE category      (cat_id   NUMBER(2),       movie_category VARCHAR2(12),       CONSTRAINT movie_cat_id_pk PRIMARY KEY (cat_id));
Execution

Plagiarism 

Please submit the assignment document on TURNITIN on Moodle BIT358 Site

All used sources must be properly acknowledged with references and citations, if you did not create it. Quotations and paraphrasing are allowed but the sources must be acknowledged. Failure to do so is regarded as plagiarism and the minimum penalty for plagiarism is failure for the assignment. The act of given your assignment to another student is classified as a plagiarism offence. Copying large chucks and supplying a reference will result in zero marks as you have not contributed to the report. Copying from Youtube or other videos is also plagiarism (including transcripts). Citation in a video can be included as credits at the end.

Late Submission

Late submission of Database Written Assessment assignments will be penalised as follows:

• For assignments 1 to 10 days late, a penalty of 5% (of total available marks) per day.
• For assignments more than 10 days late, a penalty of 100% will apply. Which means, the assignment will not be marked and attract a fail grade.

Your submission must be compatible with the software (PDF/Word/Video/Zip) in Melbourne Polytechnic, Computer Laboratories/Classrooms.

Visit:https://auspali.info/

Also visit:https://www.notesnepal.com/archives/767

For solution:

WhatsApp

+610482078788

+61482073403

+61482072848

[email protected]