Introduction to Relation Model | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Introduction

• Originated by Dr. E.F. Codd, it employs relations to represent files.
• Relations are portrayed as Two-Dimensional Tables.
• It's straightforward to implement and streamline data manipulation operations.
• Considered the most prevalent data model due to its simplicity.
• Utilizes primary and secondary keys to establish connections between files.
• Normalization Theory guides the design of the object-based data model.
• Relational Algebra and Relational Calculus handle relation processing manually.
• Many database languages are tailored for managing relational data models.
• A Relational Database Model comprises relations connected by key fields.
• Each relation possesses attributes represented in rows and columns.
• Columns in a relation are attributes, while rows are tuples.
• Every relation can feature a unique column, i.e., primary key.
• Relations can have varying numbers of columns and tuples.
• Relations are identified by their names and fields, separated by commas within parentheses.
• The relational model presents data in the format of relations or tables.

Example: Relational Model can be represented as shown below.

Terminologies

1. Relational Schema

• Definition: It outlines the structure of a relation.
• Example: The schema for the STUDENT relation could be depicted as STUDENT(STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE).

2. Relational Instance

• Definition: Refers to the values existing within a relationship at a specific time.
• Illustration: Demonstrated in Table 1 and Table 2.

3. Attribute

• Definition: Attributes are properties defining each relation.
• Example: Attributes of the STUDENT relation include STUD_NO, STUD_NAME, etc.

4. Domain of an Attribute

• Definition: It signifies the range of possible values an attribute can possess within a relation.
• Example: The domain of STUD_AGE might span from 18 to 40.

5. Tuple

• Definition: Tuples denote individual rows within a relation.
• Example: The STUDENT relation provided below consists of 4 tuples.

6. NULL Values

• Definition: Represent values that are unknown, missing, or undefined for certain attributes in tuples.
• Distinctness: Two NULL values within a relationship are considered unique from each other.
• Illustration: Depicted in Table 1 and Table 2 representing two relations: STUDENT and STUDENT_COURSE.

Student Table

Student Course Information

RDBMS Vendors

Various vendors offer Relational Database Management Systems (RDBMS), catering to different needs and preferences. Here are some of the prominent ones:

• Oracle:
  • Oracle Database is highly regarded for its robustness, scalability, and reliability.
  • Preferred by many large enterprises, it excels in data warehousing and transaction processing.
• Microsoft:
  • Microsoft SQL Server is commonly used in Windows environments.
  • Offers features like data mining, business intelligence, and reporting services.
• IBM:
  • IBM DB2 is prominent in enterprise settings.
  • Noteworthy features include high availability, disaster recovery, and scalability.
• MySQL:
  • An open-source RDBMS popular among small to medium-sized businesses.
  • Known for its ease of use, flexibility, and cost-effectiveness.
• PostgreSQL:
  • Another popular open-source RDBMS recognized for scalability and reliability.
  • Supports complex transactions and is favored by many developers.
• SAP:
  • SAP HANA is an in-memory RDBMS designed for high-performance analytics and data processing.
  • Often employed in enterprise environments for real-time reporting and business intelligence.

Relational Algebra Operators

1. UNION (U):
   • Combines two relations, displaying all unique values from both relations.
   • Syntax: A UNION B (or) A U B
   • Example: A = {clerk, manager, salesman}, B = {president, clerk, manager} → A UNION B = {clerk, manager, salesman, president}
2. INTERSECTION (∩):
   • Displays common elements between two relations.
   • Syntax: A INTERSECT B (or) A ∩ B
   • Example: A = {clerk, manager, salesman}, B = {president, clerk, manager} → A INTERSECT B = {clerk, manager}
3. DIFFERENCE (─):
   • Displays elements in relation A not present in relation B.
   • Syntax: A MINUS B (OR) A ─ B
   • Example: A = {clerk, manager, salesman}, B = {president, clerk, manager} → A MINUS B = {salesman}
4. CARTESIAN PRODUCT (X):
   • Generates a new relation with all possible combinations of tuples from two relations.
   • Syntax: A TIMES B (or) A X B
   • Example: A = {clerk, manager, salesman}, B = {president, clerk, manager} → A TIMES B = {(clerk, president), (clerk, clerk), ...}
