Task 1: Design with Database Requirements

Ocean Dew is a popular drinks company located in the central Lukewarm Kingdom. It produces several well-known drink products and sells them across the whole country. In this coursework, you are asked to design a database to support the daily operations of Ocean Dew. Please read the following paragraphs carefully and design the database with your teammates .

Requirement 1: Drinks Information

Ocean dew is capable of producing a wide variety of drinks with its current infrastructure. Currently, this company produces the following 6 drinks:

1. Pressed Coconut Milk

2. Lukewarm Coconut Water

3. Ocean Dew Mineral Water

4. Ocean Dew Beer

5. Ocean Dew Whisky

6. Peach Melon Mixed Juice

Each drink may be sold in one or more packaging designs. For example, “Ocean Dew Beer” is sold in two packaging designs called “Ocean Dew Beer can” and “Ocean Dew Beer glass bottle”, while “Lukewarm Coconut Water” is only sold in the packaging design called “Palm tree fun can”. Each packaging design is associated with a volume described in millilitres (ml) and the main material used to produce the packaging, such as plastic, aluminium or glass. In the examples given above, “Ocean Dew Beer can” has a volume of 330ml and uses aluminium as its main material for packaging, while “Ocean Dew Beer glass bottle” has a volume of 550ml and the main packaging material is glass. Note that the drink packaging does not affect the taste and components of a drink. So all “Ocean Dew Beer” taste the same no matter what the packaging design is.

