Creating and Querying a Database using SQL

Modeling the database

Our database model keeps comprehensive track of patients and staff in the hospital system. All patients have personal records, and information regarding their appointments is recorded such as date, time, bill amount, insurance, room number, etc. Information about different staff in each department is recorded, especially call operators and ambulance workers. Other personnel data is stored regarding doctors, nurses, security, lab technicians, etc.
This was a group project, and most of my contribution was in the EERD modeling, definition of tables in SQL and SQL Queries to test the database.

Data Definition

Here is some of the code we used for defining our tables in SQL. It was important to keep track of entity relations and establish our tables in the right order especially dealing with one-many relationships. We used primary keys for indexing tables and used foreign keys to establish relationships.

Mocking the Data

Once are tables were created and relations defined, we filled them with mock data using mockaroo.com. We had to make attributes matched their datatype (varchar, smallint, bool, etc.)and constraints, and that foreign keys were consistent with their primary keys. This is what some of them looked like after:

Querying the tables

With the mock data in the tables, they are now ready for querying for business purposes. Here are some potential admin needs that the database can solve

• Find all expired insurance plans

• Bree (bglasard1h@google.ca) was admitted to the hospital because of an emergency. Her husband had called for an ambulance from her phone. He handed his keys to the driver of the ambulance but forgot to get it back. Find who was driving.

• A worried friend is looking for a Walden in a semi-private room. Find out which room he’s in.

• Doctor can’t remember a patient’s name. He was diagnosed with bronchitis and his name started with J

• Find the max, avg and total cost paid by emergency patients in the cardiology dept

Reflection

Most of the actual difficulty we faced in the project was while creating the mock data. We realized only in this stage that the data generator wouldn't support some of the concepts in our model:
• We couldn't get Patient’s ‘DateAdmitted’ to be before ‘DateReleased’
• Originally we had 'Diagnosis' and 'Treatment' as two separated but correlated attributes but realized this wasn't supported by the data generator so we got rid of the treatment attribute
• We couldn't get the subtype delimiter to match up with certain staff ID so we had to manually input them into Staff ID 'Position' attribute