Projects
Database Final Project
For this database final project, group consisting of me,Jordy,and Stefan are going to create a database regarding food businesses.
For the system itself, we use 6 tables total which will be mentioned below.
The database will consists of 6 tables which is:
Product
This table consists of 3 columns which is “Product” (product name), “Category”, and “Cost”, with the column “Product” and column “Cost” as the primary key. This table will also have the right to manage the products regarding the food business.
Branch
This table consists of 3 columns which is “Branch”, “Product”, and “NumOfOffice”. “Branch” column and “NumOfOffice” both acts as a primary key while “Product” column act as foreign key referenced from “Product” column in the Product table.
Staff
This table manages information of the staffs at a specific office and specific branch. The table consists of several columns which is :
ID
Name
Gender
Age
Phone
Position
Office
Branch (Foreign key referenced from Branch(Branch)
Customer
This table deals information regarding of the customers. The tables consists of several columns which is “ID”, “Name”, “ProductBought”, “Quantity”, and “Branch”. With “ProductBought” as the foreign key referenced from “Product”(Product) and “Branch” as foreign key referenced from “Branch”(Branch).
Finance
This table manages the financial actions that happen to the specified branch. It also has several columns :
Branch, foreign key references from Branch(Branch)
NumOfOffice, foreign key references from Branch(NumOfOffice)
ProductMade, foreign key references from Product(Product)
ProductCost
QuantityMade
TotalCost, virtual generated (ProductCost * QuantityMade)
ProductSold, foreign key references from Product(Product)
ProductPrice
QuantitySold
TotalRevenue, virtual generated (ProductPrice * QuantitySold)
Profit, virtual generated (TotalRevenue – TotalCost)
Rating
This table manages the rating of a specified branch. The ratings are between 1-10 with a minimum of one and maximum ten. It has 5 columns which is “Branch”, “FoodRating”, “ServiceRating”, “ManagementRating”, and “AVGRating”, with the “AVGRating” acting as a generated column (Average of FoodRating, ServiceRating, and ManagementRating) and the Branch column as its foreign key.
NB : This post might still be updated after published depends on the progress of the work, ER model and user manual coming soon