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.
- Analyse the all user requirements given above, identify
and list all entities described in each user
requirement. (5 Marks)
- 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)
- Outline all relationships between entities.
Eg. One project may belong to
several departments – One-to-many, (5
Marks)
- Determine the functional dependences.
Eg. PatientID à name,
address,
…… (5
Marks)
- Then normalise these tables. Make the normalization
to 3NF. State for every step in the normalization, which
functional dependency that causes it. (10 Marks)
- 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)
- 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
- 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.
- Details of doctors registered at Knox
private (3 Marks)
- The management is interested in knowing Name and
address of patients who are older than 60 years. (3 Marks)
- 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
GradeMark
|
HD100%
|
DISTINCTION80%
|
CREDIT60%
|
PASS40%
|
Fail0-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
thebestassignmenthelp@gmail.com
The Best Assignment help is one of
the best website for assignment help. For more details you may contact us at
thebestassignmenthelp@gmail.com or call at +447418324884, +918607503827
No comments:
Post a Comment