DBS101_Unit2

Unit 2: Relational Model and Schema Design

After finishing Unit 1, I felt I had a basic understanding of what databases are and why they are important. However, Unit 2 focused on the Relational Model and Schema Design and it changed my perspective on databases. I understood that databases are more than just holding data, they’re also about arranging it in such a way that it’s easy to access change and scale as necessary.

What I Learned in Unit 2

The Relational Model

Unit 1 focused on the relational concept while Unit 2 discusses further into it. I learned that the relational approach involves arranging data into tables or relations with rows and columns. Each table represents a single entity (such as a student or a course) whereas the columns represent properties (such as name, age or course ID).

Understanding the primary and foreign keys was one of the most important points. Primary keys identify each row in a table whereas foreign keys create connections between tables. It also demonstrated how to link data from separate tables without duplicating the information.

Schema Design

Schema design was another important topic in this unit. I discovered that a database schema is an approach for how data is organized in a database. It defines the database’s structure which includes tables, columns, data types and relationships between tables.

We also learned about normalization, which is the process of organizing data to reduce redundancy and improve data integrity. Normalization is breaking down the tables into smaller and easier to manage sections and ensuring that each piece of data is only saved in one location.

ERD to Relational Schema Translation

One of the most beneficial aspects of this unit was learning how to convert an Entity-Relationship Diagram (ERD) to a relational schema. We transformed the ERD we constructed in Unit 1 into a set of tables that had primary keys, foreign keys and relationships. This exercise helped me understand how the conceptual design (ERD) transfers into the actual database structure.

Here’s an example of an ERD we worked on:

And here’s how we translated it into a relational schema:

Relational Algebra

We also discussed relational algebra, which is a formal system for manipulating relationships (tables) in databases.
Selection, projection, union, intersection and join are some of the operations in relational algebra. Understanding these processes was essential since they serve as the foundation for SQL queries.

Here is an easy example of a relational algebra operation:

• Selection: σ (StudentNo=”12345”) (Student) - This selects all rows from the Student table where the StudentNo is “12345”.

• Projection: Π (Name, Address) (Student) - This projects only the Name and Address columns from the Student table.

How My Perspective Has Changed

Before this unit, I thought of databases as just a collection of tables. Now I know them as highly structured systems with well-designed schemas that ensure data integrity and efficiency. I also have a better understanding of how data is linked between tables and how to create a database with little redundancy.

Key Realizations:

• The relational model is the foundation of most modern databases.
• Schema design is crucial for creating efficient and scalable databases.
• Normalization is key to reducing redundancy and improving data integrity.
• Relational algebra is the backbone of SQL queries.

Homeworks

Here are some of the homeworks we did in this unit:

Looking Ahead

Unit 2 was a deep dive into the relational model and schema design, and it really helped me understand how databases are structured and how to design them effectively. I’m excited to see what’s next in Unit 3, where we’ll be diving into SQL and actually querying databases. I feel like I’m building a strong foundation, and I can’t wait to apply these concepts in real-world scenarios.