Reference no: EM132327583
Database Technologies Assignment - Data model development and implementation
Purpose of the assessment - The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain understanding of data model development. Then implement the data model using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Make an informed and critical assessment of database management systems.
b. Develop data models and implement DB systems.
c. Analyse business decisions related to DB information systems.
d. Demonstrate skills in building a database application using a commercially available database management system development tool.
Assignment Description -
1. Database Design
Knox private Hospital contracted with GreenIT services to develop and design their hospital database, the hospital IT Manager provided the following business rules to the GreenIT services software developer:
User view 1 requirement: Patients are identified by a PatientID, and their names, addresses, and ages must be recorded.
User view 2 requirement: Doctors are identified by a DoctorID. For each doctor, the name, specialty, and years of experience must be recorded.
User view 3 requirement: Every patient has a primary physician/doctor. Every doctor has at least one patient.
User view 4 requirement: Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription can have more than one drug.
User view 5 requirement: For each drug, identified by DrugID and the trade name and formula must be recorded.
User view 6 requirement: Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
User view 7 requirement: Each pharmacy has a unique name, address, and phone number.
Complete the information-level design for the database that satisfies the above constraints and user view requirements. In order to complete this information-level design you are required to answer questions given below 1(a) - (f).
Analyse the all user requirements given above and answer the following questions.
a. Identify and list entities described in all user requirements given above.
b. Add attributes to these entities and represent them as a collection of tables and attributes as below. Eg. Patient (PatientID, name, ...)
NB: Select suitable primary key for each table and underline them.
c. Outline at least 2 relationships between those entities. Eg. Every patient has a primary physician/doctor. - One-to-many,
d. Determine the functional dependences. Eg. PatientID ?name, address, age, ......
e. Normalise these tables.
2. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described.
NB: You need to use Visio, draw.io [1] or Lucidchart [2] or any other software tool, to create the ER diagram.
3. Build this model using MS Access/SQLite by creating these tables and Relationships. Populate these tables with appropriate data, at least 2 records in each table. Include following into your MS Word document.
I. If you are using MS Access include following screen shots
- Relationship diagram created in Access. (Select database tools → Relationships in the Access menu)
- Data sheet view and design view of your tables.
II. If you are using SQLite
- Screen shots of CREATE TABLE command and INSERT INTO commands.
4. Report Generation
a. Write a query to generate one example for useful information that can be obtained from this "Knox private Hospital" database.
b. Execute the query on the database and attach the screenshot of your output.
Section to be included in the report -
1. Database Design
a. Identify all entities
b. User Views as Tables
c. Relationships
d. Functional dependences.
e. Normalise tables
2. ER Diagram
3. Model building
4. Report Generation
a. Write a query
b. Execute the query
Attachment:- Assignment File.rar