SQL_latest.txt

CREATE TABLE Passenger (
  phone VARCHAR(11) PRIMARY KEY,
  name VARCHAR(255)
);


CREATE TABLE service (
  coach_no int(4) PRIMARY KEY,
  source VARCHAR(30),
  destination VARCHAR(30),
  price int(4),
  deprature_time Time
);


CREATE TABLE bus (
  license_number VARCHAR(10) PRIMARY KEY,
  manufacturer VARCHAR(30),
  model_number VARCHAR(30),
  capacity int(40)
);


CREATE TABLE employee (
  nid_number INT PRIMARY KEY,
  biodata LONGBLOB,
  cv LONGBLOB,
  name VARCHAR(255),
  phone INT(11),
  position VARCHAR(20)
);


CREATE TABLE counter (
  location VARCHAR(40) PRIMARY KEY,
  phone VARCHAR(11),
  manager INT,
  FOREIGN KEY (manager) REFERENCES employee(nid_number)
);


CREATE TABLE counter_employee (
    employee INT PRIMARY KEY,
    counter VARCHAR(40),
    FOREIGN KEY (employee) REFERENCES employee(nid_number),
    FOREIGN KEY (counter) REFERENCES counter(location)
);


CREATE TABLE trip (
    service int,
    date Date,
    bus VARCHAR(10),
    driver int,
    superviser int,
    actual_departure_time Time,
    actual_arrival_time Time,
    status VARCHAR(20),
    PRIMARY KEY(service, date),
    FOREIGN KEY (service) REFERENCES service(coach_no),
    FOREIGN KEY (bus) REFERENCES bus(license_number),
    FOREIGN KEY (driver) REFERENCES employee(nid_number),
    FOREIGN KEY (superviser) REFERENCES employee(nid_number)
);


CREATE TABLE ticket (
    passenger VARCHAR(11),
    seat_number VARCHAR(2),
    service int,
    issue_date DATE,
    journey_date DATE,
    used tinyint(1),
    FOREIGN KEY (passenger) REFERENCES passenger(phone),
    FOREIGN KEY (service) REFERENCES service(coach_no),
    FOREIGN KEY (service, journey_date) REFERENCES trip(service, date)
);

CREATE TABLE login (
  username varchar(255) NOT NULL PRIMARY KEY,
  password varchar(255) NOT NULL
);

for user authentication