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:

An attachment has been uploaded for named "Task 2".

*** Words count = 1000 words

*** In-Text Citations and References using Harvard style.

*** An attachment has been uploaded named "Example" for how to solve the assignment.

*** The proposal must be submitted within 24 hours.