Assignment help
Mobile Android Application
January 21, 2018
BUGEN 5930 Business, Society & the Planet
CIS8100 E-Commerce Implementation
January 21, 2018

MN405 DM Development & Implementation

BUGEN 5930 Business, Society & the Planet

MN405 DM Development & Implementation

Assignment Description       60 Marks

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:

Complete the information-level design for the database that satisfies the following constraints and user view requirements. In order to complete this information-level design you are required to answer questions given below 1(a) – (e)

 

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.

  1. Analyse the all user requirements given above, identify and list all entities described in each user requirement.       (5 Marks)
  1. Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes. You are required to arrange them as given in the example below.

NB: Select suitable primary key for each table and underline them.

Eg.        Patient (PatientID, name,…….)                                                                       (5 Marks)

  1. Outline all relationships between entities.

Eg. One project may belong to several departments – One-to-many,                          (5 Marks)

 

  1. Determine the functional dependences.

Eg.  PatientID  à name, address,  ……           (5 Marks)

 

  1. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency that causes it.  (10 Marks)
  1. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that doesn’t show from the problem, they must be described.

You are required to use Visio or any other software tool to create the ER diagram.  (10 Marks)

  1. Build this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table. (10 Marks
  1. Write SQL queries for the following questions and execute them on the database you created in Question 4.

Include screen shots of the outputs and all SQL statements you used to answer following questions this model using MS SQL Server by creating these tables and Relationships.   Populate these tables with appropriate data, at least 3 records in each table.

  1. Details of doctors registered at Knox private    (3 Marks)
  1. The management is interested in knowing Name and address of patients who are older than 60 years.  (3 Marks)
  1. Explain how to formulate a query to print Prescription of a patient.

Hint : You need to print patient name, drugs and doctor’s ID by using a suitable query. (4 Marks)

Marking criteria:

Example of marking criteria is shown in the following table. Marks are allocated as follows:

Section to be included in the report Description of the section Marks
1.    
a.       identify all entities Identify entities in all user requirements 5
b.       User Views as Tables Represent the User Views as a collection of Tables 5
c.       Relationships Determine the relationships between entities 5
d.       Functional dependences. Determine Functional dependences. 5
e.       Normalise tables Analyse above tables and normalise 10
2.    
ER Diagram Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions 10
3.Model building Model building on MS access 10
4. Write SQL queries Writing queries to extract data 10
  TOTAL marks or the Report 60

 

Example Marking Rubric for Assignment 2 Report: Total Marks 60 for the report

 

Grade

Mark

HD

100%

DISTINCTION

80%

CREDIT

60%

PASS

40%

Fail

0-40%

 User Views as Tables

 

Ability to think critically and analysed clearly

 

Relevant and soundly analysed.

 

Generally relevant and analysed.

 

Some relevance and briefly presented. Not relevant to the assignment question.
 Relationships

 

 

 

 

 

Demonstrated   excellent ability to think critically and identified relationships Demonstrated good ability to think critically and identified relationships. Demonstrated   ability to think critically and identify some relationships Demonstrated   some ability to think critically.

 

 

 

Did not demonstrate ability to think critically

 

 

Normalise

Logic is very clear and easy to follow

 

Logic is clear and convincing

 

Mostly consistent and logical

 

Adequate cohesion and conviction

 

Arguments are confused and disjointed
 Dependency

 

 

All elements are present and very well integrated. Components present and well integrated Components present and mostly well integrated Most components present Representation lacks structure.
ER Diagram

 

 

 

Extremely good effort. All elements are present and very well integrated.

Outstanding

Good effort. Most components are present and well integrated Good effort made but not outstanding Made some effort  with inaccuracies Very little attempt. Lazy effort with inaccuracies
 Model building and queries

 

Extremely good effort. All elements are present Good effort. Most components are present Good effort made but some components are missing Made some effort  with inaccuracies Very little attempt. Lazy effort with inaccuracies

Buy MN405 DM Development & Implementation Assignment at punjabassignmenthelp@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *