-- Table: Ambulance
CREATE TABLE Ambulance (
    Ambulance_Id integer  NOT NULL,
    Plaque_No number NOT NULL,
    CONSTRAINT Ambulance_pk PRIMARY KEY (Ambulance_Id)
) ;

-- Table: Appointment
CREATE TABLE Appointment (
    Appointment_id integer  NOT NULL,
    Department_name varchar2(50)  NOT NULL,
    Doctor_Identify integer  NOT NULL,
    Doctor_Gender varchar2(15)  NOT NULL,
    Patient_number integer  NOT NULL,
    Date_of_birth date  NOT NULL,
    date_of_appointment date  NOT NULL,
    Patient_complaint integer  NOT NULL,
    CONSTRAINT Appointment_pk PRIMARY KEY (Appointment_id)
) ;

-- Table: Hospital
CREATE TABLE Hospital (
    Hospital_Id integer  NOT NULL,
    Name varchar2(150)  NOT NULL,
    Address varchar2(200)  NOT NULL,
    Phone integer  NOT NULL,
    Fax integer  NOT NULL,
    Email varchar2(100)  NOT NULL,
    Standart_room_number integer  NOT NULL,
    Private_room_number integer  NOT NULL,
    Ambulance_Ambulance_Id integer  NOT NULL,
    Room_info_id integer  NOT NULL,
    Personel_Info_Personal_Id integer  NOT NULL,
    Patient_info_id integer  NOT NULL,
    Appointment_Appointment_id integer  NOT NULL,
    CONSTRAINT Hospital_pk PRIMARY KEY (Hospital_Id)
) ;

-- Table: Information_about_patient
CREATE TABLE Information_about_patient (
    Patient_Id integer  NOT NULL,
    Name_Surname varchar2(100)  NOT NULL,
    Gender varchar2(15)  NOT NULL,
    Hospital_Hospital_Id integer  NOT NULL,
    Ambulance_Ambulance_Id integer  NOT NULL,
    movement_information_Id integer  NOT NULL,
    Room_information_Id integer  NOT NULL,
    CONSTRAINT Information_about_patient_pk PRIMARY KEY (Patient_Id)
) ;

-- Table: Patient_movement_information
CREATE TABLE Patient_movement_information (
    Patient_move_info_Id integer  NOT NULL,
    Made_operation varchar2(100)  NOT NULL,
    Price integer  NOT NULL,
    CONSTRAINT Patient_movement_informatio_pk PRIMARY KEY (Patient_move_info_Id)
) ;

-- Table: Personel_Information
CREATE TABLE Personel_Information (
    Personal_Id integer  NOT NULL,
    Idenity_sumber integer  NOT NULL,
    Name_surname varchar2(150)  NOT NULL,
    Profession varchar2(150)  NOT NULL,
    Start_date_to_job date  NOT NULL,
    info_Patient_Id integer  NOT NULL,
    CONSTRAINT Personel_Information_pk PRIMARY KEY (Personal_Id)
) ;

-- Table: Room_Information
CREATE TABLE Room_Information (
    Room_information_Id integer  NOT NULL,
    Bed_number integer  NOT NULL,
    Private_room varchar2(10)  NOT NULL,
    Standart_room varchar2(10)  NOT NULL,
    Hospital_name varchar2(150)  NOT NULL,
    CONSTRAINT Room_Information_pk PRIMARY KEY (Room_information_Id)
) ;

