代写COMP9120 Database Management Systems Assignment 1代做留学生SQL 程序

日期：2024-03-30 03:11

COMP9120 Database Management Systems

Assignment 1: Conceptual Modelling & Logical DB Design

Group assignment (15%)

Introduction

The purpose of this assignment is to provide you with experience in conceptual and relational database modelling. You are given adomain description for the Fine Food Kitchen. There are 2 high level tasks in this assignment:

•     Create an Entity Relationship Diagram (ERD) that captures the business concepts and requirements conveyed in this description,

•     Translate your ER diagram into a logical database design including relational database schema creation, key constraints and integrity constraints.

This is a group assignment for teams of 3 people per group. You must be enrolled in an assignment group on Canvas. You must inform. the unit coordinator if you have not formed a group by the end of Week 3.

Please also keep an eye on your email and Ed for any announcements that maybe made.

Submission Details

The submission of your solution is due at 11:59pm on Sunday 31/03/2024 (Week 6). You must submit the items for submission (detailed below) via Canvas.

Items for submission

Please submit your solution to Assignment 1, in the ’Assignment’ section of the unit’s Canvas site by the deadline, including the following four files:

1.    Firstly, you should submit an assignment coversheet as a PDF document (.pdf file suffix) which is available for download fromthis linkon Canvas.

2.    Secondly, you are  required to submit your conceptual  model in the form of an  E-R diagram using the lecture notation, formatted as a PDF document (.pdf  file suffix). Please justify your choices for entity types, relationship types, attributes, primary keys, constraints and design specialities.

3.    Thirdly, you should submit an SQL file (.sql file suffix) containing all DDL statements necessary to fully instantiate a working database based upon your ER diagram, and DML statements to populate each relation. Your file should run without errors in PostgreSQL 16.2. You can annotate your statements using ‘--‘ at the start of lines for comment. You should group your statements for ease of reading (e.g. by keeping all table constraints within the relevant CREATE TABLE statement rather than declaring them externally, if possible).

4.    Lastly, you should submit another pdf document (.pdf file suffix) including the Relational Model (RM) diagram that provides a visual model of your database schema. The following figure summarises the syntax to use for the RM diagram:

Task 1: Domain Description for Entity Relationship Diagram (ERD) Modelling

The Fine Food Kitchen (FFK) is a specialty coffee roaster and cafe in Sydney that has operated since 2017. The company has recently hired you as a database consultant. Your first task is to design a conceptual ER model for their database system that represents the customer-centric elements of the data held within their business.

FFK customers order food and drink from a menu that is season dependent (i.e. a menu for summer may be different to a winter menu). They can choose to either dine-in, takeout, or have their orders delivered to a nominated address. Customers who wish to dine-in must be able to make a reservation, based on available date, time, and number of guests. For a reservation to be accepted successfully, the system needs to be able to keep track of all table related information, including availability (based on bookings already made), allowable seating capacity, and table location (inside or outside). Each reservation is given a maximum limit of one and a half hours. Reservations can show that they are either confirmed or cancelled.

The FFK menu offers a wide range of breakfast and lunch options (e.g., French toast, eggs benedict, and poke bowl), along with coffee, fresh juices and frappes. An order should be designed to ensure customers can easily find, select, and customise menu items. Customers making a reservation or placing an order must register their basic details such as first name, last name, email address and contact number. Customers must be able to browse through menu items by name, description, category (breakfast or lunch), price. They should be able to easily find complementary items (i.e., free items that come with ones already ordered). For coffee orders, the system must allow customers to select the type (espresso,latte, cappuccino, long black, cold brew) and milk kind (whole, skim, soy).

Customers requesting for a delivery must nominate an address. For delivery, a valid address, date and time must be specified. Customer must also be able to add any special instructions for the delivery staff. One or more suburbs may be allocated to any delivery staff. The same delivery staff may deliver multiple orders belonging to different customers. The database must capture each delivery staff’s name, age, and salary. The total charge of an order will be calculated as the sum of the charge of each order item corresponding to the menu item multiplied by its quantity, plus any applicable delivery cost.

Customers must specify a payment method to pay for their orders: by either cash and/or credit card. All payment details for each order are recorded for accounting purposes. For credit card payment, the card number, cardholder name, card expiry date, and card verification value (CVV) must be recorded.

Task 2: Relational Database Design & Modelling

Your second task is to design and create a relational database schema based on the Entity Relationship Diagram (ERD) modelled from the first task. In particular, your solution should include:

•     Tables  and attributes with  appropriate data types to capture all information in the model (please use the same names as in your ER diagram for naming tables and attributes);

•     Appropriate PRIMARY KEY, UNIQUE, FOREIGN KEY constraints for all tables;

•     Correct foreign key specifications including ON DELETE clauses where suitable;

•     Appropriate additional integrity constraints expressed by means of NOT NULL or CHECK clauses;

•     INSERT statements to populate each relation with at least one record, to demonstrate a database instance consistent with the ER model.

Additional details

In addition to the model captured through your ER diagram, the following details apply:

1.    Attributes representing names should always have values.

2.    The seating capacity of a table should always be between 4 and 8 people.

3.    Fields in a tuple related to dates and/or times must have values.

4.    Delivery staff should always be older than 21 years old and earn a salary greater than zero.

5.    An order should always have a total cost of greater than nil, but not exceed $5000.

6.    The charge and quantity for an order item, and the price for a menu item should always be greater than zero.

7.    Customers must have a specified email address.

Escaping PostgreSQL keywords in DDL

If you need to escape PostgreSQL keywords like “Table”, you will need to use double quotes.

e.g. CREATE TABLE “Table” (…);

Q&A

Q: How to draw the link from foreign key in a table to its referenced candidate key in another table if the foreign key contains more than one attributes?

A: You should draw it in a similar way to the following RM diagram (specifically, see the Sell table). You can use any of the available tools such asdraw.io, Visio,Lucidchart,Excalidraw, etc. to draw your diagram.

Marking

This assignment is worth 15% of your final grade for the unit of study. Your group’s submission will be marked according to the attached rubric (see last section of this assignment description).

Group member participation

If members of your group  do not contribute sufficiently, you should alert the unit coordinator as soon as possible. The course instructor has the discretion to scale the group’s mark for each member as follows:

Percentage of contribution	Proportion of final grade received
< 5% contribution	0%
5 - 10% contribution	20%
11 - 15% contribution	40%
16 - 20% contribution	50%

21 - 24% contribution	60%
25 - 28% contribution	80%
29 - 30% contribution	90%
> 30% contribution	100%

Note: The above table assumes that each group will have 3 members, so, on average, around 33% contribution is expected from each member of the group. In special case, if the group has less than 3 members then the contribution percentage will be adjusted  accordingly. You must justify your contribution percentage by providing a detailed explanation of your individual contribution on the assignment coversheet mentioned before. You must also record and maintain a diary of your group meetings and discussions on Canvas. Furthermore, we may run random face-to-face interviews to understand and justify your contribution, if needed.