CMPT 354 Chapter Notes - Chapter 1: Er1 Electric Trainset, The Production Company, Tiramisu
29 May 2018
School
Department
Course
Professor
Cmpt 354 – Assignment 1
Question 1: (25 marks)
We would like to design a database for a dessert production company:
The types of desserts sold are:
- Small Pastries (Petite four), which include fruit tarts, macaroons, and cookies
- Cakes, which include tiramisu, black forest, and cheesecake
All desserts have a unit price, description, and recipe. Pastries have sweetness ratings (a number from 1 to 10) and color. Cakes
have size (6”, 8”, and 9”) and decorations (this is standard for each type of cake).
The company has five types of employees: Bakers, Front line workers, delivery boys, and Tech/Webmasters. Each department
has its unique manager who works within that department who is also responsible for hiring new staff. The bakers and front
line workers are responsible for producing, packaging, and providing quality control (to check for hairs, aesthetics, food safety,
etc.). Finally, the delivery boys are responsible for contacting and delivering to retail stores (dates of delivery times are to be
recorded). The Small Pastries are delivered to general supermarkets (such as Safeway, Superstore), whereas Cakes are
delivered to independent bakeries.
Draw an ER diagram that will manage a database of the dessert production company. Write down any assumptions made in
the design process (they must be reasonable assumptions). Be sure to simplify things as much as possible (as few entities and
relations as possible). Please use notations and symbols discussed in class and do not include notations which we forbade :)
Assumptions:
- The employee entity set has a SSN as primary key for purpose of payroll etc.
- The production company can be associated with several branches of the same supermarket (i.e. Safeway, branch: 21
and Safeway, branch: 32).
* Other solutions involving Hierarchy are also possible
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
We would like to design a database for a dessert production company: Small pastries (petite four), which include fruit tarts, macaroons, and cookies. Cakes, which include tiramisu, black forest, and cheesecake. All desserts have a unit price, description, and recipe. Pastries have sweetness ratings (a number from 1 to 10) and color. Cakes have size (6 , 8 , and 9 ) and decorations (this is standard for each type of cake). The company has five types of employees: bakers, front line workers, delivery boys, and tech/webmasters. Each department has its unique manager who works within that department who is also responsible for hiring new staff. The bakers and front line workers are responsible for producing, packaging, and providing quality control (to check for hairs, aesthetics, food safety, etc. ). Finally, the delivery boys are responsible for contacting and delivering to retail stores (dates of delivery times are to be recorded).
