Codd’s 12 Rules (13 Rules)

Summary

This presentation delves into Codd's 12 Rules for defining the requirements of a Relational Database Management System (RDBMS). The rules, proposed by Edgar Frank Codd, are numbered from 0 to 12, hence the name. They ensure that an RDBMS manages databases through relational capabilities, represents all information explicitly, guarantees access to every datum, systematically treats null values, and maintains physical and logical data independence. The rules also mandate the use of a comprehensive data sublanguage, support for view updating, and uphold integrity constraints. Lastly, they address the importance of distribution independence and non-subversion to ensure the system's integrity.

Takeaways

• 📚 The presentation introduces 'Codd's 12 Rules', a set of 13 foundational principles for a database management system (DBMS) to be considered relational, proposed by Edgar Frank Codd.
• 🔑 Rule 0, the 'Foundation Rule', asserts that a relational DBMS must manage databases entirely through its relational capabilities.
• 📊 Rule 1, the 'Information Rule', states that all information in a relational database should be represented explicitly at the logical level in a single way, through table values.
• 🔍 Rule 2, the 'Guaranteed Access Rule', ensures that every atomic value in the database can be accessed using the table name, primary key, and column name.
• 🚫 Rule 3 addresses the 'Systematic Treatment of Null Values', emphasizing that nulls represent missing or inapplicable information and must be treated consistently across data types.
• 📖 Rule 4, the 'Dynamic Online Catalog', requires that the database description and user authorization details are logically represented and accessible.
• 💬 Rule 5, the 'Comprehensive Data Sublanguage Rule', mandates that at least one language for the DBMS must support data definition, view definition, and transaction operations.
• 🔄 Rule 6, the 'View Updating Rule', ensures that updates to views are reflected in the underlying tables, maintaining data consistency.
• 🔧 Rule 7, the 'Relational View of Data Access', allows for high-level operations such as insert, update, and delete to be performed not just at the database level but also through application programs.
• 🏭 Rule 8, 'Physical Data Independence', ensures that application programs remain unaffected by changes in the physical storage or access methods of the database.
• 🌐 Rule 9, 'Logical Data Independence', guarantees that changes to the database schema do not impact the application logic, maintaining logical separation.
• 🔒 Rule 10, 'Integrity Independence', stipulates that integrity constraints should be definable and stored in the DBMS, not in application programs.
• 🌐 Rule 11, 'Distribution Independence', highlights the importance of data distribution across different locations for redundancy and disaster recovery, without user awareness of the distribution.
• 🛡️ Rule 12, the 'Non-Subversion Rule', asserts that a low-level language, if present, must not be used to bypass the integrity constraints defined in the higher-level relational language.

Q & A

What are Codd's 12 Rules for a database management system to be considered relational?

Codd's 12 Rules, also known as the 12 Commandments, are a set of 13 rules that define the requirements for a database management system (DBMS) to be considered a relational database management system (RDBMS). These rules were proposed by Edgar Frank Codd, the pioneer of the relational model for databases.

Why are there 13 rules in Codd's 12 Rules?

There are 13 rules in Codd's 12 Rules because the rules are numbered from 0 to 12, with the first rule being rule number zero and the last rule being rule number 12.

What is the significance of Rule Number Zero in Codd's 12 Rules?

Rule Number Zero, the foundation rule, states that a system claiming to be an RDBMS must be able to manage databases entirely through its relational capabilities, closely associating with the mathematical concept of relations.

What does the Information Rule (Rule Number One) imply for a relational database?

The Information Rule implies that all information in a relational database is represented explicitly at the logical level and in exactly one way, which is by values in the table.

Can you explain the Guaranteed Access Rule (Rule Number Two)?

The Guaranteed Access Rule states that each atomic value in a relational database must be logically accessible using a combination of table name, primary key, and column name.

How does Rule Number Three address null values in a relational database?

Rule Number Three, the Systematic Treatment of Null Values, states that null values, representing missing or inapplicable information, must be treated systematically by the RDBMS and must be independent of the data type.

