Database

Systems Project

BEST CAKES : BEST cakes is a shop that makes and sells cake.

Summary: BEST CAKES shop buys raw ingredients from several suppliers. Employee prepares cakes. Employee sells cakes to a customer.

Details: BEST CAKES shop buys raw material from suppliers. Each raw material is described by its unique CODE, description, and measurement unit.

The company keeps track of the raw materials purchases from suppliers. Keeping in record the purchase date, purchase quantity, and purchase price.

The supplier is identified by Supplier ID, name, address, and Phone number.

Each Cake-Type is the finished product that can be baked. Cake-Type has its own Identifying Code, name, description, sales method, colors, raw material ingredients, and sales price. The company keeps track of the quantity each Cake-Type needs from those raw materials.

The Cake is the baked Item that can be sold. Each cake is baked by an employee and is identified by its Cake-Type, serial_number , and color. serial_number resets for each Cake-Type.

BEST CAKES shop has several employees. Each Employee is identified by ID, name, address (city, street, building), salary, start_date , and Birth of Date.

The employee has at least one specialty on Cake-Type.

The employee bakes a cake.

The employee sells cakes to customer. The company needs to keep records of those sales records including the sales_date , quantity and price_collected.

The Customer is identified by CutomerID, name, and Phone number.

Steps to be done

Step 1: Data Modeling - Create an ER diagram that describes the BEST COOKIES

Step 2: Map ER diagram into a relational model

Step 3: Put the tables which you have mapped in the 3rd normal form

Step 4: Create the tables and fill them with records,

Step 5: SQL Queries

SQL Queries

1. List the names of all employees and their details.
2. List the purchases(s) and their associated row materials and suppliers between certain dates.
3. List the sales(s) and their associated customers and Cake-Type and the employee who sold the cake.
4. Find total value of sales done by the shop
5. Find total value of sales done by the shop per each Cake-Type.
6. List the first 5 employees with highest salaries
7. List the 5 highest sold Cake-Type.
8. List the 5 least sold Cake-Type.
9. Show all the employees that are specialized by a certain Cake-Type
10. List employees with their total sales amount from highest to lowest.

Write 5 additional queries that you find useful for this application. The queries should be complex queries easy (include union, intersection, difference)

: .

: . . .

: . CODE .

. .

.

. - . .

. serial_number . serial_number .

. ( ) start_date .

.

. sales_date price_collected.

CutomerID .

1: - ER

2: ER

3:

4:

5: SQL

SQL

1. .

2. () .

3. () .

4.

5. .

6. 5

7. 5 .

8. 5 .

9.

10. .

Systems Project

BEST CAKES : BEST cakes is a shop that makes and sells cake.

Summary: BEST CAKES shop buys raw ingredients from several suppliers. Employee prepares cakes. Employee sells cakes to a customer.

Details: BEST CAKES shop buys raw material from suppliers. Each raw material is described by its unique CODE, description, and measurement unit.

The company keeps track of the raw materials purchases from suppliers. Keeping in record the purchase date, purchase quantity, and purchase price.

The supplier is identified by Supplier ID, name, address, and Phone number.

Each Cake-Type is the finished product that can be baked. Cake-Type has its own Identifying Code, name, description, sales method, colors, raw material ingredients, and sales price. The company keeps track of the quantity each Cake-Type needs from those raw materials.

The Cake is the baked Item that can be sold. Each cake is baked by an employee and is identified by its Cake-Type, serial_number , and color. serial_number resets for each Cake-Type.

BEST CAKES shop has several employees. Each Employee is identified by ID, name, address (city, street, building), salary, start_date , and Birth of Date.

The employee has at least one specialty on Cake-Type.

The employee bakes a cake.

The employee sells cakes to customer. The company needs to keep records of those sales records including the sales_date , quantity and price_collected.

The Customer is identified by CutomerID, name, and Phone number.

Steps to be done

Step 1: Data Modeling - Create an ER diagram that describes the BEST COOKIES

Step 2: Map ER diagram into a relational model

Step 3: Put the tables which you have mapped in the 3rd normal form

Step 4: Create the tables and fill them with records,

Step 5: SQL Queries

SQL Queries

1. List the names of all employees and their details.
2. List the purchases(s) and their associated row materials and suppliers between certain dates.
3. List the sales(s) and their associated customers and Cake-Type and the employee who sold the cake.
4. Find total value of sales done by the shop
5. Find total value of sales done by the shop per each Cake-Type.
6. List the first 5 employees with highest salaries
7. List the 5 highest sold Cake-Type.
8. List the 5 least sold Cake-Type.
9. Show all the employees that are specialized by a certain Cake-Type
10. List employees with their total sales amount from highest to lowest.

Write 5 additional queries that you find useful for this application. The queries should be complex queries easy (include union, intersection, difference)