COIS20026 Database Development and Management (T3 2013)

Assessment item 1— Entity Relationship Diagram (ERD)

Objectives
This assessment item relates to the course learning outcomes numbers 1, 4 and 5 as stated on page 1 of the
course profile.

Assessment task

Important points
• You must work individually on this assignment.
• The model solution to this assignment will be released approximately TWO (2) weeks after the due date.
• Assignments that are submitted after the release of the model solution will receive zero.
• You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIS 20026 course materials' if that was all you used.
• Extension requests must be submitted online (for AIC students, contact your local campus administration staff) before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution.
• Before submitting your assignment, review the marking criteria as shown on the marking guide (the guide will be available on the course website). Ensure that all the criteria are addressed in your submission.
• After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure they are correct and uncorrupted.

ASSESSMENT 1
Case Study: Queensland Liquor National online trader database

Queensland Liquor National, better known as QLN, owns an entertainment club named as Queensland Liquor Club (QLC) and an online business trading a variety of liquors ranging from French prestigious champagnes to native Tasmanian beers. The aim of QLN is to provide premium quality liquors at affordable prices for its club members. QLN also regularly publishes e-newsletters containing the most recent reviews and ratings of various liquors ranging from old Scottish whisky to the 21st century home-brewed ale, which have been popular among liquor lovers.
The CEO of QLN needs a relational database which will be used as the most critical backend software component in the enterprise software system to run the online business so that the relevant information will be recorded appropriately and processed efficiently. The proposed database needs to track information regarding liquors, online sales records, deliveries, stocks, rating and members.
QLN has a large pool of customers. Each customer must register as a member of QLC in order to make an online purchase of liquors provided by QLN. For each member, QLN wishes to record their member number (as identification number), name, complete address, age, registration date, and contact phone numbers. QLN needs to be able to record multiple phone numbers and phone types for each member. Each member phone type is classified as either “private” or “business”. QLN often also keeps some members on record who have never made an online purchase. Sometimes members make general enquiries but never actually purchase a bottle of liquor mainly due to their budget constraint, despite their enjoyment of drinking liquor. QLN keeps their details in the database system so that they can follow up with these members at a later date to see if they have improved their income and budget situation.
The online sale business is very similar to today’s conventional eCommerce on internet. The company runs an internet website with a backend database system on a server. The front-end webpages of the website have some common graphical user interface (GUI) components and images to provide online shopping functionality and highlighting features of liquors. To purchase liquors, registered customers, i.e. members will browse the webpage, drag liquor icons to the shopping cart, fill in or select the quantities they wish to purchase, and finally click “Complete” and “Payment” buttons. Each online order will be automatically assigned to an individual staff member who will check and process the order. Staff details (including name and phone) must be recorded in the system. Not all staff process orders but majority of staff may process many orders. Normally the liquor ordered by members will be delivered to the members address with a low shipment cost in three days. The delivery information that needs to note includes the delivery company name, phone number, delivery cost and shipment date. After processing an order, the status of the order should be changed to processed, paid, delivered or cancelled, and accordingly relevant actions are taken.
For an efficient management and a faster search/query, QLN wishes to record the details of the liquors they sell online. These details include liquor identification number, brand name or description, brewer name, production date, alcohol content (in terms of percentage), unit price, a brand icon (image), storage locations, and the discount rate when QLN makes a promotion. QLN has a number of warehouses in statewide locations where various liquors are stocked under the strict health and safety guidelines.
Each warehouse has an identification number. The address of warehouse and contact phone must be recorded, in addition to the quantity of each kind of liquor in the stock either in the unit of bottle or box. QLN has made the arrangement that different warehouses can stock full-range of liquors. This is helpful for a rapid delivery. In addition, all liquors that QLN sell are placed into three distinctive categories: beer, wine and spirits. The beer style (a combination description of aroma, flavour and strength) must be noted for each kind of beer. For a wine, the grape species used to brew is required to record; while for spirits, the category description (for example, gin, vodka and Lowland whisky etc) is an essential data item.
Finally, the database also records and updates the rating information for each kind of liquor. The rating is conducted by internationally recognized organizations like Beverage Testing Institute (USA), based on their thorough research, lab testing and numerous consumer surveys. The rating information contains the content such as the name of rating organization, rating value or points, review comments and date. It should be pointed that the same liquor could be rated by different organizations.
Entity-relationship data model
You are required to develop an ER model to represent the information requirements for a database that
Queensland Liquor National online trader system will use as described on last page.

Your ER model must:
• show all necessary entities, attributes and relationships
• show unique identifiers
• show multi-valued attributes (also called repeating attributes), if any
• show participation and cardinality
• show associative entities, if appropriate
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes throughout (refer to Chapter 2 & 3 of set text).

Some business rules or other aspects of the case study may not be clear to you when you read the case study.
If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show these in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order.
To get yourself started, ask yourself, 'If I were running this business, what things would I need to keep a list of?' Write those things down. For each thing, what information would you need to record about it?

Entity and attribute definitions
You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types pp. 103-104 and Naming and Defining Attributes pp. 109110 of the set text.
For example:

STUDENT: A Student is a person who has enrolled in one or more programs at the university. This includes Start Uni Now high school students but does not include prospective students.
Student No: Uniquely identifies each student.
Student Name: The full name of the student.
Date of Birth: The student’s date of birth.
…
PROGRAM: A Program is a program of study available at the university.
Program code: Uniquely identifies each program
Program name: The full name of the program.
…
What you should submit
You should submit a Microsoft Word document containing:
• A copy of your entity-relationship model(s).
• A list of business rules that are apparent from the case study.
• A list of any assumptions that you made (include any clarifications from your tutor) with justifications.
• Your entity and attribute definitions.
Your document should contain appropriate identifying information (your student number, name, course number, etc.) and should be appropriately formatted. For example, your document should contain a very brief introduction stating the purpose of the document, as well as headings for each section.