Homework2

Out: Monday, 02 Oct, 2023(Week #5)

Due: Friday, 03 Nov., 2023 (Week #8)

Weight: 5% of the overall assessment

Submission channel: online submission on BB platform

Problem statement

Snooty Fashions is an exclusive custom fashion designer business. The Snooty Fashions Operations Database will keep track of the following:

• For each designer: a unique designer identifier and unique SSN as well as the name (composed of first and last name)

• For each customer: a unique customer’s identifier as well as his or her name and multiple phone numbers

• For each tailoring technician: a unique SSN as well as his or her name (composed of first and last name)

• For each outfit: a unique outfit’s identifier as well as the outfit’s planned date of completion and its unreasonable price

• For each fashion show: a unique show identifier as well as the date of the show and location

• Each designer designs many outfits. Each outfit has only one designer.

• Each outfit is sold (in advance) to exactly one customer. Customers can buy one or many outfits (Snooty Fashions will not keep track of customers that have not made any purchases yet).

• Each tailoring technician must work on at least one outfit but can work on many. Each outfit has at least one tailoring technician working on it but can have many.

• Snooty Fashions will keep track of the date when a tailoring technician started working on a particular outfit.

• Each designer can participate in a number of fashion shows, but does not have to participate in any. Each fashion show can feature one or two Snooty Fashions designers (Snooty Fashions will not keep track of fashion shows that do not feature Snooty Fashions designers).

Requirements

1. Create the ER diagram for the Snooty Fashions Operations Database based on the above description (1 mark).

2. Map the ER diagram for the Snooty Fashions Operations into a relational schema (1 mark).

3. Write CREATE TABLE statements to create the tables for the Snooty Fashions Operations Database depicted by the relational schema in step 2. (2 marks)

4. Use INSERT INTO statements to insert no fewer than 2 and no more than 10 records per table in the Snooty Fashions Operations Database. (1 mark)

5. Save all statements in mc3.sql