Urgenthomework logo
UrgentHomeWork
Live chat

Loading..

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

Email

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

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).

Resources

  • 24 x 7 Availability.
  • Trained and Certified Experts.
  • Deadline Guaranteed.
  • Plagiarism Free.
  • Privacy Guaranteed.
  • Free download.
  • Online help for all project.
  • Homework Help Services
Copyright © 2009-2023 UrgentHomework.com, All right reserved.