Module 2

Relational model
 INTRODUCTION

⮚ In relational database management system (RDBMS), data are

represented using set of rows and columns in tabular format.

⮚ Relational model was first introduced by Ted Codd of IBM

research in 1970.

⮚ Oracle SQL and MySQL are examples popular RDMS

language to deals with RDBMS data.
 INTRODUCTIONS(cont’d)

⮚ The table is called a relation.

⮚ A row is called a tuple.

⮚ A column is called a Field.

⮚ A column header is called an attribute.

 Relation, Tuple and Attributes
ATTRIBUTES
RELATION
( STUDENT )

ROLL_NO NAME ADDRESS PHONE AGE Department_CODE

1 RAM DELHI 9455123451 18 103

2 RAMESH GURGAON 9652431543 18 103

TUPLES

3 SUJIT ROHTAK 9156253131 20 102

4 SURESH DELHI 18 104

 IMPORTANT TERMINOLOGIES

► Relation Schema:
► Represents conceptual table details.
► Represents name of the relation with its attributes.
► Example: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, AGE, Department_CODE)

► Relation Instance: The set of tuples of a relation at a

particular instance of time is called as relation instance.

► Attribute: Attributes are the properties that define a relation.

e.g.; ROLL_NO, NAME
 IMPORTANT TERMINOLOGIES
► Tuple: Each row in the relation is known as tuple. The above
relation contains 4 tuples, one of which is shown as:
1 RAM DELHI 9455123451 18 103

► Degree: The number of attributes in the relation is known as

degree of the relation

► Cardinality: The number of tuples in a relation is known as

cardinality.
CONSTRAINTS ON RELATION

Constraints
► Constrains are used to uniquely differentiate all tuples
(records) of the relation.
► Every relation has some conditions that must hold for it
to be a valid relation. These conditions are
called Relational Integrity Constraints
CONSTRAINTS ON RELATION

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.

► Key constraints is a relation with a key attribute, no two tuples can have
identical values for key attributes.

► A key attribute cannot have NULL values.

► Key constraints are also referred to as Entity Constraints.

 Candidate Key and Primary Key
► Student schema:
Student First City Age Branc Passpor Driving DoB
ID Name h t No License No

► Minimal set of attributes in a relation that used to differentiate all

the tuples of the relations are candidate keys.
► {Student ID, Passport No, Driving License No, DoB_FirstName}
are candidate keys
► Assume combination of DoB and First Name is unique.
► One candidate key can be assigned as primary key. Ex: Student
ID
► Remaining all other keys are alternative keys.
 Simple, Compound and Super Key

► Student schema:
Student First City Age Branc Passpor Driving DoB
ID Name h t No License No

► Simple keys: Student ID, Passport No, Driving License No.

► Compound key: {Student_ID, First_Name}
► Super key indicates a set of attributes where one subset is a
candidate key.
► {Student_ID First_Name}, {Passport_No DL_No} are
examples of super keys.
Key Attribute Set
STUDENT

Stu_id Name Branch

11255234 Aman CSE

11255369 Kapil ECE

11255237 Aman CSE

11255678 Aastha ECE

Domain Constraints

► Attributes have specific set of values in real-world scenario.

► For example, age can only be a positive integer.

► Every attribute is bound to have a specific range of values.

► For example, age cannot be less than zero and number of

digits in telephone number must be a fixed number (say 10
digits).
 Referential Integrity Constraints
► The referential integrity constraints is specified between two relations or
tables.
► Used to maintain consistency among tuples in two relations.
► The concept of Foreign Keys introduced by referential integrity constraint.
► 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 relation, then that key element must exist.
Referential integrity Constraints
STUDENT
ROLL_NO NAME ADDRESS PHONE AGE Department_CODE

2031 RAM DELHI 9455123451 18 103

2032 RAMESH GURGAON 9652431543 18 103

2033 SUJIT ROHTAK 9156253131 20 102

2034 SURESH DELHI 18 104

DEPARTMENT
Department_CODE Department_NAME

103 COMPUTER SCIENCE

105 INFORMATION TECHNOLOGY

ELECTRONICS AND COMMUNICATION

102 ENGINEERING

104 CIVIL ENGINEERING

Referential integrity Constraints
► In the STUDENT relation, ‘ROLL_NO’ is the primary key and
‘Department_CODE’ is the foreign key.

► The foreign key ‘Department_CODE’ referencing another relation

DEPARTMENT.

► STUDENT relation is called referencing relation in this example.

► DEPARTMENT relation is called referenced relation in this table.

 Referential integrity Constraints
► The rules are:
► We can't delete a record from a primary table if matching records
exist in a related table.

