A database is required for the Old Print, a web-site that specialises in books printed before 1950.
While the site is open to the public, certain services are only available to members. Each member has a unique membership number (N 8 0) recorded along with their name (C), address (A), email address (S 30) and expiry date (D). A member might also have a phone number recorded (S 14).
Any visitor to the site can check for what books there are copies currently on offer. Each book (offered now or in the past) has its unique ISBN (S 12) and title (S 70) recorded along with an assigned category code (S 3). For each possible category code such as 'SFI', a description (S 30) such as 'science fiction' is recorded. The book's one to many authors are identified along with its publisher, edition (N 2) and the publication date (D). Each copy of a book has a unique inventory number (N 8 0) recorded along with a price ($ 4) and the date (D) when it became available. There may be none, one or more copies of a particular book currently on offer. The total number of book copies available needs to be known for inventory purposes.
Each author has a unique author identifier (S 8) assigned (and recorded). This is recorded along with the author's name (C). An author might initially enter the system as a book author or as a requested author. An author may currently have none to several books on offer and none to several requests.
A publisher has a unique publisher number (N 4 0) recorded along with their name (S 50). A publisher's details are entered on the system when a book published by them first enters the system. A publisher may have none to several books currently on offer.
A member may have none or one request currently registered with the site. Each request has a unique request id (N 9) plus start and expiry dates (both D). A request consists of one to six watch specifications, numbered (N 1) 1 to 6 and each with a status code (S 1) of 'R' registered or 'N' notified. Each of these watch specifications is either
The “Operations and Questions” are here to help verify/validate you design. You do not need to provide answers to questions for this part of the assignment.
While the site is open to the public, certain services are only available to members. Each member has a unique membership number (N 8 0) recorded along with their name (C), address (A), email address (S 30) and expiry date (D). A member might also have a phone number recorded (S 14).
Any visitor to the site can check for what books there are copies currently on offer. Each book (offered now or in the past) has its unique ISBN (S 12) and title (S 70) recorded along with an assigned category code (S 3). For each possible category code such as 'SFI', a description (S 30) such as 'science fiction' is recorded. The book's one to many authors are identified along with its publisher, edition (N 2) and the publication date (D). Each copy of a book has a unique inventory number (N 8 0) recorded along with a price ($ 4) and the date (D) when it became available. There may be none, one or more copies of a particular book currently on offer. The total number of book copies available needs to be known for inventory purposes.
Each author has a unique author identifier (S 8) assigned (and recorded). This is recorded along with the author's name (C). An author might initially enter the system as a book author or as a requested author. An author may currently have none to several books on offer and none to several requests.
A publisher has a unique publisher number (N 4 0) recorded along with their name (S 50). A publisher's details are entered on the system when a book published by them first enters the system. A publisher may have none to several books currently on offer.
A member may have none or one request currently registered with the site. Each request has a unique request id (N 9) plus start and expiry dates (both D). A request consists of one to six watch specifications, numbered (N 1) 1 to 6 and each with a status code (S 1) of 'R' registered or 'N' notified. Each of these watch specifications is either
- an author watch: where a member nominates a particular writer in whose books they are interested.
- a book watch: where a member nominates a book in which they are interested and can specify 1 to 5 different editions of which they are interested in.
- a category watch: where a member nominates a category plus a start and end publication year (both N 4) indicating that they are interested in any books in that category from that period.
- When a book matching a watch specification becomes available, the relevant member is emailed the book (and copy) details and the relevant watch specification's status code is changed.
- A member may purchase a copy of a book. Each purchase has unique purchase number (N 9) and the date the purchase took place (D). Upon confirmation of the purchase, which may take some time (checking book availability, any other watches on the book, etc.), an invoice is generated and sent to the member for payment. The invoice sent includes the user’s name, the book title, publisher, edition, book copy number, price, the date of the purchase and the date the invoice was sent. It also includes a status (S 1) of payment required (‘R’) or paid (‘P’) and once paid the date (D) of the payment is recorded. Only a single invoice is generated for each purchase.
The “Operations and Questions” are here to help verify/validate you design. You do not need to provide answers to questions for this part of the assignment.
- Add a new book
- List all purchases for the last month
- Which member has purchased the most books?
- Which category is the most/least popular?
- Which publisher has the most books available?
- Which author is the most popular based on watches?
- List all the book titles with a cost more than X.
- List all the members who have outstanding invoices.
- List all request that are about to expiry
- List all authors who do not currently have a book copy available
- Find all members who have spent over $1000 on books
- Find out which members have a watch on a book that currently has a copy available, but has not yet been notified
- Generate an invoice with all the required information.
- For a given book copy, find all the watches that it currently matches (it can match a category watch, book watch or author watch)