Ask a question from expert

Ask now

Database Design and Implementation in MS Access

12 Pages1110 Words215 Views
   

Added on  2020-05-16

Database Design and Implementation in MS Access

   Added on 2020-05-16

BookmarkShareRelated Documents
Running head: DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESSKnox Private Hospital Database Design and Implementationin MS AccessName of the StudentName of the University
Database Design and Implementation in MS Access_1
DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS1Table of Contents1. Information Level Design:...........................................................................................................2a. Entity Identification:....................................................................................................................2b. Attribute Identification:...............................................................................................................2c. Relationship among the Entities:.................................................................................................2d. Functional Dependencies:............................................................................................................2e. Normalization:.............................................................................................................................32. Entity Relationship Diagram:......................................................................................................53. MS Access Model building:........................................................................................................64. SQL Queries:...............................................................................................................................94.a. Details of doctors registered at Knox Private Hospital:............................................................94.b. The management is interested in knowing the names and addresses of patients who are olderthan 60 years..................................................................................................................................104.c.Prescription:.............................................................................................................................11Bibliography:.................................................................................................................................12
Database Design and Implementation in MS Access_2
DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS21. Information Level Design:a. Entity Identification: The entities of the Knox Private Hospital database are patients, doctors, drugs andpharmacies. b. Attribute Identification:The attributes of the entities has been described in the below.Patient (PatientID, name, address, contactNumber, age, sex, appointmentID,appointmentdate, time, prescriptionID, prescriptiondate, drugName, drugQuantity, doctorName,costOfDrug)Doctor (DoctorID, name, specialty, experience)Drug (DrugID, name, formula, MRP, pharmacyName, cost, quantity, deliveryDate)Pharmacy (PharmacyID, name, address, phone_number)c. Relationship among the Entities:i.One patient will see a primary doctor but a doctor will see many patients – One-to-Manyii.Various drugs can be sold by various pharmacies – Many-to-Manyiii.Many patients can consume may drugs – May-to-Manyiv.Many doctor can prescribe various drugs – Many-to-Many
Database Design and Implementation in MS Access_3
DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS3d. Functional Dependencies:PatientID name, address, contactNumber, age, sex, AppointmentID, appointmentdate,time, PrescriptionID, prescriptiondate, drugName, drugQuantity, doctorName, costOfDrugAppointmentID appointmentDate, doctorName, timePrescriptionID prescriptiondate, drugName, drugQuantity, doctorNamedrugName, drugQuantity costOfDrugDoctorID name, specialty, experienceDrugIDname, formula, MRP, pharmacyName, cost, quantity, OrderID, orderDateOrderID orderDate, pharmacyName, quantitypharmacyName, quantity costPharmacyID name, address, phone_numbere. Normalization:1st Normal Form: The 1st normal form states that each of the attribute in the table has toconsist of an atomic value. Attributes in the tables does not contain more than one value. Thusthe table is in the first normal form.2nd Normal Form: The second normal form defines that the tables has to be in firstnormal form and there must not be any partial dependency between the column and primary key.Patient (PatientID, name, address, contactNumber, age, sex)Appointment (AppointmentID, PatientID, DoctorID, appointmentDate, time)
Database Design and Implementation in MS Access_4

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
DATABASE TECHNOLOGIES.
|10
|496
|1

Database Design Assignment
|9
|448
|356

MN405 Data Model Development and Implementation Assignment
|8
|439
|68