MID EXAM

Instruction
  1. Read the following cases below.There are two cases. You are required to provide database design to support the business process given in the case.
  2. Student whose last digit of his/her ID is Odd (ganjil) work only Case 1. While student whose last digit of his/her ID is even (genap) or 0 will work on Case 2.
  3. Design a database to support all general business processes. A bonus business process is not a must, but it will give you extra score if you do it correctly.
  4. Use Entity Relationship Diagram (ERD) to create the database modeling.
  5. Use any tools to draw the ERD, and capture the diagram and copy it to Microsoft Excel.
  6. Convert the ERD into tables the represent the database. Create those tables in the same Microsoft Excel file.
  7. Insert or provide data, min 5 record per table.
  8. Test the tables. Make sure they are normal.
  9. Upload and submit the Microsoft Excel file in your course in eCampus. The file must consist of ERD and tables including the data sample.

Case 1:
A restaurant is developing a new information system. The system needs a database to support the business process in the restaurant.

Below are the business processes:

  1. Customer must be registered as member of the restaurant.
  2. Customer choose menu via offline. The customer needs to go to the restaurant to choose the menu.
  3. There are two payment methods: Cash or Credit/Debit Card.
  4. There are several types of menu. For example: meal, drinks, snack, etc.
  5. Customer received bill that contains detail of item purchased (name, quantity), total payment, payment methods, customer name, and transaction date.

Bonus:
Some menu may have discount. The discount is based on menu package. For example, IF customer buy Package A (2 nasi goreng, 2 tea, 2 ice cream) THEN the total price will get 5% discount. The package is cheaper rather than they buy the menu not in a package.
Case 2
A motorcycle repair shop (bengkel motor) is developing a new information system. The system needs a database to support their business process.
The business process are as followed:
  1. Customer must be registered as member of the motorcycle repair shop.
  2. Customer must book a schedule for motorcycle service. The book record: customer name, type of motorcycle, motorcycle number (nomor polisi), date, and note. Note can be filled with customer's complain about his/her motorcycle.
  3. After the service, the customer will receive the bill that contains of the book number (book ID), list of services (i.e clean the engine, check the engine, etc), list of product (lubricant, any sparparts, etc.), price of each services, price of each product, total payment, date.
Bonus:
There are 3 level of membership, Gold, Silver, and Bronze. Each level will give different discount. For example Gold member will have 20% discount of service, Silve will have 15% and Bronze will have 10%.