# Acs Assignment 2, University of Winnipeg

Topics: SQL, Database, Relational model Pages: 5 (1445 words) Published: March 25, 2013
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
Prescription: Prilosec. Three tablets daily
Patient ID: 1849
 Marla Johnson
Date of Birth: Jan.31.1955
Prescription: Oxycontin Two teaspoons daily
Neulasta. Four tablets daily
Lamictal Two tablets daily
Patient ID: 1850
 Jane Doe
Date of Birth: Aug.01.1949
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?d) 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,...