Lec -5

steps make
diagram
* to ER 1-

① identify Entity sets .

② identify attributes & their types .

③ "
Rel
"
& constraints
↳ Mapping
↳ Participation
__
*
F-R-moddof-B-ank.mg System
① (
Banking system - Branches .
maf )
② Bank → customers .

accounts & take loan

③ Customers -
.
,
.

⑨ Customer associated with some banker .

⑦ Bank has employees .

⑥ Accounts -
saving all
\ current a
/ c.

⑦ Loan originated by branch

loan ≥ 1 customers

↳
.

payment schedules .
① Entity sets
① Branch ② Customer ⑦ Employee
⑨ Saving A/c ⑤ Current a/c
⑥ Loan ⑦ payment Kwan) ( weak entity?

② Attributes : -

① branch → Lame , city arrests liabilities

, ,

② Customer → cent - id
,
name
,
address ,
contact no .

DOBT age ,
↓
↓
↓ Composite .

multicolored
.

derived .
③ Genployee → acid, name
,
contact no ,

, dependent name
,

years of service
,
stat date
- ↓
↓ mltivalued
derived altnhr single valued .
.

⑨ Saving account → intent rate -

, daily withdrawal limit .

Unmet a/c
⑤ →
afÉ , pertanaetwn changes ,

amount
overdraft - .

⑥ Generalized Entity
" "

Account → ace - no ,

,
balance

⑦ Loan →
lber ,
amount

⑧ Weak
Payment Payment date , amount
Entity → no .

,
.
③ Rel "
& constants .

① Customer borrow loans ,

M : N
=

② Loan originated by branch .

N % r

③ Loan loan -

payment Payment .

☆ % N
⑥ Customer deposit account
M % N

⑤ Customer banker Employee .

N % A

⑥ Cenployee manage by Employee .

N % I
 st⑦ a⊕
ÉÉÉ
→m⑤µi-tbran ⁿᵈ①
cuw①↓d →④
I

⑦
-

originated Paymg PYmntd①

¥ᵗʰᵈ
by
1

\ / i•n loan

t.nmton.IM M
borrow
_tLoanT±
loan=⊖am⑦
-

payment

banker

,w deposit aecont-mn-yhfybalane.ec
n /accounts
± / ±

④?PyT
managed
by
is -
a

uᵗa dai1ynthdm
limit

iammtak=-P lsay.mg#-
"
÷÷÷ changes
have

ovw-draftamowt@ pinterest.at#
?⃝
?⃝
?⃝
"#

① Online delivery system .

② University
 Lec -
6

Facebook DB formulate ERIdel

using
-
.

* F-
R_dgran
① Features .
& use case .

① profile → men _
profiles → friends ,

② her can post

③ Post → contains → tent content, images ,
videos .

⑨ Post → like ,
comment.
① identify entity sets .

① her _
profile ② her _
post ③ post comment
_

⑨ post like_

② AHribypes .

email ,
① her _ profile → Name
, heme , pound ,
contact no .

←
DOB
↓ t
composite . , age .

mvltrvdwd ,
mnltivdued ,

↓
derived .

② her _
post → pustid tent content , image ,
videos
,
mdltivded Intruded ,

created _
timestamp , madifed - time stop . .
③ post-commet-post-comm-t.at ,
tentcontent, timestamp
.

⑨ post _
like → pytkeid ,
timestamp

③
"

Rel rants

① Mer _
profile frienship her _
profile .

M ! N

② user _
profile posts user -
post .

p i N
=
③ her _
profile can post like
_ ,

1 : N

⑨ user _ pnfhe . Comments post _

comment
.

1 :
=N

⑤ her _
post has post comment
_

1 ! N
=

⑥ Mer _
post has pvstlike
1 : N
=
 Toft t friendship
F
,P°st_c◦mmÉt[ comments
D④
qt /p◦At"J
/ -§ke
n
N m

T user
_
profile
'
can I post

/
II
ee⊕)⑦
'
1
has posts
'
④④
/ N
has
'
user
post
I \
×ⁿᵗ① t¥⑦⑧"
modified
F.BE#D
?⃝
 Lec
-

Rdatrmddel : _

anti ☐
Customer
_

tables ↳ -
Name
-
addnen
-
Contact

table → Relator →
Cust ID
- Name addm Contact

① I Lahshay
- - -

888 -
-

② 2 Raj
-
-
-
- -
-
2) Degree oftable → No .

ofaltnbul .

Cardinality → Total no .

of tple .

→ DB days ↳

Model → ER diagram
① ER .

I
Relational model
② →

↓
③ RDBMS → software
=
DBMS
.

↓
software implantation of RM_
Oracle etc
MySQL ,
Msaccen
,
.
 °ⁿʰ%i!@ⁿʳI
#,
|p1a-
→
cotaᵈ _QRR

y←l0RDE_tmutn@
① Customer ( ¥-11 , name
,
address contact no .
)

② ORDER ( order _
ID timestamp ,
delivery -
date
?
,
?⃝
?⃝
 For
→ Ky →
g→
7 k¥ →
Primorsky → each data point
wnovely ldentfz

f / / addmf f
ant _
' D Name contact email

① Supertax Crane extant @ email]

£7
y , ,
ane
,

Cut } ] [ first
NY ?
- ID email {
, _

Name ,
]
contact, email

Kraj ,
8883
{ Raj ,
811 ?
② C. K .
→
@ ]
intact
Emt _
ID + - ☒ ietacts

@!t_iD,ema
, ,

③ PK
-

→ ✓
P.K.
Cnt _
I D) →

