RELATIONAL DATA UNIT 2

MODEL
SYLLABUS
RELATIONAL MODEL
Relational Model (RM) represents the database as a collection of relations. A
relation is nothing but a table of values. Every row in the table represents a
collection of related data values. These rows in the table denote a real-world
entity or relationship.
The table name and column names are helpful to interpret the meaning of values
in each row. The data are represented as a set of relations. In the relational model,
data are stored as tables. However, the physical storage of the data is independent
of the way the data are logically organized.
Some popular Relational Database management systems are:
•DB2 and Informix Dynamic Server – IBM
•Oracle and RDB – Oracle
•SQL Server and Access – Microsoft
RELATIONAL MODEL CONCEPTS IN DBMS
1.Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno,
NAME,etc.
2.Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A
table has two properties rows and columns. Rows represent records and columns represent attributes.
3.Tuple – It is nothing but a single row of a table, which contains a single record.
4.Relation Schema: A relation schema represents the name of the relation with its attributes.
5.Degree: The total number of attributes which in the relation is called the degree of the relation.
6.Cardinality: Total number of rows present in the Table.
7.Column: The column represents the set of values for a specific attribute.
8.Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have
duplicate tuples.
9.Relation key – Every row has one, two or multiple attributes, which is called relation key.
10.Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain
RELATIONAL INTEGRITY CONSTRAINTS

Relational Integrity constraints in DBMS are referred to conditions which  the

protocols that a table's data columns must follow. These Relational constraints in
DBMS are derived from the rules in the mini-world that the database represents.
There are many types of Integrity Constraints in DBMS. Constraints on the
Relational database management system is mostly divided into three main categories
are:
1.Domain Constraints
2.Entity Integrity Constraints
3.Referential Integrity Constraints
4.Key Constraints
1 . D o m a i n c o n s t r a i n t s
D o m a i n c o n s t r a i n t s c a n b e d e f i n e d a s t h e d e f i n i t i o n o f a v a l i d s e t o f v a l u e s f o r a n a t t r i b u t e .
T h e d a t a t y p e o f d o m a i n i n c l u d e s s t r i n g , c h a r a c t e r , i n t e g e r , t i m e , d a t e , c u r r e n c y , e t c . T h e v a l u e o f t h e a t t r i b u t e m u s t b e a v a i l a b l e i n t h e c o r r e s p o n d i n g d o m a i n .
E x a m p l e :
2. Entity integrity constraints
•The entity integrity constraint states that primary key value can't be null.
•This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those rows.
•A table can contain a null value other than the primary key field.
Example:

           
3. Referential Integrity Constraints
•A referential integrity constraint is specified between two tables.
•In the Referential integrity constraints, if a foreign key in Table 1 refers to the
Primary Key of Table 2, then every value of the Foreign Key in Table 1 must
be null or be available in Table 2.
Example:
4. Key constraints
•Keys are the entity set that is used to identify an entity within its entity set
uniquely.
•An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the
relational table.
Example:
39.8M
654
Hello Java Program for Beginners
The following are commonly used constraints available in PostgreSQL.
•NOT NULL Constraint − Ensures that a column cannot have NULL value.
•UNIQUE Constraint − Ensures that all values in a column are different.
•PRIMARY Key − Uniquely identifies each row/record in a database table.
•FOREIGN Key − Constrains data based on columns in other tables.
•CHECK Constraint − The CHECK constraint ensures that all values in a
column satisfy certain conditions.
1 . N O T N U L L C o n s t r a i n t

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then
you need to define such constraint on this column specifying that NULL is now not allowed for that
column. A NOT NULL constraint is always written as a column constraint.
A NULL is not the same as no data; rather, it represents unknown data.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds
five columns, three of which, ID and NAME and AGE, specify not to accept NULL values −

CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
2. UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular
column. In the COMPANY table, for example, you might want to prevent two or more people
from having identical age.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY3
and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two
records with same age −

CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS
CHAR(50), SALARY REAL DEFAULT 50000.00 );
3.PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be
more UNIQUE columns, but only one primary key in a table. Primary keys are important when
designing the database tables. Primary keys are unique ids.
We use them to refer to table rows. Primary keys become foreign keys in other tables, when
creating relations among tablesA primary key is a field in a table, which uniquely identifies each
row/record in a database table. Primary keys must contain unique values. A primary key column
cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When
multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having the
same value of that field(s).
Example

CREATE TABLE COMPANY4( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
4.FOREIGN KEY Constraint
A foreign key constraint specifies that the values in a column (or a group of columns) must
match the values appearing in some row of another table. We say this maintains the referential
integrity between two related tables. They are called foreign keys because the constraints are
foreign; that is, outside the table. Foreign keys are sometimes called a referencing key.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and
adds five columns.

CREATE TABLE COMPANY6( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
For example, the following PostgreSQL statement creates a new table called DEPARTMENT1,
which adds three columns. The column EMP_ID is the foreign key and references the ID field of
the table COMPANY6.

CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50)
NOT NULL, EMP_ID INT references COMPANY6(ID) );
5.CHECK Constraint

The CHECK Constraint enables a condition to check the value being entered into a record. If
the condition evaluates to false, the record violates the constraint and is not entered into the
table.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and
adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any
SALARY as Zero.

CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY >
0) );
Aggregate functions

Aggregate functions in DBMS take multiple rows from the table and return a value according to the
query.
All the aggregate functions are used in Select statement.
Syntax − 
SELECT <FUNCTION NAME> (<PARAMETER>) FROM <TABLE NAME>

1.AVG Function
This function returns the average value of the numeric column that is supplied as a parameter.
Example: Write a query to select average salary from employee table.
Select AVG(salary) from Employee;
2.COUNT Function
The count function returns the number of rows in the result. It does not count the null values.
Example: Write a query to return number of rows where salary > 20000.
Select COUNT(*) from Employee where Salary > 20000;
Types −
•COUNT(*): Counts all the number of rows of the table including null.
•COUNT( COLUMN_NAME): count number of non-null values in column.
•COUNT( DISTINCT COLUMN_NAME): count number of distinct values in a column.

3.MAX Function
The MAX function is used to find maximum value in the column that is supplied as a parameter. It can
be used on any type of data.
Example − Write a query to find the maximum salary in employee table.
Select MAX(salary) from Employee;
4.MIN Function
The MIN function is used to find minimum value in the column that is
supplied as a parameter. It can be used on any type of data.
Example − Write a query to find the minimum salary in employee table.
Select MIN(salary) from Employee;

5.SUM Function
This function sums up the values in the column supplied as a parameter.
Example: Write a query to get the total salary of employees.
Select SUM(salary) from Employee ;
QUERY LANGUAGE
The Query language is the language in which user request information from the
database. Query language is primarily created for creating, accessing and modifying
data in and out from a database management system (DBMS). Typically, QL requires
users to input a structured command that is similar and close to the English language
querying construct.
For example, the SQL query: SELECT * FROM
The customer will retrieve all data from the customer records/table.
Query Language

In simple words, a Language which is used to store and retrieve data from database is known as query language. For example –  SQL
There are two types of query language:

1.Procedural Query language

2.Non-procedural query language

     
1. Procedural Query language:
In procedural query language, user instructs the system to perform a series
of operations to produce the desired results. Here users tells what data to be
retrieved from database and how to retrieve it.
2. Non-procedural query language:
In Non-procedural query language, user instructs the system to produce the
desired result without telling the step by step process. Here users tells what
data to be retrieved from database but doesn’t tell how to retrieve it.
Relational Algebra:
Relational algebra is a conceptual procedural query language used on
relational model.
Relational Calculus:

Relational calculus is a conceptual non-procedural query language

used on relational model.
Basic/Fundamental Operations:
1. Select (σ)
2. Project (∏)
3. Union (∪)
4. Set Difference (-)
5. Cartesian product (X)
6. Rename (ρ)
Derived Operations:
1. Natural Join (⋈)
2. Association
3. Intersection (∩)
4. Division (÷)
Union Operator (∪)
Union operator is denoted by ∪ symbol and it is used to select all the rows
(tuples) from two tables (relations).
Lets discuss union operator a bit more. Lets say we have two relations R1
and R2 both have same columns and we want to select all the tuples(rows)
from these relations then we can apply the union operator on these relations.

