UNIT 2: MODULE 1

Relation: A relation in a relational database is described as a table.

Table is organized into rows and columns.
Attribute: A column header of a table is known as attribute of a
relation.
Tuple: A row in a table represents the record of a relation and known as
a tuple of a relation.

Domain: A set of possible values for a given attribute is known as

domain of a relation. A domain is atomic, that means values are
indivisible. For example, set of values for attribute FirstName of an
EMPLOYEE relation are atomic.

Codd's Rules
Dr Edgar F. Codd, after his extensive research on the Relational Model of database
systems, came up with twelve rules of his own, which according to him, a database
must obey in order to be regarded as a true relational database.
These rules can be applied on any database system that manages stored data using
only its relational capabilities. This is a foundation rule, which acts as a base for all the
other rules.
Rule 1: Information Rule
The data stored in a database, may it be user data or metadata, must be a value of
some table cell. Everything in a database must be stored in a table format.

Rule 2: Guaranteed Access Rule

Every single data element (value) is guaranteed to be accessible logically with a
combination of table-name, primary-key (row value), and attribute-name (column
value). No other means, such as pointers, can be used to access data.

Rule 3: Systematic Treatment of NULL Values

The NULL values in a database must be given a systematic and uniform treatment.
This is a very important rule because a NULL can be interpreted as one the following −
data is missing, data is not known, or data is not applicable.

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online catalog,
known as data dictionary, which can be accessed by authorized users. Users can use
the same query language to access the catalog which they use to access the database
itself.

Rule 5: Comprehensive Data Sub-Language Rule

A database can only be accessed using a language having linear syntax that supports
data definition, data manipulation, and transaction management operations. This
language can be used directly or by means of some application. If the database allows
access to data without any help of this language, then it is considered as a violation.

Rule 6: View Updating Rule

All the views of a database, which can theoretically be updated, must also be
updatable by the system.

Rule 7: High-Level Insert, Update, and Delete Rule

A database must support high-level insertion, updation, and deletion. This must not be
limited to a single row, that is, it must also support union, intersection and minus
operations to yield sets of data records.
Rule 8: Physical Data Independence
The data stored in a database must be independent of the applications that access the
database. Any change in the physical structure of a database must not have any
impact on how the data is being accessed by external applications.

Rule 9: Logical Data Independence

The logical data in a database must be independent of its user’s view (application). Any
change in logical data must not affect the applications using it. For example, if two
tables are merged or one is split into two different tables, there should be no impact or
change on the user application. This is one of the most difficult rule to apply.

Rule 10: Integrity Independence

A database must be independent of the application that uses it. All its integrity
constraints can be independently modified without the need of any change in the
application. This rule makes a database independent of the front-end application and
its interface.

Rule 11: Distribution Independence

The end-user must not be able to see that the data is distributed over various locations.
Users should always get the impression that the data is located at one site only. This
rule has been regarded as the foundation of distributed database systems.

Rule 12: Non-Subversion Rule

If a system has an interface that provides access to low-level records, then the
interface must not be able to subvert the system and bypass security and integrity
constraints.
 Relation Data Model

Relational data model is the primary data model, which is used widely
around the world for data storage and processing. This model is simple
and it has all the properties and capabilities required to process data with
storage efficiency.
Tables − In relational data model, relations are saved in the format of
Tables. This format stores the relation among entities. A table has rows
and columns, where rows represents records and columns represent the
attributes.
Tuple − A single row of a table, which contains a single record for that
relation is called a tuple.
Relation instance − A finite set of tuples in the relational database
system represents relation instance. Relation instances do not have
duplicate tuples.
Relation schema − A relation schema describes the relation name
(table name), attributes, and their names.
Relation key − Each row has one or more attributes, known as relation
key, which can identify the row in the relation (table) uniquely.
Attribute domain − Every attribute has some pre-defined value scope,
known as attribute domain.
Constraints
Every relation has some conditions that must hold for it to be a valid
relation. These conditions are called Relational Integrity Constraints.
There are three main integrity constraints −

 Key constraints
 Domain constraints
 Referential integrity constraints

Key Constraints
There must be at least one minimal subset of attributes in the relation,
which can identify a tuple uniquely. This minimal subset of attributes is
called key for that relation. If there are more than one such minimal
subsets, these are called candidate keys.
Key constraints force that −
 in a relation with a key attribute, no two tuples can have identical
values for key attributes.
 a key attribute can not have NULL values.
Key constraints are also referred to as Entity Constraints.

