Ch.5 Entity-Relationship Model (1)

1. Ch.5 Entity-Relationship Model (1) Peter P. Chen 1976

2. What to Learn • Database System Overview • Entity-Relationship diagram • Relational Data Model • Structure & Constraints • SQL language • How to use Commercial DBMS’s • MS Access • MS SQL Server • Normalization (정규화) • Transaction & Concurrency (동시성) Database System

3. What to learn • Database design • Entity-Relationship Diagrm • Entity • Attribute • Relationship • Diagram 그리기 • Logical database design • 7 steps Database System

4. Real World E-R diagram conceptual modeling conceptual world data modeling system boundary logical schema data structuring computer world physical schema stored database Database Design (1) Database System

5. Database Design (2) p.258 Database System

6. Database Design (3) • Conceptual design ERD (entity-relationship diagram) • Logical design • ERD->Relations mapping • Normalization logical schema • Physical design physical schema • Transaction design etc. Database System

7. E-R model, Chen, 1976 (Entity) • Entity any distinguishable object to be represented in the database visible vs. invisible entities (student, book, department, course) regular vs. weak entities (employee, building, dependent, apartment) p. 271 Database System

8. E-R model (Attribute) • Attribute a characteristic of an object with which we describe the object Attributes are characteristics common to all the entities or relationships of a given type • simple vs. composite attributes • key attribute • single-valued vs. multi-valued attributes • stored (base) vs. derived attributes 판매액 = 판매량 * 가격 p. 269 Database System

9. E-R model (Relationship) • Relationship an association among entities • degree number of entity types involved in the relationship p. 274 Database System

10. E-R Model (Relationship) • example of ternary relationship a) supplier S1 supplies part P1 b) supplier S1 supplies to project J1 c) project J1 uses part P1 Above three statements do not say thatd) supplier S1 supplies P1 to project J1. If S1-P2-J1, S1-P1-J2, S2-P1-J1 (S1-P1), (S1-J1), (P1-J1) statements a), b), c) are still correct but we cannot validly infer statement d). Database System

11. E-R Model (Relationship) • cardinality maximum number of elements on each side of a relationship p. 276 Database System

12. E-R Model (Relationship) • examples of cardinality • one-to-one 학과 ←학과장→교수남편←→아내, 교수←→연구실 • one-to-many 학과←소속→교수, 학생←지도→교수,판매원←담당→거래처 • many-to-many 학생←수강→과목, 부품 ←구성→부품,학생←구독→신문 Database System

13. E-R Model (Relationship) • total vs. partial participation 학생←지도→교수, 교수←사용→연구실 • Notes • entity vs. attribute publisher (pub_id, pub_name, location, phone)book (book_id, title, author, pub_date, publisher) • entity vs. relationship 물품←주문→고객 : 주문 as a relationship 주문←(구매 또는 결재)→고객 : 주문 as an entity 주문 ← 구성 → 물품 : 주문 구성 Database System

14. E-R Model (Notation) p. 285 • Notation p. 285 Database System

15. E-R Model (Other Notation) • Crow’s feet Notation (p .287) Database System

16. E-R Model (Other Notation) • Crow’s feet Notation (p .287) Database System

17. E-R Model (Other Notation) • Crow’s feet Notation (p .289) Database System