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 state-wide 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. 109-110 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.