Unit I–Relational Model

Codd proposed the relational data model in 1970. A Prototype

research project was developed at IBM UC-Berkeley by mid-1970.
Today, Most database (DBMS) products like IBM’s DB2 family,
Informix, Oracle, Sybase, Microsoft’s Access and SQLServer, FoxBase,
and Paradox depend on the relational data model.

In the relational data model each relation is a table with row and
column. The major advantage of the relational model is simple
representation and complex queries can be expressed easily

1) INTRODUCTION TO THE RELATIONAL MODEL

The main construct for representing data in the relational model

is a relation. A relation consists of a relation schema and a relation
instance.

The relation instance is a table, and the relation schema describes

the column heads for the table.

The schema specifies the relation’s name, the name of each field
(or column, or attribute), and the domain of each field. A domain is
referred to in a relation schema by the domain name and has a set of
associated values.

For example, Student relation schema can be given as

Students(sid:string,name:string,login:string,age:integer,gpa:
real)

From the above syntax, the field named sid has a domain name
dstring. The set of values associated with domain string is the set of all
character strings.
 An instance of a relation is a set of tuples , also called records, in
which each tuple has the same number of fields as the relation schema. A
relation instance can be thought of as a table in which each tuple is a row,
and all rows have the same number of fields.

An instance of the Students relation can be shown below

FIELDS(ATTRIBUTES,COLUMN

S)
Fieldnames

sid name login age gpa

50000 Dave dave@cs 19 3.3
TUPLES(RECORDS,ROWS) 53666 Jones jones@cs 18 3.4
53688 Smith smith@ee 18 3.2
53650 Smith smith@math 19 3.8
53831 Madayan madayan@music 11 1.8
53832 Guldu guldu@music 12 2.0
Figure An Instance S1of the Students Relation

The domain constraints specify a condition, that all values for the
column must be drawn from the domain associated with that column.

We can say that the relation instance satisfies the domain

constraints in the relation schema.

The degree or arity is the number of columns/attributes/fields of a

relation instance, and the cardinality of a relation instance of a relation is
the number of tuples in it. From the previous relation instance/table the
degree/arity is 5, and the cardinality is 6.

A relational database is a collection of distinct relations with

different names. The relational database schema is a collection of
schemas. An instance of a relational database is a collection of relation
instances one per schema.
 Creating and Modifying Relations using SQL:

SQLlanguage use stable store present relations. The subset of

SQL that supports the creation, deletion, and modification of tables is
called the Data Definition Language (DDL).

The CREATE TABLE statement is used to define a new table.

For example, create the Students relation, we can use the following
statement:
CREATE TABLE Students ( sid CHAR (20), name CHAR(30),
login CHAR(20), age INTEGER, gpa REAL);

Tuples are inserted using the INSERT [Link] can insert a

single tuple into the Students table as follows:

INSERT INTO Students(sid, name, login, age,gpa) VALUES (53688,

‘Smith’, ‘smith@ee’, 18, 3.2);

We can optionally omit the list of column names in the INTO

clause and list the values in the appropriate order.

We can delete tuples using the DELETE command. We can

delete all Students tuples with name equal to Smith using the command:

DELETE FROM Students WHERE [Link]=‘Smith’;

We can modify the column values in an existing row using the

UPDATE command. For example,command. We can increment the age
and decrement the gpa of the student with sid 53688:

UPDATE Students S SET [Link]= [Link] + 1, [Link]= [Link]–1

WHERE [Link]= 53688;
 2) INTEGRITYCONSTRAINTSOVERRELATIONS

An integrity constraint (IC) is a condition that is specified on a

database schema and restricts the data that can be stored in an instance of
the database. If a database instance satisfies all the integrity constraints
specified on the database schema, it is a legal instance.

A DBMS enforces integrity constraints, in that it permits only legal

instances to be stored in the database.

Integrity constraints are specified and enforce data different times:

 When the DBA or end user defines a database schema

 When a database application is run, the DBMS checks for
violations and disallows changes to the data that violate the
specified ICs

a) Key Constraints

A key constraint is a statement that a certain minimal subset of the

fields of a relation is a unique identifier for a tuple.

A set of fields that uniquely identifies a tuple according to a key

