basic sql

You own a book company with a few bookshops distributed in West Yorkshire, which deal with book sales. You purchase books from vendors (possibly more than one vendors) and distribute them to different bookshops. Books come from several vendors, which may provide the same book, but with different price. The difference of price may due to the date at which the books are available, and other reasons. It is necessary to distinguish the price difference and also the date for book available. On the other hand, we need record the details of vendors such as the address, the telephone number, the name of person to contact. There are two kinds of customers: members and non-member. Members need to pay the membership and are given some bonus points each time they buy a book according to the amount of payment (for example 1 point per 10 pounds). After accumulation of a certain number of bonus points, members are eligible for discount at different level (e.g. 95%, 90%, 85% and 80%) from all the bookshops associated with your company. For, non-member customers, they can get discount only when they have bought books at one time that the total cost is over a certain amount (e.g. £100). We need to record the information of all the customers such as their home address, the telephone number, the type of customers, and also the bonus points if he/she is a member. Your design should facilitate the efficient management for the book shop, including the management of customers, books, ordering records from vendors. For example, your system should be able to retrieve the information like ‘how many points have one member obtained’, ‘do we have this book in the bookshop in Bradford?’, ‘which books written by John do we have?’, ‘what are the prices for ordering this book from different vendors’ etc. ## Deliverables a short report (maximum 4 pages) including: l An Entity Relationship diagram, in which you are expected to represent the entities and the relationships between the entities; l Explain the design briefly; l final design (after normalization), i.e to list all the relations and the associated attributes, and keys (primary key and foreign keys). This should be in the form of \dt or \d output from PostgreSQL with the additional information of primary keys. ## Platform windows linux