What is the purpose of the Dynamic Online Catalog (Rule Number Four)?

The Dynamic Online Catalog ensures that the database description is represented at the logical level and deals with authorization, determining what data users can access.

What capabilities must a relational system support according to the Comprehensive Data Sublanguage Rule (Rule Number Five)?

The Comprehensive Data Sublanguage Rule requires that there must be at least one language supported by the system that can express data definition, view definition, data manipulation, integrity constraints, authorization, and transaction operations.

What does the View Updating Rule (Rule Number Six) require for a relational database?

The View Updating Rule mandates that any updates made to a view in an RDBMS should also update the underlying table or relation, as they both refer to the same data item in memory.

What is the importance of the Physical Data Independence (Rule Number Eight)?

Physical Data Independence ensures that application programs and terminal activities remain logically unimpaired when changes are made to the storage representations or access methods at the database level.

How does the Integrity Independence (Rule Number Ten) differ from Integrity constraints in application programs?

Integrity Independence requires that all integrity constraints specific to a relational database must be definable and stored in the relational data sublanguage, not in the application programs, ensuring that the constraints are managed at the DBMS level.

What does the Non-Subversion Rule (Rule Number Twelve) prevent in a relational system?

The Non-Subversion Rule ensures that if a relational system has a low-level language for single-record operations, it cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher-level relational language.

Outlines

00:00

📚 Introduction to Codd's 12 Rules for RDBMS

The paragraph introduces Codd's 12 rules, which are actually 13 foundational guidelines for a database management system (DBMS) to be considered relational. These rules, numbered from 0 to 12, were proposed by Edgar Frank Codd, the pioneer of the relational model for databases. The first rule, the foundation rule, mandates that a relational DBMS must manage databases entirely through relational capabilities, closely associated with the mathematical concept of relations.

05:00

🔑 Key Concepts of Codd's Rules

This section delves into the specifics of Codd's rules, starting with the Information Rule (Rule 1), which states that all information in a relational database must be represented explicitly at the logical level through table values. The Guaranteed Access Rule (Rule 2) ensures that every atomic value in the database is accessible via the table name, primary key, and column name. The paragraph also discusses the systematic treatment of null values (Rule 3), emphasizing their independence from data types and the need for the DBMS to support them.

10:02

🌐 Dynamic Online Catalog and Comprehensive Data Sub Language

Paragraph 3 continues the exploration of Codd's rules, focusing on the Dynamic Online Catalog (Rule 4), which relates to the logical representation of the database description and user authorization. It then discusses the Comprehensive Data Sub Language Rule (Rule 5), which requires that the DBMS support at least one language with standard syntax for data definition, view definition, data manipulation, integrity constraints, authorization, and transaction operations.

15:02

🔄 View Updating and Higher-Level Data Operations

The paragraph discusses the View Updating Rule (Rule 6), which asserts that updates to a view should also update the underlying table since they reference the same data. It also covers the possibility of higher-level insert, update, and delete operations (Rule 7), which should be supported by the DBMS without direct database access from the application program. This rule is also known as the relational operation rule.

🔒 Data Independence and Integrity

This section addresses the importance of Data Independence (Rules 8 and 9), where changes in data storage or access methods should not impact application programs or terminal activities. It also highlights Integrity Independence (Rule 10), which states that integrity constraints must be definable and stored within the relational data sub language of the DBMS, ensuring they are enforced at the database level, not in application programs.

🌐 Distribution Independence and Non-Subversion

The final paragraph covers Distribution Independence (Rule 11), which suggests that data should be distributed across different locations for redundancy and disaster recovery, without the end user's awareness. It concludes with the Non-Subversion Rule (Rule 12), which prohibits the use of low-level languages to bypass or subvert the integrity rules defined in the higher-level relational language.

🎉 Conclusion of Codd's 12 Rules Presentation

The presentation concludes by summarizing Codd's 12 rules and their significance in defining the requirements for a DBMS to be classified as a relational database management system. The speaker expresses gratitude for the audience's attention and wraps up the session with a musical note and applause.

Keywords

💡Relational Database Management System (RDBMS)

RDBMS is a database management system that is based on the relational model of data, as proposed by E.F. Codd. It organizes data into one or more tables with rows and columns, and it manages relationships between these tables. In the video, the concept of RDBMS is central as it discusses the 12 rules that a system must satisfy to be considered relational. The script emphasizes that all information in an RDBMS is represented explicitly at the logical level, typically through values in tables.

💡Edgar Frank Codd

Edgar Frank Codd, often referred to as the 'father of RDBMS,' was an English computer scientist who introduced the relational model for database management. His work laid the foundation for modern database systems. The video script mentions Codd as the proposer of the 12 rules that define the requirements for a DBMS to be considered relational.

💡Atomic Value

An atomic value is a data item that is indivisible in nature, meaning it cannot be further broken down into smaller parts. In the context of the video, the Guaranteed Access Rule (Rule 2) states that each atomic value in a relational database must be accessible through a combination of table name, primary key, and column name, highlighting the importance of atomicity in data retrieval.

💡Null Values

Null values represent missing or inapplicable information in a database. The video script explains that null values are distinct from empty strings or zero and must be treated systematically by the RDBMS. Rule 3, the Systematic Treatment of Null Values, emphasizes that these values should be independent of the data type and must be supported by the RDBMS.

💡Dynamic Online Catalog

The dynamic online catalog refers to the database description that is maintained at the logical level, which includes information about the structure of the database and the authorization for user access. The video script mentions this in the context of Rule 4, which ensures that the catalog is up-to-date and accessible, allowing for dynamic interaction with the database.

💡Data Sublanguage

A data sublanguage is a specialized computer language used to interact with a database, including operations for data definition, manipulation, and retrieval. The video discusses Rule 5, which mandates that an RDBMS must support at least one language with a standard syntax for various database operations, emphasizing the comprehensive nature of the sublanguage.

💡View Updating

View updating refers to the ability to modify the data in a view, which should also update the underlying table or relation in the database. Rule 6 in the video script highlights this concept, stating that any changes made to a view should reflect in the actual data stored in the RDBMS, as views are virtual tables that derive their data from one or more base tables.

💡Physical Data Independence

Physical data independence ensures that changes in the physical storage of data, such as changes in storage representations or access methods, do not affect the application programs or terminal activities. Rule 8 in the video script explains that the system should maintain logical unimpairment of application programs even when there are modifications at the physical level.

💡Logical Data Independence

Logical data independence means that changes to the logical structure of the database, such as alterations to the base tables, should not impact the application programs or terminal activities. Rule 9 in the video script discusses this concept, emphasizing that the system should maintain logical unimpairment even when logical changes are made to the database schema.

💡Integrity Independence

Integrity independence is the principle that integrity constraints should be defined and enforced within the RDBMS itself, rather than in the application programs. Rule 10 in the video script explains that all integrity constraints must be definable and storable in the RDBMS's catalog, ensuring that they are managed at the database level and not dependent on application logic.

💡Distribution Independence

Distribution independence, as discussed in Rule 11 of the video script, refers to the ability of an RDBMS to distribute data across multiple locations without the end user being aware of this distribution. This rule ensures that the system can provide redundancy and protection against failures while maintaining the illusion of a single data source for the user.

💡Non-Subversion Rule

The non-subversion rule, or Rule 12, states that if an RDBMS includes a low-level language for single-record operations, this language cannot be used to bypass or subvert the integrity rules and constraints defined in the higher-level relational language. The video script explains this as a safeguard to ensure that all operations, whether performed at a high or low level, adhere to the system's integrity constraints.

Highlights

Introduction to Codd's 12 Rules for defining the requirements of a Relational Database Management System (RDBMS).

