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.
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.
- 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.
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
Overall Total: 19.200
- 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)