Objective

This project's objective was to create a database and a web application to manage/store all the data transactions occurring in a hospital.

Database Design

The diagrams were created using Oracle SQL Developer Data Modeler.

SQL Scripting

After building a blueprint of the database, actual SQL database was created and sample data were added.

                             CREATE TABLE bank (
    bankid              CHAR(10 CHAR) NOT NULL,
    bankname            VARCHAR2(50 CHAR),
    contactid   CHAR(25 CHAR) NOT NULL
);

CREATE UNIQUE INDEX bank__idx ON
    bank (
        contactid
    ASC );

ALTER TABLE bank ADD CONSTRAINT bank_pk PRIMARY KEY ( bankid );

CREATE TABLE billing_information (
    billingid                  CHAR(10 CHAR) NOT NULL,
    creditcardnumber           CHAR(16 CHAR),
    creditcardexpirationdate   DATE,
    creditcardcvv              CHAR(3 CHAR),
    patientid          CHAR(10 CHAR) NOT NULL,
    bankid                CHAR(10 CHAR) NOT NULL
);

ALTER TABLE billing_information ADD CONSTRAINT billing_information_pk PRIMARY KEY ( billingid );

CREATE TABLE contact (
    contactid      CHAR(25 CHAR) NOT NULL,
    address        VARCHAR2(50 CHAR),
    city           VARCHAR2(50 CHAR),
    state          VARCHAR2(50 CHAR),
    zipcode        VARCHAR2(10 CHAR),
    phonenumber    VARCHAR2(15 CHAR),
    emailaddress   VARCHAR2(50 CHAR) NOT NULL
);

ALTER TABLE contact ADD CONSTRAINT contact_pk PRIMARY KEY ( contactid );

CREATE TABLE doctor (
    doctorid              CHAR(10 CHAR) NOT NULL,
    firstname             VARCHAR2(25 CHAR),
    lastname              VARCHAR2(25 CHAR),
    department            VARCHAR2(100 CHAR),
    acesslevel            CHAR(1 CHAR) NOT NULL,
    username              VARCHAR2(25 CHAR),
    password              VARCHAR2(25 CHAR),
    contactid     CHAR(25 CHAR) NOT NULL,
    hospitalid   CHAR(10 CHAR) NOT NULL
);



...
 
                          

Front-end Web Application Development

The web application was developed by one of my group members by using node.js. Click here 🔗 to view the source code for the server itself.

Demonstration