Database System Assessment Answers

PART 1

you are required to develop a database for "Megah Supermarket System". Currently, there are 4 entities for this system which are employee, customer, product, and purchase. DBA plays an important role throughout this task

1. Draw ERD model
2. Convert the ERD model to a relational model
3. Use SQL language to fulfill the following requirements for the above relational model:
   • Create Database
   • Create table
   • Add at least 10 data rows to each table.
4. Create a procedure for one of the following requirements:
   • Create a new receipt
   • Add data to the invoice
   • Add data to the product
5. Create a function for one of the following requirements:
   • Indicate the total amount for a certain receipt.
   • Indicate the total amount for a certain invoice
6. Create a trigger for one of the following requirements:
   • Update the quantity and price for the product when it is imported. For example, in the receipt PN01, enter the PEPSI product with the quantity of 10 and the price of 10,000, then increase the quantity of PEPSI by 10 in the Product table and update the new price to 10,000.
   • Update the quantity for the product when a sale is made. For example, in an invoice PB01 that sells a PEPSI product in the quantity of 10, subtract this quantity of PEPSI from the Product table.

PART 2

Students use Java or Python to implement the following requirements:

1. Input: The Input1.txt file contains the entities and relationships between the entities of the ERD model. Output: The Output1.txt file contains the data tables and the relationships between the tables. The format of the Input1.txt file is set by the students themselves.
2. Input: The Input2.txt file contains the database schema including tables, attributes, and functional dependencies. Output: The Output2.txt file contains the closure of the X attribute set (the X attribute set is entered by the user); the keys of the schema.