COIT20247 Database Design and Development

Describe and illustrate the ANSI/SPARC architecture, and relate the ER model to this Understand the ER model as a conceptual modelling technique and develop ER models using the notation given in the set text Describe the role of business rules in an organisation Be aware of the flow, structure and state aspects of data 

 

Define & describe the difference between: Entity types and entity instances Relationship types and relationship instances Attributes and values Define and appropriately select: maximum & minimum cardinality Unique identifier.

Business Rules

• Each patient may have zero or more allergies
• Each appointment depends upon any one of the appointment booking type (Phone or direct or else)
• Each appointment booked by any one of the office staff
• Each practitioner may have zero or more specialization with number of years of experience
• Each practitioner/Office Staff should be a staff in CQMC
• Each practitioner may have one or more qualifications
• Each prescription may have zero or more number of prescriptions
• Each consultation belong to any one of the fee type
• Each consultation may have zero or more medical tests
• Each consultation are referred by any one of the practitioner
• Each consultation consulted by any one of the practitioner
• Each payment belong to any one of the consultation
• Each payment paid by any one of the patient

Relational Schema

Allergy (AllergyID, AllergyName)

MedicalTest (MedicalTestID, MedicalTestName, TestFee)

Specialization (SpecializationID, SpecializationName)

Qualification (QualificationID, QualificationName)

AppBookType (AppBookTypeID, AppBookTypeName)

FeeType (FeeTypeID, FeeTypeName, Fees)

Patient (PatientID, PatientName, Address, Phone, Email, Gender, DateOfBirth, Discount)

Staff (StaffID, StaffName, StaffAddress, ContactNumber)

OfficeStaff (StaffID, Department)

Foreign Key (StaffID) References Staff (StaffID)

Practitioners (PractitionerID, RegisterNum)

Foreign Key (PractitionerID) References Staff (StaffID)

PatientAllergy (PatientID, AllergyID, Description)

Foreign Key (PatientID) References Patient (PatientID),

Foreign Key (AllergyID) References Allergy (AllergyID)

GPSpecialization (PractitionerID, SpecializationID, NumOfYears)

Foreign Key (PractitionerID) References Practitioners (PractitionerID),

Foreign Key (SpecializationID) References Specialization (SpecializationID)

GPQualification (PractitionerID, QualificationID, CompletedYear)

Foreign Key (PractitionerID) References Practitioners (PractitionerID),

Foreign Key (QualificationID) References Qualification (QualificationID)

Appointment (AppointmentID, AppointmentDate, StaffID, AppBookTypeID, PatientID)

Foreign Key (AppBookTypeID) References AppBookType (AppBookTypeID),

Foreign Key (PatientID) References Patient (PatientID)Consultation (ConsultationID, ConsultationDate, ReferralDate, ReferGPID, PractitionerID, AppointmentID, PatientID, FeeTypeID, Notes)

Foreign Key (ReferGPID) References Practitioners (PractitionerID),

Foreign Key (PractitionerID) References Practitioners (PractitionerID),

Foreign Key (AppointmentID) References Appointment (AppointmentID),

Foreign Key (PatientID) References Patient (PatientID),

Foreign Key (FeeTypeID) References FeeType (FeeTypeID)

ConsultMTest (ConsultationID, MedicalTestID, Report)

Foreign Key (ConsultationID) References Consultation (ConsultationID),

Foreign Key (MedicalTestID) References MedicalTest (MedicalTestID)

Payment (PaymentID, PaymentDate, TotalPayment, Discount, ConsultationID, PatientID, AmountPaid)

Foreign Key (ConsultationID) References Consultation (ConsultationID),

Foreign Key (PatientID) References Patient (PatientID)

Prescription (PrescriptionID, ConsultationID, Notes)

Foreign Key (ConsultationID) References Consultation (ConsultationID)