CREATE TABLE admitpatient_room ( PatientID int NOT NULL, Disease varchar(50) DEFAULT NULL, AdmitDate date DEFAULT NULL, RoomNo int DEFAULT NULL, DoctorID int NOT NULL, AP_Remarks varchar(50) DEFAULT NULL, PRIMARY KEY (PatientID), CONSTRAINT admitpatient_room_ibfk_1 FOREIGN KEY (RoomNo) REFERENCES room (RoomNo), CONSTRAINT admitpatient_room_ibfk_2 FOREIGN KEY (DoctorID) REFERENCES doctor (DoctorID) )

CREATE TABLE bill_room ( DischargeID int IDENTITY(1,1) NOT NULL, BillNo int NOT NULL UNIQUE, BillingDate date DEFAULT NULL, RoomCharges int DEFAULT NULL, ServiceCharges int DEFAULT NULL, PaymentMode varchar(20) DEFAULT NULL, PaymentModeDetails varchar(100) DEFAULT NULL, TotalCharges int DEFAULT NULL, NoOfDays int DEFAULT NULL, TotalRoomCharges int DEFAULT NULL, PRIMARY KEY (DischargeID, BillNo) )

CREATE TABLE dischargepatient_room ( AdmitID int IDENTITY(1,1) NOT NULL, DischargeDate date DEFAULT NULL, DP_Remarks varchar(50) DEFAULT NULL, PRIMARY KEY (AdmitID) )

CREATE TABLE doctor ( DoctorID int IDENTITY(1,1) NOT NULL , DoctorName varchar(20) DEFAULT NULL, FatherName varchar(20) DEFAULT NULL, Email varchar(50) DEFAULT NULL, ContacNo varchar(11) DEFAULT NULL, Qualifications varchar(50) DEFAULT NULL, Gender varchar(1) DEFAULT NULL, BloodGroup varchar(5) DEFAULT NULL, DateOfJoining date DEFAULT NULL, Address varchar(100) DEFAULT NULL, PRIMARY KEY (DoctorID) )

CREATE TABLE patientregistration ( PatientID int IDENTITY(1,1) NOT NULL, PatientName varchar(20) DEFAULT NULL, Email varchar(30) DEFAULT NULL, ContactNo varchar(11) DEFAULT NULL, Age int DEFAULT NULL, Remarks varchar(100) DEFAULT NULL, Gen varchar(1) DEFAULT NULL, BG varchar(3) DEFAULT NULL, Address varchar(100) DEFAULT NULL, PRIMARY KEY (PatientID) )

CREATE TABLE registration ( name varchar(20) DEFAULT NULL, user_name varchar(20) NOT NULL, password varchar(20) DEFAULT NULL, email_id varchar(30) DEFAULT NULL, contact_no int DEFAULT NULL, PRIMARY KEY (user_name) )

CREATE TABLE room ( RoomNo int NOT NULL, RoomType varchar(10) DEFAULT NULL, RoomCharges int DEFAULT NULL, RoomStatus varchar(10) DEFAULT NULL, PRIMARY KEY (RoomNo) )

CREATE TABLE services ( ServiceName varchar(20) DEFAULT NULL, ServiceDate date DEFAULT NULL, PatientID int DEFAULT NULL, ServiceCharges int DEFAULT NULL, ServiceID int IDENTITY(1,1) NOT NULL, PRIMARY KEY (ServiceID), CONSTRAINT services_ibfk_1 FOREIGN KEY (PatientID) REFERENCES patientregistration (PatientID) )

CREATE TABLE users ( user_name varchar(20) NOT NULL, password varchar(20) DEFAULT NULL, PRIMARY KEY (user_name) )

Questions are:

1. E-R Model

Construct an E-R diagram representing the conceptual design of the database.

Be sure to identify primary keys, relationship, etc.

2. Relational Model

After creating an initial relational design from your E-R design, refine it based on the principles of relational design (Chapter 8).

Create the relations in SQL Server/mySQL/Azure database.

Create primary keys and constraints as appropriate.

If as you refine your design, you discover flaws in the E-R design, go back and change it. Your final E-R design must be consistent with your relational design.