You were just hired by a hospital system to help them keep track of their nurses. The hospitals have been depending on a solution put together by their IT department. Their lack of 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 We will track only one nursing degree for each nurse
6 Consider the domain for each field - the sample data does not represent all valid values
7 No fields beyond those in the spreadsheet are needed (clearly explain the rationale behind including any other field!)
Your objective is to replace the spreadsheet with a relational database. Your submission will consist of a word compatible document, and an Access DB.
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, you mustinclude 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.
You must use the following outline in your submission - 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.
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. Do not submit functional dependencies for each normal form - only one set of functional dependencies are required.
The 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 dependencies. Consider all potential values for each field when determining dependencies (do not rely solely on the values shown in the below spreadsheet.) Explain one row of the submitted functional dependencies in plain English (one simple way is to use field names and values in this exercise).
3) Explain the relationship between the tables - consider the multiplicity (one-to-many, many-to-many).
4) Submit a complete set of 1NF, 2NF, and 3NF tables (ensure you label and identify 1NF, 2NF and 3NF tables). Ensure a complete set of tables (and fields) are included for each normal form (repeat the same table if appropriate, I expect to see a complete set of tables for each normal form level).
You must use the normalization definitions and process in the Connolly text (or you may use the General Forum definitions and processes - designate which you are using). Use normal form "definitions" to ensure that your set of tables are in 1NF, 2NF and then in 3NF. DO NOT SUBMIT SPREADSHEET BASED TABLES
Include a plain English description to explain why the 1NF tables are in 1NF, why the 2NF tables are in 2NF, and why the set of 3NF tables are in 3NF (you must mix plain English and normal form definitions in your explanation - but do not simply copy the definitions). One approach may be to use fields names and values as examples in your description - i.e. Field A is a determinant for Field B because...
You MUST use the table shorthand representation shown on page 111 (also shown on page 149), which describes the tables found on page 112 of the Connolly text (ensure you underline primary key fields). The notation is:
TableName (Field1Name, Field2Name, Field3Name, etc.) underline primary key field(s).
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.
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, degree, and the H-ID and City where they are employed. Sort the results by NurseID (Ascending) and then by H-ID (Descending values).
7) Create one Form: New Hospital Input - this form should include all relevant fields needed (In MS Access).
8) Create One Report: Hospital List - this report should include any relevant fields that you think might be needed (In MS Access).
9) Content organization, spelling grammar, clarity.
Here is the table notation for the spreadsheet:
Nurse (NurseID, LastName, Degree, H-ID, H-St, H-City, H-Tel)
Below you will find the spreadsheet that the IT department has put together for our use.
Hint: review the Normalization Part1, Parts and Suppliers One-To-Many, and Parts and Suppliers Many-To-Many topics in the Terms and Concepts Forum - the content may be useful.