صفحه 1:
ON ate ee |i
Pa ص۱۹
Deb Progranvwinry DOSE
Acknowledgement : this presentation uses
examples from the w3c website; available at -
http://www.w3schqals,com... «.
صفحه 2:
Outlicre
© Outbese iotoduction
OD ptivreticst
istry
ماو
9 GQD iatodurtion
Orb سم موس Orer DOS
صفحه 3:
Sh
Ortiy oP Databases
® Oar kwe vole indepeadedt oP use
© Onnnized opprowk to dota woongewedt (e.y., data
كمه
- نورد
© Chopiccte necuercay tc dott
Gkare dota
4 @rckive dott
— Geoonty oP dott
1 ate <prity oP chat
Orb سم موس Orer DOS
صفحه 4:
(Pite Dused Gpstews
OGE101T100
© 0 ohevton oP وم ما حور موه
pErvives to eu users.
® موم لو وتو مرس رو( its DLT
dara.
Orb سم موس Orer DOS
صفحه 5:
=>
Se ل #
Sales Files
File
آم ata Entry 8
a Reports handling
لب | Routines |
ile Definition
physical structure and
storage of the data
files are defined in the
program code
A ES
Reports
5
Lease Files
Orb سم موس Orer DOS
۲ >
ع
صفحه 6:
۲ ۳۲۲۲۲۲ 0
Livitatioas oP ite Bused Gustews
® Oot Oepecteuse
® Duptcatiod oP Data
"ا opowputbhe Ate Porat
How can these problems be
resolved?
Orb سم موس Orer DOS
صفحه 7:
a
Phe Dutabuse (Bpprouck
relied data desiqued to weet the
Orb سم موس Orer DOS
صفحه 8:
Outabuse ۱6
۲ ata Entry
ss reports
محر ١
Sales Application
Programs DBMS
ata Entry 3 Database
1 = reports
Leases App. Programs
Orb سم موس Orer DOS
صفحه 9:
اس
(Dutabuse QOueageweut (Spstew
(D®MG)
00۵
20 مجورو وناب thot eoubles were tv
dehice, create ved woiotaic the database urd
Which provides voutrolled users to the
حول
000 سم موس Orb
صفحه 10:
ل
I
Puvilites vP ۰ 5
Silentserckactnios (TEDL) مرح ص {Ohba .
a ۲ at ۳ ۵
a ین rer “databases mean
Provides could ues ®
Oe peony 5|517
Choe inte qriiy syste
ویو ای رصح هو و
فا و۵
000 سم موس Orb
صفحه 11:
a
Cowpourus peu DOOG
2 Wodwoe
" GoPiwoae
# Ota
جع لحم رت "
People ©
Orb سم موس Orer DOS
صفحه 12:
2 00
(Bdventages
1" OD icicod! dota recuerdo
® Ovwsisteuy ۲اه
لا tegration oP dota
"ا wproved intecrity
® Oousisteut sevunity
® Grocchards
a4 5
000 سم موس Orb
صفحه 13:
eee
لا
* Cowpleniy
© Oddiivod Wardware Custis
" Cie
عمو )سا
" عبس() -Opevintsed Persvucel
© Qotectdl orxpcisctioca Coa tot
Higher سل له )ات هب
source Puihure)
Orb سم موس Orer DOS
صفحه 14:
ها SS
جعد” هه Outabuse
SQL command interface dedicated application
web browser
Orb سم موس Orer DOS
صفحه 15:
Se
oteruntiod und (Peedback
© رازم uni oP data نمی
O remple? purchusiog wa oiice ticket pice
فح سبلا urd Post Por coewercid اوه
xy be bray ued uobed in pagceerioy applications
© Rolbok: Poy pot of 0 trexsantivs Pals, oft oocpleted parts
we “hed back” ی مه
7 بن ع سيد haved provided pour oredi cord cucber, حيلف ticket
علب مس سین مایم
اه روا وه اسلا database
7 رجا حل ربك تس فى 05
000 سم موس Orb
صفحه 16:
Se
(Relatiood Dutabuse Dodet
® Ouber
2 Dutcbuse ty « oolevtiva oP tables (rekitas)
Octo ore stoned ic tables
" Vobes
© Gack table kos a one
(ack table has a set oP ookras (Piekds) oad rows oP data (records)
1 Ol operaions process و ible tw produce a cew tbe
© ark اه هه له سا سل
7 اه واه وی مه با سا باس( rows Why?
© @used va set theory
= CQL (Ginetred Query Loceucce)
۲7 060۵ مها سل
000 سم موس Orb
صفحه 17:
Orcher Guople Tuble
Cy Grete ick
koe سس dOS
Puscoa مسب aod
مسب ی 99
Gao Diexw ادن PP
Obuquerqur | Dew Oexiow 60
000 سم موس Orb
صفحه 18:
۹۹۰۰۰۰
۱ مان
| Gack عم ه عدا ماه
وحم برط لصو و ماوق لأ
[ اوه لول موه و( prdertay
° Opes ot woke seuse to sop “the third ooluca” fike it does ico
“paper” table or spreadsheet
1 Dota fo coho وولو too porticutar dowoit
۱ اون ore the “otiibutes” oP the dataset
® Gack vdlue too ovhue is Pro the sace dowie
® Gack volue foo ooh is oP the sco dota ype
Orb سم موس Orer DOS
صفحه 19:
a
" Qows
لأ Cuck row eotry is ether a sieople volue or expt ("cul")
) Rows oe sets oP votues Por the ovhies (utribute values)
@rivery key: 9 set oP oohiwes thot uoiquely ideatPies rack
row
O لوق now west be usique given the pricvany key (a
duplicies)
Qows ae rePereaved by the privary key
Row order coco be deterwiced by the user (Oves wit
woke spose ty sup “ihe Pourth row" the it does ino “paper”
table or sprectshert)
Orb سم موس Orer DOS
صفحه 20:
Se
(Duta Pppes
© Gok rw uche is un festoue oF uo priviive dota pee
etecer
مه امه وم مه
4 Okoranter (e.y., text, hypertich, pes/a)
9/۲ جو
© Op vowplex per in stertard DODG (watrix, drawicr))
Obert viewed databases wuy dow objects ond structures
© Ovo existent vohue is “oll”
Orb سم موس Orer DOS
صفحه 21:
(Downie {pes
char(n): fixed length char string
varchar(n): variable-length char string
int or integer
smallint
numeric(p,d): fixed-point number of given
precision
real, double precision
float(n): floats with a given precision
date: containing year,month, and date
time: in hours, minutes, and seconds
Null value is part of each domain
صفحه 22:
2
GQL
8 Gteuctured Query Lupe
حول ار Cowwwunivute ®
© Osed to oreoted ocd echt databases.
© Obs wed tp pred queries, Porws, und reports
Orb سم موس Orer DOS
صفحه 23:
OOD un DOL
® GQ cossists oP two types oP stotewects
GGL Oxta DePiatica Loagquege (DOL)
perwits وونل tables to be oreuted or deleted iaserts نيجه
date foto dotobuse table
OREOTE TOOLE - credies 0 cew database toble
| @LPECR TOSLE - utters (chocges) u database table
ORO TRBLE - deletes a database table
GQ@D Outre Ouniputaiog Loaguage (DOL)
GEVECT - extracts dota Proc a database table
OPOOSTGE - updates data ita database table
OBLOTC - deletes cata Pow u database table
ADGERT WTO - teserts ew dota toto database table
Orb سم موس Orer DOS
صفحه 24:
۹۹۰۰۰۰
Oehive ver dowdies ocd tables
CREATE DOMAIN personDom CHAR(20);
CREATE TABLE emp
(ename personDom,
dno int default 0,
sal real
)
Orb سم موس Orer DOS
صفحه 25:
A
“Getevt” couse (1
= Specify attributes to project onto
SELECT co/umns FROM table WHERE condition ;
SELECT * use “’ to denote all attributes:
FROM Emp
WHERE Emp.Sal < 60K;
Emp (ename, dno,
صفحه 26:
0۸۱/٩۹٩۰٩۰٩۰
“Geter!” chase O
“SELECT” does not automatically eliminate duplicates.
Select Use keyword distinct to explicitly remove duplicates
dno
From Select distinct
ae dno
mp (ename-qdno,
ane جوا( = From
Jak 1 BOK Emp;
Alice |[ 11 90K
Lisa 8 222 ‘80K
‘Tom 333 70K
۳ 7 GOK.
Orb سم موس Orer DOS
صفحه 27:
A
“FROO” couse
SELECT co/umns FROM table WHERE condition ;
= Specify relations
Renaming relations: Use “as” to define “tuple varia bles,”
to disambiguate multiple references to the same
SELECT E1l.ename
FROM Emp as E1, Dept,
as E2
WHERE = E1.dno = Dept.d:
Dept.mgr = E2.ename AND ET>
E1: Emp (ename, d?oE2-Sal; pept(dno, dname,
5
Sabb 2 سس
me لب 3 بل نز
IT 3 = = تلا
Tiss [ar 3 -
Te 5 ۳3
‘May [5 cus
Orb سم موس Orer DOS
صفحه 28:
“OWERE” له
= Specify optional conditions
“Employees who work for Sally and have a salary < 90K”
SELECT ename
FROM Emp, Dept
WHERE Emp.dno=Dept.dno
AND
Li D.mgr = ‘Lisa’ AND sal
isa < 90000;
Emp (ename, dno, sal) Dept(dno, dname,
- I 5
حت مز 36 تب بل
Ste oa ps a =
اس لبیل متس
سول - ا جع
55-1 2
000 سم موس Orb
صفحه 29:
۹۹۰۰۰۰
دون Osed Ia Okere Chuse
= equ
ححكاا ون >
< less thor
>= yeeuter thod or equal tz
<= less thoc or equal i
<> uot equal t7
Orb سم موس Orer DOS
صفحه 30:
a
bike
Osed to woke vowplex seurcchiey eusy. IP pou
را انار to Pied of people's cves مارم صن
wits (& Por exacople:
GELEOT Pirstrave PROD ور
OWGERE Pirstanve LIKG 'C%';
Orb سم موس Orer DOS
صفحه 31:
ا يي
Ordering vutput tuples
Order the tuples by dno. Within each dept, order salaries from
highest to lowest. For salary ties, use alphabetical order on the
name.
SELECT *
FROMEmp
order by dno, sal,
ename;
سب od ۳-۹
Aleks 69 0۵۵۵۲
Roser 169 «ooo | ov
oy 90 ۵۵66 مسا[
o
Caw 98 16500 | سا
۹ 6 هه | eon
©
Ok preparers, Ore ©0006
صفحه 32:
= Union: .نا
(select mgr from D where “Find names of people
dname=“toy’) who are managers of either the
a toy or the sales department.”
where dname تحت دوز(
‘se. =
(select mgr from D where dname=‘téjtd names of people
intersect who are managers of both the
(select mgr from D where dnarigy and the sales departments.”
= Esaept: -
(select mgr from D where dname='té¥tkd names of people
except who are managers of the
(select mgr from D where dnazify but of the sales department.
‘sells’);
Orb سم موس Orer DOS
صفحه 33:
= The UNION, INTERSECT, and EXCEPT operators use
the set semantics, not bag semantics.
= To keep duplicates, use “ALL” after the operators:
UNION ALL, INTERSECT ALL, EXCEPT ALL
(SELECT ssno FROM
studentALL
UNION
(SELECT ssno FROM ta);
Student (ssno, name) TA (ssno, name) Result
معط Name ‘Sao ‘Name 111 1:13
Tit Tom 111 Tom. 222 111
222 Jack 22 Jak 24 222
3 ‘Mary, 55 ۳3 555, 222
AAA
555
000 سم موس Orb
صفحه 34:
۲ ۳۳۲۲۲۲۲۲۲۲۲۲ تا
(Byqreyetioa صصخ
= MIN, MAX, SUM, COUNT, AVG
‘input: collection of numbers/strings
(depending on operation)
“ output: relation with a single attribute with a
single row
“What is the minimum, maximum,
average salary of employees in the toy department”
SELECT MIN(sal), MAX(sal),
AVG(sal)
FROM Emp, Dept
WHERE Emp.dno =
Dept,dno,,.and..D.dname =
err F
صفحه 35:
2
00ل
= Sometimes we have to select data from
two or more tables to make our result
complete. We have to perform a join
"Tables in a database can be related to
each other with keys. A primary key is
a column with a unique value for each
row. The purpose is to bind data
together, across tables, without
repeating all of the data in every table.
Orb سم موس Orer DOS
صفحه 36:
2
IDOER JO1W
© Dre IDDER JO10 returcs ol rows Prov boik tubles where
ON Employees.Employee_ID=Orders.Employe:
Product
Printer
Table
Chair
there is a wich. |
If there are rows in Employees that do not have matches in
Orders,
those rows will not be listed
Employees?
Employee ID [Name
or Hansen, Ola
= EEE ‘T Employees.Name, Orders.Product
Nee Employees INNER JOIN
Svendson,
03 ‘Stephen Orde:
64 Pettersen, Kari Result
]
Name
Product | Employee 1D
Prod. Hansen, Ola
2 printer’ 9 Svendson, Stephen
557 10 5 Svendson, Stephen
565 سنت 5 Deb 5 20001
صفحه 37:
a
LeP\Right Uoic
© DPke LEP LOI returcs dl ke rows Proce tke Pirst tobe (Bopbyers), even P kere oe
se watches ta he sevped table (Orders).
© Dhe RCW JO rete ol ke اجره سا مت من ble (Orders), eves Pere ore
ww wnuckes i he Prot thle (Bopbyers).
LECT Employees.Name, Orders.Product مس Product
OM Employees LEFT JOIN Orders Tae OE aE
| Employees.Employee_ID=Orders.Employee_I— عي
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari
SELECT Employees.Name, Orders.Produci
FROM Employees RIGHT JOIN Orders | “2™* Predict
oN Hansen, Ola Printer
Employees.Employee_ID=Orders.Employe | $¥2ndsen. Stephen Table
eID ‘Svendson, Stephen Chair
000 سم موس Orb
صفحه 38:
A
INSERT INTO
= The INSERT INTO statement is
used to insert new rows into a
NSERT OAS table_name VALUES (value1, value2.....)
‘ specify the columns for which you want to insert «
INSERT INTO table_name (column1,
column2,...)
VALUES (value1, value2.,....)
Orb سم موس Orer DOS
صفحه 39:
a
۱6 عدا" مويو |“
LastName | FirstName | Address | City
Pettersen _| Kari Sto20 | Stav
INSERT INTO Persons VALUES (‘Hetland’,
‘Camilla’, 'Hagabakka 24', 'Sandnes')
FirstNam ۳
issewawe |e Address | City
Pettersen _| Kari 56020 | Stav
Hetland [Camilla Haga 24 | Sandnes
Orb سم موس Orer DOS
صفحه 40:
Sh
UPDATE
OPOONE statewerdt is used to دحك لا
wodity the data ict a table.
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value
Orb سم موس Orer DOS
صفحه 41:
city
Stav
Address
Kirk 56
Sto 67
Person:
FirstNam
LastName e
Nilsen Fred
Rasmussen
UPDATE Person
SET FirstName = ‘Nina’
WHERE LastName = ‘Rasmussen’
LastName | FirstName | Address city
Nilsen Fred Kirk56 Stav
Rasmussen _| Nina Sto 67
PDATE Person
ET Address = 'Stien 12', City = 'Stav'
HERE LastName = ‘Rasmussen’
LastName | FirstName | Address city
Nilsen Fred Kirk56 Stav
Rasmussen | Nina Stien 12 Stav
Orb سم موس Orer DOS
صفحه 42:
2h
DELETE
= Dhe OGLEVE statewedt is used to delete rows
faa table.
DELETE FROM table_name
WHERE column_name =
some _ value
LastName | FirstName | Address city
Nilsen Fred Kirk56 Stav
Rasmussen | Nina Stien 12 | stav
DELETE FROM Person WHERE LastName =
‘Rasmussen’
LastName | FirstName | Address | city
Nilsen Fred Kirk 56 Stav
Orb سم موس Orer DOS
صفحه 43:
a
Guwwarp
® Okv wes dotubuses insted oP Pile systews,
wht ure the pro-pous, whe to use Buck
© Okot is o relics database? Wow ooo pou
® Okat ip GQL? Oka ae OOL ud OOL? Okat
stiteweuts une retoted ty ruck GGL nue
Orb سم موس Orer DOS
صفحه 44:
000 سم موس Orb