-- foreign keys
-- Reference: Hospital_Ambulance (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Ambulance
    FOREIGN KEY (Ambulance_Ambulance_Id)
    REFERENCES Ambulance (Ambulance_Id);

-- Reference: Hospital_Appointment (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Appointment
    FOREIGN KEY (Appointment_Appointment_id)
    REFERENCES Appointment (Appointment_id);

-- Reference: Hospital_Patient (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Patient
    FOREIGN KEY (Patient_info_id)
    REFERENCES Patient_movement_information (Patient_move_info_Id);

-- Reference: Hospital_Personel (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Personel
    FOREIGN KEY (Personel_Info_Personal_Id)
    REFERENCES Personel_Information (Personal_Id);

-- Reference: Hospital_Room (table: Hospital)
ALTER TABLE Hospital ADD CONSTRAINT Hospital_Room
    FOREIGN KEY (Room_info_id)
    REFERENCES Room_Information (Room_information_Id);

-- Reference: Info_patient_Ambulance (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Ambulance
    FOREIGN KEY (Ambulance_Ambulance_Id)
    REFERENCES Ambulance (Ambulance_Id);

-- Reference: Info_patient_Hospital (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT Info_patient_Hospital
    FOREIGN KEY (Hospital_Hospital_Id)
    REFERENCES Hospital (Hospital_Id);

-- Reference: Personel_Information (table: Personel_Information)
ALTER TABLE Personel_Information ADD CONSTRAINT Personel_Information
    FOREIGN KEY (info_Patient_Id)
    REFERENCES Information_about_patient (Patient_Id);

-- Reference: patient_Room_info (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT patient_Room_info
    FOREIGN KEY (Room_information_Id)
    REFERENCES Room_Information (Room_information_Id);

-- Reference: patient_move_info (table: Information_about_patient)
ALTER TABLE Information_about_patient ADD CONSTRAINT patient_move_info
    FOREIGN KEY (movement_information_Id)
    REFERENCES Patient_movement_information (Patient_move_info_Id);

UPDATE Hospital
Set
insert into Ambulance (Ambulance_Id, Plaque_No,) values
(1, '06AB127', '1990-01-14'),
(2, '34DS111', '1988-09-07'),
(3, '35ABCD123', '1976-08-13');

insert into Appointment (Appointment_id, Department_name, Doctor_Identify, Doctor_Gender, Patient_number,Date_of_birth, date_of_appointment,Patient_complaint) values
(1, 'otorhinolaryngology', '123456', 'M', '123123', '03-01-1980', '22-05-2019','Ear'),
(2, 'dietitian', '123426', 'M', '112123', '13-08-1970', '22-06-2019','Over-weight'),
(3, 'otorhinolaryngology', '1231456', 'M', '1223123', '03-01-1982', '21-05-2019','Nose');

insert into Hospital (Hospital_Id, eName, Address, Phone, Fax,  Email, Standart_room_number, Private_room_number) values

(1, 'Gazi Hospital', 'xxxAddressxxx', '123123123', '123123', 'sdadada@gmail.com', '60','40'),
(2, 'Hacettepe Hospital', 'xxxAddressxx', '123123123', '112123', 'sdadada@gmail.com', '70','50'),
(3, 'Ankara university hospital', 'xxxAddressxx', '123123123', '1223123', 'sdadada@gmail.com', '50','30');

insert into Information_about_patient (Patient_Id, Name_Surname, Gender) values
(1, 'Canberk Akgoz', 'Man'),
(2, 'Bilal Akgoz', 'Man'),
(3, 'Sehnaz Akgoz''Women');

insert into Patient_movement_information(Patient_move_info_Id, Made_operation, Price) values
(1, 'Test', '300'),
(2, 'Surgery', '300'),
(3, 'Take blood', '300')
insert into Personel_Information(Personal_Id, Idenity_sumber, Name_surname, Profession, Start_date_to_job,) values
(1, '123456', 'Clark xx ', 'Ear', '22-05-2015', 'sdadada@gmail.com', '60','40'),
(2, '234562', 'Michal xx', 'Surgeon', '21-10-2005', 'sdadada@gmail.com', '60','40'),
(3, '345612', 'Emma xx', 'Throat', '12-07-2016', 'sdadada@gmail.com', '60','40')

CREATE TABLE Room_Information (
    Room_information_Id integer  NOT NULL,
    Bed_number integer  NOT NULL,
    Private_room varchar2(10)  NOT NULL,
    Standart_room varchar2(10)  NOT NULL,
    Hospital_name varchar2(150)  NOT NULL,
    CONSTRAINT Room_Information_pk PRIMARY KEY (Room_information_Id)
) ;
insert into Room_Information(Room_information_Id, Bed_number, Private_room, Standart_room, Hospital_name) values
(1, '2', 'No', 'Yes', 'Gazi Hospital'),
(2, '1', 'Yes', 'No', 'Hacettepe Hospital'),
(3, '2', 'No', 'Yes', 'Ankara university hospital')
select * from Information;