??ER????????

1
??ER????????
? 3 ?
2
????

• ??????????
• ER?????
• ?????
• ?????????
• ????????????
• ??????????
• ?????
• ER??????
• (min, max)?ER????
• UML???????
• ??????

3
?????????? (1/2)
4
?????????? (2/2)

• ???????(requirements collection and
  analysis)????????????????????
• ????(conceptual design)??????????????????????(con
  ceptual schema)
• ????(logical design)????????DBMS???????????,?????
  ??????????????????
• ????(physical design)????????????????????????????
  ???

5
???????

• ???????
• ????? (database design)
• ?????????????
• ??????????(conceptual schema)???
• ?????? (application design)
• ??????????????????
• ????????????

6
COMPANY????? (1/2)

• ?????
• ?????????? (DEPARTMENT) ????
• ?????????????,?????????????
• ??????????????????
• ?????????????
• ????????????? (PROJECT)?
• ?????????????????????

7
COMPANY????? (2/2)

• ?????(?)
• ????????(EMPLOYEE)???????????????????????
• ??????????????,?????????????
• ?????????,??????????????
• ????????????????
• ???????????(DEPENDENT)?
• ???????????????????????????

8
ER??????
9
ER?????

• ER???????????
• ??(entity)?????????????????,?????????????????????
  ?
• ??(attribute)?????????,???????????????
• ????????????(value),??????????????????
• ??????????????? ,?????? (data type),???????
• ??(relationship)??????????????????????,?????????

10
?????

• ?3.3Employee??e1?Company??c1,??????

11
????? (1/4)

• ???? vs. ????
• ??(simple or atomic)????????
• ??,Ssn ? Sex
• ??(composite)????????????????
• ??,EMPLOYEE???Address??????Street_address?City?Sta
  te?Zip
• ???????????????

12
????? (2/4)

• ???? vs. ????
• ??(single-valued)????????
• ??,Age(??)???????
• ??(multivalued)???????????
• ??,???Colors(??)??,???College_degrees(????)??

13
????? (3/4)

• ???? vs. ????
• ??,Age(??)?Birth_date(??)???????
• ??,Age????????????Birth_date????????
• Birth_date????????(stored attribute)
• Age????????(derived attribute)
• ????(Complex attribute)
• ??,????????address_phone??
• ????????????,?????????????
• ??Phone?Address??????????

14
????? (4/4)

• ??(NULL)?????????????????
• ??,College_degrees???????????????
• ??????????????
• ???(not applicable)
• ????????,?College_degrees?NULL
• ??(unknown)
• ??????(Exists but is missing)
• ????Height(??)???NULL
• ???(Not known)
• ????Home_phone(????)???NULL

15
????????? (1/4)

• ????(entity type)??????????????EMPLOYEE?????PROJE
  CT????
• ????(entity set)?????,??????????????????????

16
????????? (2/4)

• ???? (key attribute)?????????????,???????????????
  ???
• ??,EMPLOYEE?Ssn
• ????????????????
• ???????????
• ??,Registration?CAR???????,??? Number?State???????
  ?
• ?????(weak entity)???????????,????

17
????????? (3/4)

• ??????????????????
• ??,CAR????????????Vehicle_id ?Registration??

18
????????? (4/4)

• ??? (domain)??????????????????(value set)
• ?????????????(data type)???,????(integer)???(strin
  g)????(boolean)????(float)???(enumerated)?????/???
  ?

19
COMPANY???????? (1/2)

• ???????,??????????
• DEPARTMENT
• ??Name?Number?Locations?Manager
  ?Manager_start_date
• ??Location?????
• ??Name?Number?????,????????Name?Number?????????
• PROJECT
• ??Name?Number?Location ? Controlling_department
• Name?Number??????????

20
COMPANY???????? (2/2)

• EMPLOYEE
• ??Name?Ssn?Sex?Address?Salary?Birth_date?Departme
  nt ? Supervisor
