Reference no: EM132473116

LO1: Construct a data model for a relational database management system

Objectives

1. Determine the business rules to describe the data requirement for a given business process.

2. Identify the entities and attributes for a relational database using the business rules.

3. Draw the conceptual model (ERD) for the proposed database using MS Visio.

4. Create the Data Dictionary for the proposed database.

5. Implement the proposed database using MS Access 2016.

6. Build some simple queries, forms, and reports for your database using MS Access 2016.

Assessment Overview

In this assignment, you will create a database for Fiji's Ministry of Health to help the ministry manage data about distribution of supplies that is drugs and equipment, to the health centers and major hospitals around Fiji.

Your database will store information about each item (drug or equipment) provided by the health deSectionment - including its name, supplier, price, the number of units currently available at the Government Pharmacy in Suva, and the number of units on order. For drugs, your system will store information about dosages and side effects. Doctors and nurses will use your database to get information about supplies and check on their availability.

Your database will maintain information about the quantities of specific items shipped to health centers and major hospitals throughout Fiji. The main supply is in the Government Pharmacy in Suva. Distribution clerks will use your database to record when supplies arrive at the main pharmacy. They will also enter data about weekly shipments to hospitals and health centers. This means that your database should store information about the items distributed to the hospitals and health centers from the Government Pharmacy.

Every week, the Chief Pharmacist will request the database for a report of distribution of supplies to each hospital and health center. Every month the database should generate a report on the amount of supplies currently available.

As Section of this assignment, you will create a Database Design Report, MS Visio file, and a MS Access database. Only the MS Access database and the Database Design Report will be uploaded on Moodle

Section A - Database Design Report in PDF format

Submit Section A in ONE PDF document with the answers for each Section below under separate sections/headings. The sections are as follows:

Part 1. Cover Page
• INCLUDE the ID number and full name, Lab Day and Time.
Part 2. Table of contents

Part 3. Introduction
• Introduce the business process, the role of the database, and the advantage(s) of using the database for this Sectionicular scenario.

Part 4. Business Rules (BR)
• From the scenario given, identify the business rules (BR). The BRs should lead directly to your ERD and capture concisely the entities/relationships in the rule
• Briefly explain each business rule, that is, explain the importance of each business rule to your database. You should have a valid reason for including the business rule.

Part 5. Entity Relationship Diagram (ERD)
• Create an Entity Relationship Diagram (ERD) using Crow's Foot notation in MS Visio. Label the relationships as well, and copy and paste it into your PDF document. There is no need for you to submit the MS Visio file.

Part 6. Data Dictionary
• Create a data dictionary for your database. A sample Data Dictionary is on Moodle beneath the Assignment 1 specifications link. Follow the same format as in the sample.

Part 7. Conclusion
• Discuss what you have achieved in this assignment, and difficulties that you have faced while doing the assignment.

Section B- MS Access Database (DB)

Part 1. Using your ERD and Data Dictionary, create the required tables in a MS Access Database.

Part  2. Enter some sample data into the tables, for example 10 - 20 records per table.

Part  3. Create queries to answer the following questions:

a. List the Item ID, Name, Item type and Cost price of all items sorted by first the item's type (equipment/drug) and then by item name.

b. Create a parameterized query that requests for the item ID and displays the item's ID, Name, Type, and Purchase Details. The Purchase Details includes the date purchased, quantity purchased, and supplier name. Each item can have multiple purchases.

c. List the Item ID, Name, Item type and Average Cost Price of all items purchase in the last 30 days. Sort the list by first the item's type (equipment/drug) and then by item name.

Part  4. Create forms in MS Access to allow you to enter or update the table records. All data entry or update should be done using a form.

Part  5. Create two reports in MS Access:
a. One report based on the query from 3 Section (a) above.
b. Another report based on the query from 3 Section (c) above.

Attachment:- Relational database management system.rar