constraint is called a candidate key or key for the relation. In the
above example sid is a candidate key or key.

The set {sid,name} is an example of a superkey, which is a set of

fields that contains a key. A relation can have several candidate keys like
for student roll number and email id. Out of all the available candidate
keys, a database designer can identify a primary key.

Specifying Key Constraints in SQL

 In SQL we can declare that a sub set of the columns of a table
constitute a key by using the UNIQUE constraint. At most one of these
‘candidate’ keys can be declared to be a primary key, using the
PRIMARY KEY [Link] us revisit our example table definition
and specify key information:

CREATE TABLE Stdents ( sid CHAR(20), name CHAR(30),

login CHAR(20), age INTEGER, gpa REAL, UNIQUE(name, age),
CONSTRAINT Students Key PRIMARY KEY(sid));

This definition says that sid is the primary key and that the
combination of name and age is also a key. The definition of the primary
key also illustrates how we can name a constraint by preceding it with
CONSTRAINT [Link] the constraint is violated, the
constraint name is returned and can be used to identify the error.

b) Foreign Key Constraints

Sometimes there can be a relationship between two tables. If one table

is modified, the other table must also be modified to keep data consistent.

An IC involving both relations must be specified if a DBMS is to

make such checks. The most common IC involving two relations is a
foreign key constraint.

Suppose that in addition to Students, we have a second relation:

Enrolled(sid: string,cid:string,grade:string);

To ensure that only Students in the above relation is

enrolled, any value that appears in sid of enrolled must appear
in field of Students. The sid of enrolled is called as foreign
key.

However, every sid value that appears in the instance of the Enrolled
table appears in the primary key column of a row in the Students table.
 NOTE: Every sid of enrolled is appearing inside of students.

 The primary key cannot be null.

Specifying Foreign Key Constraints in SQL

Let us define Enrolled (sid:string,cid:string,grade:string):

CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20),

grade CHAR(10), PRIMARY KEY (sid, cid),
FOREIGNKEY (sid) REFERENCES Students);

c) Domain Integrity-

Domain integrity means the definition of a valid set of

values for an attribute. You define a datatype, length or size, is
null value allowed, is the value unique or not for an attribute,
the default value, the range (values in between) and/or specific
values for the attribute.

d) General Constraints

Domain, primary key, and foreign key constraints are

considered to be a fundamental part of the relational data model
and are given special attention in most commercial systems.
Sometimes, however, it is necessary to specify more general
constraints.
For example, we may require that student ages be within a
certain range of values; given such an IC specification, the
DBMS will reject inserts and updates that violate the constraint.

3) ENFORCING INTEGRITY CONSTRAINTS

ICs are specified when a relation is created and enforced when a

relation is modified. The impact of the domain, PRIMARY KEY and
UNIQUE constraints is straightforward: if an insert, delete, or update
command causes a violation, it is rejected.

Consider the previous student instance, The following insertion

violates the primary key constraint because there is already a tuple with
the sid 53688 and it will be rejected by the DBMS:

INSERT INTO Students (sid, name, login, age, gpa) VALUES

(53688, ‘Mike’, ‘mike@ee’,17,3.4);

The following insertion violates the constraint that the primary key
cannot contain
null:

INSERT INTO Students (sid,name,login, age, gpa)

VALUES(null, ‘Mike’, ‘mike@ee’, 17, 3.4)

When we violate a domain constraint then all command will be

reject. Deletion does not cause a violation of domain, primary key or
unique constraints. However, an update can cause violations, similar to an
insertion:

UPDATE Students S [Link] =[Link]=53688

This update violates the primary key constraint because there is

already a tuple with Sid 50000.
 Consider previous Enrolled relation deletion of tuples does not
cause violation of referential integrity of tuples in Enrolled could.

For example, the following insertion is illegal because there is no

student tuple with sid 51111:

INSERT INTO Enrolled (cid,grade,sid) VALUES

(‘Hindi101’,‘B’,51111);

On the other hand, insertions of Student tuples may not

cause violation but deletion of student tuple can cause violation
because the key may be used in other relations.

SQL provides several alternative ways to handle foreign key

[Link] must consider three basic questions:

1. What should we do if an Enrolled row is inserted, with a sid