• ??Name?Address???????(???????????)???,Name
  ????First_name?Middle_initial?Last_name
• DEPENDENT
• ??Employee?Dependent_name?Sex?Birth_date ?
  Relationship(????????)

21
???????????
22
??????? (1/3)

• ????????????????????????????
• EMPLOYEE Franklin Wong????Research DEPARTMENT
• EMPLOYEE John Smith???Productx??
• ???????????????(relationship type)
• WORKS_ON?????EMPLOYEE???PROJECT???????
• MANAGES?????EMPLOYEE???DEPARTMENT???????

23
??????? (2/3)

• ???,???????????????????,?????????
• DEPARTMENT?Manager???????????????
• PROJECT?Controlling_department???????????????
• EMPLOYEE?Supervisor??????????(???????)
• EMPLOYEE?Department?????????????

24
??????? (3/3)

• ??????? (degree) ????????????
• ??,MANAGES?WORKS_ON???? (binary)????
• ??????????????,?????????????
• ??,MANAGES?WORKS_FOR?EMPLOYEE?DEPARTMENT??????????
  ,???????,???????

25
WORKS_FOR?????????
26
WORKS_ON?????????
27
?????????

• ????(relationship type)
• ??????????(schema description)
• ??????????????
• ????????
• ????(relationship set)
• ????????(relationship instance)?????
• ?????????
• ?ER??,???????????
• ????????????
• ????????????

28
?????COMPANY???

• ????????,?????????(binary relationship)
• WORKS_FOR(?EMPLOYEE?DEPARTMENT?)
• MANAGES(?EMPLOYEE?DEPARTMENT?)
• CONTROLS(?DEPARTMENT?PROJECT?)
• WORKS_ON(?EMPLOYEE?PROJECT?)
• SUPERVISION(?EMPLOYEE?EMPLOYEE?)
• DEPENDENTS_OF(?EMPLOYEE?DEPENDENT?)

29
???????

• ??????,????????????????? (???3.8)
• DEPARTMENT?Manager ? MANAGES
• EMPLOYEE?Works_on ? WORKS_ON
• EMPLOYEE?Department ? WORKS_FOR
• etc.
• ???????????,?????????????,??
• MANAGES?WORKS_FOR???EMPLOYEE?DEPARTMENT??????????

30
?????????

• ?????????????,????????????(role)
• ??,???WORKS_FOR?,EMPLOYEE???????????,?DEPARTMENT??
  ?????????
• ????????????????????
• ??,EMPLOYEE?SUPERVISION????????????????,????????
• ?3.11?,???1???????????,????2????????????

31
???????
32
??????? (1/3)

• ??? (cardinality ratio)?????????????????????
• ?????????????
• ??? (11)?3-12
• ??? (1N) ???? (N1)?3-9
• ??? (MN) ?3-13

33
????????
34
????????
35
????????
36
??????? (2/3)

• ????(participation constraint)???????????????????
  ??????????
• ?????????(minimum cardinality constraint)
• ? (????,??????)
• ??? (???,?????)

37
??????? (3/3)

• ?????????
• ????(total participation)
• ?????????????????????????,??3-9
• ????????(existence dependency)
• ?ER??,??????
• ????(partial participation)
• ??????????????????????????,??3-12
• ?ER??,??????
• ??????????????????????????(structural constraint)

38
???????

• ???????????,??
• WORKS_ON???Hours??
• ????EMPLOYEE???PROJECT??????
• MANAGES???Start_date??
• ?????????????????
• ???????
• ?11????????,???????????????????
• ?1N????????,?????N?????????
• ?MN????????,???????????

39
????? (1/3)

• ?????(weak entity type)?????????????
• ?????(strong entity type)????????????
• ???????????????????
• ?????????????? (identifying) ???? (owner) ????
• ??????????????
• ??????????? (partial key)
• ???????????????

40
????? (2/3)

• ??????????????????????
• ??,????????????????????
• ??,DRIVER_LICENSE(??)??,???????(License_number),??
  ??????,???????PERSON????,???????
