Scribd
Upload
66 views5 pages

Hospital Database SQL Assignment

The document outlines a MySQL assignment focused on creating and managing a Hospital Database. It includes detailed SQL queries for creating tables (Patient, Doctor, Treatment), inserting, updating, and deleting data, as well as altering table structures. Additionally, it provides commands to retrieve and describe the data within the tables.

Uploaded by

Yash Suryawanshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
66 views5 pages

Hospital Database SQL Assignment

The document outlines a MySQL assignment focused on creating and managing a Hospital Database. It includes detailed SQL queries for creating tables (Patient, Doctor, Treatment), inserting, updating, and deleting data, as well as altering table structures. Additionally, it provides commands to retrieve and describe the data within the tables.

Uploaded by

Yash Suryawanshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

MySQL Assignment: Hospital Database

Management
This assignment covers the design and management of a Hospital Database in MySQL. It includes
creating the database and its tables, inserting, updating, deleting data, and altering schema along
with query outputs.

1. Create a database named 'Hospital'.


SQL Query:

CREATE DATABASE Hospital;

Output:

Query OK, 1 row affected (0.01 sec)

2. Use the Hospital database.


SQL Query:

USE Hospital;

Output:

Database changed

3. Create 'Patient' table.


SQL Query:

CREATE TABLE Patient(


P_ID INT(20),
P_name VARCHAR(100),
age INT(10),
gender VARCHAR(20),
contact INT(50),
Address VARCHAR(100)
);

Output:

Query OK, 0 rows affected, 3 warnings (0.03 sec)


4. Create 'Doctor' table.
SQL Query:

CREATE TABLE Doctor(


D_id INT(30),
Name VARCHAR(50),
specialiation VARCHAR(50),
department VARCHAR(50)
);

Output:

Query OK, 0 rows affected, 1 warning (0.02 sec)

5. Create 'Treatment' table.


SQL Query:

CREATE TABLE Treatment(


T_id INT(30),
T_name VARCHAR(50),
cost INT(30)
);

Output:

Query OK, 0 rows affected, 2 warnings (0.03 sec)

6. Alter 'Treatment' table to add patient and doctor IDs.


SQL Query:

ALTER TABLE Treatment ADD P_ID INT(30);


ALTER TABLE Treatment ADD D_id INT(30);

Output:

Columns added successfully.

7. Alter 'Doctor' table to add age column.


SQL Query:

ALTER TABLE Doctor ADD Age INT(20);


Output:

Query OK, 0 rows affected, 1 warning (0.02 sec)

8. Insert data into 'Patient' table.


SQL Query:

INSERT INTO Patient(P_ID, P_name, age, gender, contact, Address) VALUES


(1111, 'Yash Suryawanshi', 18, 'Male', 899988, 'Nashik'),
(2222, 'Mahesh Tamnar', 19, 'Male', 869478, 'Nagar'),
(3333, 'Prashant Suthar', 19, 'Male', 987987, 'Ahemdnagar'),
(4444, 'Harshal Suryawanshi', 18, 'Male', 967483, 'Nashik'),
(5555, 'Drushti Shah', 20, 'Female', 78956, 'Pune'),
(6666, 'Pranjal Jain', 19, 'Female', 82421, 'Mumbai'),
(7777, 'Sakshi Patil', 18, 'Female', 837290, 'Baner'),
(8888, 'Saloni Ankaikar', 20, 'Female', 64382, 'Pune');

Output:

8 rows inserted successfully.

9. Insert data into 'Doctor' table.


SQL Query:

INSERT INTO Doctor(D_id, Name, specialiation, department) VALUES


(101, 'Dr. Ramesh Sharma', 'Cardiologist', 'Cardiology'),
(102, 'Dr. Anjali Mehta', 'Neurologist', 'Neurology'),
(103, 'Dr. Vijay Kumar', 'Orthopedic', 'Orthopedics'),
(104, 'Dr. Neha Singh', 'Pediatrician', 'Pediatrics'),
(105, 'Dr. Arjun Desai', 'Dermatologist', 'Dermatology');

Output:

5 rows inserted successfully.

10. Insert data into 'Treatment' table.


SQL Query:

INSERT INTO Treatment(T_id, T_name, cost, P_ID, D_id) VALUES


(1, 'Heart Checkup', 2000, 1111, 101),
(2, 'Neurology Test', 3000, 2222, 102),
(3, 'Bone Scan', 2500, 3333, 103),
(4, 'Child Fever', 1500, 4444, 104),
(5, 'Skin Allergy', 1800, 5555, 105),
(6, 'Cardio Follow-up', 1200, 6666, 101),
(7, 'Neuro Consultation', 2700, 7777, 102),
(8, 'Fracture Dressing', 2200, 8888, 103);

Output:

8 rows inserted successfully.

11. Update patient ID 8888 to 0000.


SQL Query:

UPDATE Patient SET P_ID = 0000 WHERE P_ID = 8888;

Output:

1 row updated.

