BN204 Database Technologies, Melbourne Institute Of Technology, Australia

DON'T MISS YOUR CHANCE TO EXCEL IN DBMS ASSIGNMENT! HIRE TUTOR OF EXPERTSMINDS.COM FOR PERFECTLY WRITTEN BN204 DATABASE TECHNOLOGIES ASSIGNMENT SOLUTIONS!

Question 1: Analyse the all user requirements given above and answer the following questions.

a. Identify and list entities described in all user requirements.

Answer: Database Design of Knox Private Hospital

Knox Private Hospital provides health care services to various people. The database of the Knox Private Hospital Contains various entities named as Patient, Doctor, Pharmacy, Drug and Prescription. These entities have various attributes associated with them.

Entities in the database are

• Patient

• Doctor

• Pharmacy

• Drug

• Prescription

b. Add attributes to these entities and represent them as a collection of tables and attributes.

Answer: Attributes associated with the Entities

• Patient(Pat_id,Pat_name,Pat_addr,Pat_age,Doc_id,Pres_id)

• Doctor(Doc_id,Doc_name,Speciality,Doc_exp)

• Pharmacy( Pname,Paddress,Pcontact)

• Drug(Drug_id, Tradename, Formula, Pname, Price)

• Prescription(Pres_id,Drug_id,Doc_id)

Information level design

c. Outline at least 2 relationships between those entities.

Answer: Relationships between attributes

1) Every patient has at least one doctor and every doctor has at least patient -One-to- many Relationship.

2) Patient purchase one or more drugs prescribed n=by doctor -One-to- many Relationship.

3) Each patient obtains one or more prescriptions from different doctors- One-to- many relationship.

4) Each doctor provides prescriptions to patients- One- to- many relationship.

5) Each doctor prescribes one or more drugs to patient- One-to-many relationship.

6) Pharmacy makes many drugs available- One-to-many relationship.

d. Determine the functional dependences.

Answer: Functional Dependencies

• Pat_id→Pat_name, Pat_addr,Pat_age

• Doc_id→Doc_name,Speciality,Doc_exp

• Pname→Paddr,Pcontact

• Drug_id→Tradename, Formula,Price

Assumptions: Each Pharmacy can have single address and contact number.

Each patient is treated by primary doctor

GET ASSURED A++ GRADE IN EACH BN204 DATABASE TECHNOLOGIES ASSIGNMENT ORDER - ORDER FOR ORIGINALLY WRITTEN SOLUTIONS!

e. Normalise these tables.

Answer: Normalization of Tables

• Patient(Pat_id,Pat_name,Pat_addr,Pat_age,Doc_id,Pres_id): The primary key is Pat_id which identifies the patient. There will be one Patient name per patient id because only one name will be recorded for each patient. The patient name will be recorded with patient age and address and only a single address will be recorded for each patient. Therefore, the relation is in 1NF

The primary key is Pat_id as other attributes are not unique. All the other attributes are functionally dependent on the primary key. So ,relation is in second normal form.

The Pat_name can not determine the other attributes as two patients can have same name likewise , Pat_addr and Pat_age also. So, there are no transitive dependencies in the table. So, it is in third normal form.

• Doc_id (Doc_name,Speciality,Doc_exp): The primary key is Doc_id which identifies the Doctor. There will be one doctor name per Doctor id because only one name will be recorded for each doctor. The patient name will be recorded with Speciality and Experience and only a single Experience will be recorded for each Doctor. Therefore, the relation is in 1NF

The primary key is Doc_id as other attributes are not unique. All the other attributes are functionally dependent on the primary key. So , relation is in second normal form.

The Doc_name can not determine the other attributes as two Doctors can have same name likewise , Doc_exp and speciality also. So, there are no transitive dependencies in the table. So, it is in third normal form.

• Pname (Paddr,Pcontact): The primary key is Pname because every pharmacy has unique name registered by law and It can not be null. The pharmacy address and the contact details are recorded with the name and each pharmacy can have single address. So, it is in 1NF. 

The primary key is Pname as other attributes are not unique. All the other attributes are functionally dependent on the primary key . So , relation is in second normal form.

The Paddr can not determine the other attributes as two Pharmacies can have same address likewise Pcontact also as if the owner of two pharmacies is same. So, there are no transitive dependencies in the table. So, it is in third normal form.

• Drug_id→Tradename, Formula,Price: The primary key is Drug_id which identifies the Drug. There will be one trade name per Drug id because only one tradename will be recorded for each drug. The trade name will be recorded with formula and Price and only a single Price will be recorded for each Drug. Therefore, the relation is in 1NF

The primary key is Drug_id as other attributes are not unique. All the other attributes are functionally dependent on the primary key. So , relation is in second normal form.

The Tradename can not determine the other attributes as two Drugs can have same tradename likewise , Formula and Price also. So, there are no transitive dependencies in the table. So, it is in third normal form.

Question 2: Represent the structure of your database visually by using the entity-relationship (E-R) diagram.

Answer:

E-R diagram of Knox Private Hospital

Question 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.

Answer: 1. Screenshots of MS ACCESS

a) Relationship between the tables the tables

b) Design view of the tables

Patient

Doctor

Pharmacy

Drug

Prescription

MOST RELIABLE AND TRUSTWORTHY BN204 DATABASE TECHNOLOGIES ASSIGNMENT HELP & HOMEWORK WRITING SERVICES AT YOUR DOORSTEPS!

c) Datasheet View

Patient

Doctor

Pharmacy

Drug

Prescription

2. Report Generation

a) Example and screenshot of query

Query 1: SELECT Patient.Pat_name, Patient.Pat_addr, Patient.Pat_age, Doctor.Doc_name, Doctor.Speciality, Doctor.Doc_exp

FROM Doctor INNER JOIN Patient ON Doctor.Doc_id = Patient.Doc_id;

Screenshot of Query 1 showing which is treated by which doctor

b) Additional Queries

Query 2: SELECT Patient.Pat_name, Patient.Pres_id, drug.Drug_id, Doctor.Doc_name

FROM Doctor INNER JOIN (Patient INNER JOIN (drug INNER JOIN Prescription ON drug.Drug_id = Prescription.Drug_id) ON Patient.Pres_id = Prescription.Pres_id) ON (Doctor.Doc_id = Prescription.Doc_id) AND (Doctor.Doc_id = Patient.Doc_id);

Screenshot of Query2 showing drugs prescribed by doctor

Report showing the drugs prescribed by particular doctor to patient

WE HELP STUDENTS TO IMPROVE THEIR GRADES! AVAIL TOP QUALITY BN204 DATABASE TECHNOLOGIES ASSIGNMENT HELP AND HOMEWORK WRITING SERVICES AT CHEAPER RATE!

Get best Melbourne Institute of Technology, Australia Assignment Help Services for its related courses such as:

• BN103 - Platform Technologies Assignment Help
• BN104 - Operating Systems Assignment Help
• BN110 - Information Systems Fundamentals Assignment Help
• BN106 - Networking Fundamentals Assignment Help
• BN108 - Programming for Networking Assignment Help
• BN109 - Web and Multimedia Systems Assignment Help
• BN200 - Network Security Fundamentals Assignment Help
• BN201 - Professional Issues of IT Assignment Help
• BN202 - Internetworking Technologies Assignment Help
• BN205 - Project Management Assignment Help