Database for bookstores

An online bookstore needs to create a relational database where it can store information about its books and customers. The owners of the bookstore have discussed back and forth and finally concluded that they initially want the following information in the database:

For each book  ID number (unique)  Title  Author (one or more)  Publisher  Subject / category (one or more)  Description  Each book can be in different editions (hardcover, paperback, e-book, audiobook), see below

For each edition  ISBN number (unique)  Type of edition  Release year  Number of pages (audiobook: length in minutes)  For audiobook: reader of the book  For E-book and audiobook: available electronic formats (one or more)  Award  Number of copies in stock

For each customer  Customer number (unique)  Customer's name, address and telephone number  Which books a customer has bought (the customer can buy several copies of the same book) + date of purchase

a) Make a model of the database in the form of an E/R diagram or equivalent where all the above information is included. If you think something in the information is unclear, do own assumptions (which must be reported). If you think something is missing that should be but you are welcome to add it if you want, as long as you state this clearly. b) Map the E/R diagram to the relationships you would create for the database, together with their attributes. Determine all candidate keys, primary keys and foreign keys. NOTE! All relationships must be in the BCNF. However, you do not need to show this specifically, but just about it is an attribute that has an unclear meaning. c) Give examples (in text) of two database-specific privacy checks that might be needed defined for the database (except attributes which must not be NULL).