Question $1$

Scenario:

In this assessment, you will work with an imaginary Health System Database for a medical facility named "HealthCare Central." The purpose of this assessment is to evaluate your skills in creating a database, populating it with data, checking data integrity, monitoring database activity, and understanding database transactions using SQL Server. You will also explore the usage of Dynamic Management Views $($DMVs$)$ to gain insights into database operations.

Health System Database Description:

The "HealthCare Central" medical facility requires a comprehensive database system to manage patient information, doctor appointments, treatment history, and medical staff details.

Database Tables:

Patients:

PatientID $($Primary Key$)$

FirstName

LastName

Gender

DateOfBirth

ContactNumber

Address

Doctors:

DoctorID $($Primary Key$)$

FirstName

LastName

Gender

Specialization

ContactNumber

Email

Appointments:

AppointmentID $($Primary Key$)$

PatientID $($Foreign Key referencing Patients$)$

DoctorID $($Foreign Key referencing Doctors$)$

AppointmentDate

AppointmentTime

TreatmentHistory:

TreatmentID $($Primary Key$)$

PatientID $($Foreign Key referencing Patients$)$

DoctorID $($Foreign Key referencing Doctors$)$

TreatmentDate

Diagnosis

Prescription

Assessment Questions:

Database Creation and Population $(25$ points$)$

a$.$Using SQL Server Management Studio $($SSMS$),$ create a new database named "HealthCareDB."

b$.$Create the tables "Patients," "Doctors," "Appointments," and "TreatmentHistory" based on the provided schema. Defineappropriate data types, relationships, and constraints.

c$.$Populate each table with $15$ sample records that represent diverse patient and medical staff information.

Data Integrity and Monitoring $(20$ points$)$

a$.$Run a DBCC CHECKDB command on the "HealthCareDB" database with the DATA$\_$PURITY option to check for datapurity issues.

b$.$Provide a screenshot or description of the results showing any columns with data purity problems.

c$.$Use Dynamic Management Views $($DMVs$)$ to monitor database activity. Retrieve information on active sessions, queries,and wait statistics.

Transactions and DMVs $(25$ points$)$

a$.$Perform the following sequence of actions within a single transaction:

Insert a new patient record.

Schedule an appointment for the patient.

Add a treatment entry for the patient.

b$.$Use DMVs to monitor the transaction's progress and locks during execution.

NO$\_$INFOMSGS and Submission $(10$ points$)$

a$.$Run a DBCC CHECKDB command on the "HealthCareDB" database with the NO$\_$INFOMSGS option to suppressinformational messages.

b$.$Explain the purpose of using the NO$\_$INFOMSGS option during DBCC CHECKDB.

Grand Total: $80$

End of Formative Assessment $2$