Question

1. Superior Bake Shop sells a variety of baked goods online. Attributes of Baked Good include ProductID, ProductName, Product Category, Weight, and Price. Attributes of Customer include CustomerID, CustomerName, and CustomerAddress (composed of Street, City, State, and ZipCode). Customers place orders with Superior BakeShop. Attributes of order are OrderID and OrderDate. All customers have placed at least one order and customers may place many orders over time. Each order is placed by a single Customer. Orders can include one or more baked good. A baked good can be included in one or more order. Superior Bake Shop keeps track of the quantity of each baked good that is included in an order.

2. Blue Orchard Bake Shop (BOBS) offers a variety of baking workshop. BOBS keeps track of the workshops it offers as well as its teachers and students. Attributes of workshop include WorkshopID, Date, Time, and Fee. Attributes of teacher include TeacherID, TeacherName, (composed of FirstName, MiddleInitial, and LastName), PaymentPerWorkshop, and Skills. Many teachers have more than one skill. Attributes of student include StudentID, StudentName (composed of FirstName, MiddleInitial, and LastName), StudentAddress (composed of Street, City, State, and ZipCode), DateOfBirth, and Age. Students can participate in more than one workshop, but must participate in at least one workshop. Each workshop can have multiple students, but must have at least one student. All workshops are taught by a single teacher. Teachers can teach any number of workshops including zero in the case of a new teacher who has not yet offered a workshop.

3. Main Street Catering has residential, business, and school clients. Attributes of residential clients include ClientNumber, ClientName (composed of FirstName, MiddleInitial, and LastName), ClientAddress (composed of Street, City, State, and ZipCode), and NearestCrossStreets. For each residential client, the two nearest cross streets are stored. Attributes of business clients include ClientNumber, BusinessName, ClientAddress (composed of Street, City, State, and ZipCode), and AnnualCateringBudget. Attributes of school clients include ClientNumber, SchoolName, SchoolDistrictCode, and ClientAddress (composed of Street, City, State, and ZipCode). Some residential clients are also business clients, and some residential clients are also school clients. Main Street Catering has a few clients who are neither residential, nor business, nor school clients.

Draw a conceptual data model to model the scenario in each problem. Use the model constructs (e.g., the material on the E-R model and the enhanced E-R model) from chapters 2 and 3 as appropriate and follow the modeling conventions used in the example problems in the online lessons. Do not use the modeling conventions shown in Figure 2-22 of the Hoffer textbook which illustrate a data model in Visio notation. Be sure to include all appropriate completeness constraints, disjointness constraints, and subtype indicators in your conceptual data models.

Answer 1

The generated ERD’s have below symbols used in it:

Please find below the solutions for given problems:

1. Bake Shop ERD:

Entities: The system will have below entities with attributes:

· Product(ProductID, Product_Name, Product_Category, Weight, Price)

· Customer(CustomerID, CustomerName, CustomerAddress (Street, City, State, and ZipCode))

· Order(OrderID, Order_Date)

Relationship: Below are the relationships in the entities

· Product can be added in many Orders and an order can have many products. It is M:N relationship.

· Customer places one or many order but an order has one customer only.

Conceptual data model: Please find below the data model:

2. Blue Orchard Bake Shop (BOBS) ERD:

Entities: The system will have below entities with attributes:

· Workshop(WorkshopID, Date, Time, Fee)

· Teacher(TeacherID, TeacherName (FirstName, MiddleInitial, LastName), PaymentPerWorkshop)

· Skill(Skill_id, Name, Description)

· Student(StudentID, StudentName (FirstName, MiddleInitial, LastName), StudentAddress (Street, City, State, ZipCode), DateOfBirth, Age)

Relationship: Below are the relationships in the entities

· Workshop must have exactly one Teacher whereas a Teacher can have taught in none or many Workshops.

· Student can join atleast one or many workshops.

· A workshop must have atleast one to many Students.

· A teacher must have atleast one to many Skills.

Conceptual data model: Please find below the data model:

3. Main Street Catering ERD:

Entities: The system will have below entities with attributes:

· Client (ClientNumber, ClientAddress (Street, City, State, and ZipCode))

Client has below sub entities, where the inheritance is of Overlapping type as a client can be of two types or can be of none of the below type:

· Client -> ResidentialClient(ClientName (FirstName, MiddleInitial, astName), NearestCrossStreets (multivalued))

· Client -> BusinessClient(BusinessName, AnnualCateringBudget)

· Client -> SchoolClients(SchoolName, SchoolDistrictCode)

Conceptual data model: Please find below the data model: