DBS101 Unit 2
Unit 2: Entity-Relationship Model and Relational Schema
After completing Unit 1, I was excited to dive deeper into the world of databases. Unit 2 introduced me to the Entity-Relationship Model (ERD) and Relational Schema, and it was a game-changer for my understanding of how databases are designed and structured. Let me take you through my journey of learning and how it transformed my perspective.
Previous Understanding of ERD and Relational Schema
Before starting this unit, I had a vague idea of what an ERD was. I thought it was just a fancy diagram that showed how tables were connected in a database. I didn’t realize how crucial it was for designing a database or how it could help in visualizing relationships between different entities. As for relational schema, I had no clue what it was or how it worked. I thought it was just another term for a database table.
Key Learnings from Unit 2
Entity-Relationship Model (ERD)
The ERD is like the blueprint of a database. It helps us visualize the structure of the database by showing the entities (tables), their attributes (columns), and the relationships between them. Here’s what I learned:
Entities: These are the “things” or objects in the real world that we want to store data about. For example, in a university database, entities could be Student, Course, or Instructor.
Attributes: These are the properties or details of an entity. For example, a Student entity might have attributes like StudentID, Name, and Address.
Relationships: These show how entities are connected. For example, a Student enrolls in a Course, and an Instructor teaches a Course.
Cardinality: This defines the number of relationships between entities. For example, one student can enroll in many courses, and one course can have many students (many-to-many relationship).
Relational Schema
The relational schema is the actual implementation of the ERD in a database. It defines the structure of the database, including tables, columns, and relationships. Here’s what I learned:
Tables: Each entity in the ERD becomes a table in the relational schema. For example, the Student entity becomes a Student table.
Primary Keys: These are unique identifiers for each row in a table. For example, StudentID would be the primary key for the Student table.
Foreign Keys: These are used to create relationships between tables. For example, the Course table might have a StudentID as a foreign key to link it to the Student table.
Normalization: This is the process of organizing the database to reduce redundancy and improve data integrity. I learned about the different normal forms (1NF, 2NF, 3NF) and how they help in designing efficient databases.
Mapping ERD to Relational Schema
One of the most interesting parts of this unit was learning how to convert an ERD into a relational schema. Here’s how it works:
Entities to Tables: Each entity in the ERD becomes a table in the relational schema.
Attributes to Columns: The attributes of an entity become the columns of the table.
Relationships to Foreign Keys: Relationships between entities are represented using foreign keys in the tables.
Handling Complex Relationships: For many-to-many relationships, a new table (junction table) is created to handle the relationship.
Types of Relationships
I also learned about the different types of relationships in databases:
One-to-One: For example, one student has one student ID.
One-to-Many: For example, one instructor teaches many courses.
Many-to-Many: For example, many students enroll in many courses.
Reducing E-R Diagrams to Relational Schemas
Create a table for each entity type.- All single valued attribute becomes a column for the table.- Primary key attribute of entity → Primary key of table.
Multivalued attribute is represented by a separate table.
For composite attributes, only the sub attributes are made into columns of the table.
Derived attributes are not made into columns.
Relational Query Languages
Imperative query language: user gives specific instructions.
Functional query language: input is given as evaluation of functions that may operate on data in the database.
Declarative query language: the user describes the desired information without giving a specific sequence of steps or function calls for obtaining that information.
Relational Algebra
We explored relational algebra, which is a formal system for working with relations (tables) in a database. It includes operations like selection, projection, union, intersection, and join. These operations are key to understanding SQL queries, making them an essential topic.
Here’s a different example of relational algebra operations:
Selection: σ (Salary > “50000”) (Employee)
This selects all rows from the Employee table where the Salary is greater than “50,000.”
Projection: Π (FirstName, JobTitle) (Employee)
This projects only the FirstName and JobTitle columns from the Employee table.
Comparison of Previous and Current Understanding
Before this unit, I thought ERDs were just simple diagrams, and relational schema was just a fancy term for tables. Now, I understand that ERDs are essential for designing a database, and relational schema is the actual implementation of that design. I also learned how to map an ERD to a relational schema, which is a crucial skill for database design.
Conclusion
This unit has been an eye-opener for me. I now understand the importance of ERDs and relational schema in designing efficient and effective databases. I’m excited to apply these concepts in real-world scenarios and continue learning more about database systems.
Homework
Here are some screenshots of the homework I completed for this unit:
Reflection
This unit has been so rewarding for me. I started with only a basic idea of ERDs and relational schema, but now I can design and use them confidently. I’m excited to learn more about database systems in the next units and to use these ideas in real-life projects.