Transcript Codd’s Twelve Rules - Castle of SARAVANESH J

Codd’s Twelve Rules
Rules that a DBMS should
follow to be classified as
fully relational
Oracle’s grading scale
0 = doesn’t meet
criteria
1/2 = meets some of
the criteria
1 = meets the criteria
1. Information Rule
information is to be represented as data
stored in cells
Vehicle Identification Number in one cell
would violate this rule
paint color
body style
2. Guaranteed access rule
Each data item must be accessible by
combination of
table name + primary key of row +column
name
violation of rule--ability to access by using
arrays or pointers
How does Oracle fair on
the 1st two points??
BLOBs-Binary Large OBjects
early RDBMS technique to store and manage
multimedia data types
typically stored outside the database (in files), and
pointers are placed in their corresponding column
positions
BLOBs push the 1st two rules
Implementation is transparent to the user
Do not explicitly break 1st two rules
Score 1 1/2
1/2 pt deducted for “bad conduct”
competitors grade out the same
3. Nulls must be used in a
consistent manner
Violation--0 used as value for missing
numbers and blank for missing character
values
correct way--nulls simply be missing data
and have no values
If values are desired for missing data,
vendors usually offer the ability to use
defaults for this purpose.
Grade for rule 3
1/2
current implementation for nulls in SQL is
poor for all vendors
has been attacked by Codd and another
relational pioneer, C. J. Date
Codd proposes 3 valued logic
Date proposes abolishing of nulls altogether
some very real data problems with the use of
NULLs today in RDBMSs.
4. An active, online data
dictionary should be
stored as relational tables
and accessible through the
regular data access
language.
If any part of the data dictionary is stored
in the operating system, this rule would
be violated.
Grade for rule 4
1/2
Oracle allows SELECT only, but INSERT,
UPDATE, and DELETE are not possible
This probably should not occur frequently
and may not even be desired, but it should
be possible according to rule 4.
5. The data access
language must provide all
means of access and be
the only means of access,
except possibly for lowlevel access routines.
If you could access the file supporting
table, through a utility other than an SQL
interface, this might violate this rule.
6. All views that may be
updatable should be
updatable.
If, for example, you could join three
tables as the basis for a view, but not be
able to update that view, then this rule
would be violated.
Rule 6 Grade
1/2
Improved for version 8 over 7
Version 8 is not 100% compliant
7. There must be set-level
inserts, updates, and
deletes.
Currently, this is provided by most RDBMS
vendors to some degree.
8. Physical data
independence.
An application cannot depend on physical
restructuring. If a file supporting table
was moved from one disk to another, or
renamed, then this should have no impact
on the application.
9. Logical data
independence.
How a User views data should not change
when the logical structure (tables
structure) of the database changes.
This rule is particularly difficult to satisfy.
Most databases rely on strong ties
between the user view of the data and
the actual structure of the underlying
tables.
10. Integrity independence.
Integrity rules should be stored in the
data dictionary. Primary key constraints,
foreign key constraints, check constraints,
triggers, and so forth should all be stored
in the data dictionary.
11. Distribution dependence.
A database should continue to work
properly even if distributed (extension of
rule 8 -- except rather than only being
distributed on a single system (locally), a
database may also be distributed across a
network of systems (remotely).
Rule 11 Grade
 1/2
 Oracle does offer
database links, snapshots, symmetric replication, and distributed
database with a two-phase commit (2PC).
 Some of the networking is user-apparent, 2PC works but
not well, and replication has its own problems
 Majority of complaints center on the fact that all these
capabilities require the user to know more than one
should have to know about the setup behind the
technology
12. The nonsubversion rule.
 If low-level access is allowed, it must not bypass
security nor integrity rules, which would otherwise be
obeyed by the regular data access language. A backup
or load utility, for example, should not be able to bypass
authentication, constraints, and locks. However,
vendors often provide these abilities for the sake of
speed. It is then the DBA’s responsibility to ensure that
security and integrity, if momentarily compromised, are
reinstated. An example is disabling and re-enabling
constraints for a VLDB load.
Through some of its utilities, such as
SQL*Loader and Import, Oracle allows
data to be stored in the database by
bypassing the standard SQL route,
opening up the database to potential
corruption
Final Grade
8 1/2 out of 12
give or take a point
Point is not the precision of final score
FACT--Oracle does not meet all of Codd’s
12 rules
Neither does Sybase nor Informix
It is a RDBMS
Oracle and its competitors have more work
to do
Oracle 7 only fully passed rule 10
Codd’s Rule Zero
For a system to qualify as a RDBMS, that
system must use its relational facilities
exclusively to manage the database.
Meet all fundamentals discussed in
relational database chapter and these 12
rules --- database may be designated as
relational