Entity Relationship Model Of Book Store System

Author Entity

This is not an assignment that can be done at the last minute! This assignment may involve a paper or an electronic submission (PDF via FLO). The paper submission must have a completed cover sheet (available via FLO) as its first page. You are encouraged to work in pairs for this assignment, however it is not mandatory that you do so. WORTH: The assignment is worth 20% of the overall topic assessment.

1 General Specification (a) Construct the information model

1. List all entities on the Entity Data Dictionary worksheet. 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 worksheet. 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 worksheet. 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 worksheet. 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: Data Modelling Assignment Part I

Publisher Entity

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 RelationshipEntity Data Dictionary and 5 for 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).

This is only a guide since the submissions are inter-dependent and will not be marked independently. Data Modelling Assignment Part I COMP2761/8761/8711 Page 3 of 5 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

4.1 General details. 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

Book Entity

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. Data Modelling Assignment Part I COMP2761/8761/8711 Page 4 of 5 4.2 Old Print description 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. -

Book Store Entity

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.

Data Modelling Assignment Part I COMP2761/8761/8711 Page 5 of 5 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. a. Add a new book b. List all purchases for the last month c. Which member has purchased the most books? d. Which category is the most/least popular? e. Which publisher has the most books available? f. Which author is the most popular based on watches? g. List all the book titles with a cost more than X. h. List all the members who have outstanding invoices. i. List all request that are about to expiry j. List all authors who do not currently have a book copy available k.

Find all members who have spent over $1000 on books l. Find out which members have a watch on a book that currently has a copy available, but has not yet been notified m. Generate an invoice with all the required information. n. For a given book copy, find all the watches that it currently matches

(it can match a category watch, book watch or author watch) Kindly note-

• Ensure that you do not have foreign keys in your entities (FKs are derived during the logical modelling stage based your conceptual model).

• Ensure that you identify primary keys (except for weak entities).

• Ensure that you use UML as describe in textbook for the ERD.

• Remember to include the multiplicities on the ER model.

• Ensure that you do not have redundant information (e.g. duplicate attributes, unnecessary relationships).

• Most attributes will be NOT NULL, unless it is specified in the specification as being optional in some way.

• The “Operations and Questions” are there to help verify/validate you design – you do not need to provide answers to them for the assignment.

• You should list each relationship in the data dictionary, even if they have the same name as they describe a relationship between different entities.

• Ensure you are representing the composite attributes and multi-valued attributes correctly (in both the data dictionaries (Ch. 16) and the ER model).

• Include enhanced ER modelling techniques (e.g. specialisation) to aid in the descriptive power of your ER model.

• Submit an Assumptions document if you want to clarify any of your decisions.

• There are multiple "correct" solutions to the assignment due to the way the specification can be interpreted. I am open to any sensible interpretation. Remember that you can submit an Assumptions document if you want to clarify any of your decisions with regards to any ambiguity in the specification.

• If you have a Tutorial session on Wednesday this week (a public holiday), I recommend that you attend another class to get some feedback on your ERD before submission.

Attributes Data Dictionary

Student Name:                                                    Student Id:                                         Student FAN:

Student Name:                                                    Student Id:                                         Student FAN: 

Entity or Relationship Name	Attributes	Description	Data Type and Length	Nulls	Multi-valued
Author	AuthorID	Unique id of author	Int	NotNull	No
	AuthorName	Name of the author	Varchar (20)	NotNull	No
Publisher	PublisherID	Unique id of publisher	Int	NotNull	No
	PublisherName	name of the publisher	Varchar (20)	NotNull	No
Books	ISBN	Unique identity of books	Int	NotNull	No
	Title	Title of books	Varchar (20)	NotNull	No
	AuthorID	Author of the book	Int	NotNull	No
	PublisherID	Publisher of the book	Int	NotNull	No
	CategoryCode	Category code of the books	Int	NotNull	No
Book Store	BookStoreId	Unique id of book store	Int	NotNull	No
	ISBN	Book identity	Int	NotNull	No
	Price	Book price	Currency	NotNull	No
	Date	Data of registration	Date	NotNull	No
	NumberOfCopies	Number of copies	Number	NotNull	No
Invoice	InvoiceNo	Invoice number	Int	NotNull	No
	PurchaseID	Purchase identification	Int	NotNull	No
	CuastomerId	Identification of customer	Int	NotNull	No
	BooksStoreId	Id of the Book Store	Int	NotNull	No
	Title	Title of the book	Varchar (20)	NotNull	No
	InvoiceDate	Date of invoice	Date	NotNull	No
	PaymentStatus	Payment status of the book	Varchar (20)	NotNull	No
Request	RequestID	Unique id of request	Int	NotNull	No
	StartDate	Start date of request	Date	NotNull	No
	ExpiryDate	Expiry date of request	Date	NotNull	No
	watchSpecification	Watch specification	Varchar (20)	NotNull	No
	StausCode	Status of the request	Varchar (20)	NotNull	No
	CustomerID	Customer unique identification	Int	NotNull	No
Customer	CustomerNo	Customer unique field	Int	NotNull	No
	CustomerName	Name of the Customer	Varchar (20)	NotNull	No
	CustomerAddress	Address of the Customer	Varchar (20)	NotNull	No
	CustomerMail	Mail of the Customer	Varchar (20)	NotNull	No
	CustomershipExpiry	Expiry date of the Customership	Date	NotNull	No
	CustomerContact	Contact  of the Customer	Number	NotNull	No
Category	CategoryCode	Category code	Int	NotNull	No
	CategoryName	Name of the category	Varchar (20)	NotNull	No
Purchase	PurchaseId	Purchase unique key field	Int	NotNull	No
	RequestId	Request id	Int	NotNull	No
	BookStoreId	Identification of the book Store	Int	NotNull	No