column value that does not appear in any row of the Students
table?

In this case the INSERT command is simply rejected.

2. What should we do if a Students row is deleted?

The options are:

 Delete all Enrolled rows that refer to the deleted Students row.
 Disallow the deletion of the Students row if an Enrolled row refers
to it.
 Set the sid column to the sid of some(existing) ‘default’
student, for every Enrolled row that refers to the deleted
Students row.
 For every Enrolled row that refers to it, set the sid column to null.
In our example, zl this option conflicts with the fact that sid is
part of the primary key of Enrolled and therefore cannot be set to
null. Thus, we are limited to the first three options in our example,
although this fourth option (setting the foreign key to null) is
 available in the general case.

3. What should we do if the primary key value of a Students row is

updated?

The options here are similar to the previous case.

SQL allows us to choose any of the four options on DELETE and

UPDATE.

For example, we can specify that when a Students row is deleted,

all Enrolled rows that refer to it are to be deleted as well, but that when
the sid column of a Students row is modified, this update is to be
rejected if an Enrolled row refers to the modified Students row:
CREATE TABLE Enrolled ( Sid CHAR(20), cid CHAR(20), grade
CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY(sid)
REFERENCES Students(sid) ON DELETE CASCADE ON UPDATE
NO ACTION);

When action is set to NO ACTION, the update will be rejected

and when option is cascade, if a students row is deleted all rows od
enrolled refereeing to students will be deleted.

If we want to update all enrolled rows we can use “NO UPDATE

CASCADE”.

For example, When a student row is deleted, we can give

‘default’ student value by using “NO DELETE SET DEFAULT”.

The default is specified in the definition of sid of enrolled like sid

CHAR(20) DEFAULT‘53666’.

SQL also allows to use of null as default value by specifying “NO

DELETE SET NULL”.

4) QUERYING RELATIONAL DATA

 A relational database query (query, for short) is a question about
the data, and the answer consists of a new relation containing the result.
Language is a specialized language for writing queries.

For example, we might want to find all students younger than 18 or all
students enrolled in Reggae 203.A query SQL is the most popular
commercial query language for a relational DBMS. Some of the SQL
queries on previous table can be given as follows.

sid name login age gpa

50000 Dave dave@cs 19 3.3
53666 Jones jones@cs 18 3.4
53688 Smith smith@ee 18 3.2
53650 Smith smith@math 19 3.8
53831 Madayan madayan@music 11 1.8
53832 Guldu guldu@music 12 2

From the above table we can retrieve the students younger than 18, with

following query.

SELECT * FROM Students WHERE age<18;

Means we want to retain all fields. The result of above query is as follows:

sid name login age gpa

53831 Madayan madayan@music 11 1.8
53832 Guldu guldu@music 12 2.0

Figure Students with age<18 on Instance S1

If we give the condition in where clause as age=name, it does not
make sense because it compares an integer value with a string value

We can also write a query to extract only subset fields. For

example, if want to retrieve name, login of students younger than 18 the
query can be written as

SELECT name, login FROM Students WHERE age<18;

Figure3.7 shows the answer to this query; it is obtained by applying the
selection to the instance S1 of Students (toget the relation
showninFigure3.6), followed by removing unwanted fields. Note that the
order in which we perform these operations does matter—if we remove
unwanted fields first, we cannot check the condition [Link] <18,which
involves one of those fields.

We can also combine information in the Students and Enrolled relations.

If we want to obtain the names of all students who obtained an A and the
id of the course in which they got an A, we could write the following
query:
SELECT [Link], [Link] FROM Students S ,Enrolled E WHERE
[Link]=[Link] AND [Link]=‘A’

5) LOGICAL DATABASE DESIGN: ER TO RELATIONAL

The ER model is convenient for representing an initial, high-level

database design. Given an ER diagram describing a database, there is a
standard approach to generating a relational database schema that closely
approximates the ER design.

Translate an ER diagram into a collection of tables with

associated constraints, i.e., a relational database schema.

a) Entity Sets to Tables

Converting an entity set to a table is very simple. Each attribute of

Entity set becomes column/attribute of the table.

Consider the following employee entity set

name

ssn lot

Employees

