Clinic Database System
DATABASE ASSIGNMENTS
Part 1: Clinic Database System
Part 1: Introduction
The database case study chosen for this particular assignment is of a Doctor’s Clinic. The clinic registers several doctors from different medical fields. Patients can register into the system with a new account and then they can book appointments for the respective doctors as needed. The appointments can be made based on specific slots available each day.
Objectives
- Doctor’s secured data storage
- Patient’s secured data storage
- Slot appointments booking by patients
- A doctor can attend only one slot at a time
Chosen Scenario and Analysis
The chosen business scenario is of a Clinic Database System.
The Four main identified entities and their respective attribute data types are as presented below:
Patient
|
Attribute |
Data Type |
|
Patient_id |
Varchar |
|
Name |
Varchar |
|
Password |
Varchar |
|
Phone |
Bigint |
|
|
Varchar |
|
Medical_history |
Varchar |
Doctor
|
Attribute |
Data Type |
|
Doctor_id |
Varchar |
|
Name |
Varchar |
|
Password |
Varchar |
|
Phone |
Bigint |
|
Field |
Varchar |
|
Fees |
Integer |
Slots
|
Attribute |
Data Type |
|
Slot_id |
int |
|
Slot_time |
Varchar |
Appointments
|
Attribute |
Data Type |
|
Appointment_id |
Varchar |
|
Patient_id |
Varchar |
|
Doctor_id |
Varchar |
|
Slot |
Integer |
|
Booked_date |
Date |
|
Processed_date |
Date |
|
Patient_description |
Varchar |
|
Prescription |
Varchar |
ER Diagram
SQL
DDL Create Tables
Patient Table
CREATE TABLE patient (
id varchar(20) NOT NULL,
name varchar(30) NOT NULL,
password varchar(20) NOT NULL,
phone bigint(12) NOT NULL,
email varchar(30) NOT NULL,
medical_history varchar(1000) NOT NULL,
PRIMARY KEY (id)
)
Doctor Table
CREATE TABLE doctor (
id varchar(20) NOT NULL,
name varchar(30) NOT NULL,
phone varchar(12) NOT NULL,
password varchar(20) NOT NULL,
field varchar(30) NOT NULL,
fees int(11) NOT NULL,
PRIMARY KEY (id)
)
Slots Table
CREATE TABLE slots (
slot_id int(11) NOT NULL,
slot_time varchar(10) NOT NULL,
PRIMARY KEY (slot_id)
)
Appointments Table
CREATE TABLE appointments (
appo_id` varchar(20) NOT NULL,
patient_id` varchar(20) NOT NULL,
doc_id` varchar(20) NOT NULL,
booked_date date NOT NULL,
processed_date date DEFAULT current_timestamp(),
slot int(11) NOT NULL,
patient_desc varchar(1000) NOT NULL,
prescription varchar(1000) NOT NULL,
PRIMARY KEY (appo_id)
)
ALTER TABLE appointments ADD CONSTRAINT doc_fk FOREIGN KEY (doc_id) REFERENCES doctor (id), ADD CONSTRAINT ptn_fk FOREIGN KEY (patient_id) REFERENCES patient (id), ADD CONSTRAINT slot_fk FOREIGN KEY (slot) REFERENCES slots (slot_id);
DML Insert Statements
Patient Table
INSERT INTO `patient` (`id`, `name`, `password`, `phone`, `email`, `medical_history`) VALUES ('jerry101', 'Jerry Collins', 'qwe123', 1231231231, 'jc@mail.com', 'Fit and fine'),
('john101', 'John Doe', '123qwe', 1231331231, 'jode@mail.com', 'Peanut Allergies'),
('roy301', 'Mason Roy', 'max12@75', 1231231231, 'royj@gmail.com', 'Weak heart'),
('billy402', 'Billy Bob', 'bul@212', 2431235231, 'billy1@mail.com', 'Diabetes'),
('rachel101', 'Rachel Greene', 'rach213', 2461264231, 'mx@gmail.com', 'Thyroid');
Slots Table
INSERT INTO `slots` (`slot_id`, `slot_time`) VALUES (1, '10 AM'), (2, '12 PM'), (3, '02 PM'), (4, '04 PM'), (5, '06 PM'), (6, '08 PM');
Doctor Table
INSERT INTO `doctor` (`id`, `name`, `phone`, `password`, `field`, `fees`) VALUES ('derek@11', 'Derek Brad', '1234567890', 'asdasdasd', 'Skin', 22), ('max@doc', 'Billy Max', '1599511595', 'zxc123', 'Skin', 65), ('bobby@101', Bobby Cyrus, '4334567890', 'qweqwe123', 'General', 22), ('monana121', 'Monana Chappel', '5115567800', '123456asd', 'Heart', 22), ('ross13gyl', 'Rossie Gyllenhal', '10214566600', 'rossie9123', 'ENT', 22);
Appointments Table
INSERT INTO `appointments` (`appo_id`, `patient_id`, `doc_id`, `booked_date`, `Processed_date`, `slot`, `patient_desc`, `prescription`) VALUES ('apt12212020012815', 'jerry101', 'max@doc', '2020-12-20', '2020-12-17', 3, 'skin issue', 'Lipid ointments prescribes, twice a day'), ('apt12222020012815', ''billy402', 'max@doc', '2020-12-22', '2020-12-11', 4, 'Hair Fall', ''), ('apt12232020012815', ' rachel101', ross13gyl', '2020-12-23', '2020-12-13', 1, 'Nose Bleeding', ''), ('apt12202020212815', ' roy301', 'bobby@101', '2020-12-22', '2020-12-20', 1, 'Fever', 'Take paracetamol thrice for a week'), ('apt12232023012816', ' rachel101', 'monana121', '2020-12-23', '2020-12-23', 5, 'Chest Pain', '');
Conclusion
The database will help the system to perform the required business operations fruitfully and also gain the required results out of it. The ER diagram allows the database developer to utilize the design and develop a robust database system.
Part 2: Student Application Database System
Part 2: Student Table Queries
- SELECT * FROM student;
- INSERT INTO student VALUES (‘53660’,’John’,’john@cs’,18,4.1);
- UPDATE student SET age=age+4 WHERE sid='53666;'
- ALTER TABLE student RENAME COLUMN login TO email;
- SELECT * FROM student WHERE age>15 AND gpa>3.5;
References
Kriegel, A. and Trukhnov, B.M., 2008. SQL bible (Vol. 742). John Wiley & Sons.
Silva, Y.N., Almeida, I. and Queiroz, M., 2016, February. SQL: From traditional databases to big data. In Proceedings of the 47th ACM Technical Symposium on Computing Science Education (pp. 413-418).