Syntax of Union Operator (∪)

table_name1 ∪ table_name2
Intersection Operator (∩)

Intersection operator is denoted by ∩ symbol and it is used to select

common rows (tuples) from two tables (relations).
Lets say we have two relations R1 and R2 both have same columns
and we want to select all those tuples(rows) that are present in both
the relations, then in that case we can apply intersection operation on
these two relations R1 ∩ R2.
Note: Only those rows that are present in both the tables will appear in
the result set.
Syntax of Intersection Operator (∩)
table_name1 ∩ table_name2
Set Difference (-)

Set Difference is denoted by – symbol. Lets say we have two relations R1 and R2 and we want to select
all those tuples(rows) that are present in Relation R1 but not present in Relation R2, this can be done
using Set difference R1 – R2.
Syntax of Set Difference (-)

table_name1 - table_name2
Set Difference (-) Example
Lets take the same tables COURSE and STUDENT that we have seen above.
Query:
Lets write a query to select those student names that are present in STUDENT table but not present in
COURSE table.

∏ Student_Name (STUDENT) - ∏ Student_Name (COURSE)

Output:
Student_Name
------------
Carl
Rick
Cartesian product (X)

Cartesian Product is denoted by X symbol.

Lets say we have two relations R1 and R2 then the cartesian product of these two relations (R1
X R2) would combine each tuple of first relation R1 with the each tuple of second relation R2. I
know it sounds confusing but once we take an example of this, you will be able to understand
this.

Syntax of Cartesian product (X)

R1 X R2
Relational Calculus
Relational calculus is a non-procedural query language that tells the
system what data to be retrieved but doesn’t tell how to retrieve it.
Types of Relational Calculus
1. Tuple Relational Calculus (TRC)
Tuple relational calculus is used for selecting those tuples that satisfy the given condition.
Table: Student

First_Name Last_Name Age

---------- --------- ----
Ajeet Singh 30
Chaitanya Singh 31
Rajeev Bhatia 27
Carl Pratap 28

Query to display the last name of those students where age is greater than 30

{ t.Last_Name | Student(t) AND t.age > 30 }

In the above query you can see two parts separated by | symbol. The second part is where we define the condition and in the first part we specify the fields
which we want to display for the selected tuples.
The result of the above query would be:

Last_Name
---------
Singh

Query to display all the details of students where Last name is ‘Singh’
{ t | Student(t) AND t.Last_Name = 'Singh' }
Output:
First_Name Last_Name Age
---------- --------- ----
Ajeet Singh 30
Chaitanya Singh 31
2. Domain Relational Calculus (DRC)

In domain relational calculus the records are filtered based on the domains.
Again we take the same table to understand how DRC works.
Table: Student
First_Name Last_Name Age
---------- --------- ----
Ajeet Singh 30
Chaitanya Singh 31
Rajeev Bhatia 27
Carl Pratap 28
Query to find the first name and age of students where student age is greater than 27

{< First_Name, Age > | ∈ Student ∧ Age > 27 }

Note:
The symbols used for logical operators are: ∧ for AND, ∨ for OR and ┓ for NOT.
Output:
First_Name Age
---------- ----
Ajeet 30
Chaitanya 31
Carl 28
VIEWS
•Views are kind of virtual tables.
•A view also has rows and columns as they are in a real table in the database.
•We can create a view by selecting fields from one or more tables present in the
database.
•A View can either have all the rows of a table or specific rows based on certain
condition.
•To define a view ,we must give the view a name and must state query that computes
the view.
•Syntax of View command is
create view view_name as <query expression>
Where <query expression> is any legal query expression
The view_name is represented by any name related to the table.
Eg:
STU_ID NAME ADDRESS
1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram

Table name:Student_Detail
Query:
CREATE VIEW DetailsView AS  SELECT NAME, ADDRESS  FROM Student_Details  
WHERE STU_ID < 4;  

SELECT * FROM DetailsView;  

Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad