ywen58script1.txt

SHOW DATABASES;
DROP DATABASE IF EXISTS ywen58assign2db;
CREATE DATABASE ywen58assign2db;
USE ywen58assign2db;
GRANT USAGE ON *.* TO 'ta'@'localhost';
DROP USER 'ta'@'localhost';
CREATE USER 'ta'@'localhost' IDENTIFIED BY 'cs3319';
GRANT ALL PRIVILEGES ON ywen58assign2db.* TO 'ta'@'localhost';
FLUSH PRIVILEGES;
SHOW TABLES;

-- create tables
CREATE TABLE doctor (firstname VARCHAR(20), lastname VARCHAR(20), license CHAR(4), licensedate DATE,
specialty VARCHAR(30), hospitalcode CHAR(3), PRIMARY KEY(license));
CREATE TABLE hospital (name VARCHAR(20), city VARCHAR(20), province CHAR(2), numberofbeds INT NOT NULL,
code CHAR(3), head CHAR(4), headdate DATE, PRIMARY KEY(code),
FOREIGN KEY(head) REFERENCES doctor(license));

-- add FOREIGN key for doctor
ALTER TABLE doctor
ADD FOREIGN KEY (hospitalcode) REFERENCES hospital(code);
-- create patient table
CREATE TABLE patient (OHIP CHAR(9), firstname VARCHAR(20), lastname VARCHAR(20), PRIMARY KEY(OHIP));
-- create treat
CREATE TABLE treat (
    OHIP CHAR(9),
    license CHAR(4),
    FOREIGN KEY(OHIP)
        REFERENCES patient(OHIP),
    FOREIGN KEY(license)
        REFERENCES doctor(license)
        ON DELETE CASCADE
);
SHOW TABLES;