Database Management
In this project, me and my team have created an entire database for an animal hospital with locations in New Jersey and New York city.
Tools used: Lucid Charts, SQL Server, Ms Access, Google Sheets
View Complete Presentation ->
Step 1
Understanding the business
Step 2
In order to create a database, it is extremely important to understand the business and the way it will function. We started by discussing the business and the data it will need in order to function on a daily basis.
Creating an Entity Relationship Diagram
Once we understood the requirements of the hospital, we then started listing down all the entities that will be required for the organization to work ant communicate smoothly.
The entities that are required are:
-
Appointments
-
Animal
-
Pet Owner
-
Animal Type
-
Examinations
-
Staff
-
Branch
-
Job Description
-
Pet Treatments
-
Medication
-
Invoices
-
Insurance
​
After listing down the entities required in the business, we started creating an Entity Relationship Diagram using Lucid Charts to understand the business model.
Below is the Entity relationship diagram which explains the functioning of the business.
In the above ER Diagram, pk refers to the primary key in the table using which we will be able to link them with the foreign keys in different tables. The primary key is unique to each row in a table.
For eg. EmployeeID is a primary key in the Staff table and is a foreign key in Branch table which helps in linking both tables together.
The numbers 0..1, 0..* ( * = many), 1..* and 1..1 refer to the relationship between the two tables which is called as Cardinality
For eg. the relationship between Branch and Staff is as follows:
Branch employs 1 or many Staff,
Staff is employed by 1 and only 1 branch.
Conducting Normalization
Step 3
After creating the Entity Relationship diagram, we started the normalization process.
Normalization is a technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships.
For eg.
Before Normalization
Staff (EmployeeID, BranchID (fk), FirstName, LastName, Street, City, State, StaffZipCode, PhoneNumber, Position, Salary)
​
Conducting Normalization
Key: EmployeeID
Functional Dependency 1:
EmployeeID → FirstName, LastName, Street, City, State, StaffZipCode, Country, PhoneNumber, Position, Salary, BranchID
Functional Dependency 2:
StaffZipCode→ City, State
Functional Dependency 3:
Position → Salary.
​
After Normalization
Staff_Info (EmployeeID, BranchID(fk), Position(fk), StaffZipCode(fk), FirstName, LastName, Street, PhoneNumber)
StaffZip (StaffZipCode, City, State)
Job Description (Position, Salary)
Creating Tables and relationships in Microsoft Access
Step 4
After completing Normalization, we started creating tables and relationships in Ms Access using Queries. This is called Data Design Language (DDL). DDL is a language for describing data and its relationships in a database.
Step 5
Step 6
Step 7
Creating Dummy Data for our tables
Once our framework was created in Microsoft Access, we started creating dummy data in order to input them in our tables. We used Google sheets to create the data.
Inputting Data and Running Queries
After creating the data, we added them to our table using queries. We then ran a few queries to check if our database runs smoothly and is well built.
Data Manipulation Language or DML is a subset of operations used to insert, delete, and update data in a database. Below are a few queries that represent DML.
In the query below we are using INNER JOIN to join a few tables together using primary and foreign keys in various tables.
Creating Forms
One of the most useful tools in Ms Access is Forms. A form in Access is a database object that you can use to create a user interface for a database application. A bound form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source.
THANK YOU!!
Team Members:
Ewa
Jasmine
Vlada
Urvaksh
Andres