Ask DBMS Expert


Home >> DBMS

Assignment: Information and Knowledge Management

Background

Three years ago, primary care providers John Anderson and Melanie Miller decided to combine their individual practices to form Community Health and Wellness Clinic. They wanted to concentrate on preventive medicine by helping patients maintain health and wellness. Dr. Anderson recently asked you to work with him as an IT consultant. He wants you to help Community Health develop an Access database system that will support the clinic's operation and future growth.

Part 1 - Creating Tables

Instructions for Table Creation:

Create an Access Database with the title: Community Health and Wellness Clinic
Create an Access Database table with the title: Providers
Create a second Access Database table with the title: Nurses
Create a third Access Database table with the title: Patients
Create a fourth Access Database table with the title: Patient Records

The table Providers should have the following field names:

Provider Last Name
Provider First Name
Provider ID
Address
Zip Code
Office Telephone
Mobile Phone
E-mail address

The table Nurses should have the following field names:

Nurse Last Name
Nurse First Name
Nurse ID
Address
Zip Code
Office Telephone
Mobile Phone
E-mail address

The table Patients should have the following field names:

Patient Last Name
Patient First Name
Medical Record Number
Address
Zip Code
Office Telephone
Mobile Phone
E-mail address

The table Patient Records should have the following field names:

Account Number
Medical Record Number
Provider ID
Nurse ID
Date Admitted
Chief Complaint
Record Criteria

Select the appropriate data type for each field in each table.

Select the primary key for each table.

Create ten realistic database records for each of the tables. You may be creative as you enter the information in each field, but ensure that your entries are valid. For example, do not enter ABCDE into every field in an entry-include realistic names, numbers, etc.

As you create 10 records for the PATIENT RECORDS table, make sure that the information in the Medical Record Number, Provider ID, and Nurse ID fields corresponds to previous information you entered into the PROVIDERS, NURSES, and PATIENTS tables. This will ensure that when you establish relationships and run queries, you will return meaningful results.

Note: A patient can have more than one record in PATIENT RECORDS because patients can have many visits to the clinic.

Part 2 - Establishing Relationships

Instructions for Establishing Relationships

Begin by reviewing how to establish relationships in Microsoft Access. Next, using the tables that were developed in Part 1 of the assignment and using Access:

Create a relationship between PROVIDERS and PATIENT RECORDS.
Create a relationship between PATIENTS and PATIENT RECORDS.
Create a relationship between NURSES and PATIENT RECORDS.

There is no submission this week.

Reminder: You will submit the database with the established relationships along with a 1- to 2-page paper explaining how you established the relationships, the primary and foreign keys, and the factors that influenced your choices.

Part 3 - Queries and Reports

You began working on Parts 1 and 2 of this Assignment. This week you begin working on Part 3.

Instructions - Creating Queries

Create a query that displays all patient records for each provider (PROVIDERS and PATIENT RECORDS)

Provide an appropriate query title.

Create another query that displays the nurse and patient information for each patient visit listed in PATIENT RECORDS. This query should display the following fields from PATIENT RECORDS, PATIENTS, and NURSES:

Nurse Last Name
Nurse First Name
Patient Last Name
Patient First Name
Date Admitted
Chief Complaint
Provide an appropriate query title.

Create another query that displays the provider and nurse information for each patient visit listed in PATIENT RECORDS. This query should display the following fields from PATIENT RECORDS, PATIENTS, PROVIDERS, and NURSES:

Provider Last Name
Provider ID
Nurse Last Name
Nurse ID
Medical Record Number
Account Number
Patient Last Name
Date Admitted
Chief Complaint
Provide an appropriate Query title.

Instructions - Creating Reports

Prepare two reports as follows from queries #2 and #3 created above:

Create a report from each customizing the header to "Community Health and Wellness Clinic"

Using query #2 create a summary report; group the contents under Nurse Last Name

Using the query #3 create a summary report; group the contents under Provider Last Name.

You will submit the database with the established relationships along with a 1- to 2-page paper explaining how you established the relationships, the primary and foreign keys, and the factors that influenced your choices.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92394290
  • Price:- $80

Priced at Now at $80, Verified Solution

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As