5. SELECTION (σ):
   • Filters rows from a relation based on specified conditions.
   • Syntax: σ condition (relation name)
   • Example: σ (salary > 50000) (EMPLOYEE)
6. PROJECTION (π):
   • Displays specified columns from a relation.
   • Syntax: π (col1, col2...) (Relation Name)
   • Example: π (sno, sname, total) (MARKS)
7. JOIN:
   • Combines two or more relations based on common attributes.
   • Types include Inner Join, Outer Join, Left Outer Join, Right Outer Join.
8. DIVIDE (÷):
   • Divides the tuples of one relation by another relation.
   • Syntax: A DIVIDE B (OR) A ÷ B
   • Example: A = {clerk, manager, salesman}, B = {clerk, manager} → A DIVIDE B = {salesman}
9. RENAME (ρ):
   • Renames a relation or its attributes.
   • Syntax: ρ (OLD RELATION, NEW RELATION)
   • Example: ρ (STUDENT, MARKS)

Relational Model Features and Codd's Rules:

Features of the Relational Model:

• Tables/Relations: Basic building block representing a collection of related data, composed of columns (attributes) and rows (tuples).
• Primary Keys: Ensure each row has a unique identifier.
• Foreign Keys: Link tables together and enforce referential integrity.
• Normalization: Organizes data into tables, eliminating redundancy for consistency and maintenance.

Codd's Rules:

1. Rule 0: Foundation Rule
   • DBMS must manage databases entirely through its relational capabilities.
2. Rule 1: Information Rule
   • Data in the Relational model must be a value of some cell of a table.
3. Rule 2: Guaranteed Access Rule
   • Every data element must be accessible by table name, primary key, and attribute name.
4. Rule 3: Systematic Treatment of NULL Values
   • NULL values in the database must correspond only to missing, unknown, or not applicable values.
5. Rule 4: Active Online Catalog
   • Database structure must be stored in an online catalog accessible by authorized users.
6. Rule 5: Comprehensive Data Sub-language Rule
   • A database should be accessible by a language supported for definition, manipulation, and transaction management operation.
7. Rule 6: View Updating Rule
   • Different views created for various purposes should be automatically updatable by the system.
8. Rule 7: High-level Insert, Update, and Delete Rule
   • Relational Model should support insert, delete, update operations at each level of relations. Set operations like Union, Intersection, and Minus should be supported.
9. Rule 8: Physical Data Independence
   • Any modification in the physical location of a table should not enforce modification at the application level.
10. Rule 9: Logical Data Independence
    • Any modification in the logical or conceptual schema of a table should not enforce modification at the application level. For example, merging two tables into one should not affect the application accessing it which is difficult to achieve.
11. Rule 10: Integrity Independence
    • Integrity constraints modified at the database level should not enforce modification at the application level.
12. Rule 11: Distribution Independence
    • Distribution of data over various locations should not be visible to end-users.
13. Rule 12: Non-Subversion Rule
    • Low-level access to data should not be able to bypass the integrity rule to change data.

Advantages of Relational Algebra:

• Simplicity: Provides simple and easy-to-understand operators for data manipulation.
• Formality: Offers a standardized and rigorous way of expressing queries.
• Abstraction: Provides a high-level abstraction of the database structure, focusing on logical aspects.
• Portability: Independent of specific database systems, enabling easy query porting.
• Efficiency: Optimized for efficient query execution, particularly important for large databases.
• Extensibility: Provides a flexible framework that can be extended with new operators and functions.

Disadvantages of Relational Algebra

• Complexity: Syntax and semantics can be challenging for non-experts.
• Limited Expressiveness: May struggle to express complex queries with its limited set of operators.
• Lack of Flexibility: Designed primarily for relational databases, may not be suitable for other data storage systems.
• Performance Limitations: May face performance issues with large or complex datasets.
• Limited Data Types: Designed for simple data types, may not support complex data types effectively.
• Lack of Integration: Integration with other systems and tools may require additional effort.

GATE Question

Given the basic ER and relational models, which of the following is INCORRECT? [GATE CS 2012]

1. An attribute of an entity can have more than one value.
2. An attribute of an entity can be a composite.
3. In a row of a relational table, an attribute can have more than one value.
4. In a row of a relational table, an attribute can have exactly one value or a NULL value.

Answer: In the relation model, an attribute can’t have more than one value. So, option 3 is the answer.