Question

Write SQL statements that will create a Database, three Entities and the necessary tables/columns of a Hospital. Do not forget to submit an ER diagram to show the relationship and Cardinality also

Answer 1

Answer)
CREATE TABLE Patient(patientid int primary key, pname varchar, address varchar, amount int, ptype varchar);

create table hospital (hospitalid int primary key, hname varchar, htype varchar);

create table doctor (doctorid int primary key, dname varchar, specialization varchar, hospitalid int, status varchar, FOREIGN KEY (hospitalid) REFERENCES hospital(hospitalid));

create table billing (billingid int primary key, patientid int, doctorid int, fees int, billdate date, FOREIGN KEY (patientid) REFERENCES patient(patientid), FOREIGN KEY (doctorid) REFERENCES doctor(doctorid));

hospital : Doctor = 1:MANY relationship.
Patient : Billing = 1:1 relationship
Billing : Doctor = 1:1 relationship.

The ERD is as below: