introduction to relational model and codds rule.pptx

codd rule in relational modeling

1. Group Members Arju Thakur (05) Abhijeet Moghe (18) Rutuj Langde (45) Introduction of Relational Model and Codd Rules in DBMS Guided by :- Prof. Anirudh Bhagwat G. H. Raisoni College of Engineering, Nagpur Department Of Artificial Intelligence

2. INDEX Sr No. Topic 1 Introduction to relational model 2 Example of relational model 3 Important Terminologies 4 Advantage and Disadvantages of the Relational Model 5 Codd Rules in Relational Model

3. Introduction to relational model Proposed by E.F. Codd : The Relational Model was introduced by E.F. Codd to structure data in the form of relations (tables). ER Diagram to Relational Model : After designing the conceptual database using an ER diagram, it is converted into a relational model, which can be implemented using RDBMS languages like SQL. Data Representation in Tables : The Relational Model uses a collection of tables. Tables (or relations) consist of rows (records) and columns (attributes), with each column having a unique name. Widely Used Data Model : The Relational Model is the most commonly used database model, forming the foundation of the majority of database systems today.

4. Example of Relational model

5. Important Terminologies Attribute:  Attributes are the properties that define an entity. e.g.; ROLL_NO, NAME, ADDRESS Relation Schema :   A relation schema defines the structure of the relation and represents the name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema. Tuple:   Each row in the relation is known as a tuple. Degree :   The number of attributes in the relation is known as the degree of the relation. Cardinality :  The number of tuples in a relation is known as cardinality . NULL Values :   The value which is not known or unavailable is called a NULL value. It is represented by blank space

6. Advantage and Disadvantages of the Relational Model

7. Introduction to Codd’s Rule What are Codd's 12 Rules? A set of guidelines proposed by E.F. Codd in 1985 to define what a true Relational Database Management System (RDBMS) should comply with. Purpose : Ensure data consistency , integrity , and independence . Set the foundation for modern relational databases . Key Focus Areas : Data representation in tables. Data accessibility and independence. Handling null values , integrity, and view management. Significance : These rules laid the groundwork for the development of relational databases like SQL -based systems (MySQL, PostgreSQL, Oracle).

8. Codd’s Rules 1,2 Definition: All information (data and metadata) is represented explicitly as values in tables. Explanation: In a relational database, everything, including the schema (structure), should be stored in tables. Importance: This ensures data is stored in a standardized, structured way, accessible through queries. Example: A customer’s information is stored as rows in a table where columns represent different attributes (Name, Age, Address). Definition: Every data element must be accessible by specifying the table name, primary key, and column name. Explanation: The DBMS should allow direct access to any piece of data using a combination of table name, row identifier (primary key), and attribute (column). Importance: Ensures that data is organized in a predictable way and no hidden data can exist. Example: You can retrieve a customer’s email by specifying the table name (Customer), primary key ( CustomerID ), and attribute (Email). 1. The Foundation Rule 2. The Information Rule

9. Codd’s rules 3,4 Definition : The system must support NULL values (representing missing or inapplicable data) and treat them systematically. Explanation : A NULL represents the absence of a value and should be treated differently from a blank or zero. It must be consistently handled across the DBMS. Importance : Ensures that missing data is recognized but not confused with valid data. Example : In a customer database, a missing phone number should be marked NULL, not an empty string. 3 . Systematic Treatment of NULL Values 4. Dynamic Online Catalog Based Definition : The database schema and structure should also be stored in relational tables and accessible via SQL queries. Explanation : The catalog (or metadata) that describes the structure of the database should be queryable just like any other data. Importance : Allows the database structure to be explored and manipulated programmatically. Example : Retrieving table structure information (like column names, data types) using SQL queries.

