ICT330: Transform the ERD in Figure Q3(a)(i) into a logical model: Database Management Systems Assignment, SUSS, Singapore

Question 3 (35 marks)
(a)
(i) Transform the ERD in Figure Q3(a)(i) into a logical model.

(ii) Refer to the description of the columns for the column definition, and then write the appropriate SQL statements to implement the tables, derived from the ERD in Figure Q3(a)(i). List your SQL statements in the order that they should be executed.

All columns are required. Description for columns in the tables as follows:

• orderNumber is a whole number starting from 1 with step increment 1 and
is auto-generated.

• orderDateTime is datetime and has the default value the current date and
time.

• runningNumber is a whole number, and starts from 1 with step increment
of 1 for every new order. The auto-generation of runningNumber will be
implemented using a trigger. Simply insert the value as specified in the
given data as you are not expected to code the trigger at this point.

• qty is a whole number greater than 0 with a default value 1.

• mealCode is exactly 3 characters starting with one letter (A-Z) followed by
2 digits (0-9).

• price is a decimal number and is between 20 and 100.

• itemCode is exactly 3 characters starting with two letters (A-Z ) followed
by 1 digit (0-9).

• name is at most 80 character long.

• category is exactly 12 character and takes one of these values: soup,
appetizer, main, dessert, hot beverage.

• period is exactly 6 character and takes one of these values: lunch, dinner.