Assignment 2 specification

The assignment must be submitted electronically via the online assignment submission link on the study desk. Instructions will be provided on the course study desk.

If you are unable to complete the whole assignment, submit what you have done rather than nothing.

You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein's techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book and the tutorials use this methodology.

If you do not use the USQ methodology, you will probably be awarded a mark of zero.

You might wish to use a word processor or a drawing tool. In either case, you must adapt the drawing to conform to the USQ methodology.
This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are of examination standard.

SECTION A (Data Modelling) (40 marks)

A local pharmacy store in Wollongong wants to develop an online system to track their product inventory as well as prescription records of their products for regulatory requirements. As part of systems development, a data model is required to determine data entities and associated attributes and business rules. You are required to develop a data model which will allow the information and activities to be recorded.

The most important data entity for the system is the ITEM entity - this entity represents all the products in the pharmacy. Item code, shelf, location and quantity are the attributes for ITEM entity. Every time an item is ordered, some of the order details such as order date, order quantity and special instructions to the patient for each item in every order are stored. Patient information includes an id, patient name, date of birth, contact number and next of kin details. Each patient may have multiple orders of several items. While most orders are from patients this is not always the case. An order can be created without necessarily being associated with a patient, e.g. order from a casual customer buying items.

An item can be of two types: medical or non-medical. Understandably more than 95% of items in the pharmacy are medical items. For non-medical items, promotion period is an additional attribute to store since the pharmacy only sells non-medical items during promotions. Medical items are further classified into three types: doctor-prescription items, over-the-counter prescription items, and no-prescription items. Prescription records are required to be stored in the system. One doctor can prescribe multiple doctor-prescription items and vice versa. We store the prescription date and special instructions by the doctor prescribing a particular item. It is also important to store the doctor's name, specialty and registration number from the Medical Board of Australia.

An over-the-counter prescription item must be given by one of the store's pharmacists after consultation with the customer. Several over-the-counter items can be prescribed by different store pharmacists; therefore we also need to store the prescription date and special instructions by the pharmacist prescribing a particular item. A pharmacist is one of two types of staff at the pharmacy store. The other type is general staff. For all staff, we store their name, date of birth. joined date and qualifications and level. For a pharmacist, we also need to store their registration number from the Pharmacy Board of Australia.

Prepare the following:

a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.

c) A SQL CREATE TABLE statement for the ITEM relation along with the creation of primary and foreign key constraints as required.