Figure The Employees Entity Set

 The attributes are ssn, name, lot. Consider there are employee
entities then this entity set will be converted to table form as shown
below

ssn name lot

123-22-3666 Attishoo 48
231-31-5368 Smiley 22
131-24-3650 Smethurst 35

Figure An Instance of the Employees Entity Set

The following SQL statement captures the preceding

information, including the domain constraints and key information:

CREATE TABLE Employees( ssn CHAR(11), name CHAR(30), lot

INTEGER, PRIMARYKEY(ssn));

b) Relationship Sets (without Constraints) to Tables

To represent a relationship set into a table we have to identify

participating entities, and descriptive attributes of the relationship.

The attributes of the relation include:

 The primary key attributes of each participating entity set, as
foreign key fields.
 The descriptive attributes of the relationship set.

The set of non descriptive attributes is a super key for the relation. If
there are no key constraints this set of attributes is a candidate key.

Consider the following relationship set among three entity sets

 since
name dname

ssn lot did budget

Departments
Works_In2
Employees

Locations
address capacity

Figure A Ternary Relationship Set

All the available information about the WorksIn2 table is captured by the
following SQL definition:
CREATE TABLE WorksIn2 (ssn CHAR(11), did INTEGER, Address
CHAR(20), since DATE, PRIMARYKEY(ssn,did,address), FOREIGN
KEY(ssn) REFERENCES Employees, FOREIGN KEY(address)
REFERENCES Locations, FOREIGN KEY(did) REFERENCES Departments);

Note that address, did and ssn fields cannot take on null values
and should be unique since they are primary keys.

Consider a relationship report as shown below

name

ssn lot

Employees

supervisor subordinate

Reports_To

Figure The Reports To Relationship Set

This relationship set can be converted to a table as follows

CREATE TABLE Reports To (supervisor ssn CHAR(11), subordinate

ssn CHAR(11), PRIMARY KEY(supervisor ssn, subordinate ssn), FOREIGN
KEY(supervisor ssn) REFERENCES Employees(ssn), FOREIGN KEY
(subordinate ssn) REFERENCES Employees(ssn) );

Observe that we need to explicitly name the referenced field of

Employees because the field name differs from the name(s) of the
referring field(s).

c) Translating Relationship Sets with Key Constraints

If a relationship set involves n entity sets and some m of the m are

linked via arrows in the ER diagram, the key for any one of these m entity
sets constitutes a key for the relation to which the relationship set is

since
name dname

ssn lot did budget

Employees Departments
Manages

mapped. Thus we have m candidate keys, and one of these should be

designated as the primary key.
Consider following relationship set Manages.

Figure Key Constraint on Manages

The table for manages relationship consists of attributes ssn, did,

since. did as primary key and ssn, did becomes foreign key

CREATE TABLE Manages (ssnCHAR(11), Did INTEGER, Since DATE,

PRIMARY KEY (did), FOREIGNKEY(ssn) REFERENCES Employees(ssn),
FOREIGNKEY(did) REFERENCES Departments(did));

Now this relationship consists of three tables Employee,

Department and Manages. By using second approach we can have two
tables as follows.

Modify the table which corresponds to primarykey in above

relation (i.e;didof department) by adding key field of employee and
descriptive attribute “since”.

Since did should be unique and ssn can be same i.e; one
department should have almost one employee as manager and one
manager can manage many department.
CREATE TABLE DeptMgr (did INTEGER, dname CHAR(20), budget
REAL,
Ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGNKEY(ssn)
REFERENCES Employees);

d) Translating Relationship Sets with Participation Constraints

Consider the ER diagram below Figure, which shows two relationship

sets, Manages and WorksIn.

since
name dname

ssn lot did budget

Manages
Employees Departments

Works_In

since

Figure Manages and WorksIn

 In manages relationship each department required to have a
manager ( Total participation constraint) and at most one manager (one
to many key constraint) and total participation at Employee and
Department in Work_In relationship. This relationship set can be
converted to a table as

CREATE TABLE Dept_Mgr (did INTEGER, dname CHAR(20), budget

REAL,
ssn CHAR(11) NOT NULL, since DATE, PRIMARYKEY (did),
FOREIGNKEY(ssn) REFERENCES Employees(ssn), ON DELETE NO
ACTION);
The NO ACTION specification is the default it tells that ssn
employee can’t be deleted if it is pointed by the Dept_Mgr table.