Explanation of why the term 'Codd's 12 Rules' is used despite there being 13 rules, due to numbering from 0 to 12.

Rule 0: Foundation Rule - RDBMS must manage databases entirely through relational capabilities.

Rule 1: Information Rule - All information in a relational database is represented at the logical level by values in tables.

Rule 2: Guaranteed Access Rule - Every atomic value in a relational database is accessible via table name, primary key, and column name.

Rule 3: Systematic Treatment of Null Values - RDBMS must handle null values in a systematic way, independent of data type.

Rule 4: Dynamic Online Catalog - The database description is represented at the logical level, including user authorization.

Rule 5: Comprehensive Data Sublanguage Rule - At least one language supported by the RDBMS must be able to express data definition, view definition, and other operations.

Rule 6: View Updating Rule - Any update to a view should also update the underlying table or relation.

Rule 7: High-Level Insert, Update, and Delete - RDBMS should allow high-level operations for data manipulation.

Rule 8: Physical Data Independence - Application programs should remain logically unimpaired by changes in the storage or access methods.

Rule 9: Logical Data Independence - Changes to the base table should not affect the application programs or terminal activities.

Rule 10: Integrity Independence - Integrity constraints must be definable in the relational data sublanguage and stored in the catalog.

Rule 11: Distribution Independence - Data distribution across different locations should be transparent to the end user.

Rule 12: Non-Subversion Rule - A low-level language, if present, cannot be used to bypass the integrity rules expressed in the higher-level relational language.

Conclusion - Codd's 12 Rules define the essential requirements for a DBMS to be considered relational.

Transcripts

foreign

welcome back in this presentation we are

going to focus on chords 12 rules and

this is a set of 13 rules is it

confusing no worries once we step into

the topic of the day you will understand

things clearly let's step into cards 12

rules basically chords 12 rules are a

set of 13 rules for defining certain

requirements what are the requirements

if a database management system is

considered to be a relational database

management system simply rdbms then it

must satisfy all these 13 rules in

simple terms there are 13 rules for

defining the requirements for a dbms to

be considered to be relational and these

13 rules are proposed by the English

computer scientist Edgar Frank card and

this is considered to be the pioneer of

the relational model for databases

chords 12 rules are also referred as

chords 12 Commandments now we need to

understand one thing then why the name

course 12 rules though it has 13 rules

because the rules are numbered from 0 to

12. so a total of 13 rules are there and

the last rule is rule number 12 because

the first rule starts with rule number

zero in simple terms chords 12 rules are

a set of 13 rules numbered from 0 to 12.

let's see all the rules one by one now

basically it has 13 rules the rule

starts from rule number zero and it ends

with rule number 12. so there are a

total of 13 rules let's see rule number

zero rule number zero is the foundation

rule

what do we mean by this this is the

foundation for any system that is

advertised as or claimed to be a

relational database management system

that system must be able to manage the

databases entirely through its

relational capabilities I already told

you in the previous lecture that rdbms

is having a close association with the

mathematical concept called relations so

any system that is claimed or advised to

be a relational database management

system simply rdbms then that system

must be able to manage the databases

entirely through its relational

capabilities this is rule number zero

the foundation rule we are done with

rule number zero let's now move on to

rule number one which is the information

rule what do we mean by this it means

all information in a relational database

is represented explicitly at The Logical

level and in exactly one way and that

way is by values in the table in simple

term all the information that we are

going to process in rdbms it is

represented explicitly at The Logical

level by referring to the values in the

table and this is what rule number one

is the information rule

and coming to rule number two the

guaranteed access rule what do we mean

by this each and every datum datum means

it's singular the plural form of datum

is data so when we say data it is

already in the plural form let's come to

the definition of the guaranteed access

rule each and every datum which is

already an atomic value in a relational

database I hope you know what is an

atomic value it is indivisible in nature

if you are not sure about the atomic

values I request you to watch my

previous lecture where I have explained

about Atomic values let's continue

dealing with this definition each and

every datum which is already an atomic