► We can't enter a value in the foreign key field of the related table
that doesn't exist in the primary key of the primary table.

► We can enter a Null value in the foreign key. Because, data may not
be available at the time the data is entered.
SPECIFYING CONSTRAINTS IN
SQL
Types of constraints available in SQL
► NOT NULL: Enforces a column to NOT
accept NULL values.
► UNIQUE: Ensures that all values in a column
are different.
► DEFAULT: Is used to set a default value for
a column.
► CHECK: Limit the value range that can be
placed in a column.
► Key Constraints – PRIMARY KEY, FOREIGN KEY
SPECIFYING CONSTRAINTS IN
SQL
Syntax Example
CREATE TABLE table_name
CREATE TABLE STUDENT
(
(
Column_name 1 data type size, ROLL_NO INT,
Column_name 2 data type size, STU_NAME VARCHAR2(35),
Column_name 3 data type size, STU_AGE INT,
……….. STU_ADDRESS
Column_name n data type size VARCHAR(235)
);
);
or
CREATE TABLE STUDENT( ROLL_NO INT, STU_NAME VARCHAR (35), STU_AGE INT, STU_ADDRESS
VARCHAR (235));
SPECIFYING CONSTRAINTS IN
SQL
NOTNULL
STUDENT

CREATE TABLE STUDENT ROLL_NO STU_NAME STU_AGE STU_ADDRESS

(
11255234 Aman 18 Kerala
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (35) NOT NULL,
11255369 Kapil 20 Karnataka
STU_AGE INT NOT NULL,
11255237 Aman 19 Andra
STU_ADDRESS VARCHAR (235)
); 11255678 Aastha 18 NULL
SPECIFYING CONSTRAINTS IN
SQL
UNIQUE
STUDENT

CREATE TABLE STUDENT ROLL_NO STU_NAME STU_AGE STU_ADDRESS

(
11255234 Aman 18 Kerala
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (35) UNIQUE, 11255369 Kapil 20 Karnataka
STU_AGE INT NOT NULL,
11255237 Anu 19 Andra
STU_ADDRESS VARCHAR (35)UNIQUE
); 11255678 Aastha 18 Tamilnadu
SPECIFYING CONSTRAINTS IN
SQL
DEFAULT STUDENT

CREATE TABLE STUDENT ROLL_NO STU_NAME STU_AGE EXAM_FEE STU_ADDRESS

(
11255234 Aman 18 10000 Kerala
ROLL_NO INT NOT NULL,
STU_NAME VARCHAR (35) NOT NULL,11255369 Kapil 20 20000 Karnataka
STU_AGE INT NOT NULL,
11255237 Aman 19 10000 Andra
EXAM_FEE INT DEFAULT10000,
STU_ADDRESS VARCHAR (35) 11255678 Aastha 18 30000 Tamilnadu
);
SPECIFYING CONSTRAINTS IN
SQL
CHECK

CREATE TABLE STUDENT

(
ROLL_NO INT NOT NULL CHECK(ROLL_NO >1000), STUDENT
STU_NAME VARCHAR (35) NOT NULL,
STU_AGE INT NOT NULL, ROLL_NO STU_NAME STU_AGE EXAM_FEE STU_ADDRESS

EXAM_FEE INT DEFAULT 10000,

11255234 Aman 18 10000 Kerala
STU_ADDRESS VARCHAR (35)
); 11255369 Kapil 20 20000 Karnataka

11255237 Aman 19 10000 Andra

11255678 Aastha 18 30000 Tamilnadu

SPECIFYING CONSTRAINTS IN
SQL
KEY CONSTRAINTS
PRIMARY KEY STUDENT

CREATE TABLE STUDENT ROLL_NO STU_NAME STU_AGE STU_ADDRESS

( 11255234 Aman 18 Kerala

ROLL_NO INT ,
11255369 Kapil 20 Karnataka
STU_NAME VARCHAR (35) NOT NULL UNIQUE,
STU_AGE INT NOT NULL, 11255237 Aman 19 Andra
STU_ADDRESS VARCHAR (35) UNIQUE,
11255678 Aastha 18 Tamilnadu
PRIMARY KEY(ROLL_NO)
);
SPECIFYING CONSTRAINTS IN
SQL
FOREIGN KEY
CREATE TABLE STUDENT
(
ROLL_NO INT ,
STU_NAME VARCHAR (35) NOT NULL UNIQUE,
STU_AGE INT NOT NULL,
STU_ADDRESS VARCHAR (35) UNIQUE,
PRIMARY KEY(ROLL_NO)
);

CREATE TABLE ENROLLMENT

