Solution
Shikha answered on
May 12 2020
Database Systems 2
Sweet Shop – Database Systems
Submitted By
Course
Professo
Date
Application Requirements
1. Data Requirements
Employees – All the staff members are responsible for performing their daily activities. The data included in the employees table will be – EmployeeID, Employee_name, address, city, contact_no, job_profile, salary. Here, we will keep EmployeeID as a unique, so that they can be searched easily on the basis of their employeeid.
Supplier – Suppliers will be responsible for supplying all the ingredients that are required to make all the sweets. The main attributes of supplier will be SupplierID, Supplier_name, Supplier_address, Contact_person, Supplier_contact.
Products – This is the main table of the whole database as it will save all the details of all the sweets. This will be master table of sweets. All the inventory of the sweets will be saved in this. The main attributes of the table will be ProductID, Product_name, Stkinhand, price etc.
Customers – This table will be used to store all the important details of a customer. The main attributes of customer table will be CustomerID, customer_name, customer_address, customer_city, customer_contact. When customer comes in the shop, the staff will store all the customer details in the database.
Purchase – The sweet shop will purchase various sweets from various suppliers. After receiving an order from supplier, the quantities of items will be updated in items table as well. The main fields of purchase table will be PurchaseID, purchase_date, supplierid, purchase_amount, discount, employyeid.
Purchase_details – This will be used to save all the item details of a purchase order. The main attributes will be Id, PurchaseID, ProductID, qty, price.
Sale – Sale as the name suggest will be used to save all the details of a sale to a customer. The main attributes will be Sale_id, ProductID, CustomerID, qty, price, sale_date, employeeid.
2. Transactional Requirements
Data Entry
· The information of a new employee with employeeid=’E005’ will be inserted in the Employees table.
· A new supplier will be added to supplier table.
· Products received from supplier will be inserted into products table unique productid.
· When a customer a
ives to the sweet shop, all the details of customer will be saved in customer table.
· All the purchase from supplier will be done by creating purchase order with unique purchase order number.
· The items which are ordered on particular purchase order will be saved in purchase_details.
· The sales which are made to customer will be inserted in sales table.
Queries
· List all the details of product that has been sale with the details of a custome
· List all the details of Products that are ordered from a particular supplier.
· List the total value of sale which has been made to particular customer in a given month.
· List the employees who has not made any sale.
· List the details of the products that has not been ordered in a given month.
Entity Relationship Diagram
Relational Schema
1. Employees EmployeeID, Employee_name, address, city, contact_no, job_profile, salary
Primary key EmployeeID
2. Supplier SupplierID, Supplier_name, Supplier_address, Contact_person, Supplier_contact.
Primary key SupplierID
3. Products ProductID, Product_name, Stkinhand, price
Primary key ProductID
4. Customers CustomerID, customer_name, customer_address, customer_city, customer_contact
Primary key CustomerID
5. Purchase PurchaseID, purchase_date, supplierid, purchase_amount, discount, employeeid.
Primary key PurchaseID
Foreign key ...