Table of Contents
Course Description
The course description from MyPlan:
Introduction to relational database management systems, focused on relational theory and the application of conceptual, logical, and physical database modeling. Key topics include the relational model, SQL, entity-relationship modeling, three-tier architectures, implementation of database applications, and related topics in information systems.
Introduction
In my personal projects before this class, I had used databases as a way to hold my application's data. However, these were very small databases built only to support my applications. After taking INFO 330 at the University of Washington in Spring 2020, I got a good understanding of how important databases are in shaping how an entire application is structured. In Spring 2020, INFO 330 was taught by Randal Root and focused on a practical approach in teaching about databases. The class taught me about database design and the SQL code to create databases from those designs.
This post will outline what I learned from INFO 330 and will be structured and organized very similar to my previous course posts.
In Spring 2020, the University of Washington had to offer their courses in a completely remote format. As such, my experience may have been somewhat different to what the class usually would be like. Online classes were a new experience for me and I was able to learn a lot about managing my time. I outlined what I learned in an online quarter in another blog post here.
The code I have written for this class will not be publicly available. This is to prevent others from plagiarizing. Instead, I will try and offer the important lessons that I learned from each assignment. Prior to posting this publicly, on June 11, 2020, I emailed my instructor to read over this article in order to check for anything that might be deemed as academic misconduct. After receiving a response from my instructor the next day, I got approval through email.
Modules
We were given a module every week which consisted of lecture/lab recordings and an assignment. Each assignment would correspond with the content that we had learned in the module.
Module 1
The first module taught us about entity-relationship diagrams (ERDs) and the design rules of databases. More specifically, we talked about database normalization and how a good database design can help reduce redundant data. The assignment had us design an ERD and write SQL code to create a basic database.
Module 2
In Module 2, we built on the normalization rules from before and also introduced database constraints. By adding constraints to the data in our database, we can ensure that the data is valid and dependable. We also learned about using metadata spreadsheets as a tool to outline the types and constraints that we wanted our data to have. Another thing that was introduced was the use of views as an abstraction layer. Using database views as an abstraction layer, a developer can change the underlying tables and maintain the view so that the functionality would stay the same. This showed me how important abstraction layers were for an entire application since they could allow implementation details to be hidden while still providing functionality.
Module 3 - 5
Module 3, 4, and 5 was where we learned about SQL queries, transactions, and stored procedures. Since our data was normalized when it entered the database, we learned about joins that could piece back the data in order to make our database understandable. Even though we had normalized our data, we could find what we wanted by joining our data on shared information (usually an identifier). Transactions and stored procedures allowed us to add another abstraction layer. Transactions allowed us to group statements together and ensure that they all ran together. This might be ideal for a situation where a user transferred money to a different account. If the withdrawal succeeded but the deposit in the other account failed, that money would be lost. When combined with transactions, stored procedures allowed us to create an abstraction layer that would stop clients from manipulating data on the tables directly.
Module 6
This module taught us things about database administration like security and backups. Although we had created abstraction layers to protect our underlying tables, without proper permissions, users could still access these tables directly. Ensuring that our database has the right security and permissions is important.
Module 7 - 10
During Modules 7 - 10, we went over many different database applications. This included reporting applications like Excel and Tableau. Although I had used programs like Excel before, I had never known where the data had come from or what processes were used to prepare the reports. Through ETL processes, data can be stored and processed in a way that makes reporting easy. We were introduced to Non-SQL databases, multi-tier applications, and cloud databases.
The main thing that was really interesting to me during these modules was how multi-tier applications where built. These applications are usually divided into the presentation, application, and data tier. Having experience with web applications, I could definitely see that my personal projects had been following this exact architecture.
Final
The final project was completed during Modules 7 - 10 and combined everything we had learned throughout Modules 1 - 6. The work was divided into four different milestones and was about building a database for a patient appointment application. In the first milestone, we designed our database by creating an entity-relationship diagram and metadata worksheet. Here, we outlined the data and constraints that our application would need to store. Our next milestone had us write the SQL script that would construct the database based on our instructor's developer document. Since we had already outlined the design and constraints of the database, the script was easy to write. The third milestone had us create a script that imported mock data into the database. Additionally, we had to write a script that created reporting views and use that data in Excel and Tableau. The very last milestone was to create documentation for our database and write about what we had learned over the quarter. Overall, this project took us through the entire design, development, and documentation of a database system.
Overall Thoughts and Conclusion
My personal projects had always used databases primitively, but after taking INFO 330, this class has shown me the different features of databases and how they can be used as abstraction layers to protect an organization's data. This class taught me how to leverage database features like views and stored procedures to create abstraction layers that would conceal implementation details. Using abstraction layers in database and programming in general, it can help protect an application by making the implementation easier to understand. Overall, this class was very design-focused and taught me a lot about database design. At the same time, it also taught me how to write SQL code that would implement those designs.