Codd's
Twelve rules for RDBMS
Codd's
twelve rules
are a set of thirteen rules proposed by Edgar F. Codd, a pioneer of the relational model for databases, designed to define what is required
from a database management system in order for it to
be considered relational, i.e., a relational database management system
(RDBMS). They are known as ‘Codd’s Twelves
Rules’ as follows-
Rule
(0):
The
system must qualify as relational, as a database, and as a management system. For a system to
qualify as a relational database management system (RDBMS), that system must use its relational facilities
(exclusively) to manage the database.
Rule
1:
The information rule:
All
information in a relational database (including table and column names) is
represented in only one way, namely as a value in a table.
Rule
2:
The guaranteed access rule:
All
data must be accessible. This rule is essentially a restatement of the
fundamental requirement for primary keys. It says that every
individual scalar value in the database must be logically addressable by
specifying the name of the containing table, the name of the
containing column and the primary key value of the containing row.
Rule
3:
Systematic treatment of null values:
The
DBMS must allow each field to remain null (or empty). Specifically, it must
support a representation of "missing information and inapplicable
information" that is systematic, distinct from all
regular values (for example, "distinct from zero or any other
number", in the case of numeric values), and independent of data type. It is also implied that such
representations must be manipulated by the DBMS in a systematic way.
The
system must support an online, inline, relational catalog that is accessible
to authorized users by means of their regular query language. That is, users
must be able to access the database's structure (catalog) using the same query
language that they use to access the database's data.
Rule
5:
The comprehensive data sub language rule:
The
system must support at least one relational language that Has a linear syntax Can be used both
interactively and within application programs, Supports data definition
operations (including view definitions), data manipulation operations (update
as well as retrieval), security and integrity constraints, and transaction management
operations (begin, commit, and rollback).
Rule
6:
The view updating rule:
All
views that are theoretically updatable must be updatable by the system.
Rule
7:
High-level insert, update, and delete:
The
system must support set-at-a-time insert, update, and delete operators. This
means that data can be retrieved from a relational database in sets constructed
of data from multiple rows and/or multiple tables. This rule states that
insert, update, and delete operations should be supported for any retrievable
set rather than just for a single row in a single table.
Rule
8:
Physical data independence:
Changes
to the physical level (how the data is stored, whether in arrays or linked
lists etc.) must not require a change to an application based on the structure.
Rule
9:
Logical data independence:
Changes
to the logical level (tables, columns, rows, and so on) must not require a
change to an application based on the structure. Logical data independence is
more difficult to achieve than physical data independence.
Rule
10:
Integrity independence:
Integrity
constraints
must be specified separately from application programs and stored in the catalog. It must be
possible to change such constraints as and when appropriate without
unnecessarily affecting existing applications.
Rule
11:
Distribution independence:
The
distribution of portions of the database to various locations should be
invisible to users of the database. Existing applications should continue to
operate successfully :
when
a distributed version of the DBMS is first introduced; and
when
existing distributed data are redistributed around the system.
Rule
12:
The nonsubversion rule:
If
the system provides a low-level (record-at-a-time) interface, then that
interface cannot be used to subvert the system, for example, bypassing a
relational security or integrity constraint.
Difference between
SQL and SWL*Plus
SQL:
# A language
# ANSI standard
# Keywords cannot be abbreviated
# Statements manipulate data and table definitions
in the database
SQL*Plus:
# An environment
# Oracle proprietary
# Keywords can be abbreviated
# Commands do not allow manipulation of values in
the database
Basic SQL
Fundamental
The
SQL was initially called SEQUEL (Structured English Query Language).
The acronym SEQUEL was later changed to SQL because "SEQUEL"
Relational Database
– The
Relational Database uses relations or two dimensional tables to store
information.
ER Modeling – An entity
relationship (ER) model is an illustration of various entites in a business and
the relationships among them.
Entity – A thing of
significance about which information needs to be known. Examples are students,
departments, employees.
Attribute/Column – Something that
describes or qualifies an entity. For example employee entity has emp no, name,
job title, hire date, etc. attributes.
Relationship – A named
association between entities showing optionality and degree.
Field – A field is the
smallest unit in a table.
Record/Row/Tuple – A record is
nothing more than a collection of fields or columns horizontally.
Database Objects
Schema – Collection of
objects is called Schema. For example table, view, index, sequence.
Table – Logical
structures of rows and columns. Or/ Basic storage structures of an RDBMS.
Or/
A table holds a collection of records. A table can have upto 1000 columns and
‘n’ number of rows.
Views – Virtual table which
does not contain any data that refers to the base-table.
Index – Pointer for faster
retrieval of data.
Sequence – It is a unique
sequential number generated by database objects.
Synonyms – Alternative name of
the database.
Constraints
Rules
which are enforce on the data when we are inserting data in database.
Not Null(C)- It should not be blank in the columns.
Unique(U)- It should not be
repeated value.
Primary Key(P)- It is a
combination of not null & unique. And should not be blank & repeated
values.
Foreign Key(R)- It is use to
define to relates table, we should enter a valid data except null.
Check(C) – It is condition
if condition is satisfied we can insert data into database.
SQL Statements
Data Manipulation
Language(DML) – It
retrieves data from the database, enters new rows, changes existing rows, and
removes unwanted rows from tables in the database. It consists of SELECT,
INSERT, UPDATE, DELETE, MERGE.
Data Definition
Language(DDL) – It
sets up, changes and removes data structures from tables. It consists of
CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT.
Data Control
Language(DCL) – It
gives of removes access rights to both the Oracle Database and the structures
within it. It consists of GRANT and REVOKE.
Transaction Control
Language(TCL) – It
manages the changes made by the DML statements. Changes to the data can be
grouped together into logical transactions. It consists COMMIT, ROLLBACK,
SAVEPOINT.
Data Manipulation
Language(DML)
Data
Manipulation Language(DML) is a core part of SQL. When you want to add, update,
or delete data in the database, you execute a DML statement. A collection of
DML statements that form a logical unit of work is called a transaction.
Capabilities of SQL
Select Statement
The
select statement retrieves information from the database.
Capabilities of SQL
Select Statement
#
Selection- Choose the rows in a
table that are returned by a query. Various criteria can be used to restrict
the rows that are retrieved.
#
Projection- Choose the columns in a
table that are returned by a query. Choose as few or as many of the columns as
needed.
#
Join- Bring together data that is
stored in different tables by specifying the link between them. SQL joins are
used to display data from multiple tables.
Select Statement
Format
##
Select * |{[DISTICT] column | expression [alias],….}
FROM
table;
Where
DISTICT- All retrieved records are of different values.
Null Values
If
a row lacks a data value for a particular column, that value is said to be null
or to contain a null. A null is a value that is unavailable, unassigned, unknown,
or inapplicable. A null is not the same as a zero or a space. Aero is a number
and a space is a character.
Column Alias
An
alias renames a column heading. It immediately follow the column names follow
by AS.
Insert Statements
It
is used to add new rows to a table by using the INSERT statement.
Syntax:
INSERT INTO TABLE ([column, ……,.., n] VALUES [value,….., n]);
Update Statements
It
is used to modify the existing rows in a table.
Syntax:
UPDATE
TABLE
SET
COLUMN = VALUE
WHERE
CONDITION;
Delete Statements
It
is used to remove existing rows from a table.
Syntax:
DELETE TABLE;
Row deletion from a
table
#
Specific rows are deleted if you specify the WHERE clause as
Syntax:
DELETE
FROM TABLE
WHERE
CONDITION;
It
deletes all the rows satisfying the CONDITION.
#
All rows in the table are deleted if you omit the WHERE clause as
Syntax:
DELETE
FROM TABLE;
It
deletes all the rows from the table.
Truncate Statement
It
is a data definition language(DDL) statement. It removes all rows from a table,
leaving the table empty and the table structure intact.
Syntax:
TRUNCATE TABLE
Data Definition
Language(DDL)
Create Statement
It
is used to create tables to store data by executing the SQL create table
statement. To create a table, a user must have the CREATE TABLE privilege And a
storage area in which to create objects. The database administrator uses data
control language statements to grant privileges to users.
Syntax:
CREATE TABLE TABLE_NAME( COLUMN_NAME
DATATYPE,…………., COULUMN_NAME_N DATATYPE_N);
Alter Statement
It
is used to add a new column, to modify an existing column, to define a default
value for the new column, or to drop a column.
Syntax:
#
To add column-
ALTER TABLE table
ADD (column
datatype [DEFAULT expr]
[, column datatype]...);
#
To modify a table
ALTER TABLE table
MODIFY (column
datatype [DEFAULT expr]
[, column
datatype]...);
#
To drop a column from a table
ALTER TABLE table
DROP (column);
Drop
Statement
It
is used to drop all data and structure of a table. When you issue a drop
command any pending transaction will be committed. It also dropped all the
constraints of the table and it cann’t be rollback again.
Syntax:
DROP TABLE table_name;
Rename
Statement
It is used to change the table column name or
constraint name.
Syntax:
ALTER TABLE table RENAME old_column_name to
new_column_name;
ALTER TABLE table RENAME old_constraint_name to
new_constraint_name;
Transaction
Control Language(TCL)
Database Transactions begin when the first DML SQL
statement is executed and end with one of the following statements-
# A COMMIT or ROLLBACK statement is issued
# A DDL or DCL statement executes (automatic commit)
# The user exits iSQL*Plus
# The system crashes.
Savepoint
Statement
It is used to create a marker in a current
transaction by using the SAVEPOINT statement.
Syntax:
Delete from table…;
SAVEPOINT ONE;
Rollback
Statement
It is used to discard all pending changes. It is
also used to undone data changes which restores the data the data to the previous
state. It also release the locks on the affected rows.
Syntax:
DELETE FROM table;
ROLLBACK;
This undone the delete table.
ROLLBACK TO SAVEPOINT; (If savepoint has been
created.)
Commit
Statement
An automatic commit occurs under the following circumstances
# DDL statement is issued
# DCL statement is issued
# Normal exit from iSQL*Plus/SQL*Plus,
without explicitly issuing COMMIT or ROLLBACK statements
# An automatic rollback occurs under an abnormal
termination of iSQL*Plus or a system failure.
The commit statement is used to make the data change
permanent in the database. The previous state of the database is permanently
lost. After commit locks on the affected rows are released and all the
savepoints are erased.
Data
Control Language(DCL)
The data control language(DCL) is use to give and
remove privileges from the user mainly by the SYS or SYSTEM user. Other users
who are having the privileges can also grant and remove the privileges under
certain circumstances.
GRANT
Statement
It is use to give permission to the users so that
the users can access the database or schema.
Syntax:
GRANT privilege_names to user_name;
REVOKE
Statement
It is use to remove the privilege/s granted to the
users.
Syntax:
REVOKE privilege_name from user_name
For example:
SQL> create user megha identified by megha;
User created.
SQL> grant connect, resource to megha;
Grant succeeded.
SQL> revoke connect, resource from megha;
Revoke succeeded.
Controlling
User Access
In this section mainly deals with the security of
the database. And security of the database mainly consists of two parts-
# System Security and
# Data Security
System
Security
It is related to the safety of the system as a whole
such the system where the database is stored should not be accessible by the
unauthorized users. All the system related issues are included in the security
of the system whether it may be manual or not.
Data
Security
It includes all the issues related to the data in
the database. For safety of the data should be secured from the unauthorized
users, should be backed up on regular basis so that data can be restored to the
previous state, it may be due to the unpredictable system crash or user error.
Data
Dictionary Vies of Privileges
Data Dictionary
Views
|
Descriptions
|
ROLES_SUS_PRIVS
|
System
privileges granted to roles.
|
ROLES_TABLES_PRIVS
|
Table
privileges granted to roles.
|
USER_ROLES_PRIVS
|
Roles
accessible by the users.
|
USER_TAB_PRIVS_MADE
|
Object
Privileges granted to the user’s object.
|
USER_TAB_PRIVS_RECD
|
Object
privileges granted to users.
|
USER_COL_PRIVS_MADE
|
Object
privileges granted to the columns of the user’s objects.
|
USER_COL_PRIVS_RECD
|
Object
privileges granted to the users on specific column.
|
USER_SYS_PRIVS
|
System
privileges granted to the users.
|
Privilege and its
Types
It
is the right to access the database or execute a particular SQL statement. The
database administrator(DBA) has the high level privileges in the system and
database. There are two types of privileges
#
System Privilege and
#
Object Privilege
System Privilege
The
system privilege/s allow the users to access the database.
Some
system privileges are
#
CREATE SESSION
#
CREATE PROCEDURE
#
CREATE VIEW
#
CREATE SEQUENCE
Object Privilege
It
allows the users to perform a particular task on the objects in the database
such as tables, indices, sequences, views, etc. It differs from object to
object.
Roles
It
is a group of privileges. It is used to ease to management and granting of
privileges to users.
Syntax
for creating Role:
CREATE
role role_name;
GRANT
connect, resource to role_name;
Some DBA Privileges
#
CREATE A USER
#
REMOVE A USER
#
BACKUP DATABASE
#
STARTUP DATABASE
#
SHUTDOWN DATABASE
Reference:
Oracle’s Documentation
www.wikipedia.com
No comments:
Post a Comment