Question

Database exercise: inpatient cases

• Create database using name RUMKIT
• Create tables below in that database

• patient(idPatient, fullName, biologicalMother, birthdate, address)
• doctor(idDr, fullName, specialization, consulRates)
• inpatient(idPatient, entryTime, outTime, idDr, idRoom). Please make entryTime as column that is going to be filled automatically when care record is being add
• room(idRoom, roomName, cost)

• fill the data above to each table
• Create sql query and relational algebra expressions for the query

Please give me detailed answer so I could learn from it. Thank you very much

Answer 1

MySQL queries for inpatient cases

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

CREATE DATABASE RUMKIT;

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

   .....

   columnN datatype,

   PRIMARY KEY( one or more columns )

);

The PRIMARY KEY constraint uniquely identifies each record in a table.Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

o patient(idPatient, fullName, biologicalMother, birthdate, address)

CREATE TABLE patient(

   idPatient int NOT NULL,

   fullname varchar(100),

   biologicalMother varchar(100),

birthdate date,

   address varchar(200),

   PRIMARY KEY(idPatient)

);

o doctor(idDr, fullName, specialization, consulRates)

CREATE TABLE doctor(

   idDr int NOT NULL,

   fullname varchar(100),

   specialization varchar(100),

consulRates int,

   PRIMARY KEY(idDr)

);

o inpatient(idPatient, entryTime, outTime, idDr, idRoom). Please make entryTime as column that is going to be filled automatically when care record is being add

CREATE TABLE inpatient(

   idPatient int,

   entryTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

   outTime TIMESTAMP,

idDr int,

   idRoom int,

   PRIMARY KEY(entryTime),

   FOREIGN KEY (idPatient) REFERENCES patient(idPatient),

   FOREIGN KEY (idDr) REFERENCES doctor(idDr),

   FOREIGN KEY (idRoom) REFERENCES room(idRoom)

);

o room(idRoom, roomName, cost)

CREATE TABLE room(

   idRoom int NOT NULL,

   roomName varchar(100),

cost int,

   PRIMARY KEY(idRoom)

);

The INSERT INTO statement is used to insert new records in a table.

SYNTAX

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Inserting values into the patient table

INSERT INTO patient
VALUES (30001, samyuktha, Catherine, 2002-10-11,third cross mumbai);

INSERT INTO patient
VALUES (30002, rosy, marry louise, 2004-08-07,patel avenue mumbai);

INSERT INTO patient
VALUES (30003, samskruthi, poonam, 2000-11-01,saradhamma apartment mumbai);

idPatient	fullName	biologicalMother	Birthdate	address
30001	Samyuktha	Catherine	2002-10-11	third cross mumbai
30002	Rosy	marry louise	2004-08-07	patel avenue mumbai
30003	Samskruthi	poonam	2000-11-01	saradhamma apartment mumbai

Inserting values into the doctor table

INSERT INTO doctor VALUES (1001, Dr.Robert, cardiologist, 2000);

INSERT INTO doctor VALUES (1002, Dr.Sagayaraj, Allergists/Immunologists, 2000);

INSERT INTO doctor VALUES (1003, Dr.Sreenath, Dermatologists, 1500);

idDr	fullName	Specialization	consulRates
1001	Dr.Robert	cardiologist	2000
1002	Dr.Sagayaraj	Allergists/Immunologists	2000
1003	Dr.Sreenath	Dermatologists	1500

Inserting values into the room table

INSERT INTO room VALUES (301, sharing ward, 1500);

INSERT INTO room VALUES (401, special ward, 2000);

INSERT INTO room VALUES (501, private room, 3000);

idRoom	roomName	cost
301	sharing ward	1500
401	special ward	2000
501	private room	3000

Inserting values into the inPatient table

INSERT INTO inPatient (idPatient, , outTime, idDr, idRoom)
VALUES (30001, 2020-04-11 11:20:00, 1001,301);

INSERT INTO inPatient (idPatient, , outTime, idDr, idRoom)
VALUES (30002, 2020-04-11 11:30:00, 1002,302);

INSERT INTO inPatient (idPatient, , outTime, idDr, idRoom)
VALUES (30003, 2020-04-11 11:50:00, 1003,303);

idPatient	entryTime	outTime	idDr	idRoom
30001	2020-04-11 11:00:00	2020-04-11 11:20:00	1001	301
30002	2020-04-11 11:22:00	2020-04-11 11:30:00	1002	302
30003	2020-04-11 11:32:00	2020-04-11 11:50:00	1003	303