ACS 1803 – ASSIGNMENT 2 Due: February 11, 2013 To be done individually. 1) a) 8 A hospital ward wants to computerize its data about patients and medications / dosages prescribed for the patients. {In your mind, compare this situation with the in-class example about students, courses and grades: student is like patient, course is like medication and grade is dosage}. Then, use the following fields of hospital data that are put into one file (table): patient No. , patient name, patient address, patient birth date; medication name, medication dosage (this means how many tablets or teaspoons of that medication the patient must take each day).
Use the following data: ? John Smith Date of Birth: Oct. 10. 1969 Address:123 Dialysis Way. Prescription: Prilosec. Three tablets daily Adavart . Three tablets daily Patient ID: 1849 ? Marla Johnson Date of Birth: Jan. 31. 1955 Address:842 Blossom St. Prescription: Oxycontin Two teaspoons daily Neulasta. Four tablets daily Lamictal Two tablets daily Patient ID: 1850 ? Jane Doe Date of Birth: Aug. 01. 1949 Address:725 Olive Ave. Prescription: Remicade. Three tablets daily Diovan. Four tablets daily Gardasil. One teaspoon daily Patient ID: 1851 Show this in one table with all your data records.
Then explain fully why this would not be a good way of organizing data on disk. b) 10 Convert the one table in the previous question to a relational database design. Fill each table in your design with data from the previous question such that no information is lost. Show how your design satisfies the criteria for being a relational database, and why it is better to store data on disk in this way rather than all in one table. c) 7 What is database management system? What steps would the hospital ward need to take to prepare its data to be ready for producing meaningful output from the DBMS? ) 5 What are two main forms of output from a DBMS? Provide one example of each form of output using hospital data from your database in b). Hint: Utilize the “Input-Processing-Output” Model and focus on the ways data can be produced i. e. Report versus Query. See Data Modeling Explanation from Lecture 4. 2 Notes for additional information. 2) 10 If a business organization has its data stored on disk as a relational database which is managed by a database management system such as Access, why might it want to go further and have a management information system constructed, that would use this relational database as input?
Why would just using Access directly not be enough for the organization? Explain fully with examples. ACS-1803-053 Assignment 2 1. a. ) Patient NO. | Patient Name| Patient Address| Patient Birth Day| Medication Name| MedicationDosage| 1849| John Smith| 123 Dialysis Way| Oct. 10. 1969| Prilosec| Three tablets daily| 1849| John Smith| 123 Dialysis Way| Oct. 10. 1969| Adavart| Three tablets daily| 1850| Marla Johbson| 842 Blossom St. | Jan. 31. 1955| Oxycontin| Two teaspoons daily| 1850| Marla Johbson| 842 Blossom St. | Jan. 31. 1955| Neulasta| Four tablets daily| 1850| Marla Johbson| 842 Blossom St. Jan. 31. 1955| Lamictal| Two tablets daily| 1851| Jane Doe| 725 Olive Ave. | Aug. 01. 1949| Remicade| Three tablets daily| 1851| Jane Doe| 725 Olive Ave. | Aug. 01. 1949| Diovan| Four tablets daily| 1851| Jane Doe| 725 Olive Ave. | Aug. 01. 1949| Gardasil| One teaspoon daily| Using one table to record all the data would be an unefficient way. As the table above, the same information such as: No. , patient name, address, birth day, has been record more than once, which causes the waste of store space and costs more labour time.
However, with the increase in data, it might make some mistakes by multiple-display the some information. For instance, we have five patients named Jane Doe, and each of them has three kinds of medications. When we search the key words “Jane Doe”, we will get 15 results with the same patient name, Jane Doe. It makes mistakes easy and takes more time when we have to find the only one information we need among such a bunch of results. b. ) Patient Personal Information: Patient Patient NO. | Patient Name| Patient Address| Patient Birth Day| 1849| John Smith| 123 Dialysis Way| Oct. 10. 1969| 850| Marla Johbson| 842 Blossom St. | Jan. 31. 1955| 1851| Jane Doe| 725 Olive Ave. | Aug. 01. 1949| Patient Medication Information: Medication Patient NO. | Medication Name| Medication Dosage| 1849| Prilosec| Three tablets daily| 1849| Adavart| Three tablets daily| 1850| Oxycontin| Two teaspoons daily| 1850| Neulasta| Four tablets daily| 1850| Lamictal| Two tablets daily| 1851| Remicade| Three tablets daily| 1851| Diovan| Four tablets daily| 1851| Gardasil| One teaspoon daily| The tables in relational databases organize data in rows and columns, simplifying data access and manipulation.
It is easier for manager to understand the relational model than put all data in one table. Besides, a relational database allows tables to be linked. And the linkage reduces data redundancy and allows data to be organized more logically. In a word, relational database is easier to control, more flexible, and more intuitive than approaches. c. ) The database management system (DBMS) a group of programs used as interface between a database and application programs, or a database and the user. It is used to create and implement the right database system ensures that the database will support both business activities and goals.
For the hospital ward, there are four basic steps need to take to prepare its data to be ready for producing meaningful output from a DBMS: 1. Setting schemas. Installing and using a large database involves “telling” the DBMS the logical and physical structure of the data and the relationships among the data for each user. 2. Establishing a data dictionary. In this case, the data dictionary should contain the following information: patient No. , patient name, patient address, patient birth date; medication name, medication dosage. . Storing and retrieving data. Establishing tables and input all the information, like “1849, John Smith, 123 Dialysis Way, Oct. 10. 1969”, into them according to the data dictionary. 4. Manipulating data and generating reports. After a DBMS has been installed, users can use it to review reports and obtain important information. d. ) There are two main forms of output from a DBMS, which are query and report. The query is a database function that extracts and displays information from a database given selection parameters.
Database users use Structured Query Language (SQL) to select and extract data from a database, and the SQL is the industry standard language for relational databases. The report is a compilation of data from the database that is organized and produced in printed format or presented on screen. The report generator is a specialized program that uses SQL to retrieve and manipulate data (aggregate, transform, or group), and the reports are designed using standard templates or can be custom generated to meet informational needs. Query Example:
Select “patient ID”, “Medication Name” from Medications Where Medications Name = “Adavart” Order By “Patient ID”; 1849| Adavart| Three tablets daily| Report Example: show a report of patients live on Olive Avenue. Patient No| First Name| Last Name| Street| Patient Birth date| 1851| Jane| Doe| 725 OliveAve. | 1949-08-01| 2. The Access is a database management system (DBMS), organizations use Access to implement database, after database designed. There are two main steps to use Access: step one is setting up the database, the DBA needs to create the database, figure out tables and link them together.
The second step is using the database through the DBMS, where we have to know the features to design and create queries and reports. We can also use Access as an information system developer, and use a customized information system for specific purposes and needs. That is the reason why it might want to go further and have a customized information system constructed, which would use this relational database as input. Using Access directly just only open a computer files in order to get or add information, it might not be enough for an organization.
The customized information system is created according to processes and specific needs in order to improve the operational efficiency. The customized information system consists two parts: up-front and controls. Up-front refers to its own menus, input screens, output (query) screens, and reports and, in the background, it will have a database with related tables, and programs that take the raw data from the database and convert it to the required queries and reports; the system will also have controls, that is, mechanisms that try to ensure that the output is correct and that the data is safe from accidental or deliberate destruction.