• DEPENDENT(??)????
• ????????????Name?Birth_date?Sex?Relationship??????
  ,???????????
• EMPLOYEE?????????
• ???????DEPENDENT_OF
• DEPENDENT?????Name

41
????? (3/3)

• ?ER??????
• ?????????
• ????????
• ???????(dashed line)???(dotted line)

42
?????COMPANY??? (1/4)

• MANAGES
• ???EMPLOYEE?DEPARTMENT???11????
• EMPLOYEE????????
• DEPARTMENT????????(??????????????)
• ????????Start_date??
• WORKS_FOR
• ???DEPARTMENT?EMPLOYEE???1N????
• ????????

43
?????COMPANY??? (2/4)

• CONTROLS
• ???DEPARTMENT?PROJECT???1N????
• PROJECT????????
• DEPARTMENT????????(??????????????????)
• SUPERVISION
• ???EMPLOYEE(????)?EMPLOYEE (????)???1N????
• ????????(????????????,????????????)

44
?????COMPANY??? (3/4)

• WORKS_ON
• ???EMPLOYEE?PROJECT???MN????
• ????????(??????????????,?????????????????)
• ????????Hours??
• DEPENDENTS_OF
• ???EMPLOYEE?DEPENDENT???1N????
• ???????DEPENDENT?????
• EMPLOYEE????????
• DEPENDENT??????

45
?????COMPANY??? (4/4)

• ?????6??????,???3.8????????????
• ?DEPARTMENT?
• ??Manager?Manager_start_date
• ?PROJECT?
• ?? Controlling_department
• ?EMPLOYEE?
• ?? Department?Supervisor?Works_on
• ?DEPENDENT?
• ?? Employee

46
ER??????
47
???ER????? (min, max)

• (min, max)?????????(min, max)???,??????R?????E???
  ????
• ?E??????e??R???min????max?????
• min0??????,?mingt0??????
• ??? (????)min0, maxn
• 0 ? min ? max ? max ? 1

48
(min, max)??????

• ?????????????,????????????(MANAGES)????
• ??EMPLOYEE??MANAGES??????(0, 1)
• ??DEPARTMENT??MANAGES??????(1, 1)
• ?????????(WORKS_FOR)????,???????????????
• ??EMPLOYEE??WORKS_FOR??????(1, 1)
• ??DEPARTMENT??WORKS_FOR?????? (0, n)

49
(min, max)????????
50
(min, max)????COMPANY????ER?
51
UML????????? (1/3)

• UML???
• ?ER?????????UML???(class)
• ?ER?????UML???????(object)
• ??(class)???????,??????
• ???????? (class name)
• ??????????????? (attribute)
• ?????????????? (operation)

52
UML????????? (2/3)

• ??????????(association),???????????(link)
• ??????????????
• ???????????(link attribute),??????????,???????????
  ??
• ?????????..???(min..max) ???
• ??()??????????

53
UML????????? (3/3)

• UML????????(association)???(aggregation)
• ?ER???????????
• ????????????????????
• ????????
• ??3.16?,??(DEPARTMENT)????
• ??3.16?,??(PROJECT)???
• ?????????(qualified association)?????(qualified
  aggregation)??????
• ???????????????
• ??3.16?DEPENDENT???EMPLOYEE??

54
?UML?????????COMPANY
55
???????

• ????2????????? (binary)
• ????3????????? (ternary)
• ????n???????n? (n-ary)
• ??n??????? n ?????
• ???????????????????????
• ??????????????,???????????

56
n ??????

• ?????????
• ?3.17(a)?????? SUPPLY
• ?3.17(b)??????,CAN_SUPPLY? USES?SUPPLIES
• CAN_SUPPLY? USES?SUPPLIES???????(s, p)?(j, p)?(s,
  j)????,????????SUPPLY????(s, j, p)????
• ?????????????????ER??,???SUPPLY???????,???????????
  ,??3.17(c)
