Relational Database Systems

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.

Proposal: (280 Words)

Complete the work proposal in Microsoft Word file format (may include possible answers based on your initial understanding). Work proposal must include:

a) Understanding of deliverables – a detail description of deliverables.

b) General overview of proposed plan – initial understanding of solution to task 2 which includes, the name of entities, associative entities and relationships and a brief write up on the concept of referential integrity. Initial understanding of solution to task 3 which includes the process to be followed for normalization and a brief write up on the concept of data integrity.

Task 1:

Scenario:

Consider the following scenario modeling pharmacy management system

The Muscat Pharmacy places requests to the Purchase Department whenever the stock of medicines needs to be reordered. Pharmacy is identified by pharmacy id, pharmacy name, location and contact number. Purchase Department is identified by a purchase department id. The other elements are contact number and manager name. The pharmacy may send one or many requests but each request belongs to only one pharmacy. The Purchase Department submits many orders or may not submit any order but each order belongs to only one department.

An order is uniquely identified by an order id. The other elements of order are placement date, fulfillment date, received date, order amount. An order consists of one or more medicines, also each medicine can be a part of one or more orders. The attributes of medicine are medicine id, medicine name, type, manufacturer id, manufacture date, expiry date, batch number, unit cost. A medicine is supplied by one or more supplier.

A supplier supplies many medicines but must supply at least one medicine. Whenever a supplier supplies a medicine, their company wants to record the delivery details such as delivery date, payment status. A supplier is uniquely identified by supplier id. The other elements of supplier are supplier name, supplier address, supplier contact number, email and location.

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

b) Analyze and discuss about the possible integrity constraints that can be considered with reference to the above scenario. State any assumptions necessary to support your design.

Note: Minimum 5 integrity constraints are expected from different categories.

Task2:

 

  1. Normalize the below given bill summary of a patienttoFirst Normal Form, Second Normal Form and Third Normal Form. Make assumptions for the identification of the primary key wherever necessary.
Atlas Pharmacy

Bill Summary

 

Patient No.: P1000                                                                                          Bill No.: D18-12345

Name:  Abdul Rahman                                                                   Bill Date: May 10, 2018

Age:  60                                                                                                Salesperson Id: S12

Contact No.:  97647948                                                  Salesperson: Geoby Paul

Address: Al Khuwair, Oman

 

Treatment details:

Medicine code Medicine_name BatchNo Manufacturing date Expiry date Quantity Unit Price Total Price
LT213 CBC B1213 12/12/2017 12/12/2020 1 5.000 5.000
MI435 Dextrose MN7888 9/3/2016 12/1/2018 2 3.000 6.000
M987 Suprax 500mg BNG677 12/1/2015 1/12/2020 4 0.250 1.000
MI112 Medical kit TY78 1/12/2016 12/12/2019 1 5.000 5.000
M987 Suprax 500mg ASF45 22/10/2017 1/12/2019 4 0.250 1.000
M768 Lorinase Y7888 3/1/2018 12/12/2020 1 1.200 1.200

 

Overall Total: 19.200

 

  1. Discuss how the referentialintegrity can be appliedto the above scenario and how it helps in preventing loss of data. (At least 400 words). (15 marks)