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%)
Place Similar Order Now!
- Our Support Staff are online 24/7
- Our Writers are available 24/7
- Most Urgent order is delivered with 6 Hrs
- 100% Original Assignment Plagiarism report can be sent to you upon request.