Vis Library is a multi-categories book library that provides a wide range of genres from kids books
Fantastic news! We've Found the answer you've been seeking!
Question:
Vis Library is a multi-categories book library that provides a wide range of genres from kids books to special braille books. The customers can read the books on-site or choose a delivery service. As a database designer, they want you to design and implement a database to support their business.
At the initial meeting to discuss the database design, the following requirements were gathered:
- A new customer is required to register the account, pick the membership type, and pay the necessary cost.
- The systems must be able to manage the detailed information of each member.
- There are two membership types: silver dan gold. This membership is necessary to borrow books and read on the spot.
- Silver membership can be obtained by showing the id card and filling the registration form without any cost.
- On the other hand, Rp 20000 registration costs and Rp 10000 monthly costs are needed to get a gold membership.
- Gold member is entitled to special delivery and pick-up services from Vis Library to avoid a late book return
- The systems must be able to manage the detailed information of each book including its availability.
- Each member can borrow only two books in one transaction, and the borrowing period is 7 working days.
- There is an AudioBook version for some of the books. To distinguish the physical book from its Audio version, a different product_id format will be applied. The physical book will have 10-digits numbers, while the audio version id will be started by “AU” and followed by 3-digit numbers (e.g., AU241).
- Each late book return will be fined Rp 1000 per day, and the maximum fine is Rp. 20000.
- All book borrowing services in Vis Library will be handled by a registered and authorized operator.
- Book delivery and pick-up service will be scheduled by the operator and to be assigned to a courier.
- Several forms are required for the system. Three main important ones are:
- o Loan Form, capture all information related to a book borrowing transaction
- o Return Form, capture detailed information of all books return and the condition
- o Member Profile, capture detailed information of each member
Based on the form and explanation above,
- Create a set of third normal form relations. You must show all relations at each stage in the normalization process!
- Create an appropriate ERD, including the relationship and multiplicity!
- Based on the ERD that you made, please create the entire table and the constraints (based on the explanation above)!
- Based on your table (in point 3), change the constraint for physical book product_id with this format: started with “PBV” and followed by 4-digit numbers (e.g., PBV4252)!
Posted Date: