Hello Everyone, Welcome to Rajasthan Polytechnic Blogs.
Subject - Introduction to DBMS CS 4002(Same as CI/IT 4002)
Branch - Computer Science Engineering
Semester - 4th Semester
UNIT 3: The Relational Data Model and Relational Database Constraints
This unit covers the Relational Data Model, its constraints, Codd's rules, and the process of mapping ER/EER models to relational models. It also delves into Relational Algebra and Relational Calculus, which are formal query languages used to manipulate relational data.
3.1. The Relational Data Model and Relational Database Constraints 🔄
The Relational Data Model is a way of structuring data using tables (also called relations) where data is stored in rows and columns.
Key Concepts:
Relation: A table in a relational database. It consists of rows (tuples) and columns (attributes). For example, a Student table can be a relation with columns like Student_ID, Name, Age, etc.
Tuple: A row in a table. It represents a single data entry, such as a student with a unique Student_ID.
Attribute: A column in the table. It represents a specific property or characteristic of the entity. For example, Name, Age, Gender are attributes of a student.
Relational Database Constraints:
Primary Key (PK): Uniquely identifies each record in a relation. No two tuples can have the same value for the primary key.
Foreign Key (FK): An attribute in one relation that links to the primary key of another relation. It is used to establish relationships between tables.
Unique Constraint: Ensures that all values in a column are unique.
Not Null Constraint: Ensures that a column cannot have NULL values.
Check Constraint: Ensures that the values in a column satisfy a specific condition.
Domain Constraint: Ensures that the values in a column are valid according to a defined domain, such as numeric ranges or a set of predefined values.
3.2. Codd’s Rule of DBMS 📜
Dr. Edgar F. Codd proposed 13 rules (also called Codd’s 12 Rules or Codd’s 12 Commandments) for relational database management systems (RDBMS) to ensure the proper functioning and structure of a relational database.
The key rules include:
Information Rule: All information in a relational database is represented by values in tables (relations), which are shown in the form of rows and columns.
Guaranteed Access Rule: Every value in the relational database is accessed through a combination of table name, primary key, and column name.
Systematic Treatment of Null Values: The DBMS should handle NULL values uniformly and consistently across all tables.
Dynamic On-Line Catalog: The structure of the database should be stored in a catalog (data dictionary), and this catalog should be accessible using the same relational query language.
Comprehensive Data Sublanguage Rule: The database should support at least one relational language that is comprehensive enough to manage all aspects of the database (querying, updates, etc.).
View Updating Rule: All views (virtual tables) should be updateable, meaning data in a view can be modified.
High-Level Insert, Update, and Delete: The DBMS should support high-level operations like inserting, updating, and deleting multiple tuples at once.
Physical Data Independence: Changes in the physical storage of data should not affect how data is accessed by the user.
Logical Data Independence: Changes in the logical schema (structure) should not affect the external schema or application programs.
Integrity Independence: The DBMS should be able to enforce integrity constraints independently from the user application.
Distribution Independence: The DBMS should be able to handle data distribution across multiple machines or systems without affecting the user or application.
Non-subversion Rule: If the relational system supports low-level access, the low-level operations should not be able to subvert the relational integrity of the database.
3.3. ER/EER to Relational Model Mapping 🗺️
The process of mapping an ER (Entity-Relationship) or EER (Enhanced Entity-Relationship) model to a Relational Model is crucial in transforming an abstract database design into a physical relational schema.
Mapping Steps:
Entities to Tables: Each entity in the ER/EER model is mapped to a table in the relational model.
- Example: The Student entity becomes a Student table with attributes like Student_ID, Name, Age, etc.
Attributes to Columns: The attributes of entities or relationships are mapped to columns in the corresponding tables.
- Example: Student_Name becomes a column in the Student table.
Primary Keys: The primary key in the ER/EER model becomes the primary key in the relational table.
- Example: Student_ID can be the primary key for the Student table.
Relationships to Foreign Keys: In the relational model, the relationships between entities are mapped to foreign keys.
- Example: If a Student enrolls in a Course, the Student_ID can be used as a foreign key in the Enrollment table to link the Student to the Course.
Weak Entities: For weak entities, which do not have a primary key, a composite key involving the primary key of the parent entity is used.
- Example: A Course_Enrollment table may have a composite primary key of Student_ID and Course_Code.
3.4. Relational Algebra 🔢
Relational Algebra is a procedural query language used to query and manipulate data in a relational database. It consists of a set of operations that take one or more relations as input and produce a new relation as output.
Basic Operations in Relational Algebra:
Selection (σ): Selects rows from a relation that satisfy a given condition.
- Syntax: σ_condition(Relation)
- Example: σ_Age>20(Student) selects all students older than 20.
Projection (π): Selects specific columns from a relation.
- Syntax: π_column1, column2(Relation)
- Example: π_Name, Age(Student) selects the Name and Age columns from the Student table.
Union (∪): Combines the tuples from two relations, removing duplicates.
- Syntax: Relation1 ∪ Relation2
- Example: If Student1 and Student2 have different students, Student1 ∪ Student2 combines them.
Set Difference (−): Returns the tuples that are present in one relation but not in the other.
- Syntax: Relation1 − Relation2
- Example: Student1 − Student2 returns students that are in Student1 but not in Student2.
Cartesian Product (×): Returns the Cartesian product of two relations, combining each tuple of one relation with each tuple of the other.
- Syntax: Relation1 × Relation2
- Example: Student × Course combines all students with all courses.
Join (⨝): Combines two relations based on a common attribute.
- Syntax: Relation1 ⨝ Relation2
- Example: Student ⨝ Enrollment links students with their enrollments.
3.5. Relational Calculus 📊
Relational Calculus is a non-procedural query language that uses mathematical logic to express queries. It defines what data to retrieve without specifying how to retrieve it. There are two types of relational calculus:
Tuple Relational Calculus (TRC): Specifies queries using variables that represent tuples.
- Syntax: {T | condition(T)}
- Example: {T | T ∈ Student ∧ T.Age > 20} selects tuples from the Student relation where Age is greater than 20.
Domain Relational Calculus (DRC): Specifies queries using variables that represent attribute values.
- Syntax: {(x1, x2, ..., xn) | condition(x1, x2, ..., xn)}
- Example: {(Name, Age) | ∃Student(Student.Name = Name ∧ Student.Age > 20)} selects the Name and Age of students where Age is greater than 20.
Conclusion 🎯
- The Relational Data Model is a widely used model that structures data into tables, making it easy to understand and manage.
- Relational Algebra and Relational Calculus provide the formal foundations for querying relational databases.
- Codd's Rules set the guidelines for creating efficient and effective relational databases.
- Mapping ER/EER Models to the Relational Model is essential for creating a functional database schema.
Understanding these concepts is crucial for designing, managing, and querying databases efficiently.
0 Comments