Data Dictionary and SQL Queries for Care and Cure Database System

This assignment solution encompasses a comprehensive data dictionary and SQL queries designed for a care and cure management system database. The data dictionary meticulously defines various tables, including Login, Room, Patients, Bill, Department, Floors, Building, Stuff, Employees, Roaster, and Doctor, detailing their respective fields, data types, constraints, and descriptions. The SQL queries address several scenarios, such as identifying regular patients over 55, retrieving patient operation details, querying nursing staff, finding patients admitted within a specific timeframe and without health insurance, analyzing doctor qualifications, querying room availability, and calculating total payments. This assignment provides a practical application of database design and SQL querying principles, offering valuable insights for students studying database management.

1Data dictionary and sql queries
Data dictionary and sql queries
Student’s Name:
Institution affiliation:
Part 1

2Data dictionary and sql queries
Data dictionary
Database name: Care and cure management systems
Table Login
Field Data Type Constraints Description
Username varchar(80)
Password varchar(90)
Table Room
Field Data Type Constraints Description
Room_no int Primary key Auto Increament
Room_type varchar(20)
Rate_per_day int
Floor_ID int Foreign Key
Room_Location varchar(100)
Room_Status boolean
Date datetime

3Data dictionary and sql queries
Table patients
Field Data Type Constraints Description
Patient_ID int Primary Key Auto Increament
First Name varchar(60)
Patient_Type varchar(60)
Last Name varchar(60)
Address varchar(150)
Date_of_Birth datetime
Info_ID int Foreign Key
Phone varchar(13)
Emergency_contact varchar(13)
Date_of_admit datetime
Mode_of_Payment varchar(150)
Initial_Condition varchar(400)
Diagnosis varchar(500)
Treatment varchar(400)
Doctor varchar Foreign Key
Health_Insurance boolean
Room_No int Foreign Key
Operation_Type varchar(200)

4Data dictionary and sql queries
Date_of_Operation datetime
Table Bill
Field Data Type Constraints Description
Bill_ID int Primary Key Auto Increament
Patient_ID int Foreign Key
Room_No int Foreign Key
DateOfDischarge datetime
TotalAmount int
Year varchar(40)
Table Department
Field Data Type Constraints Description
Department_ID int Primary Key Auto Increament
Department_Name varchar(100)
Location varchar(100)
Facilities varchar(1000)
Table Floors

5Data dictionary and sql queries
Field Data Type Constraints Description
Floor_ID int Primary Key Auto Increament
Floor_Type varchar(100)
Building_ID int Foreign Key
Table Building
Field Data Type Constraints Description
Building_ID int Primary Key
Building_Name varchar(150)
Table Stuff
Field Data Type Constraints Description
Employee_ID int Primary Key Auto Increament
Department_ID int Foreign Key
Info_ID int Foreign Key
Employee_Type varchar(80)
Table Employees

6Data dictionary and sql queries
Field Data Type Constraints Description
Employee_ID int Primary Key Auto Increament
Info_ID Int
Job_Description varchar(100) Foreign Key
First_Name varchar(60)
Last_Name varchar(60)
Age int
Phone_Number varchar(13)
Address varchar(160)
Date_of_Joining datetime
Salary float
Gender varchar(50)
Qualification varchar(150)
Table Roaster
Field Data Type constraints Description
Roaster_ID int Primary Key Auto Increament
Employee_ID int Foreign Key
Employee_Type varchar(70)

7Data dictionary and sql queries
Table Doctor
Field Data Type Constraints Description
Doctor_ID int Primary Key Auto Increament
First_Name varchar(60)
Last_Name varchar(60)
Qualification varchar(200)
Salary float
Employee_ID int Foreign Key

8Data dictionary and sql queries
Part 2
SQL Queries
1. Regular patients Query who are over 55.
SELECT * FROM Patients WHERE (Age>55 AND Patient_Type = ‘Regular’);
2. Patients operation Query
SELECT Patient_ID, First_Name, Last_Name, Operation_Type, Doctor FROM Patients
WHERE Date_of_Operation >= '1/1/2018';
3. Nursing Query.
SELECT Employee_ID, First_Name + ', ' + Last_Name AS Emp_FullName FROM Employees
WHERE Job_Description = 'Nurse';
4. Patients Query.
SELECT * FROM Patients WHERE (Date_of_admit BETWEEN '01/01/2017' AND
'31/12/2018' AND Health_Insurance = False);
5. Education.
SELECT * FROM Doctor WHERE Qualification > 1;
SELECT Employee_ID, count(Qualification), FROM Doctor;
6. Rooms Query.

9Data dictionary and sql queries
SELECT count(Room_no), Rate_per_day WHERE (Room_Status = False AND Date >
‘01/01/2019’) FROM Room GROUP BY Floor ORDER BY Building;
7. Total Payment.
SELECT YEAR([Date]) AS [Year], MONTH([Date]) AS [Month], SUM(Paid) AS
TotalAmount FROM Bill GROUP BY YEAR([Date]), MONTH([Date]) ORDER BY
TotalAmount;