Reference no: EM13937881

Using, Finkelstein methodology, prepare the following:

1: ER diagram + 2: List of Relations + 3: Normalisation to 3NF 4: SQL Table Creation statement Refer attached file for details. Take your time to read both the Qs and understand before quoting.

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. CIS 2002 - Assignment 2 3 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