Domain Constraints
Attributes have specific values in real-world scenario. For example, age
can only be a positive integer. The same constraints have been tried to
employ on the attributes of a relation. Every attribute is bound to have a
specific range of values. For example, age cannot be less than zero and
telephone numbers cannot contain a digit outside 0-9.
 Referential integrity Constraints
Referential integrity constraints work on the concept of Foreign Keys.
A foreign key is a key attribute of a relation that can be referred in other
relation.
Referential integrity constraint states that if a relation refers to a key
attribute of a different or same relation, then that key element must
exist.
ID NAM AG
E E

STUD

ID PRN MARKS

EXAM

Enterprise Constraints
Enterprise constraints – sometimes referred to as semantic
constraints – are additional rules specified by users or database
administrators and can be based on multiple tables.
Here are some examples.

 A class can have a maximum of 30 students.

 A teacher can teach a maximum of four classes per semester.
 An employee cannot take part in more than five projects.
 The salary of an employee cannot exceed the salary of the employee’s
manager.

Views
A view is nothing more than a SQL statement that is stored in the
database with an associated name. A view is actually a composition of a
table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table. A
view can be created from one or many tables which depend on the
written SQL query to create a view.
Views, which are a type of virtual tables allow users to do the following
−
 Structure data in a way that users or classes of users find natural or
intuitive.
 Restrict access to the data in such a way that a user can see and
(sometimes) modify exactly what they need and no more.
 Summarize data from various tables which can be used to generate
reports.

Creating Views
Database views are created using the CREATE VIEW statement.
Views can be created from a single table, multiple tables or another
view.
To create a view, a user must have the appropriate system privilege
according to the specific implementation.
The basic CREATE VIEW syntax is as follows –
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example to create a view from the CUSTOMERS table.

This view would be used to have customer name and age from the
CUSTOMERS table.
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;
Now, you can query CUSTOMERS_VIEW in a similar way as you
query an actual table. Following is an example for the same.

SQL > SELECT * FROM CUSTOMERS_VIEW;

This would produce the following result.

+----------+-----+
| name | age |
+----------+-----+
| Ramesh | 32 |
| Khilan | 25 |
| kaushik | 23 |
| Chaitali | 25 |
| Hardik | 27 |
| Komal | 22 |
| Muffy | 24 |
+----------+-----+

The WITH CHECK OPTION

The WITH CHECK OPTION is a CREATE VIEW statement option.
The purpose of the WITH CHECK OPTION is to ensure that all
UPDATE and INSERTs satisfy the condition(s) in the view definition.
If they do not satisfy the condition(s), the UPDATE or INSERT returns
an error.
The following code block has an example of creating same view
CUSTOMERS_VIEW with the WITH CHECK OPTION.
CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any
NULL values in the view's AGE column, because the view is defined
by data that does not have a NULL value in the AGE column.

Updating a View
A view can be updated under certain conditions which are given below
−
 The SELECT clause may not contain the keyword DISTINCT.
 The SELECT clause may not contain summary functions.
 The SELECT clause may not contain set functions.
 The SELECT clause may not contain set operators.
  The SELECT clause may not contain an ORDER BY clause.
 The FROM clause may not contain multiple tables.
 The WHERE clause may not contain subqueries.
 The query may not contain GROUP BY or HAVING.
 Calculated columns may not be updated.
 All NOT NULL columns from the base table must be included in
the view in order for the INSERT query to function.

So, if a view satisfies all the above-mentioned rules then you can update
that view. The following code block has an example to update the age
of Ramesh.
SQL > UPDATE CUSTOMERS_VIEW
SET AGE = 35
WHERE name = 'Ramesh';

This would ultimately update the base table CUSTOMERS and the
same would reflect in the view itself. Now, try to query the base table
and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Inserting Rows into a View

Rows of data can be inserted into a view. The same rules that apply to
the UPDATE command also apply to the INSERT command.
Here, we cannot insert rows in the CUSTOMERS_VIEW because we
have not included all the NOT NULL columns in this view, otherwise
you can insert rows in a view in a similar way as you insert them in a
table.
Deleting Rows into a View
Rows of data can be deleted from a view. The same rules that apply to
the UPDATE and INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE = 22.
SQL > DELETE FROM CUSTOMERS_VIEW
WHERE age = 22;

This would ultimately delete a row from the base table CUSTOMERS
and the same would reflect in the view itself. Now, try to query the base
table and the SELECT statement would produce the following result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Dropping Views
Obviously, where you have a view, you need a way to drop the view if
it is no longer needed. The syntax is very simple and is given below −
DROP VIEW view_name;
Following is an example to drop the CUSTOMERS_VIEW from the
CUSTOMERS table.
DROP VIEW CUSTOMERS_VIEW;