Assignment 1 - Database (Conceptual) Modelling

Rationale

This assignment has been designed to assess students' ability to model a database, by constructing an entity-relationship diagram for a particular business scenario. This assignment addresses the following learning objectives for this subject:

- Develop a database model using the entity-relationship model
- Apply the techniques of normalisation

Requirements (Tasks)

1) You are to write a brief discussion of your solution, i.e. how you approached the modeling problem and any issues you may have encountered (maximum of 1/2 page)

2) You are to write all applicable business rules necessary to establish entities, relationships, optionalities, connectivities, cardinalities and constraints. If a many-to- many relationship is involved, state the business rules regarding the bridging entities after breaking down the many-to-many relationship. An example business rules format can be found in Appendix A of this document.

3) Based on these business rules*, draw a fully labeled and implementable Entity- Relationship Diagram (ERD). Include all entities, relationships, optionalities, connectivities, cardinalities and constraints. You must use Crow's foot notation and MS Visio (or other software) to create the ERD. A Hand-drawn ERD will NOT be accepted. A sample ERD can be found in Appendix A of this document. (Note: The ERD created using Visio will need to be saved as an image file and then be included in your document file to be submitted)

4) A summary to describe the major justifications, assumptions and limitations related to your database design. For example:
- Assumption/justifications for optionalities, connectivities, constraints data type and data domain; and
- Special cases or data integrity issues that cannot be handled.

Business Description (Scenario)

Francine has inherited from an old relative a bookstore that went bankrupt 10 years ago, including all the stock items that did not sell. She decided to reopen the store as an op shop called FOPS (Francine's Op Shop). She knows that many of her neighbors visit op shops frequently and this would be a good opportunity for business in her suburb. She decided to store her data in a database and computerize the store's operations. You have been asked to design a database that satisfies the requirements provided by Francine, some of which are summarized below.

First of all, the database should keep information about the old inventory which consists of many item types such as books and DVDs. No matter the item type, each item in the inventory has a unique id, price, note if the item is on special or not, discount applicable, and the quantity on hand.

Items can be sold generating receipts, each receipt showing the timestamp of the purchase, the staff id who made the sale, total amount on receipt, total number of items sold with that purchase, and details of items and quantities sold.

Francine will try to resell the old books; for this book data should include the book's title, publisher, ISBN, year of publishing, type of book (such as SF, art, fiction, etc.), author(s) and whether the book is a paperback or not. Each publisher has a code that uniquely identifies the publisher and the publisher's name and city; each author's information consists at this stage of last and first names and country of birth.

About the DVDs, the database needs to keep information of movie or DVD title, year of release, category, critics' rating, the MPAA rating (G, PG, PG-13, or R), actors who played (name, the year born, the year of death if deceased), and directors (similar information as actors).

A number of five employees, some of which are actually volunteers, currently work at FOPS. For now the system should be able to generate a report to show the current employee records: staff id, date hired or joined, name, phone number, email address, address, position and number of hours worked weekly, weekly performance comments, if the person is volunteer or hired, casual or permanent, full time or part time; the hired employees have an hourly pay rate and must supply a valid BSB and bank account number. Francine works with volunteers to try them out, hoping to recruit some of them as paid workers then the store will start to sell.

All FOPS staff wear a uniform which is supplied to each employee when they join and which they return when they leave. Each uniform has a number for stock-take purposes. The database should generate a report showing, for each uniform, the uniform's number, its size (small, medium, large etc) and the name of the employee it has been given to. If the employee or volunteer is not working with FOPS any longer, the system should be able to retain an amount of $30 from their last paycheck (if hired).