Entity-Relationship ?a?ade??

1
Entity-Relationship ?a?ade??µata

• ?????f???a?? S?st?µata ?a? ??se?? ?ed?µ????
• F???t?st???? 1
• ?aµ?a??? ?at??a?t?????

2
?pa??????
3
?pa??????
4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
?a??de??µa
13
?a??de??µa

• S?efte?te µ?a efa?µ??? p???s?? ß?ß???? (p.?.
  amazon.com). T????µe ?a µ??te??p???s??µe t?
  p??ß??µa a?t? ???s?µ?p????ta? t? µ??t??? ?-S.
• S????afe?? ß?ß???? (name, address, URL)
• ??ß??a (title, year, price, ISBN)
• ??d?t???? ????? (name, address, phone, URL)
• ?e??te? (name, address, email, phone)
• ?p????? ß?ß???? (code, address, phone)
• ?a????a a????? (basketID)

14
??s?
15
(No Transcript)
16
??ad???? se ???ad????
17
???????µ???t?ta
18
??µ??????a p??a?a

• ??µ??????a t?? p??a?a Students. ???e ped??
  ???sµ?? d????eta? ?a? ep?ß???eta? ap? t? S???
  ???e f??? p?? e?s????µe/e??µe?????µe µ?a p?e??da.
• Enrolled p??a?a? ??at?e? p????f???e? ??a ta
  µa??µata e??? f??t?t?.

CREATE TABLE Students (sid CHAR(20), name
CHAR(20), login CHAR(10), age INTEGER,
gpa REAL)
CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2))
19
??sa????/??a??af?

• ?p????µe ?a e?s????µe µ?a p?e??da

INSERT INTO Students (sid, name, login, age,
gpa) VALUES (53688, Smith, smith_at_ee, 18, 3.2)

• ??a??af? p?e??d?? p?? ??a??p????? µ?a s??????
  (e.g., name Smith)

DELETE FROM Students S WHERE S.name Smith
20
?e?????sµ?? a?e?a??t?ta?
CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid) )

• For a given student and course, there is a
  single grade. vs. Students can take only one
  course, and receive a single grade for that
  course further, no two students in a course
  receive the same grade.
• Used carelessly, an IC can prevent the storage of
  database instances that arise in practice!

CREATE TABLE Enrolled (sid CHAR(20) cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid), UNIQUE (cid, grade) )
21
?e?????sµ?? a?e?a??t?ta?

• ???? f??t?t?? t?? p??a?a Students ep?t??peta? ?a
  e???af??? ??a µa??µata.

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students )
Enrolled
Students
22
?e?????sµ?? a?e?a??t?ta?

• Te??e?ste t??? p??a?e? Students and Enrolled. ??
  sid st?? Enrolled e??a? ???? ??e?d? p??
  a?af??eta? st?? Students
• ?? ?a ???e? a? µ?a ??aµµ? µe a??pa??t? student id
  e?sa??e? st?? Enrolled? (Reject it!)
• ?? ?a ???e? a? µ?a ??aµµ? st?? Students
  d?a??afe??
• Also delete all Enrolled tuples that refer to it.
• Disallow deletion of a Students tuple that is
  referred to.
• Set sid in Enrolled tuples that refer to it to a
  default sid.
• (In SQL, also Set sid in Enrolled tuples that
  refer to it to a special value null, denoting
  unknown or inapplicable.)
• ?a??µ??a a? t? primary key t?? Students e??µe???e?

23
?e?????sµ?? a?e?a??t?ta?

• SQL/92 and SQL 1999 ?p?st??????? ??a ta 4 options
  ?ta? ????µe deletes ?a? updates
• NO ACTION default (delete/update is rejected)
• CASCADE (also delete all tuples that refer to
  deleted tuple)
• SET NULL / SET DEFAULT (sets foreign key value
  of referencing tuple)

CREATE TABLE Enrolled (sid CHAR(20), cid
CHAR(20), grade CHAR(2), PRIMARY KEY
(sid,cid), FOREIGN KEY (sid) REFERENCES
Students ON DELETE CASCADE ON UPDATE SET
DEFAULT )
24
?s??s? 3.8

• Emp(eidinteger, enamestring, ageinteger,
  salary real)
• Works(eid integer, did integer, pct_time
  integer)
• Dept(did integer, dname string, budget real,
  managerid integer)

25
??s? - 1
26
??s? - 2
27
??s? 3,4,5
28
??s? - 6
DELETE
29
?s??s? 3.12 ??s?
30
?s??s? 3.12 - ??s?
31
?s??s? 3.12 - ??s?
32
?s??s? 3.12 - ??s?