(
ENROLL_ID INT PRIMARY KEY,
STU_ID INT,
COURSE_NAME VARCHAR (35),
FOREIGN KEY(STU_ID)REFERENCES STUDENT(ROLL_NO)
);
SPECIFYING CONSTRAINTS IN
SQL
FOREIGN KEY

STUDENT
ENROLLMENT

ROLL_NO STU_NAME STU_AGE STU_ADDRESS ENROLL_ID STU_ID COURSE_NAME

11255234 Aman 18 Kerala 101 11255234 DBMS

11255369 Kapil 20 Karnataka 102 11255234 ToC

11255237 Aman 19 Andra 103 11255237 OS

11255678 Aastha 18 Tamilnadu 104 11255678 DAA

 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION

► Integrity constraints are specified when a relation is

created and enforced when a relation is modified.

► Insert or update command causes a violation of

PRIMARY KEY, UNIQUE and Domain constraints.

► Delete commands causes a violation of referential

integrity constraints.

► Deletion does not cause a violation of domain, primary

key or unique constraints.
 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION
SQL provides several alternative ways to handle foreign key violations.

► CASCADE - Delete all Enrolled rows that refer to the deleted Students
row. It specifies that the child data is deleted when the parent data is
deleted.

► NO ACTION- “ON DELET NO ACTION” Disallow the deletion of the

Students row if an Enrolled row refers to it.

► SET DEFAULT- Set the sid column to the sid of some (existing) ‘default’
student, for every Enrolled row that refers to the deleted Students row.

► SET NULL - For every Enrolled row that refers to it, set the sid column to
null.
 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION
SQL provides several alternative ways to handle foreign key
violations.

CREATE TABLE Enrolled

(
sid INT,
cid VARCHAR(20),
grade VARCHAR(10),
PRIMARY KEY (cid),
FOREIGN KEY (sid) REFERENCES Students (sid) ON DELETE CASCADE );
 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION
SQL provides several alternative ways to handle foreign key
violations.

CREATE TABLE Enrolled

(
sid INT,
cid VARCHAR(20),
grade VARCHAR(10),
PRIMARY KEY (cid),
FOREIGN KEY (sid) REFERENCES Students (sid) ON UPDATE NO ACTION);
 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION
SQL provides several alternative ways to handle foreign key
violations.

CREATE TABLE Enrolled

(
sid INT DEFAULT 5000,
cid VARCHAR(20),
grade VARCHAR(10),
PRIMARY KEY (cid),
FOREIGN KEY (sid) REFERENCES Students (sid) ON DELETE SET DEFAULT);
 ENFORCING INTEGRITY CONSTRAINTS ON
RELATION
SQL provides several alternative ways to handle foreign key
violations.

CREATE TABLE Enrolled

(
sid INT DEFAULT 5000,
cid VARCHAR(20),
grade VARCHAR(10),
PRIMARY KEY (cid),
FOREIGN KEY (sid) REFERENCES Students (sid) ON DELETE SET NULL);
 QUERYING RELATIONAL DATA
► A relational database query is a question about
the data, and the answer consists of a new
relation containing the result.

► A query language is a specialized language for

writing queries.

► SQL is the most popular commercial query language

for a relational DBMS.
 QUERYING RELATIONAL DATA
► Example 1: find all students younger than 18

SELECT *
FROM Students S
WHERE [Link] < 18;

OUTPUT
 QUERYING RELATIONAL DATA
► Example 2: Compute the names and logins of
students who are younger than 18.

SELECT [Link], [Link]

FROM Students S
WHERE [Link] < 18;

OUTPUT
 QUERYING RELATIONAL DATA
► Example 3: Obtain the names of all students who
obtained an grade A and the id of the course in
which they got an A.

SELECT [Link], [Link]

FROM Students S, Enrolled E
WHERE [Link] = [Link] AND [Link] = ‘A’;

OUTPUT
name cid
Smith Topology112
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.
LOGICAL DATABASE DESIGN: ER TO RELATIONAL
Entity Sets to Tables

CREATE TABLE Employees

(
Ssn INT,
name VARCHAR(30),
lot INT,
PRIMARY KEY (ssn)
);
ER MODEL TO RELATIONAL DATABASE
Relationship Sets (without Constraints) to Tables

CREATE TABLE Works In2

(
ssn INT,
did INT,
address CHAR(20),
since DATE,
PRIMARY KEY (ssn, did, address),
FOREIGN KEY (ssn) REFERENCES Employees(ssn),
FOREIGN KEY (address) REFERENCES
Locations(address),
FOREIGN KEY (did) REFERENCES Departments(did)
);
Inserting Data Into Table

