Phase P2: DatabaseCreation

Notation

Primary keys are marked in bold. Foreign keys are marked with FK* and the referenced table is written in parentheses ().

Tables

Owners & Pets

Owner (owner_id, last_name, first_name, phone, email, address, gender)

Pet (pet_id, name, is_active, type, breed, age, medical_history, age_display, owner_id*(Owner))

Staff

Role (role_id, name, description)

Employee (employee_id, last_name, first_name, phone, email, address, date_employment, experience, gender, role_id*(Role), supervised_by*(Employee))

Specialization (specialization_id, specialization, license_number, employee_id*(Employee))

Certificate (certificate_id, certificate_name, category, employee_id*(Employee))

Scheduling

Appointment (appointment_id, date_appointment, reason, phone, owner_id*(Owner), pet_id*(Pet))

Examination_room (examination_room_id, room_number, type, capacity, status)

Examination (examination_id, date_examination, status, description, appointment_id*(Appointment), employee_id*(Employee), examination_room_id*(Examination_room))

Treatments

Treatment_type (treatment_type_id, name)

Treatment (treatment_id, date_treatment, notes, treatment_type_id*(Treatment_type), examination_id*(Examination))

Treatment_attribute (treatment_attribute_id, name, data_type, treatment_type_id*(Treatment_type))

Treatment_attribute_value (treatment_attribute_value_id, value, notes, treatment_attribute_id*(Treatment_attribute), treatment_id*(Treatment))

Prescriptions & Medicine

Prescription (prescription_id, date_start, date_end, description, examination_id*(Examination))

Prescription_medicine (prescription_id*(Prescription), medicine_id*(Medicine), dosage, num_days)

Medicine (medicine_id, name, manufacturer, description, shop_item_id*(Shop_item))

Shop

Shop_item_category (shop_item_category_id, name, parent_id*(Shop_item_category))

Shop_item (shop_item_id, name, price, stock, shop_item_category_id*(Shop_item_category))

Shop_item_attribute (shop_item_attribute_id, name, data_type, shop_item_category_id*(Shop_item_category))

Shop_item_attribute_value (shop_item_attribute_value_id, value, notes, shop_item_attribute_id*(Shop_item_attribute), shop_item_id*(Shop_item))

Discount (discount_id, type, value, description, date_from, date_to, shop_item_id*(Shop_item))

Billing

Coupon (coupon_id, code, type, value, valid_from, valid_to, usage_limit, usage_count, is_active, min_total)

Invoice (invoice_id, date_invoice, total, coupon_id*(Coupon), owner_id*(Owner))

Invoice_item (num_item, invoice_id*(Invoice), price, quantity, type, shop_item_id*(Shop_item), treatment_id*(Treatment))

Payment (payment_id, date_payment, amount, method, invoice_id*(Invoice))

Script for creating the tables and populating them with data

https://develop.finki.ukim.mk/projects/pawcare/attachment/wiki/DatabaseCreation/console_1.sql