TMF2034 Database Concept and Design Assignment 1

Design a database according to the description below.

Meow-meow Cakehouse is a well-known layered cake maker in Kuching. It sells various type of layered cakes in the shop. As layered cake is often baked for religious or cultural celebrations, thus they must be classified by both color and pattern. Meow-meow Cakehouse is looking for a database system to ease their daily operation. Beside storing cake information and their classification, the system must be able to store different recipes of different layered cakes accordingly to different bakers. Information such as ingredients, their quantity/measurement, cost per units must be recorded in order to computed the total cost for a layered cake.

Meow-meow Cakehouse usually open from 9am to 10pm to allow walk in customers to purchase their favorite cake in-store. They also allow pre-order of layered cake if certain cakes are not available at that time. Pre-order requires deposit of RM25. However, bear in mind that customer only allow to order cake from the already defined cakes. In addition, they sell their cake online which each cake will be charged flat shipping fee of RM7.90.

Meow-meow Cakehouse offers membership program for in-store only. Each member enjoy 10% discount of all the layer cakes. For every layered cake sold, the baker will be commissioned 5% of the cake prices and the cashier will receive 3% of commissions.

Meow-meow Cakehouse requires a database system (includes point of sale, recipe management, employee commission(excluding their payroll)) which is able to print invoices, recipe, monthly and yearly sale report, bakers commission and etc.

The following assumptions applied:

The customer could register as member without making any purchase. Annual fee is RM10 only.
A customer only requires to be registered once, either in-store or online.
The same customer could buy in store or online, however, only in-store purchase entitles discounts.
The store usually buys ingredients in bulk.
A chief can creates as many recipes as possible, but it is possible that a recipe may not end up with any cake for sale.
Besides general purpose layered cakes, there are specialized or subtype of layered cake made for religious and celebration, they can be identified exclusively by the decoration (religious), pattern (religious), color (cultural), and number of layer (cultural).
Cashiers have to login to the system in order to register his/her commission, however, chiefs’ commission is the number of cake sold (in-store or online) based on his/her recipe.

Task:

Identify relevant entities
Extract business rules, make assumptions if possible.
Identify PK, FK, connectivity and cardinalities.
Draw your Crow Foot ERD
Generate a data dictionary

Your submission should include business rules, an ERD and a data dictionary. The ERD should include entities, attributes, relationships, connectivities and cardinalities.

Method of hand-in:

A copy of printed work for each group. Each member must upload soft copy through a link provided on eLEAP

	5	4	3	2	1	0
Notation	Diagram uses appropriate crow foot ER notation. The notation is used correctly for all elements of the diagram.	Diagram uses an appropriate crow foot ER notation. The notation is used correctly for 75% elements of the diagram.	Diagram uses an appropriate crow foot ER notation. The notation is used correctly for 50% elements of the diagram.	Diagram uses an appropriate crow foot ER notation. The notation is used correctly for 25% elements of the diagram.	Diagram does not use an appropriate crow foot ER notation or uses a notation incorrectly for less than 25% elements.	Diagram does not use crow foot ER notation.
Entity			Diagram captures all entity sets necessary for a database that would satisfy the initial problem statement.	Diagram captures most entity sets necessary for a database that would satisfy the initial problem statement.	Diagram captures less than 3 of the entity sets necessary for a database that would satisfy the initial problem statement.	Diagram captures none of the entity sets necessary for a database that would satisfy the initial problem statement.
Keys	Diagram captures all attributes, primary keys and foreign necessary for a database that would satisfy the initial problem statement.	Diagram captures 75% attributes, primary keys and foreign necessary for a database that would satisfy the initial problem statement.	Diagram captures 50% attributes, primary keys and foreign necessary for a database that would satisfy the initial problem statement.	Diagram captures 25% attributes, primary keys and foreign necessary for a database that would satisfy the initial problem statement.	Diagram captures less than 25% attributes, primary keys and foreign necessary for a database that would satisfy the initial problem statement.	Diagram captures none of the primary and foreign keys necessary for a database that would satisfy the initial problem statement.
Constraints	Diagram captures all cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.	Diagram captures 75%cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.	Diagram captures 50% of the cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.	Diagram captures 25% of the cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.	Diagram captures only cardinality or participation constraints necessary for a database that would satisfy the initial problem statement.	Diagram captures none of the cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.
EERM	N/A	N/A	Diagram captures all disjoint/overlapping and completeness constraints, necessary for a database that would satisfy the initial problem statement.	Diagram captures most disjoint/overlapping and completeness constraints, necessary for a database that would satisfy the	Diagram captures some disjoint/overlapping and completeness constraints, necessary for a database that would satisfy the	Diagram captures none disjoint/overlapping and completeness constraints, necessary for a database that would satisfy the
				initial problem statement.	initial problem statement.	initial problem statement.
Relationship	N/A	N/A	All relationship are named	More than 50% relationship are named	Less than 50% relationship are named	No relationship name.
Business Rules	N/A	N/A	All business rules can be translated into ERD	More than 50% business rules can be translated into ERD	Less than 50% business rules can be translated into ERD	No business rule.

Criteria (weight)	5	3	1	Score
	Exemplary	Satisfactory	Needs Improvement	(Weighted)
Notation (x2)	Diagram uses an appropriate E-R notation. The notation is used correctly for all elements of the diagram.	Diagram uses an appropriate E-R notation. The notation is used correctly for most elements of the diagram.	Diagram does not use an appropriate E-R notation or uses a notation incorrectly for most or all elements.
Complexity (x1)	The required number of tables and foreign key relationships will be needed to implement the database.	As drawn, the required number of tables and for- eign key relationship may not be needed, but the required complexity can be achieved with minor changes.	The required number of tables and foreign key rela- tionship will not be needed. It is unclear how the pro- ject could satisfy the re- quired complexity.
Professionalism (x1)	Diagram presents a professional appearance. It could be shared with a “real-world” customer	Diagram largely presents a professional tone. It could be shared with a “real- world” customer with minor	Diagram is unprofessional. Major revisions would be necessary before sharing the document with a “real-

Grading Rubric

TMF2034—Database Concept and Design

Criteria (weight)	5	3	1	Score
	Exemplary	Satisfactory	Needs Improvement	(Weighted)
Constraints (x1)	Diagram captures all cardinality and participation constraints necessary for a database that would satisfy the initial problem statement. (Recognizing that if all relationships are legitimately many-many with partial participation,	Diagram captures most of the cardinality and partici- pation constraints neces- sary for a database that would satisfy the initial problem statement.	Diagram captures none or few of the cardinality and participation constraints necessary for a database that would satisfy the initial problem statement.