Clinic Database System

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

1. SELECT * FROM student;
2. INSERT INTO student VALUES (‘53660’,’John’,’john@cs’,18,4.1);
3. UPDATE student SET age=age+4 WHERE sid='53666;'
4. ALTER TABLE student RENAME COLUMN login TO email;
5. 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).