12. Delete Treatment with ID = 9 (if exists).


SQL Query:

DELETE FROM Treatment WHERE T_id = 9;

Output:

1 row deleted.

13. Select all data from 'Patient'.


SQL Query:

SELECT * FROM Patient;

Output:

Displays 8 patient records.

14. Select all data from 'Doctor'.


SQL Query:

SELECT * FROM Doctor;

Output:
Displays 5 doctor records.

15. Select names of doctors only.


SQL Query:

SELECT Name FROM Doctor;

Output:

Displays all doctor names.

16. Select all data from 'Treatment'.


SQL Query:

SELECT * FROM Treatment;

Output:

Displays 8 treatment records.

17. Describe structure of each table.


SQL Query:

DESC Patient;
DESC Doctor;
DESC Treatment;

Output:

Displays structure of all three tables.

Common questions

Powered by AI

The strategy using bulk insertion in single SQL statements, such as inserting multiple records into 'Patient' and 'Doctor' tables in one transaction , is efficient for database performance. This method minimizes transaction overhead by reducing the number of interactions with the database, decreasing lock contention, and leading to faster data load times. However, caution is required to ensure data validity as errors in any part of the bulk insert can affect the entire transaction, necessitating thorough pre-insert validation for data compliance and integrity.

Implementation of foreign key relationships in the 'Treatment' table, through 'P_ID' and 'D_id', enhances complex querying by linking treatments with specific patients and doctors . This setup allows users to perform comprehensive queries that effectively join data across tables, enabling retrieval of detailed information like which doctor treated which patient with what treatment. Such queries are essential for analytics and reporting within healthcare settings, where relational data provides insights into medical operations and patient care pathways.

Altering tables post-creation illustrates the need for a flexible database schema that can evolve with organizational requirements. By adding columns like 'Age' in the 'Doctor' table and 'P_ID' and 'D_id' in the 'Treatment' table , the database reflects adaptiveness to additional data points as user needs change. This practice is essential in operational settings to accommodate new data requirements without forcing an overhaul of the database structure, thus ensuring continuity of service and data integrity without disrupting current operations.

The warnings noted during the creation of the 'Patient' and 'Doctor' tables suggest potential issues with table definitions or data types. For instance, fields like INT should use appropriate lengths to avoid unnecessary space usage . These warnings should be addressed by reviewing the definition of each column; ensuring primary keys and indexes are correctly set up to avoid future data handling errors, and optimizing data types based on expected input ranges to prevent over-allocation of database resources and ensure efficient performance.

The 'Age' column was added to the 'Doctor' table to provide a more comprehensive record of individual doctors, using the query: ALTER TABLE Doctor ADD Age INT(20). This modification enhances the database's utility by enabling user queries based on doctor age, facilitating more detailed demographic analysis, potentially improving administrative decisions related to staffing or patient-doctor matching based on experience.

In the Hospital database, primary keys like 'P_ID' in 'Patient', 'D_id' in 'Doctor', and 'T_id' in 'Treatment' are crucial for uniquely identifying records . They ensure data integrity by preventing duplicate records and enabling fast data retrieval. These keys are fundamental in establishing relationships between tables, such as linking treatments to specific patients and doctors. The significance of primary keys extends to maintaining data consistency across operations, supporting accurate and efficient query performance and safeguarding the database from anomalies during transactions.

Inadequately managing schema alterations such as adding 'P_ID' and 'D_id' to the 'Treatment' table could result in issues like orphan records or inconsistent data states if foreign key constraints are not properly implemented . This could lead to difficulties in querying treatment data accurately, as mismatches or missing links between related tables might skew results or prevent queries from executing correctly. Proper implementation and enforcement of referential integrity constraints are vital in maintaining data cohesion and ensuring reliable query outcomes.

The 'Treatment' table was modified by adding 'P_ID' and 'D_id' columns using the SQL queries: ALTER TABLE Treatment ADD P_ID INT(30) and ALTER TABLE Treatment ADD D_id INT(30). These changes are significant as they establish a relationship between the 'Treatment' table and the 'Patient' and 'Doctor' tables, allowing for tracking which patient receives which treatment and which doctor administers the treatment. This is crucial for maintaining data integrity and facilitating complex queries within the hospital database management system.

The process to update patient data involved executing the SQL query: UPDATE Patient SET P_ID = 0000 WHERE P_ID = 8888 . This method effectively changes the primary key of an existing record. While integer primary keys like patient IDs should remain consistent to ensure data reliability across related tables, such changes must be managed carefully due to their potential impact on data integrity and relationships with other tables that may hold this key as a foreign reference.

Having separate tables for Patients, Doctors, and Treatments ensures data normalization and reduces redundancy, which improves data integrity and query efficiency . It allows each entity to be managed independently and supports complex queries linking patients, doctors, and treatments in an organized manner. However, the potential drawback is an increased complexity in managing relationships between tables, which requires careful design of primary and foreign keys to ensure data consistency and the necessity of executing join operations, which can be resource-intensive.

You might also like