A K) P.K.
① K → ⇐ →
- -
⑨ Foreignkiy f- -

Curtner ( cmt_D ,
name
, address ,
contact no )
order ( oD , timestamp , ddwy date
cust-IDI.F.IS
,

Referenced Ren

% "f÷
"
Cmt _
ID Name addsn contact / Parent table .

Jo
} dada - -

•ⁿ÷ñ,÷-
"" •

/
table

/ /
order - ID
Timestamp delivery date
-
Cust - ID .

21
/ Referencing
22 red ?
23
I
A-
Computer
-
A
surrogates >

Table of school A
¥0 reg _
no
,
name

|
name 1 101 Ram
reg.no

[
2 Monika
101 Ram 102

3 103 tata
Monika
4 ABIOI Tommy
103 Tata .

5 AB / 02 Nota .

table of school B

/
name
Hi
.

Tammy
A- 13102 Nota .
CR

/
!:!:"" ⊖
1 A - -

HE 1 = .
*
Referential c-omtra.int

/ :-|
Customer Cmt add contact

f
! Name
-
no .

"

Phatrel
ᵗᵈᵈ "
'

' Kaz
I = :
referenced }Dada-#
%

ORDER 1-

D) T.mstanpydetreydateycust-ID-F.la
Order '

↳
_
.

Child 21 - -

Ruth/
-

l
-
-

22 -
- - -

23 * Null
Refereeing table
/
- -
-

1
-
_
.

① Insert constraint 1- value can't be inserted in child

- table if the value is not lying in

parent table .

② Delete constraints -3 Value can't delete from parent table

table
if the value is
lying in child .
a) ON
Ddetecasade 1-

delete vahe from parent table if the

Can we value is
lying
in the child table w/o violating dete constant ?

→ delete valve from parent table → delete

cowepudng
from child table too
entry
.

Create table order ( order

- ID int Pik
,
_
. -

,
- . -

,
cut -
ID int
customer
referencing
on delete cascade);

Can F. K have Null value ?

a)
0N DeleteNull
→
delete value from parent table → Put compounding
F. K rake Null .
*
key Emts 1-
attribute /
By defeat column can
① Noticed → be Null
a

↳
.

enforce a column to not accept

Nudd .

Create Table Customer (

int Not Null ,
ID
Name rancher ( 50) Not Null ,

Age int
,

%
② Unique comfort ,
↳ ensure all valves in cot .
are
diff .

-
Both Unione & Rk constant provide uniqueness .

have unique constants table

you many per
-

may
But only one P K
. contents per
table .

Create Table Customer C

' D int Not Null
,

Name voucher [ To ) Not Null ,

Unique ( I
D)
5 ,
③ Default Emts
set default value
↳ of cot ,

create table customer (

:

Prime _
status int DEFAULT 0,

?
:
%

① Cʰ¥ ! -

limit ( Domain ?
-
vahe Range
C
÷
CHECK [
age >= 18 ?
>
⊕ Primary kymt-
→ Unlovely identif each tuple .

P.la/-- Null .

re e .

' '

PRIMARY KEY ( ID ?
i.
,
⑥ Foreign key constant

-ps rel "

btw 2 table .

Create table order (

PRIMARY KEY Corder _

ID)

FOREIGN KEY @ 1--1 D) Referencing

Customer Cent -

I
Lec-8-X-TrasfomatmfmERM.IE del .

]
loan → Loan - numb amount

weak Entity payment

_

/ /
payment
-
date
payment
-
amount

¥
 attribute
*
Comport attribute → sep . for each component .

Customers le

| / statehood / /
customer - name address at - add -
-

pincode add streetno

-

/
add streetname
-

* Multi value
- attribute

F-t-E-m.nu relative .

↑
]
emp-idfdname-fenp-id.ae
name

↓
Rk . .
☒
Generated -

method I
=

*
Aggregation
= -
-
-
-
-
-
-
-
-
-

,
,

I '

iemf-AE-lbm.TT
"

i
' - -
-

Entity
- -

1¥
- - - - - -

work
-

- on .

/managef
(
Table
m-gr-id-P-id-b-E-ig.pk
'

manages
,
 Unary
.RU#vship:rE..T--:.
*

we will add another attribute in

Employee table .

Urich will be F. K .

Emp
201
-
id
( name

- -
-

/ joining
-
-
date
-

/ Snp
205
_

mgr _
id

\
- - 205
202
- -
-

205
- - - 1 -
-
- , Null .

mi• 1Peno
* I :| →

Person / )
id
- ,
Name
, sponged
SFK}
 *
M →
com
④ Come ( id > Title ic◦mf-
M\prere
"

prerealid-E-idt-p.gg
②

4¥11 ↓
LED
?⃝
 * FB Relational Model

(1) User _
profile ( username ,
name -

first ,
name - last,
password ,
DOB )
(2) her -

profile -
email ( username SEK ] ,
email )

(3) user -

profile -
contact ( m#kÉÉr)
(4) friendship ( profile -
,
_
req # k3 profile accept# k3)
_
→ compound key

(5) post -
like t.p-st.li#id timestamp , ,
post id # k }
-

,
username { f. k3)

(6) user -

post / Post_id_ ,
created -

timestamp ,
modified timestamp
-

,
tent content
-

,
username
)
{ f. K ]
(7) user -

post image (
-

po_tidÉnage± )
(8) her -

post -
video ( _
po-t-idt.ES/video--ur )
(9) post - comment ( po_st--id, text content
-

, timestamp , post id -
username )
,

{ 8.1<3 [Jk)