value in a relational database

management system is guaranteed to be

logically accessible by resorting to a

combination of table name primary key

and column name in simple terms each and

every data that we are going to access

in rdbms it should be referred through

the table name primary key and the

column name anyway we will understand

things clearly when we see SQL where we

are going to give some SQL queries which

contains the table name primary key

attribute and also the column names

we are done with rule 2 let's now focus

on rule 3 the systematic treatment of

null values if you recall the previous

lecture where I have explained about

null values we must understand clearly

that null values are not empty character

string or a string of blank characters

even it is not 0 or any other number

these null values are either missing

information or inapplicable information

and that should be treated in a

systematic way by the relational

database management system this is what

exactly rule number three is and these

null values must be independent of the

data type for example number data type

the role number should accept null value

character data type name should also

accept null value date of birth data

type should also accept null value and

that's why I quoted these null values

must be independent of the data type and

all these null values must be supported

by the relational database management

system this is all of about rule number

three let's assume there are two

different tables when we join these two

tables and produce a new table when we

do that there are chances for some

inapplicable information in the result

table where that result table may not

have the exact value what is required

while joining the table these

inapplicable or missing information we

call as null values no worries we will

be elaborately understanding about null

values when we deal with SQL and coming

to rule number four which is dynamic

online catalog what do we mean by this

the database description is also

represented at The Logical level so this

Dynamic online catalog is related to

database description and also it deals

with the authorization which users can

access what kind of data and coming to

rule number five which is the

comprehensive data sub language rule a

relational system May support several

languages and various modes of terminal

use this terminal use means we are going

to access the database from a terminal

application or a terminal software and

this can be of any type it may be a GUI

application the graphical user interface

and it can also be like the fill in the

blanks mode anyway I am just

representing it as a terminal

application program or a terminal

software or The Terminal usage mode

however there must be at least one

language though we have several

languages there must be at least one

language whose statements are

expressible with some standard syntax

some character strings and also it

should support the following items the

data definition the ddl the view

definition I know it will be difficult

for you to understand what is a view

anyway in the next rule that is rule

number six I will explain about views

then data manipulation Integrity

constraints authorization and operations

related to transactions so all these

things should be expressible in the

statements and there may be multiple

languages and and there should be at

least one language that can support all

these we are done with Rule 5 let's see

the view updating rule which is Rule 6.

see for a table we can create multiple

views whenever we update any view that

should also update the table basically

all the data are stored in the relations

and we can create multiple views for the

relation both View and relation are

referring to the same data item in the

same memory location both are referring

to the same memory so whenever we make

any modifications in the view it means

the relation is also getting modified

because both are referring to the same

data item only thing is all the

information are stored in the relation

and we are creating multiple views as

per the need so rule number six states

that any rdbms that should support the

view updating rule simply whenever we

update the rule the table or the

relation should also be updated we are

done with rule number six let's now move

on to rule number seven the rule is it

is possible for higher level insert

update and delete so the name itself

says that there should be a possible

ability for the relational database

management system to perform higher

level of insertion updation and deletion

operations what do we mean by higher

level

whenever we want to insert some records

or update the records or delete the

records it doesn't mean that we should

always go to the database access the

table and perform the operation even we

should be able to do that from the

application program whenever we do these

operations from the application program

or from the client end or the front end

so that should also be supported it

doesn't mean that we should always

Supply queries to the database to

perform the operation in other words the

capability of handling a base relation

or a derived relation as a single

operand applies not only to the

retrieval of data but also to the

insertion updation and the deletion of

the data and hence rule number seven is

also called as relational operation rule

we should always have the possibility

for higher level insert update and

delete operations no worries when the

course progresses you will be able to

understand rule number seven even more

clearer we are done with rule number

seven let's now move on to rule number

eight which is physical data

Independence we have already seen about

this generally any application program

or any terminal activities it should

remain logically unimpaired Whenever

there are changes made in the storage or

the access method let's take the