INSERT INTO table_name (comma-separated column

names)
VALUES (comma-separated values );

► Have to mention table_name, comma-separated

column names and comma-separated values.

INSERT INTO suppliers

(supplier_id, supplier_name, supply_date)
VALUES (5000, ‘Intel’, DATE ‘2022-07-31’);
ER MODEL TO RELATIONAL DATABASE
Translating Relationship Sets with Key Constraints
•Each department has at most one manager. Although,
single employee is allowed to manage more than one
department.

CREATE TABLE Manages

(
ssn VACHAR (11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES
Employees,
FOREIGN KEY (did)REFERENCES
Departments);
ER MODEL TO RELATIONAL DATABASE
Translating Relationship Sets with Key Constraints
❑Combine manager and Dept in a table. Each department has at
most one manager.

CREATE TABLE Dept_Mgr

(
did INT,
dname VARCHAR (20),
budget REAL,
ssn VARCHAR (11) ,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees
);
ER MODEL TO RELATIONAL DATABASE
Translating Relationship Sets with Participation
❑Combine manager and Dept in a table. Every department is required
Constraints
to have a manager.

CREATE TABLE DeptMgr

(
did INT,
dname VARCHAR(20) ,
budget REAL,
ssn VARCHAR (11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees ON DELETE NO
ACTION
);
ER MODEL TO RELATIONAL DATABASE
Translating Weak Entity Sets. If a tuple in the owning entity is
deleted the dependent tuple must be deleted.

CREATE TABLE Dep_Policy

(
pname VARCHAR(20) ,
age INT,
cost REAL,
ssn INT,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees(ssn) ON DELETE
CASCADE
);
 Introduction to Views
► In some cases, it is not desirable for all users to see the entire
logical model (that is, all the actual relations stored in the
database.)
► Consider two tables Students and Enrolled.
► Suppose, a faculty is often interested in finding student_name,
student_id and course_id who got A grades.
CREATE VIEW A-Grade-Student (name, st_id,
course_id)
AS SELECT [Link], [Link], [Link]
FROM Students S, Enrolled E
WHERE [Link] = [Link] AND [Link] = ‘A’

► A view is a table whose rows are not explicitly stored

in the database but are computed as needed.
 View Definitions
► A view is a table whose rows are not
explicitly stored in the database but are
computed as needed.
► View is Virtual relation that does not necessarily
actually exist in the database but is produced
upon request, at time of request.
► An SQL View is a specific representation of data
from one or more tables.
► The tables referred in the views are known as Base
table.
► Creating view does not take any storage space as
only the query is stored in the data dictionary.
Actual data is not stored.
► A View is a "Virtual Table” which contains columns and
data from different tables (may be one or more tables)
Issues with Updating View
► Now suppose that we want to delete the row (Smith, smith@ee, Hiking,
1997) from ActiveStudents. How are we to do this?
► This task can be accomplished in one of two ways:
► by either deleting the row (53688, Smith, smith@ee, 18, 3.2) from Students
► or deleting the row (Hiking, 1997, Smith) from Clubs.
► But neither solution is satisfactory.
► Removing the Students row has the effect of also deleting the row
(Smith, smith@ee, Rowing, 1998) from the view ActiveStudents.
► Removing the Clubs row has the effect of also deleting the row (Smith,
smith@math, Hiking, 1997) from the view ActiveStudents.
► Neither of these side effects is desirable. In fact, one reasonable
solution is to disallow such delete on views.
Views Based on User Needs

► Example1: In a telephone directory, a user might want to look

up the name associated with a number, without concern for
the street address.
► The best view for this purpose would have two columns: the
phone numbers (in numeric sequence) in the first column, and
the name associated with each number in the second column.
► Example2: Another user might want to look up the phone
number associated with a street address, without any need
to know the name.
► The best view for this purpose would have two columns: the street
addresses (in alphanumeric order) in the first column, and the
phone number in the second column.
Advantages of views

► Security Each user can be given permission to

access the database only through a small set of
views that contain the specific data the user is
authorized to see
► Query Simplicity A view can draw data from
several different tables and present it as a single
table, turning multi-table queries into single-
table queries against the view.
► Structural simplicity Views can give a user a
"personalized" view of the database structure,
presenting the database as a set of virtual tables
that make sense for that user.
Advantages of views

► Consistency A view can present a consistent,

unchanged image of the structure of the
database, even if the source tables are split,
restructured, or renamed.

► Data Integrity If data is accessed and entered

through a view, the DBMS can automatically
check the data to ensure that it meets the specified
integrity constraints.
Thank you