Assignment Part 1: Entity-Relationship Diagram For Old Print Bookstore

General Specification

Requirement:  no need references 

(a)  15 marks (Entity Data Dictionary, Relationship Data Dictionary, Relationship-Entity    Data Dictionary and   Attributes Data Dictionary)

(b)  30 marks (Marks allocated for entity, relationship, and attribute layout, multiplicities and general   diagrammatic representation)And 5 marks for consistency between (a) and (b). 

I also atteched Template in mail.   1 General Specification

(a) Construct the information model

1. List all entities on the Entity Data Dictionary Record the name, a description and any aliases. In the Occurrence column described how the entity exists in the enterprise and how it relates to any dependent entities.
2. List all relationships on the Relationship Data Dictionary Record the name, a description and any aliases. In the Occurrence column described how the relationship exists in the organisation.
3. List all relationships and entities on the Relationship-Entity Data Dictionary For each participating entity, give the multiplicity (both the participation and cardinality). If the relationship is greater than binary then list other entities on subsequent rows.
4. For each identified entity and relationship list its name and its attributes on the Attributes Data Dictionary For all attributes, give a description, an indication of the data type, length and domain, whether it can be NULL (i.e. optional), and if it can be multi-valued. If it can be multi-valued then list the range of values.

It is recommended that you use copies of the data dictionary work-sheets templates available on FLO.

(b) Using your answer to (a) draw up and Entity-Relationship Diagram (ERD). The diagram can be hand drawn. It should be at least A4 size. The important criteria are consistency with part (a), clarity and readability.

Entities in the ERD should fully represent the information contained in the data dictionary.  That is, include all the attributes and any other necessary information. It should also conform to the Unified Modelling Language specification discussed in lectures and the textbook. Using a different modelling language will result in a grade of Fail with a mark of 1, which allows for resubmission with a maximum mark of 50%.  Using software that does not use UML is not an excuse.

 An example of entities is shown here:

2 Submissions

The submission of any work will be taken as your claim that it is your own work (or that of you and your partner if working in pairs), i.e. that you cognitively and physically created it. It also signals your agreement to re-do it (or similar work) under supervision if any doubts are raised about your authorship of the work.The required submission for Part I of the assignment is:

Tasks (a) and (b): You are to submit finished (final) versions of the work-sheets and the associated ER diagram electronically on FLO as a single or set of PDF files. If your ER diagram is hand-drawn, you should scan and submit it electronically as well.

You may also optionally submit a document (a PDF) explaining any assumptions you have made about the specifications.  This may have to do with particular multiplicities you assign to relationships and entities, different types of attributes, entities, relationships versus entities.  Essentially any piece of information you thought was ambiguous and you think requires additional explanation beyond tasks (a) and (b).  This could include information that was clarified by a tutor.

3 Assessment

If you a working as a pair each member will receive the same overall mark.The assignment is worth 20% of the total assessment. It will be marked out of 50. A guide to the breakdown of the marks over the parts is (a) 15 marks (3 for Entity Data Dictionary, 2 for Relationship Data Dictionary, 5 for Relationship-Entity Data Dictionary and 5 for Attributes Data Dictionary)

Operations and Questions

(b) 30 marks (Marks allocated for entity, relationship, and attribute layout, multiplicities and general diagrammatic representation)

And 5 marks for consistency between (a) and (b).

This is only a guide since the submissions are inter-dependent and will not be marked independently.

4 Specifications

In the case-study description the following notation is used to indicate the type of data items:

(N x) a digit string (integer) of length x

(S x) a character string of length x

($ x) x dollar digits and 2 cent digits

(C) a combination of

family name (S 20)

personal name (S 20)

title (S 4)

(A) a combination of

street address (S 45)

postcode (S 4)

(D) time and date or either

The description is unrealistic in its simplicity and stripped down details but hopefully it has a real world flavour (and gives students insight into the real world use of databases). I have tried to make the description as precise as possible. Any solution that could be reasonably derived from the description will be accepted. For instance, it can often be argued whether a minimum participation should be 0 or 1.

PLEASE base the design on the description, that is, treat the description like requirements. Failure to adhere to the description is very poor professional practice. (If you really believe the description is invalid, please discuss it with the topic coordinator as soon as possible.).  Also keep up to date on FLO for any clarifications.

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

• 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. 4.3 Operations and Questions

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.

1. Add a new book
2. List all purchases for the last month
3. Which member has purchased the most books?
4. Which category is the most/least popular?
5. Which publisher has the most books available?
6. Which author is the most popular based on watches?
7. List all the book titles with a cost more than X.
8. List all the members who have outstanding invoices.
9. List all request that are about to expiry
10. List all authors who do not currently have a book copy available
11. Find all members who have spent over $1000 on books
12. Find out which members have a watch on a book that currently has a copy available, but has not yet been notified
13. Generate an invoice with all the required information.
14. For a given book copy, find all the watches that it currently matches (it can match a category watch, book watch or author watch).

Attributes Data Dictionary

Student Name:                                                    Student Id:                                         Student FAN:

Student Name:                                                    Student Id:                                         Student FAN: