”
Assignment Instructions

Case Study – week 6 – (18%):

You were just hired by a hospital system to help them keep track of their nurses. The hospitals have been depending on a spreadsheet put together by their IT department. Their lack of a database is hurting the image of the hospitals as they have been unable to properly manage their nurses. They put together a spreadsheet (see below) and they have been using it as a database of sorts.

The following key points have been agreed:
1 Each HospitalID (H-ID) is unique
2 Each Hospital employees many nurses
3 Each nurse can be employed by only one hospital (this may not be practical in today’s environment, but we need to design the system as specified)
4 Each NurseID is unique
5 The H-Tel filed contains the main switchboard number for the hospital
6 We will track only one degree for each nurse
7 Consider the domain for each field – the sample data does not represent all valid values
8 No fields beyond those in the spreadsheet are needed

Your objective is to replace the spreadsheet with a relational database. Your submission will consist of a word compatible document, and an Access DB.

Below is a spreadsheet that the IT department has put together for our use.

NurseID

LastName

Degree

H-ID

H-St

H-City

H-Tel

N01003

Smith

LPN

Hos100

South St

Miami

555-878-1234

N01004

Rosenberg

RN

Hos300

East St

Davis

555-656-4567

N01005

Jones

NP

Hos100

South St

Miami

555-878-1234

N01006

Garcia

CNS

Hos200

North St

Davis

555-888-8765

N01007

Rosenberg

APN

Hos200

North St

Davis

555-888-8765

Here is the table notation for the spreadsheet:
Nurse (NurseID, LastName, Degree, H-ID, H-St, H-City, H-Tel)
One functional dependency is:
H-ID – > H-St, H-City, H-Tel

The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task: Organize your Word compatible document to match the tasks below, label each section.
Name your document Last Name_CaseStudy (i.e. Smith_CaseStudy). When you are asked to provide an explanation or description, include sufficient content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.

SUGGESTION: Review the topics in the Terms and Concepts Forum. There is also a normalization MP4 file that can be downloaded and viewed in the Resources section.

Use the section labels below to separate your responses.
1) Review the existing spreadsheet, and the sample data and document any assumptions (beyond those in the key points) you need to make about the content. (10%)

2) Submit “one” complete functional dependency analysis (all fields must be included at least once), use the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5 to document the dependencies.

The functional notation is: Field A -> Field B, Field C, means that Field A is a determinant for Field B and Field C.
Similarly if a field is dependent on the combination of values of two fields then: Field X, Field Y -> Field Z

There may be one or more rows of functional dependency notation. Explain one row in plain English (one simple way is to use the field names and values in this exercise). (20%)

3) Explain the relationship between the entities (tables) – (one-to-many, many-to-many). (10%)

4) Work through the normalization process. An example of table notation is: TableName (Field1Name, Field2Name, Field3Name, etc.) underline primary key field(s).

4a) Submit a complete set of 1NF table(s), you must use the above table notation, include a plain English statement of why the table(s) is (are) in 1NF – base the explanation on the definitions in the Connolly text (or the Terms and Concepts forum) (10%)
4b) Submit a complete set of 2NF table(s), you must use the above table notation, include a plain English statement of why the table(s) is (are) in 2NF – base the explanation on the definitions in the Connolly text (or the Terms and Concepts forum) (10%)
4c) Submit a complete set of 3NF table(s), you must use the above table notation, include a plain English statement of why the table(s) is (are) in 3NF – base the explanation on the definitions in the Connolly text (or the Terms and Concepts forum) (10%)

5) Create an MS Access database, including the data provided, using only the 3NF tables. Name your database Last Name_CaseStudy (i.e. Smith_CaseStudy). Upload the database to the assignment area as one of the deliverables (5%).
6) Create a Query (In MS Access) that may include one or more tables, as needed, that provides the results for the following request: List each NurseID, their last name, their degree, the H-ID and City where they are employed. Sort the results by H-ID (Ascending) and then by NurseID (Descending values) (5%)
7) Create one Form: New Hospital Input – this form should include all relevant fields needed (In MS Access) (5%)
8) Create One Report: Hospital List – this report should include any relevant fields that you think might be needed (In MS Access) (5%)
9) Content organization, spelling grammar, clarity (10%)