Find Trusted Online BN204 Database Technologies Assignment Help And Assessment Writing Service At Cost Effective Price!

Home   Course  
Previous << || >> Next

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

Database Technologies.jpg

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

Database Technologies1.jpg

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

Database Technologies2.jpg

b) Design view of the tables

Patient

Database Technologies3.jpg

Doctor

Database Technologies4.jpg

Pharmacy

Database Technologies5.jpg

Drug

Database Technologies6.jpg

Prescription

Database Technologies7.jpg

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

c) Datasheet View

Patient

Database Technologies8.jpg

Doctor

Database Technologies9.jpg

Pharmacy

Database Technologies10.jpg

Drug

Database Technologies11.jpg

Prescription

Database Technologies12.jpg

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;

Database Technologies13.jpg

Screenshot of Query 1 showing which is treated by which doctor

Report showing patient and doctors

Text0

Text1

Pat_name

Pat_addr

Pat_age

Doc_name

Speciality

Doc_exp

AccessTotalsPat_name

Thursday, May 30, 2019

4:28:56 PM

Andrew

Victoria

32

Anglina

Cardio

17

3

Thursday, May 30, 2019

4:28:56 PM

Mona

Melbourne

65

Roger

Kidney

20

3

Thursday, May 30, 2019

4:28:56 PM

Honey

Washinton

34

Ronalrdo

Knee

15

3

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);

Database Technologies14.jpg

Screenshot of Query2 showing drugs prescribed by doctor

Report showing the drugs prescribed by particular doctor to patient

Report showing drugs precribed bt doctor

Text0

Text1

Pat_name

Pres_id

Drug_id

Doc_name

Text2

AccessTotalsPat_name

Thursday, May 30, 2019

4:34:53 PM

Andrew

101

111

Anglina

Page 1 of 1

2

Thursday, May 30, 2019

4:34:53 PM

Mona

102

111

Roger

Page 1 of 1

2

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
Tag This :- WPS653593105DBS BN204 Database Technologies Assignment Help

get assignment Quote

Assignment Samples

Get Academic Excellence with Best Skilled Tutor! Order Assignment Now! Submit Assignment