Image 1 Examples of drink packaging designs (http://k2graphix.com/beverage-packaging-portfolio/)

Detailed information about each bottle/can of the drink is printed on its packaging. The information includes a unique production serial for this single bottle/can, the production date, the expiry date, a nutrition facts table and an ingredients table.

The nutrition facts table stores the total calories and a list of nutrition items per 100ml. All nutrition items should be listed. For example, if a drink does not contain any saturated fat, the item “Saturated Fat” should still be listed in the nutrition facts table. An example nutrition facts table containing all nutrition items considered in this coursework is shown below:

The ingredient table lists the main ingredients used to produce the drink. The ingredient table is different from the nutrition facts table in that the items listed are dependent on the drink. For example, “Ocean Dew mineral water” only contains “mineral water” in its ingredient table, while “Peach Melon Mixed Juice” contains peach juice, melon juice, sugar and water. Your database should allow the company to flexibly assign ingredients to drinks. An example from Tropicana (a real-world brand) is given below:

Image 2 Tropicana (https://blog.dandkmotorsports.com/tropicana-100-apple-juice-nutrition-facts/)

When  designing  your  database  for this  part,  make  sure  your  design  minimises  data redundancy. For example, think about whether an ingredients table is the same for different packaging  of the  same  drink.  If your  design  leads to  more  storage  usage,  marks will  be deducted.

Use Cases of Requirement 1

Please consider the following use cases when designing the database for requirement 1. If you cannot write working SQL queries for any one of the use cases below, it strongly indicates that your database design is flawed.

1.   List all drink packaging designs with a volume of 330 ml.

2.   Set the amount of “Saturated Fat” per 100 ml to 1g for the drink “Lukewarm Coconut Water”.

3.   List all drink packaging designs for drinks containing more than 4mg of Cholesterol per 100ml.

4.   Find out the drinks that have the most ingredients.

5.   List all drinks that contain no protein.

You do not need to include answers for these use cases in your group report.

Requirement 2: Vending Machine Information

All drinks of Ocean Dew are sold through Ocean Dew vending machines placed across the Lukewarm Kingdom. Each vending machine has a unique ID and a unique location name. For instance, a machine with ID “OD7100” is located at “Lukewarm Shopping Mall North Gate”. The company also hired several staff teams to replenish and maintain these vending machines. Each team has a unique team name, a unique phone number, a truck and a list of vending machines to maintain. Note that the lists of vending machines for staff teams do not overlap.

Your  database  should  also  keep  track  of  currently  available  drinks  inside  vending machines. Users of this database should be able to obtain the number of bottles/cans left for each drink’s packaging. To better illustrate this requirement, here is an example: the machine with  ID  “OD7001”  currently  has  0  bottles  of  pressed  Coconut  Milk, 0 cans  of  Lukewarm Coconut Water, 12 bottles of Ocean Dew Mineral Water, 1 bottle of Ocean Dew Beer, 5 cans of Ocean Dew Beer, 0 bottles of Ocean Dew Whisky, 0 bottles of Peach Melon Mixed Juice. Users of this database should also be able to track the serial numbers of each drink.

Again,  your  database  should  support  any  new  drinks  and  their  packaging  designs introduced in the future.

Use Cases of Requirement 2

Please consider the following use cases when designing the database for requirement 2. If you cannot write working SQL queries for any one of the use cases below, it strongly indicates that your database design is flawed.

1.   Find out all vending machines that have no Ocean Dew Beer left.

2.   Count the number of drinks (bottles and cans) left for each drink packaging in the vending machine with ID “5F056”.

3.   List all of the vending machines managed by the team “VM123”.

4.   List all of the production serials of the drinks stored in the vending machine “5F056”.

5.   List  all  drink  serial  numbers  that  have  passed  the  expiry  date  along  with  their corresponding vending machines. (you may assume a date in this case)

You do not need to include answers for these use cases in your group report.

Task 2: Extending the Database Design

This is an individual task based on the database design from task 1. In this task, each team member should identify a new requirement and its  related 5 use cases. Then extend the database in task 1 to satisfy the new requirement and use cases. The new requirement should have a similar complexity level to requirement 2 in task 1 and should have practical value. The use cases should have a certain level of difficulty like those use cases I listed for requirement 2. They should also have practical values. You need to write down correct SQL queries for all use cases.

If you find it hard to get ideas about new requirements, you can get inspirations by looking up online resources or checking the real-world activities related to drinks manufacturing and retailing. To further help you get started, here are some possible directions:

1. Extending the database to support logistics operations.

2. Extending the database to help manage staff members.

3. Extending the database to support drink quality checking.

4. Extending the database to manage raw ingredients for drinks.

The extension made by each team member should be put together and form a single database design and put into the group report. Each team member’s idea should be different and share no overlapping tables. However, you can build your ideas based on other team members’ tables. That is, you can add foreign keys to connect to your team member’s tables but you cannot claim that it is your work in the report. If you are modifying existing tables instead of creating new tables, make sure to highlight the changes you made in your individual report section.

All team members’ work needs to be put into the group report. Please make sure to write down your student ID and name in the report so that I can tell who contributed what idea.

!!! Not writing down your name and ID might lead to a zero mark !!!

This part is marked based on:

1. The correctness of your database extension.

2. The practical value of your extension.

3. The thoroughness of thinking displayed in the requirement.

4. Whether  the  requirement  description  is  concise  and  clear.  (Long  paragraphs  with repeating explanations should be avoided.)

5. Whether the queries of your use cases cover important operations of your requirement.

Submission

D1. Report

You  are  required  to  write  a  detailed  group  report,  called  “report.pdf”,  explaining  your database design. Please do not submit word documents directly. Export the word document to PDF first (Word, WPS and LibreOffice all support this). 5 marks will be deducted if the file format is not PDF. The file should be a valid PDF file that can be opened by Acrobat Reader or similar software.

For the database design, all ER diagrams must fit into one single page. Please do not split the diagram into several pages. 5 marks will be deducted if the ER diagram doesn’t fit one page.

Please strictly follow the  report template and answer all the questions  in  it. The  ER diagram only constitutes a small part of your marks . The majority of your marks come from the quality of your discussions and ideas.

All tables must be in the third normal form (3NF) and has no M:M or 1:1 relationships.

You can make some assumptions about the data if they are not specified in the requirements. For example, you can assume address occupies up to 200 characters, or people’s names can be up to 30 characters long. You need to indicate these decisions in the report.

D2. Script

A script called “script.sql”, which contains all CREATE TABLE statements that create the tables.

Your SQL script and your ER diagram must match (attributes = columns, entities = tables, 1:m relationships = foreign keys).

University Policy on Late Submissions

If you submit coursework after the deadline, you will be penalized:

-    5%  of  the  total  marks  available  for  the  assessment  will  be  deducted  from  the assessment  mark  for  each  working  day  after  the  submission  deadline,  up  to  a maximum of 25%;

-      Coursework received more than five working days after the submission deadline will receive a mark of zero.

University Policy on Academic Integrity

The University aims to foster a learning environment which produces students who embrace academic  integrity,  understand  that  they  must  produce  their  own  work,  are  able  to acknowledge explicitly any material that has been included from other sources or legitimate collaboration, and to present their own findings, conclusions or data based on appropriate and ethical practice.

The University will support you to understand the standards of academic integrity, while you are responsible for learning and upholding professional standards of research, writing, assessment, and ethics in your area of study. Violation of academic integrity comes in many forms, including but not limited to the following:

•   improper citation or referencing;

•   unauthorised   collaboration   with   another   person   in   the   preparation   and production of a submitted work;

•   copying g directly from other persons without their knowledge as your own work;

•   submitting  all  or  part  of the  same  academic work for two  or  more  modules without permission;

•   consciously representing another’s work or concept as your own without proper acknowledgment and citation of the sources;

•   altering data obtained by legitimate means or making up a portion or whole set of data and reporting them in your own assignment;

•   requesting another party to prepare all or part of an assignment (with or without payment) on your behalf.

Any violation of academic integrity is a serious offence and is therefore subject to an appropriate penalty. According to the individual case and the seriousness of the offence, penalties applied will vary and may include one or a combination of the following:

•   a written warning;

•   a mark penalty or a zero mark for the assessment;

•   a zero mark for the module;