Pet Hospital Example

db.sql

CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(255) NOT NULL
);

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

CREATE TABLE owners (
    owner_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20),
    email VARCHAR(255)
);

CREATE TABLE pets (
    pet_id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT,
    name VARCHAR(255) NOT NULL,
    species VARCHAR(255) NOT NULL,
    breed VARCHAR(255),
    age INT,
    FOREIGN KEY (owner_id) REFERENCES owners(owner_id)
);

CREATE TABLE appointments (
    appointment_id INT AUTO_INCREMENT PRIMARY KEY,
    pet_id INT,
    user_id INT,
    appointment_date DATETIME NOT NULL,
    notes TEXT,
    FOREIGN KEY (pet_id) REFERENCES pets(pet_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE services (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    service_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE appointment_services (
    appointment_id INT,
    service_id INT,
    PRIMARY KEY (appointment_id, service_id),
    FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id),
    FOREIGN KEY (service_id) REFERENCES services(service_id)
);

-- Insert roles
INSERT INTO roles (role_name) VALUES ('Admin');
INSERT INTO roles (role_name) VALUES ('Staff');
INSERT INTO roles (role_name) VALUES ('Receptionist');

INSERT INTO users (username, password, first_name, last_name, email) VALUES ('john_doe', 'password', 'John', 'Doe', '[email protected]');
INSERT INTO users (username, password, first_name, last_name, email) VALUES ('jane_doe', 'password', 'Jane', 'Doe', '[email protected]');

-- Assign roles to users
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1); -- John is a Veterinarian
INSERT INTO user_roles (user_id, role_id) VALUES (2, 2); -- Jane is a Receptionist

-- Insert pet owners
INSERT INTO owners (first_name, last_name, phone_number, email) VALUES ('Alice', 'Smith', '123-456-7890', '[email protected]');
INSERT INTO owners (first_name, last_name, phone_number, email) VALUES ('Bob', 'Johnson', '098-765-4321', '[email protected]');

-- Insert pets
INSERT INTO pets (owner_id, name, species, breed, age) VALUES (1, 'Buddy', 'Dog', 'Golden Retriever', 3);
INSERT INTO pets (owner_id, name, species, breed, age) VALUES (2, 'Whiskers', 'Cat', 'Siamese', 2);

-- Insert services
INSERT INTO services (service_name, price) VALUES ('General Checkup', 50.0);
INSERT INTO services (service_name, price) VALUES ('Vaccination', 20.0);
INSERT INTO services (service_name, price) VALUES ('Surgery', 300.0);

-- Insert appointments
INSERT INTO appointments (pet_id, user_id, appointment_date, notes) VALUES (1, 1, '2023-06-20 10:00:00', 'Regular checkup for Buddy');
INSERT INTO appointments (pet_id, user_id, appointment_date, notes) VALUES (2, 1, '2023-06-20 11:00:00', 'Vaccination for Whiskers');

-- Assign services to appointments
INSERT INTO appointment_services (appointment_id, service_id) VALUES (1, 1); -- General Checkup for Buddy
INSERT INTO appointment_services (appointment_id, service_id) VALUES (2, 2); -- Vaccination for Whiskers