database.py

import sqlite3

conn = sqlite3.connect("HospitalDB.db")
print("DATABASE CONNECTION SUCCESSFUL")

# Create PATIENT table
conn.execute("""CREATE TABLE IF NOT EXISTS PATIENT (
            PATIENT_ID INTEGER PRIMARY KEY,
            NAME VARCHAR(20) NOT NULL,
            SEX VARCHAR(10) NOT NULL,
            BLOOD_GROUP VARCHAR(5) NOT NULL,
            DOB DATE NOT NULL,
            ADDRESS VARCHAR(100) NOT NULL,
            CONSULT_TEAM VARCHAR(50) NOT NULL,
            EMAIL VARCHAR(20) NOT NULL
            )""")
print("PATIENT TABLE CREATED SUCCESSFULLY")

# Create CONTACT_NO table
conn.execute("""CREATE TABLE IF NOT EXISTS CONTACT_NO (
            PATIENT_ID INTEGER PRIMARY KEY,
            CONTACTNO INTEGER NOT NULL,
            ALT_CONTACT INTEGER,
            FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID)
            )""")
print("CONTACT_NO TABLE CREATED SUCCESSFULLY")

# Create EMPLOYEE table
conn.execute("""CREATE TABLE IF NOT EXISTS EMPLOYEE (
            EMP_ID VARCHAR(10) PRIMARY KEY,
            EMP_NAME VARCHAR(20) NOT NULL,
            SEX VARCHAR(10) NOT NULL,
            AGE INTEGER NOT NULL,
            DESIG VARCHAR(20) NOT NULL,
            SAL INTEGER NOT NULL,
            EXP VARCHAR(100) NOT NULL,
            EMAIL VARCHAR(20) NOT NULL,
            PHONE INTEGER
            )""")
print("EMPLOYEE TABLE CREATED SUCCESSFULLY")

# Create TREATMENT table
conn.execute("""CREATE TABLE IF NOT EXISTS TREATMENT (
            PATIENT_ID INTEGER PRIMARY KEY,
            TREATMENT VARCHAR(100) NOT NULL,
            TREATMENT_CODE VARCHAR(30) NOT NULL,
            T_COST INTEGER NOT NULL,
            FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID)
            )""")
print("TREATMENT TABLE CREATED SUCCESSFULLY")

# Create MEDICINE table
conn.execute("""CREATE TABLE IF NOT EXISTS MEDICINE (
            PATIENT_ID INTEGER PRIMARY KEY,
            MEDICINE_NAME VARCHAR(100) NOT NULL,
            M_COST INTEGER NOT NULL,
            M_QTY INTEGER NOT NULL,
            FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID)
            )""")
print("MEDICINE TABLE CREATED SUCCESSFULLY")

# Create ROOM table
conn.execute("""CREATE TABLE IF NOT EXISTS ROOM (
            PATIENT_ID INTEGER NOT NULL,
            ROOM_NO VARCHAR(20) PRIMARY KEY,
            ROOM_TYPE VARCHAR(10) NOT NULL,
            RATE INTEGER NOT NULL,
            DATE_ADMITTED DATE,
            DATE_DISCHARGED DATE,
            FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID)
            )""")
print("ROOM TABLE CREATED SUCCESSFULLY")

# Create APPOINTMENT table
conn.execute("""CREATE TABLE IF NOT EXISTS APPOINTMENT (
            PATIENT_ID INTEGER NOT NULL,
            EMP_ID VARCHAR(10) NOT NULL,
            AP_NO VARCHAR(10) PRIMARY KEY,
            AP_TIME TIME,
            AP_DATE DATE,
            DESCRIPTION VARCHAR(100),
            FOREIGN KEY(PATIENT_ID) REFERENCES PATIENT(PATIENT_ID),
            FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID)
            )""")
print("APPOINTMENT TABLE CREATED SUCCESSFULLY")

conn.commit()
conn.close()