sql file.txt

CREATE DATABASE dp;

USE dp;

CREATE TABLE `admin` (
  `admin_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `email` varchar(20) NOT NULL UNIQUE,
 PRIMARY KEY (`admin_id`)
);

CREATE TABLE `organ` (
  `organ_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20)NOT NULL,
  `password` varchar(20) NOT NULL,
  `email` varchar(20) NOT NULL UNIQUE,
  `phone` varchar(20) NOT NULL,
  `gender` varchar(20) NOT NULL ,
  `blood_type` varchar(20) NOT NULL ,
  `organ` varchar(20) NOT NULL,
  PRIMARY KEY (`organ_id`)
);

CREATE TABLE `patient` (
  `patient_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL ,
  `email` varchar(20) NOT NULL UNIQUE,
  `phone` varchar(20) NOT NULL ,
  `gender` varchar(20) NOT NULL ,
  PRIMARY KEY (`patient_id`)
);

CREATE TABLE `doctor` (
  `doctor_id` int NOT NULL AUTO_INCREMENT ,
  `username` varchar(20) NOT NULL ,
  `password` varchar(20) NOT NULL ,
  `email` varchar(20) NOT NULL UNIQUE,
  `phone` varchar(20) NOT NULL ,
  `gender` varchar(20) NOT NULL ,
  `specialization` varchar(20) NOT NULL ,
  PRIMARY KEY (`doctor_id`)
);

CREATE TABLE `clinic` (
  `clinic_id` int NOT NULL AUTO_INCREMENT ,
  `doctor_id` int NOT NULL ,
  `location` varchar(20) NOT NULL ,
  `phone` varchar(20) NOT NULL ,
  `park_price` int NOT NULL ,
  `book_price` int NOT NULL ,
  `start_time` int NOT NULL ,
  `end_time` int NOT NULL ,
  PRIMARY KEY (`clinic_id`),
  FOREIGN KEY (`doctor_id`) REFERENCES doctor(doctor_id)
);

CREATE TABLE `appointment` (
  `appointment_id` int NOT NULL AUTO_INCREMENT ,
  `patient_id` int NOT NULL ,
  `doctor_id` int NOT NULL ,
  `clinic_id` int NOT NULL ,
  `hour` int NOT NULL ,
  `date` varchar(10) NOT NULL ,
  `confirmed` boolean NOT NULL ,
  PRIMARY KEY (`appointment_id`),
  FOREIGN KEY (`patient_id`) REFERENCES patient (patient_id),
  FOREIGN KEY (`doctor_id`) REFERENCES doctor (doctor_id),
  FOREIGN KEY (`clinic_id`) REFERENCES clinic (clinic_id)
);

CREATE TABLE `ceditcard` (
  `appointment_id` int NOT NULL ,
  `name` varchar(20) NOT NULL ,
  `card_number` varchar(20) NOT NULL ,
  `cvv` varchar(20) NOT NULL ,
  `date_of_expiry` varchar(20) NOT NULL ,
  FOREIGN KEY (`appointment_id`) REFERENCES appointment (appointment_id)
);

CREATE TABLE `feedback` (
  `description` varchar(250) NOT NULL 
);

INSERT INTO admin 
(username,password,email)
VALUES ('admin','admin','admin@admin');