Unit 2
Introduction to relational model
Relational model: Relational Model in DBMS
Relational model can represent as a table with
columns and rows. Each row is known as a tuple.
Each table of the column has a name or attribute.
Domain: It contains a set of atomic values that an
attribute can take.
Attribute: It contains the name of a column in a
particular table. Each attribute Ai must have a
domain, dom(Ai)
Relational instance: In the relational database
system, the relational instance is represented by a
finite set of tuples. Relation instances do not have
duplicate tuples.
Relational schema: A relational schema contains
the name of the relation and name of all columns
or attributes. Relational key: In the relational key, each row has
one or more attributes. It can identify the row in
the relation uniquely.
ADVERTISEMENT,
Example: STUDENT Relation
NAME ROLL.NO PHONE.NO ADDRESS AGE
Ram 14795 7305758992 Noida 24
Shyam 12839 9026288936 Delhi 35
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 9028 913988 Delhi 40
© In the given table, NAME, ROLL.NO, PHONE_NO,
ADDRESS, and AGE are the attributes.
© The instance of schema STUDENT has 5 tuples.
° t3 =
Properties of Relations
o Name of the relation is distinct from all other
relations.
o Each relation cell contains exactly one atomic
(single) value
o Each attribute contains a distinct name
© Attribute domain has no significance
© tuple has no duplicate value
o Order of tuple can have a different sequence Integrity constraints over relation: Integrity Constraints
o Integrity constraints are a set of rules. It is used
to maintain the quality of information.
o Integrity constraints ensure that the data
insertion, updating, and other processes have to
be performed in such a way that data integrity is
not affected.
o Thus, integrity constraint is used to guard
against accidental damage to the database.
Types of Integrity Constraint
Integrity Constraint
Referent
Integnty Cor
1. Domain constraints
o Domain constraints can be defined as the
definition of a valid set of values for an attribute.
o The data type of domain includes string,
character, integer, time, date, currency, etc. The
value of the attribute must be available in the
corresponding domain. Example:
ADVERTISEMENT,
ADVERTISEMENT,
1D NAME SEMENSTER | AGE
1000 __| Tom 1 17
1001 Johnson 2nd 24
1002 Leonardo 5th 21
1003__| Kate 34 19
1004 [Morgan | 8® A,
Not allowed. Because AGE is an integer attribute 2. Entity integrity constraints
ADVERTISEMENT,
ADVERTISEMENT,
o 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.
o A table can contain a null value other than the
primary key field.
Example:
ADVERTISEMENT,
NOES as ==
EMPLOYEE
EMP_ID EMP_NAME ‘SALARY
123 Jack. 30000
142 Harry ‘60000
164 John 20000
n Jackson, 27000
Not allowed as primary key can’t contain a NULL value 3. Referential Integrity Constraints
ADVERTISEMENT,
ADVERTISEMENT,
° A referential integrity constraint is specified
between two tables.
eln 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:
ADVERTISEMENT,
PNET =V SNL
(rable 1)
lemp_wame} wame | ace | D.No —~—— Foreign key
sce | 20 "
nay | ao | 2
- 12-1 Not allowed as 0_No 18
ay ‘ol getied 2s a Prmary
Dea 2 2 key of fable 2 and In table 1,
1D_No'sa foreign key
deine
Relationships pn
Primary Key ———[— p.no | D_Location
* Mamba:
2 en
3 Noida 4. Key constraints
ADVERTISEMENT,
ADVERTISEMENT,
© 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:
ADVERTISEMENT,
ADVERTISEMENT,
1D, NAME ‘SEMENSTER | AGE
1000 [Tom ra 17
1001 [Johnson | 2° 24
1002 | Leonardo | 5 21
1003 | Kate 3a 19
1002 [Morgan [3 22
Not allowed. Because all row must be unique Enforcing integrity constraints: Enforcing integrity constraints ina
Database Management System (DBMS) is
essential to maintain the accuracy and
consistency of the data stored in the
database. There are several ways to
enforce integrity constraints:
Me dea ASU ole Kom Marcle
each row ina table has a unique identifier,
which helps prevent duplicate or null
values in the primary key column.
. Unique Constraints: Ensure that values in
a particular column or set of columns are
unique across all rows in a table.
. Foreign Key Constraints: Maintain
referential integrity by ensuring that
values in a column match values in
another table's primary key column.
. Check Constraints: Define custom
conditions that must be satisfied for data
Vv
to be inserted or updated in a table. This . Check Constraints: Define custom
conditions that must be satisfied for data
to be inserted or updated in a table. This
can be used to enforce business rules or
data validation.
. Default Constraints: Specify default
values for columns, ensuring that a value
is assigned when no explicit value is
provided during an INSERT operation.
. Not Null Constraints: Specify that a
particular column cannot contain NULL
values, ensuring that essential data is
always present.
. Triggers: Use triggers to execute custom
code automatically in response to
specific events, such as INSERT, UPDATE,
or DELETE operations, to enforce
complex integrity rules.
. Domain Constraints: Define domain-
specific rules and validations for
columns, ensuring that data adheres tov
specific constraints. Querying relational data: —
» Query is a question formulated for a relation\table to retrieve some useful
information from the taBle .
» Arelational database query is a question about the data and the answer
consist of anew relation containing result.
» Different languages are used to frame queries , the most popular is sql
Syntax : select column_list
from table_name ;
Example : select *from student ;
The above query gives the table student as result Ex:2: select *from employe® { s\
wheTeerMip_no=50 7 7 SV TECH
KNOWLEDGE
The above query gives the complete information about the employee with
number 50.
ex:3: select *from employee
where dept_no=10;
The above query will list the information about the employees who are working
for the department number 10.
» select ‘*’indicates all columns from table
» Combine information from two tables
Select s.name , enroll_id
From student s, enrolled e
Where s.sid=e.stdid AND e,grade’= ‘a’; » The above query gives us ‘a’ grade students
NN
» Here where clause used to operate conditjons on relations it can usq
logical operations like AND”, OR, WOT TC...
Ee
SQL :ISTRUCTURED QUERY LANGUAGE):
» SQL stands for Structured Query Language 4
» SQL lets you access and manipulate databases
» Itis also called as SEQUAL
» Developed in 1970 by ibm
» The main objective of sql is to store , miniplate or retrieve data.
» Sql is used by most rdbms users .
» In1979 oracle corporation introduced the first commercially available implementation
of sql . Features of sql | =
KNOWLEDGE
» Sqlis anon procedural language
» Sqlis very simple to understand , it is English like language , uses such words
like select , insert , delete etc.. 8
» It saves time to retrieves the data & process .
» Ituses the querying the data.
» Guaranteeing database consistency . _— WhatCanSQLdo? we
{2}
SV TECH
KNOWLEDGE
» SQL can execute queries against a database
» SQL @p retrieve data from a database
» SQL can insert records in a database
» SQL can update records in a database
» SQL can delete records from a database
» SQL can create new databases
» SQL can create new tables in a database
» SQL can create stored procedures in a database
» SQL can create views in a database
» SQL can set permissions on tables, procedures, and views Types of SOL Commands —
Creat@ Grant Commit
Alter Revoke Rollback
Drop Saveprint
Truncate
Delete
Rename Logical data base design: ~Srieeas dlcinse a
T ts) 4p. paces ar . (ox)! tah
the concephey ots ” Ch ey am
Pehl nll mah
7 Te Joiel db b has cuts concepts heh SS
1. eT of anti cel) inke ables:
nappy weak ont ‘ato table
, more si beak class inte teibleo
. Meng felon sels, (coheat Consteainks ) int 6 «
table
oe ] rdabon scts\ vith eats fahin conta
ted
wit es conte rinhs -
Mori ‘al
Off 4 ER chert, coil, pepe inko
' i
°, Sebles
at of Sy seh into teub fo
Clie) ) corake a Jabfe for enlity
*) Te catsibaube of ent
will Lecome attibade of o labk 2) efrieg becok ofl leks inte _- _
te of
1) here we hawe te cxute 400 tables
t
2» ast teble tos “il vat KX
lily sek sy) eve he
) and gable fos weak ent
J
te inchde he pity cf Hs adel
ae entity eS as a Fesep af
Empeyse
Aepercent |
|
i a stable « Creoke fob de Qudant ( Nace varchar (52) ,
solino int pore ae
Cechon Vercher ( 1) } b
Converting RE model to table or relation is called logical base design J Chidenk el,
noon ~alino Ceck
mon
Bat Ghodect (coma vorchert Z
archery (S90,
cal Int prey
a Nexclior ( to) ys Ki | Vee >> wld
Gade table Duatngs (
Roo ink prem
Addven Vorthe a) “yy
J
Dept nacet — yorchor( =)
Praga kay ( dachiid ) vebrereas Qaldrat (02) feo ee prey HL
Addeas Voxher (1°) J |
Apt -id ie,
Dept noc vorchor( m)
Frogs Kap (Sahl), ea Baldr ( ) Comertng, “Plation Ship Cot ito bab
fe pid ‘int
Cine int
Progen by (enptd) ‘elect
ereplleyer lend),
Parieyn Hy (Did) refowres
deportrert: (0°) Introduction to views: | Mize.
> View %3 2 Vivhiel Table bated m ome of moe tables.
> fp Viud Can be Geated from one ovmove tables hich depends on tha wilt
SHL Aug te unl a Viw.
+ A Yiud Can imtein oll wus % 2 table or Selected vous from a Table-
> mat 4 Te opetation that Can be Camicd on fable can be Galkied Out
on View. Any Updating 4 vous © Re table url) automaticuly reHet
fn tke Viww-
DisAdv S—
SCH CARR, be bndexect:
may or maynt be diretly
' ide dale Security and Corfidentially.
A: View use Cie Strage Space -
rs a Culterited View for UIA,
4 not arise -
updatable . View
ed
tay aki
Syntee: “oe
Views Prom
where
Sdet x frm Stdviad’
Sewusity and Gofidentially.
ie, ese, Space Destroying/alternating tables and views: DESTROYING/ALTERING TABLES AND VIEWS
* If we decide that we no longer need a base table and
want to destroy it (i.e., delete all the rows and remove
the table definition information), we can use the
DROP TABLE command.
For example, DROP TABLE Students RESTRICT
destroys the Students table unless some view or
integrity constraint refers to Students; if so, the
command fails.
* If the keyword RESTRICT is replaced by CASCADE,
Students is dropped and any referencing views or
integrity constraints are (recursively) dropped as well;
one of these two keywords must always be specified.
* A view can be dropped using the DROP VIEW
command, which is just like DROP TABLE. ALTER TABLE modifies the structure of an existing table.
To add a column called maiden-name to Students, for
example, we would use the following command:
ALTER TABLE Students
ADD COLUMN maiden-name CHAR(10)
The definition of Students is modified to add this column,
and all existing rows are
padded with nu// values in this column. ALTER TABLE can
also be used to delete columns and to add or drop integrity
constraints on a table;
we will not discuss these aspects of the command beyond
remarking that dropping columns is treated very similarly
to dropping tables or views. * A view is a relation whose instance is not
explicitly stored but is computed as needed.
*In addition to enabling logical data
independence by defining the external schema
through views, views play an important role in
restricting access to data for security reasons.
* Since views might be defined through complex
queries, handling updates specified on views is
complicated. * A view is a relation whose instance is not
explicitly stored but is computed as needed.
*In addition to enabling logical data
independence by defining the external schema
through views, views play an important role in
restricting access to data for security reasons.
* Since views might be defined through complex
queries, handling updates specified on views is
complicated. Relational algebra: ie Relotimol PAlgebya Cv) Relafioal Set OLAS lations Ctabha)
| ined cod 7 pul relat tobles )-
ional Algebra con be defined ab a Set abs Used % Manipy le
| ek opesator of tre velational algebra aes tite. ane or fio veletios of is topuk ard
| duces a how valetion of tts “output Relational model Supports Nine a Te
ONION t= Tt Combines all rows From iyo Tables, excluding dopliele ows. That
in" the Same attribube characteristics Cdalk type should ‘hatch >: Le .
| Syptees Select cealumnilisty fron AtaWlenamer> UNION Select dtolumnslisk> From
UNION ALLE Te Gmblnes all wow! fren two tebh . ,
zon have the Same attribut chauehilsice, Inducing duplicate TOUS. That table mast
Somtes Sled cesblist > from
Ex: Sdeu x from Ti UNION ALL Lele x from Te;
3 Enheasect Tt oe only the vows ther appey fo beth tables dak means Comincn ows.
Syntax:- Sled from ;
exe Sele x from T INTERSECT Select M From Try
. Mi te Tt gives all tke your fn one table ther ate net found br te other table.
° Mines o EXPE Thr Set Tt Subpvact one table from Ihe other Table.
Syrter- Sted From fiom :
ex- Sele 4 from Minos Selec % fram Tay
from :
mers yp tee Relotimol Algebva (7) Relation! Set opratrs
5. Sclecki- Te ts Used te Scleck all Te dala vor or ar of dala rows fron The table.
Select % from > Std
Syotes
oxy Sede %* from Std;
6 project i- It give all Values for Sdecked atlrbutes.
Synteo: Sdlek from 5
ex: Sele — Rnd sSName From Stay
ay *
1 7, - ge gives all possible pows of vous from two
a predut: OF Bet ee chtuien produ:
! Syntoy: Sdek From | € javatpoint A subject = databa
Output: This query will yield the article, page, and
subject from the relational javatpoint, where the
subject is a database.