Describe the data requirement for a given business process

Assignment Help Other Subject

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

Reference no: EM132473116

Questions Cloud

Inflation rate are measured in percentage points : "If the equilibrium real interest rate (r) is fixed at 4% and expected inflation is 2%, then the real money balance is 800." True/False/Uncertain, explain.

Compare the budgeted measures with the actual results : Compare the budgeted measures with the actual results. Decide which results should be brought to the attention of each manager.

Elasticity of demand at the price : What is likely to be the point elasticity of demand at the price you decide to charge?

Prepare personal balance sheet for tom : The following table shows the items of assets, liabilities, cash inflows, and cash outflows for Tom in September. Prepare Personal Balance Sheet for Tom

Describe the data requirement for a given business process : Determine the business rules to describe the data requirement for a given business process and Draw the conceptual model (ERD) for the proposed database

What are the types of costs : As the accounting department lead, what are the types of costs you should consider in working with the production supervisor to evaluate the problem?

Derive the function that describes the demand : Derive the function that describes the demand for X as a function of the prices of X and Y and of income (budget) B.

What was three-variance breakdown : The total factory overhead spending variance in 2016, based on a three-variance breakdown of the total overhead variance for Bluecap Co., was what

Beginning of the month than the end : ii) You are uncertain about your total monthly spending? iii) You spend more at the beginning of the month than the end?

User Account

All Pages