• ????????????(?? n ?)??????,??3.19??
• ??????????????????

57
?????????
58
????????????
59
???????ER?
60
??????????? (1/3)

• AIRPORT
• The database represents each AIRPORT, keeping its
  unique AirportCode, the AIRPORT Name, and the
  City and State in which the AIRPORT is located.
• FLIGHT
• Each airline FLIGHT has a unique number, the
  Airline for the FLIGHT, and the Weekdays on which
  the FLIGHT is scheduled
• For example, every day of the week except Sunday
  can be coded as X7.
• AIRPLANE
• For each AIRPLANE, the AirplaneId, Total number
  of seats, and TYPE are kept.
• AIRPLANE_TYPE
• For each AIRPLANE TYPE (for example, DC-10), the
  TypeName, manufacturing Company, and Maximum
  Number of Seats are kept.
• The AIRPORTs in which planes of this type CAN
  LAND are kept in the database.

61
??????????? (2/3)

• FLIGHT_LEG
• A FLIGHT is composed of one or more FLIGHT LEGs
• For example, flight number CO1223 from New York
  to Los Angeles may have two FLIGHT LEGs leg 1
  from New York to Houston and leg 2 from Houston
  to Los Angeles.
• Each FLIGHT LEG has a DEPARTURE AIRPORT and
  Scheduled Departure Time, and an ARRIVAL AIRPORT
  and Scheduled Arrival Time.
• LEG_INSTANCE
• A LEG INSTANCE is an instance of a FLIGHT LEG on
  a specific Date
• For example, CO1223 leg 1 on July 30, 1989.
• The actual Departure and Arrival AIRPORTs and
  Times are recorded for each flight leg after the
  flight leg has been concluded.
• The Number of available seats and the AIRPLANE
  used in the LEG INSTANCE are also kept.

62
??????????? (3/3)

• RESERVATION
• The customer RESERVATIONs on each LEG INSTANCE
  include the Customer Name, Phone, and Seat
  Number(s) for each reservation.

63
BANK????ER?
64
BANK???????? (1/2)

• Each BANK has a unique Code, as well as a Name
  and Address.
• Each BANK is related to one or more
  BANK-BRANCHes, and the BranhNo is unique among
  each set of BANK-BRANCHes that are related to the
  same BANK. Each BANK-BRANCH has an Address.
• Each BANK-BRANCH has zero or more LOANS
• Each BANK-BRANCH has zero or more ACCTS.

65
BANK???????? (2/2)

• Each ACCOUNT has an AcctNo (unique), Balance, and
  Type and is related to exactly one BANK-BRANCH
  and to at least one CUSTOMER.
• Each LOAN has a LoanNo (unique), Amount, and Type
  and is related to exactly one BANK-BRANCH and to
  at least one CUSTOMER.
• Each CUSTOMER has an SSN (unique), Name, Phone,
  and Address, and is related to zero or more
  ACCOUNTs and to zero or more LOANs.

66
COMPANY????ER?
67
COMPANY????????

• An employee may work in up to two departments or
  may not be assigned o any department.
• Each department must have one and may have up to
  three phone numbers.
• Each department can have anywhere between 1 and
  10 employees.
• Each phone is used by one, and only one,
  department.
• Each phone is assigned to at least one, and may
  be assigned to up to 10 employees.
• Each employee is assigned at least one, but no
  more than 6 phones.

68
COMPANY????(min, max) ER?
69
COURSE????ER?
70
COURSE????????

• A course may or may not use a textbook
• A text by definition is a book that is used in
  some course.
• A course may not use more than five books.
• Instructors teach from two to four courses.
• Each course is taught by exactly one instructor.
• Each textbook is used by one and only one course.
  
• Add the relationship ADOPTS between INSTRUCTOR
  and TEXT
• An instructor does not have to adopt a textbook
  for all courses.
• An instructor does not adopt more than three
  textbooks for each course.

71
COURSE????(min, max) ER?