Relational Database
Unit 2
Syllabus
Unit 1 : Introduction - History: Advantages and limitations of
DBMS; Users of DBMS, Software Modules in DBMS;
Architecture of DBMS. Different Types of Models,
Relationships, ERD with cases.
Unit 2 : Relational Database - Codd's 12 Rules, Concept of
Domain, Tuple, cardinality, attributes, keys
Unit 3: Normalization - Advantages and disadvantages of
Normalization; 1NF-2NF-3NF rules with examples; Case
Study; BCNF-4NF-5NF rules, Concept of Denormalization
Unit 4 : SQL DDL - Create/drop a Database,
Create/Modify/Alter/Drop Table, DML – Insert, Update,
Delete, Select
Aggregate Function – Max, Min, Avg, Count, Sum
Clauses - GROUP BY, ORDER BY, HAVINGJoins –
Types of Joins, Queries on Joins using SQL
Unit 5 : Views and Sequences
Unit 6: Integrity Constraints -
Entity-Domain-Referential integrity rules; Assertion and
Triggers concept, Constraints examples using SQL
Unit 7: Back up and Recovery Mechanisms
Backups, importance of backup, Recovery from
various problems of volatile and non-volatile
storage devices; Concept-properties-states of
Transaction; Introduction to mechanisms such as
- Log, Checkpoint and Shadow Paging.
Unit 8: Distributed Databases, Data Distributions
Techniques, Data Warehousing and Data Mining
Codd’s Rules
Information Rule: All information in a relational database including
table names, column names are represented by values in tables. This
simple view of data speeds design and learning. User productivity is
improved since knowledge of only one language is necessary to access
all data such as description of the table and attribute definitions,
integrity constraints. Action can be taken when the constraints are
violated. Access to data can be restricted. All these information are also
stored in tables.
Guaranteed Access Rule: Every piece of data in a relational database,
can be accessed by using combination of a table name, a primary key
value that identifies the row and column name which identified a cell.
User productivity is improved since there is no need to resort to using
physical pointers addresses. Provides data independence. Possible to
retrieve each individual piece of data stored in a relational database by
specifying the name of the table in which it is stored, the column and
primary key which identified the cell in which it is stored.
Systematic Treatment of Nulls Rule: The RDBMS handles records
that have unknown or inapplicable values in a pre-defined fashion.
Also, the RDBMS distinguishes between zeros, blanks and nulls in
the records hand handles such values in a consistent manner that
produces correct answers, comparisons and calculations. Through
the set of rules for handling nulls, users can distinguish results of the
queries that involve nulls, zeros and blanks. Even though the rule
doesn’t specify what should be done in the case of nulls it specifies
that there should be a consistent policy in the treatment of nulls.
Active On-line catalog based on the relational model: The
description of a database and in its contents are database tables and
therefore can be queried on-line via the data manipulation language.
The database administrator’s productivity is improved since the
changes and additions to the catalog can be done with the same
commands that are used to access any other table. All queries and
reports can also be done as any other table.
Comprehensive Data Sub-language Rule: A RDBMS may support
several languages. But at least one of them should allow user to do
all of the following: define tables and views, query and update the
data, set integrity constraints, set authorizations and define
transactions. User productivity is improved since there is just one
approach that can be used for all database operations. In a multi-user
environment the user does not have to worry about the data integrity
an such things, which will be taken care by the system. Also, only
users with proper authorization will be able to access data.
View Updating Rule: Any view that is theoretically updateable can
be updated using the RDBMS. Data consistency is ensured since the
changes made in the view are transmitted to the base table and vice-
versa.
High-Level Insert, Update and Delete: The RDBMS supports
insertions, updation and deletion at a table level. The performance is
improved since the commands act on a set of records rather than one
record at a time.
Physical Data Independence: The execution of ad-hoc requests
and application programs is not affected by changes in the physical
data access and storage methods. Database administrators can
make changes to the physical access and storage method which
improve performance and do not require changes in the application
programs or requests. Here the user specified what he wants an
need not worry about how the data is obtained.
Logical Data Independence: Logical changes in tables and views
such adding/deleting columns or changing fields lengths need not
necessitate modifications in the programs or in the format of adhoc
requests. The database can change and grow to reflect changes in
reality without requiring the user intervention or changes in the
applications. For example, adding attribute or column to the base
table should not disrupt the programs or the interactive command that
have no use for the new attribute.
Integrity Independence: Like table/view definition, integrity
constraints are stored in the on-line catalog and can therefore be
changed without necessitating changes in the application programs.
Integrity constraints specific to a particular RDB must be definable in
the relational data sub-language and storable in the catalog. At least
the Entity integrity and referential integrity must be supported.
Distribution Independence: Application programs and adhoc
requests are not affected by change in the distribution of physical
data. Improved systems reliability since application programs will
work even if the programs and data are moved in different sites.
No subversion Rule: If the RDBMS has a language that accesses
the information of a record at a time, this language should not be
used to bypass the integrity constraints. This is necessary for data
integrity.