Design the logical structure of a database

Assignment Help Database Management System

Reference no: EM131318404

Relational Database Systems (COMP 1005)

Objective: This is an individual assignment aimed to give the student exposure in understanding, designing, building and analyzing database systems for a given real-time based scenario and be able to conduct optimal strategies for efficient management of databases.

Intended Learning Outcomes covered:

1. Design the logical structure of a database using Entity-Relationship diagram.

2. Apply normalization techniques to reduce redundancy in a database.

Task 1:

Complete the work proposal in Microsoft Word file format (may include possible answers based on your initial understanding). Work proposal for the assignment must be submitted before the end of week 7 (before 11:55 PM, November 24, 2016) and must include:
- What you will do with the given tasks: task 2, task 3 and the dates (timeline) by when they will be completed
- General overview of initial understanding of solutions to task 2 and task 3
- Identification of Literature Resources

Task 2: Scenario:

Apollo Hospitals (AH) is a popular medical service provider in Oman. AH wants to automate its business functions into an information system. The proposed information system must be developed keeping in mind the following business rules. As part of this, you need to analyse and develop an Entity Relationship model depicting the following various business requirements and functionalities for the proposed Pharmacy Information System of AH.

Dr. Prathap Reddy, Chairman, is aware that other multi-specialty hospitals are coming up in a big way and AH will face fierce competition from new healthcare systems. So, he has decided to take all measures to always be ahead of others in the market. It is increasingly important to AH that Reddy be able to analyse services rendered by AH and perception of patients more thoroughly.

The Pharmacy places requests to the Purchase Department whenever it is in need of drugs. Pharmacy is identified by unique id, name and telephone number. Purchase Department is identified by a unique number. The other elements are phone number, manager. The Purchase Department submits many orders or may not submit an order. An order is uniquely identified by an id. The other elements of order are placement date, fulfillment date, received date, order amount. An order consists of one or more drugs or items. A drug is supplied by one or more supplier. A supplier supplies many drugs or may not supply a drug. Whenever a supplier supplies a drug, AH wants to record the shipment details such as date of supply. The elements of drug are unique id, name, type, manufacturer id, manufacture date, expiry date, batch number, unit cost. A supplier is uniquely identified by id. The other elements of supplier are name, address, phone number, email, location and track record.

a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.

b) For the task 2a, analyse and recommend how the proposed ER model could accommodate the shipment and payments.

Task 3:

a) Normalize the below given Patient Prescription Bill to First Normal Form, Second Normal Form and Third Normal Form.

PHARMACY INFORMATION SYSTEM, APOLLO HOSPITALS

Patient Prescription Bill

Bill number: 0000501 Bill date: 02-May-2016

Prescription Number: 501 Patient Name: Mohsin

Patient Address: Al Khuwair Patient Category: M

Doctor ID: 129 Doctor Name: Imaan

*Pres. Date*	*Drug ID*	*Drug Name*	*Quantity*	*Unit Price*	*Total*
29/12/2015	1111	AAAAA	10	2.00	20.00
19/03/2016	1232	GGGGG	4	9.00	36.00
19/03/2016	2425	SYRINGE	10	0.50	5.00
21/03/2016	1111	AAAAA	10	2.00	20.00
21/03/2016	3339	PANADOL	10	1.00	10.00
				TOTAL LESS 10%	91.00 9.10
			NETT PAYABLE AMOUNT (OMR)		81.90

b) For the finally arrived normalized form in task 3a above, discuss how the redundancy has been minimized.

Task 4: Be ready for a written viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the written viva will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the written viva.

Note: Task 4 is compulsory. No marks will be awarded to tasks 2 & 3 if written viva is not taken by a student.

Reference no: EM131318404

Questions Cloud

Who does your country identify itself nationalistically : Who are the main actors in creating your country's foreign policy? Who does your country identify itself nationalistically? Does the countries nationalism play a role in determining domestic or foreign policy

Explain how the situation would be different : Next, imagine yourself as a practitioner of servant leadership. You have been appointed into the leadership role in this same circumstance. Explain how the situation would be different.

Describe factors you believe should be considered in risk : Describe other factors you believe should be considered in risk management. The assignment should be comprehensive and include specific examples. The paper should be formatted according to APA.

How long are the project and feeder buffers : What is the duration of the project? How much slack is currently available in the non-critical path? How long are the project and feeder buffers?

Design the logical structure of a database : Design the logical structure of a database using Entity-Relationship diagram - Apply normalization techniques to reduce redundancy in a database.

Which firm is firm a also comment on your reasons : Which firm is Firm A? Comment on your reasons.- Which firm is Firm B? Comment on your reasons.- Which firm is Firm C? Comment on your reasons.

Nww marginal tax rate : A call premium of 14% would be required to retire the old bonds, and flotation costs on the new issue would amount to $3 million. NWW's marginal tax rate is 40%. The new bonds would be issued when the old bonds are called.

Plot the original series, and each of the forecasts : Create a 4-period moving average forecast, an exponential smoothing forecast with alpha=0.2, and a linear trend forecast for your data.

Level of the firms current liabilities : Company A has inventory equal to $100,000 with a current ratio of 2.3 and quick ratio of 1.8, what is the level of the firms current liabilities?

User Account

All Pages