three-tier architecture we have front

end at the top level a database at the

low level whenever any changes are made

in the low level I mean whenever we make

any changes in the storage

representations or access method it

should not affect the application

program level access that is what is

called as physical data Independence

simply changes made in the low level

data storage representation or the data

access methods should not impact the

application programs or The Terminal

activities and coming to rule number

nine which is logical data Independence

in rule number eight we dealt with the

physical aspect right whenever we make

changes in the storage it shouldn't

affect the application Level access and

coming to this one where the application

programs and terminal activities it

should be logically unimpaired when any

modifications are turned to the base

table so this is about rule number nine

coming to rule number 10 the Integrity

Independence we have already seen about

integrity constraints right all these

Integrity constraints that are specific

to a particular relational database

management system must be definable in

the relational data sub language and

also it should be stored in the catalog

what do we mean by this it means all the

Integrity constraints that we are going

to enforce on the database should be in

the database only to be precise it

should be in the relational database

only but not in the application programs

say for example the banking database and

let's assume there is a table called

account or a relation called account in

this account relation we have a column

called balance and this balance should

not be lesser than or equal to zero

let's assume this is the constraint we

are enforcing for this particular

relation this enforcement of Integrity

constraints should be in the dbms level

not from the application Level say we

may be writing an application program in

Python or Java or C plus plus the

constraint should not be applied there

rather it should be applied in the

relational database of language or

simply relational databases we are done

with rule number 10 the Integrity

Independence coming to rule number 11

the distribution Independence when we

say data are stored in a single place

maybe in a single server it is always

good to have a backup because all

systems are subject to failures it could

be a hardware failure or a software

failure whatsoever generally a failure

failure in a system should not be

permitted I personally believe in

technology two is one and one is none

when you have one it means it is none

because there is no redundancy so we

should always have enough backup for all

the activities let's assume you are

having a database it's always

recommended to have another copy of the

database somewhere in a distant location

because not only hardware and software

failures can cause damage to the

database there may be natural disasters

like earthquake or hurricane may affect

your data center and hence we are

required to distribute the data and this

distribution of data not only helps us

to achieve a duplicate copy of the

database and also it protects the data

from the natural disasters say if one

site is affected by the natural disaster

obviously the other site will contain

the duplicate copy of the database

because it is in a remote or a distant

location and this distribution of data

should not be known to the end user

let's assume there are two sites where

our databases are stored site a and site

B let's assume site a and site B are

geographically separated by thousands

and thousands of miles

it is always good to have like this

let's say user is now connected to site

a and he is retrieving or accessing all

the data from site a let's assume site a

is encountered with some Hardware or

software failure or even the natural

disaster so obviously ITA cannot give

data to the user the end user in this

scenario site B comes into action and it

starts scattering the need of the user

by providing all the data that is

required user must not be able to see

that the data is distributed across

various locations users should always

get the impression that the data is

stored only in one place or in one site

and he should not be aware that the data

is distributed this is rule number 11

the distribution Independence and coming

to rule number 12 the last rule the

non-subversion rule what we mean by this

if a relational system has a low level

language it means we can perform the

operations of single record at a time

that is what I represent it as low level

language if a relational system has a

low level language that low level cannot

be used to subvert or bypass the

Integrity rules or the constraints

expressed in the higher level relational

language the higher level language what

I mean is multiple records can be

accessed at the same time or multiple

records can be processed at the same

time so rule number 12 states that if

relational database system has a low

level language that low level language

cannot be used to either subvert or

bypass the Integrity rules and

constraints expressed in the higher

level relational language so this is

what rule number 12 is all about

we are done dealing with all the 13

rules these are the rules that are

designed to Define what is required from

a database management system in order

for it to be considered as relational

database management system at the BMS in

simple term course 12 rules are designed

to Define what is required from a dbms

in order for it to be considered rdbms

and that's it guys I hope you guys

enjoyed this presentation and thank you

for watching

[Music]

[Applause]

thank you