e) Translating Weak Entity Sets

A weak entity set always participates in a one-to-many binary

relationship and has a key constraint and total participation at a weak
entity.

Consider the following ER diagram of weak entity.

name
age
cost pname
ssn lot

Dependents
Policy
Employees

Figure The Dependents Weak Entity Set

The weak entity can’t be uniquely identified by its partial key, so

it should be combined with the primary key of owning entity to form the
primary key of the relationship. This relation can be written as
CREATE TABLE DepPolicy( pname CHAR(20), age INTEGER, cost
REAL,
ssn CHAR(11), PRIMARYKEY(pname,ssn), FOREIGNKEY(ssn) REFERENCES
Employees(ssn), ON DELETE CASCADE)

The cascade option ensures that information about the policy is

deleted if the employee tuple is deleted.
6) INTRODUCTION TO VIEWS
A view is a table whose rows are not explicitly stored in the database
but are computed as needed from a view definition. Consider the Students
and Enrolled relations.

sid name age gpa stdid Course_name grade

1 Ramu 25 7.5 1 DBMS A
2 Ravi 24 8.5 2 OS B
3 Raju 22 5.6
4 OS A
4 Rama 23 8.0
5 DBMS B
5 Rani 24 7.0

Student Enrolled

Suppose we want to find out sid and name of student who got grade
B in a course along with course name. For this can define a view as
follows.

CREATE view B_student (sid, name, course) As select [Link], [Link],

E.course_name From Student S, Enrolled E Where [Link]= [Link] and
[Link]=‘B’;
The view B-Students has three fields called name, sid, and
course with the same domains as the fields name and sid in Students and
course_name in Enrolled. These are optional because if we omit then
they will be inherited.

The tables from which view is created is called as basetable. We

can perform query on views as he do on table.

Select*from B_student;

sid name course

2 Ravi OS
5 Rani DBMS
B_student
Views provide logical data independence. Because even the
student of the table is changed, we can the same view if required.

Views also provide security. We can create with only necessary

data, the
Unwanted data hide in the table.

Update on views:

An update on the view is possible by updating its basetable. For

example, consider the following view updates are simple on single base
table views.

CREATE VIEW Good_Student (sid,gpa) As select [Link], [Link] From

Student S Where gpa>7.0;

The option of this view can be given as

Select * from Good_Student;

sid gpa
1 7.5
4 8.5
5 8
Good_Student

We can modify the gpa of a Good_Student row by modifying row

of student table. We can delete a row in view by deleting corresponding
row of a base table.
 We can insert rows into view by inserting rows in basetable, and
keeping null for columns that does not corresponding to views in above
table Sname, age. But we can’t keep a null value for the primary key. If
we do this insertion will be rejected.

Need to Restrict View Updates

Updates on views with more than base table is problematic

consider a view on student and Enrolled table.

CREATE VIEW avg_student(sid,name,course_name,grade) As select

[Link], [Link], E.course_name, [Link] From Student S, Enrolled E
Where [Link]=E.course_name and [Link]>6;

Consider Enrolled table is updated but with name.

name course_name Grade

Ramu DBMS A
Ravi OS B
Ramu OS A
Rani DBMS B
Enrolled

sid name course_name grade

1 Ramu DBMS A
1 Ramu OS A
2 Ravi OS B
2 Ravi DBMS B
4 Rama OS A
4 Rama DBMS B
5 Rani DBMS B
Student

Now consider we want to delete a row(1,Ramu,DBMS,A), how to do

this?
This can be accomplished by either deleting row(1,Ramu,25,7.5) from
Student base table, if we do this then other row in view(1, Ramu, OS, A)
will also deleted.

The reasonable solution to such problem is to disallow such update on

view.

7) DESTROYING/ALTERINGTABLES AND VIEWS

View can be destroyed same as table by using Drop view

command.

When a table is deleted by using Drop table the view will be

deleted.

If a view is using primary key of table, we can prevent

deleting of view by using restrict key word. For example Drop
table Student Restrict;

 If Restrict is replaced by cascade the view will be deleted.