Database Building in Microsoft SQL Server

Used Relational Database design to create and implement a database from scratch in Microsoft SQL server.

CAtegory

School Projects

Created on

February 2023

Database Building in Microsoft SQL Server

About the Project

This project took place inside a class for my Business Intelligence specialization in my Masters of Information Management program. This was a quarter-long project where we learned about relational database management systems (RDBMS) and created our own database from conceptual and logical bu8ilding of the database to the physical implementation in Microsoft SQL server. Alongside this, we learned SQL, data management, database administration and data modeling tools and techniques. Our database could be built around whatever subject we pleased with the guiding questions being "who will use this database?" and "what decisions will this database help them make?" They emphasized the importance of keeping the problem space simple and the scale small, as this was most of the student's first time using SQL and building a relational database from scratch. My project took the form of creating a database for an individual who just got off work and had to decide what after-work activity they would want to do that day having the options of going for a run, taking the dog for a walk, and playing video games.

Conceptual and Logical Database Design:

Designing the conceptual and logical database was the beginning steps we had to take in order to get a grasp on how our database would function. This class focused on building relational databases, so due to this, we practiced normalization techniques on other sample databases. We learned about primary and foreign key relationships, defining entity relationships and taking those from 1NF-> 3NF to practice going from the most basic form to the most detailed. This practice showed how imperative the conceptual and logical database design process is, especially in relational database management systems. The more precise and accurate you are on this step will guarantee a stronger data integrity and easier data loading and manipulation when the physical implementation takes place. We then took this knowledge to create the Entity Relationship Diagram (ERD) for our own database. This involved correctly defining relationships between entities, properly labeling data types and accurately characterizing primary and foreign keys. We used the software draw.io to do this and went through multiple rounds of trial and error before having a final ERD diagram to use when creating our physical database. My final ERD diagram is presented below. I took into account the individual, where they live, the weather and temperature for that given day to showcase that these factors could influence an individual's choice for their after-work activity. I then defined how long an individual had to do their activity on the given day and various details about each activity.

Final ERD Diagram:

Physical Database Implementation:

After my ERD was finished and my SQL knowledge had greatly increased, I was ready to do the physical implementation of my database in Microsoft SQL Server. This process was highly technical and detailed. Creating each of the tables, relationships and populating them using advanced stored procedures was a test of my SQL skills as well as the accurateness of my normalization processes in my conceptual and logical phases of the RDBMS process. We then had to implemented other features into our database to test our SQL knowledge such as creating computed columns, triggers and complex queries. An example of two triggers are shown below with information on their purposes for my given database. I also included an example of an advanced stored procedure used to populate one of the tables in my database.

Example of triggers as well as computed columns
Example of a nested stored procedure used to populate my database

Reflection:

This class and project gave me tangible skills into RDBMS. I learned an immense amount of SQL in this time, as well as about the intricacies of entities, and primary-foreign key relationships inside the conceptual and logical database design. When looking back, I wish I had more time to create a larger, more impactful database; the timescale of this class as well as my beginner knowledge when I started made this not feasible. In addition, the resources provided for this class were one of the worst I have experienced at UW. Unfortunately the professors and TAs were not just unhelpful, but actually contradicting at times, which caused a lot of confusion and frustration for me and my classmates. Almost everyone in this class had a very bad experience with how the class content was taught and lead to many to be forced to W3 schools and other online materials as our main resource for learning, instead of the instruction from the highly regarded (and expensive) university we attended. If it is not clear in my tone thus far, I can assure you I still have some animosity for this course and how it was taught. Despite such, I do believe because of this I was taught to advocate for myself and proved my ability to teach myself SQL in a highly technical manner. I was able to showcase my ability to create manage and implement a database from scratch. I hope I will be able to use this knowledge in an organization in the future to assist in database management and analysis to guide an organization to use their data as rationale for business decisions. The final SQL file cannot be attached as it is not supported on this platform; if you have any inquiries about this project, or wish to see my final database, please do not hesitate to reach out, I would love to discuss it more and hear feedback from others!

Click to Open if Applicable
rain cloud
Made in Webflow