10. Codd’s Rules 5, 6 Definition : The system must support a comprehensive language for data definition, manipulation, and querying (like SQL). Explanation : A relational database should have a well-defined query language that allows the user to define the schema, insert/update/delete data, and perform queries. Importance : Provides a standard and unified way to interact with the database. Example : SQL is the most widely used comprehensive language for relational databases. 5. The Comprehensive Data Sublanguage Rule 6. The View Updating Rule Definition: All views (virtual tables derived from base tables) must be updatable, as long as the update doesn’t violate integrity constraints. Explanation: Views allow users to see a subset of data. If a view represents data from a single table, it should be possible to update that view. Importance: Provides flexibility by allowing users to work with different views of the same data. Example: If you have a view that shows only employees in a specific department, you should be able to update the salary of an employee directly through that view.

11. Codd’s Rules 7,8 Definition: The system must support high-level operations like inserting, updating, and deleting data in multiple rows simultaneously. Explanation: A relational DBMS should allow users to operate on sets of rows at once (set-at-a-time) rather than requiring procedural row-by-row operations. Importance: Makes data manipulation more efficient and less error-prone. Example: In SQL, you can update the salary for all employees in a department with a single command. Definition: Changes in the physical storage of the data (like how it is stored on disk) should not affect how the data is accessed and manipulated logically. Explanation: Users should not need to know or be concerned with how the data is physically stored. The DBMS should handle that abstraction. Importance: Ensures that the system can evolve (storage optimizations) without affecting applications that use the database. Example: Changing the way customer data is partitioned across storage disks should not affect how the application queries customer information. 7. Possible for High-Level Insert, Update, and Delete 8. Physical Data Independence

12. Codd’s Rules 9,10 Definition: Integrity constraints must be stored and enforced independently of application programs. Explanation: Integrity rules, such as foreign keys, primary keys, and constraints (e.g., age must be > 18), should be defined in the DBMS and not at the application level. Importance : Ensures that data integrity is maintained across all applications and not reliant on external programs. Example: A foreign key constraint between two tables should be enforced by the DBMS, preventing invalid data entry, regardless of how the data is being accessed or inserted. 10.Integrity Independence 9 . Logical Data Independence Definition: Changes to the logical structure (e.g., adding or modifying columns) should not affect the way data is accessed unless it directly relates to that change. Explanation : Applications accessing the data should remain unaffected if the schema is modified, such as adding a new column or modifying a table structure. Importance: Allows the database structure to evolve without breaking existing applications. Example: Adding a new “ MiddleName ” column to a customer table should not affect queries that only use FirstName and LastName .

13. 11. Distribution Independence Definition: The system should work the same whether data is distributed across multiple locations or stored in a single location. Explanation: A user should not need to know whether the data is distributed across different databases or located centrally. Importance: Enables scalability and distributed systems without affecting data access or integrity. Example: A DBMS should allow seamless querying and updating, even if the data is distributed across multiple servers. 12. The Non-subversion Rule Definition: If a relational system has any low-level access methods (e.g., direct record access), they must not be able to bypass relational integrity rules or constraints. Explanation: The DBMS should ensure that even low-level operations (like direct record manipulation) must follow the same integrity constraints as high-level operations (like SQL queries). Importance: Prevents corrupting the data or bypassing integrity rules. Example: Direct file access should not allow a user to violate primary key constraints. Codd’s Rules 11,12

14. Importance of Codd’s Rules in Modern Databases Ensures Relational Integrity: Codd's rules enforce a consistent and standardized approach to managing data. Widespread Adoption in SQL Databases: Major RDBMS systems like Oracle, MySQL, PostgreSQL, and SQL Server have adopted Codd's principles. Impact on Data Management: Codd’s rules continue to influence how data is modeled, queried, and maintained.a

15. Limitations and Challenges Modern Systems Don't Fully Comply: Not all relational databases strictly adhere to all 12 rules due to practical limitations. Example: Some systems do not fully support view updating or integrity constraints independent of applications. Shift Toward NoSQL Databases: Newer database systems like NoSQL prioritize flexibility, scalability, and handling unstructured data, which are not strictly bound by relational rules.

16. Thank You!