DATABASE SYSTEMS
Information that will be used:
AllTaste Restaurant database design:
Some entity information and documents are known as follows:
- Food Menu: food menu code, food menu name, food category, regional specialty, price, menu image (one menu can have many pictures), number of portions available per day.
- Customer: customer code, customer's full name, place of birth, date of birth, gender, address, email, phone number, WA number.
- Employee: employee code, employee name, phone number, position.
- Restaurant Branch: Branch code, branch name, address, city, phone number, WA number, email.
An example of a purchase receipt form as follows (picture):
Normalization:
Unnormalized schema
Receipt (Receiptcode, Date, Customercode-Name, CustPhone,CustAddress Payment mode, EmployeeNum, EmployeeName,ItemCode,ItemName, Quantity, UnitPrice, Discount, TotalPrice, TotalPay)
1NF ( identifying the key attributes and avoiding redundancy)
Receipt (Receiptcode, Date, Customercode,Name,Phone, Address ,Payment mode, EmployeeNum, EmployeeName,ItemCode,ItemName, (Quantity,UnitPrice, Discount),TotalPrice, TotalPay)
2NF ( removing the partial dependencies)
ReceiptCode, ItemCode together determines the quantity, price, discount and the total Price. Hence the schema is restructured as
Receipt (Receiptcode, Date, Customercode,Name, CustPhone, CustAddress, Payment mode, EmployeeNum, EmployeeName, TotalPay)
Receipt_Item( ReceiptCode, ItemCode, quantity, TotalPrice, Discount)
Item ( ItemCode, ItemName, UnitPrice)
3NF ( Removing the transitive dependencies)
Receipt (Receiptcode, Date, CustomerCode, Payment mode, EmpNum, TotalPay)
Customer(CustomerCode,CustName, CustPhone, custAddress)
Employee( EmpNum, EmpName)
Receipt_Item( ReceiptCode, ItemCode, quantity, TotalPrice, Discount)
Item ( ItemCode, ItemName, UnitPrice).
TO-DO:
Please draw the ERD from the table of results for the 3NF stages. ERD is completed with all attributes of each entity, PK, FK, multiplicity (participation and cardinality).
Extracted text: AllTaste Restaurant - South Jakarta Branch Address: JI. Raya Rasa No.20 Jakarta Selatan, Phone: 021-59983744, WhatsApp: 08123456789 Transaction Code : 202012120 : 11 Nov 2020 12:00 Code - Customer : 100 - Andy, Phone number: 082123456789 : Cash/e-payment : KSR1905 - Julia Date Payment Type Cashier Menu Code Menu Price Quantity Discount Total (Rupiah) 77 Grilled Chicken 15000 2 2500 30000 65 Satay 13000 2 26000 25 Tongkol Asam Padeh 12000 1 12000 12 Sayur Lodeh 10000 20000 2500 88000 Discount 2500 Total Pay 85500