Data World

1. Data World • - Database represents a real world which is changing continuously • - Data model is the procedure for representing a real world • 3 Data Worlds data modeling infornation modeling object properties values entity set attributes values record type field values real world(reality) conceptual world(concept) computer world(data)

2. Data World (cont'd) • Database and Real World database design information modeling conceptual world real world data modeling data structuring consistency stored database logical data structure

3. Data Model Concepts • Data model • - conceptual tools for describing data and data relationship • - specify the overall logical structure of database • Data models fall into three different group : conceptual data model, • logical data model and physical data model. • Conceptual data model (High-level) • - relationship between entity sets, not considering physical storage • device • - human oriented data model • - facilitate database design • - Entity-Relationship (E-R) model • - OO data model

4. Data Model Concepts (cont'd) • Logical data model (Representational, Implementation) • - describing relationship between data field (record-based) • - overall logical structure of a database • - high level description of the implementation • - hierachical model • collection of trees • - network model • collection of record types and links, i.e., graphs • - relational model • collection of tables

5. data modeling data structuring consistency stored database logical, representational model (logical data structure) physical, low-level model Data Model Concepts (cont'd) • Physical data model (Low-level) • - describe how data is stored in the computer • - e.g. record format, record orderings, and access paths

6. E-R Model • - by Peter Chen, 1976 • - based on a perception of real world which consists of a set of basic • objects called entities and relationships among these objects • - E-R diagram is graphical database structure in E-R model which • is consist of entity set, relationship, attribute and link • Entity • - an object that exists and is distinguishable from other objects • - ex) Yoon S. A. with social security number 700123-1684321 • - may be abstract, such as a holiday or a concept

7. E-R Model (cont'd) • EntitySet • - a set of entites of the same type • - ex) customer : set of all persons having an account at a bank • account : set of all accounts maintained at a bank • - rectangles, which represent entity sets • - entity sets need not be disjoint • a person entity may be an (bank) employee entity, • a customer entity, both, or neither • Attribute • - entity is represented by a set of attributes • - ex) customer entity set's attributes are customer-name, • security-number, street, and customer-city

8. E-R Model (cont'd) • Domain • - a set of permitted values • - ex) domain of customer--name : set of all text strings • of a certain length • Relationship • - an association among several entities • - ex) Harris(customer) 401(account) • Relationship Set • - a set of relationships of the same type • - {(e1, e2, ... , en) | e1E1, e2 E2, ... , en En} • - ex) CustAcct denote association between customers and accounts • - ternary relationship set CAB : Harris, 401, and Redwood

9. E-R Model (cont'd) • Attribute of Relationship • - relationship set can have attributes • - ex) CustAcct's (Harris, 401) has property {(date, 20 Sept 1995)}

10. Example 교수 교수번호이름 학과 지도 강의 과목 학생 등록 학번 이름 학년 과목번호 과목이름 학점

11. Constraints on Relationship Set • Cardinality ratio constraint • number of entities to which another entity can be associated • - one-to-one : for each entity in either set, there is at most • one associated member of the other set Manages Dept Emp e1 e2 e3 e4 . . d1 d2 d3 d4 d5 . . • . • .

12. Constarints on Relationship Set (cont'd) - many-to-one : each entity in E1(Courses) is associated with at most one entity in E2(Teachers), but each entity in E2 can be associated any number of entities in E1 taught-by Teachers Courses - many-to-many : no restriction export Products Countries

13. Constarints on Relationship Set (cont'd) • Participation constraint • the existence of an entity depends on the existence of • the relationship to another entity • - existency dependency • this means that if y is deleted, so is x subordinate entity, weak entity dominant entity, strong or regular entity

14. Constarints on Relationship Set (cont'd) account# Balance transaction# date amount Account Log Transaction dominant, strong subordinate, weak 462-21-23 3,000 1 11 Oct 1996 +50 262-45-20 6,000 4 12 Oct 1996 +350 645-44-09 5,000 7 12 Oct 1996 - 150 ... ...

15. Keys • it is important to be able to discriminate how entities and • relationships • Superkey • - a set of one or more attribute wich allow us to identify • uniquely an entity in the entity set • - may have unnecessary attributes to distinct • Candidate Key • - minimal superkey • Primary Key • - a candidate key chosen by the DB designer

16. E-R Diagram • graphical database structure in E-R model • - rectangle : (strong) entity set • - double rectangle : weak entity set • - ellipse : attribute • - diamond : relationship set • - line : (1) link attribute to entity set • (2) link entity set to relationship set • see Slide 2-11

17. Reducing E-R diagrams to Tables • (1) Representation of strong entity sets • D1 X D2 X ..... Dn • see Fig. 2-14, Fig. 2-15 Korth Book • (2) Representation of weak entity sets • {attributes of weak entity set} U {primary key of strong entity set) • see Fig. 2-16 • (3) Representation of relationship sets • {primary key set} U {attribute of relationship set R) • see Fig. 2-17, Fig 2-18

18. Reducing E-R diagrams to Tables (Ex) account# Balance date transaction# date amount Account Log Transaction (1) (2) (3)

19. Generalization There are similarities between the checking-account entity set and the saving-account entity set in the sense that they have several attribute in common. This commonality can be expressed by generalization. - Creation method : create a high-level entity set from two or more lower level entity set's common attributes - Usage : is used to emphasize the similarities among lower level entity type and to hide their differences - Inheritance : lower level entity set (saving-account and checking account) inherit the attributes of higher level entity set (account)

20. Generalization (cont'd) account number balance • saving-account is an • account account ISA account number saving- account checking- account account number balance interest- rete balance overdraft- amount

21. Generalization (cont'd) • Transforming E-R diagram including generalization to • tabular form • (1) Primary key of high-level entity set plus lower level entity sets • account + savings-account, checking-account • (2) Only lower level entity sets • savings-account, checking-account (including higher-level • attributes)

22. Aggregation One limitation of the E-R model is that is not possible to express relationship among relationships - Aggregation is an abstraction through which relationships are treated as high-level entities hospital 1 hospitalization 입원 m n 1 doctor treatment patients 치료

23. Hierarchical Data Model • Basic Concepts and Structure • - Hierarchical database consists of a collection of records which • are connected to one another records through links • - record : collection of fields (attributes) • link : inary relstionship (parent-child relationship) • * restriction : only one-to-many subject Hierarchical Definition Tree (subject database schema) prerequisite subject lecture student lecturer

24. Hierarchical Data Model (cont'd) subject • ordered tree • one-to-many C100 Database 3 prerequisite subject lecture (record type) L1 Tue-Thu 2-204 C99 L2 Mon-Wed 3-403 student S93002 Lee S. H. 85 lecturer S93002 Hur S. 92 P94 Kim C. S. Computer S93001 Park E. Y. 90 subject Database Tree (record occurrence)

25. Hierarchical Data Model (cont'd) • Characteristics • - parent-child relationship is one-to-many relationship • - ordered tree (left to right) • - n:m relationship can be represented by two 1:n representations • Restrictions • - all record occurrences except root record, must be connected • to parent record • * need to connect its parent for record insertion • * have to delete all its child record for record deletion • - one-to-many relationship : to associate with two its parent, • make another tree (m:n => two 1:n relationship)

26. Network Data Model • Basic Concepts and Structure • - Network database consists of a collection of records which • are connected to one another records through links • but, Data Structure Diagram (DSD) which represent logical • structure of network database, is network form • - extension of hierarchical data model • - CODASYL(Conference on Data Systems Language) DBTG • (Data Base Task Group) proposed the network data model formally • many-to many link is not allowed (implementation problem) • - Network Database System : • IDMS (Computer Associates) • TOTAL (Cincom Systems) • IDS II (Honeywell Information Systems)

27. Network Data Model (cont'd) • Data Structure Diagram (DSD) branch customer name assets city name street city CustAcct BrncAcct number balance account

28. Network Data Model (cont'd) • Set Occurrence (database value) • 1: n relationship • graph 305 500 226 336 177 205 155 62 402 100 408 300 Kim Daesin Pusan Uam 5,000,000 Pusan Chung Choongmu Seoul Namsan 2,000,000 Ulsan Lee Hanbat Taejon

29. Relational Data Model • Background • - Proposed by E. F. Codd (1970, ACM TODS) • - Non-procedual database language (easy to use) • - Standard database language : SQL • * ISO, ANSI, X/Open • * SQL, SQL89, SQL2, SQL3, .... • - Theoretical background (mathematics-based) • - Relational DBMSs : • Oracle - 2nd largest S/W maker in the world • Informix - UNIX RDBMS special group • Sybase - Client/Server DBMS • Ingres - ?

30. Relational Data Model (cont'd) • Overview • Relational model = structure + constraints + operation • Structure • - Domain : set of atomic values • - Relational scheme : set of attribute names • - Relation : any subset of the Cartesian product of domains • of attributes int relation scheme • Set of Tuples Workfor Ename Dname Smith Physics James Mathematics Employee E# Ename City 10 Smith LA 20 James NY

31. Relational Data Model (cont'd) - Terminologies table : relation row : tuple column : attribute - Arity(degree) number of attribute in the relation - Tuple ordered list of values from domain D1, D2, ... , Dn mapping from attribute names to values in the domains ex) (10, Smith, LA) m(E#) = 10, m(Ename) = Smith, m(City) = LA

32. Relational Data Model (cont'd) • Integrity Constraints • - Entity integrity constraint • no primary key can be null • - Referential integrity constraints (foreign key constraints) • foreign key : set of attributes which is the primary key • of another relation • (ex) Dept = (Dname, Manager, Budget) • Employee = (Ename, Bdate, Salary, Dname) • Every value of the foreign key must exist in the set of the • corresponding primary key values of another relation(Dept) foregin key

33. Relational Data Model (cont'd) • Operation • - Operation to manipulate a relation is needed • - There are two types of operation, Relational Algebra and • Relational Calculus • Relational Algebra (How) • query is expressed by applying specialized algebraic • operators operators => SQL • Relational Calculus (What) • query is describes a desired set of tuples by specifying a • predicate the tuples must satisfy => QUEL, QBE

34. Object-Oriented Data Model • Shortcomings of the Conventional Database Technology • - Too simple for modeling complex nested entities • - A limited set of atomic data types • - No frequently useful semantic concepts • - Impedance mismatch between programming language • and database language • - Inappropriate for long-duration transactions necessary in • interactive, cooperative design environments (CAD) • - Unacceptable for various types of computer-intensive • applications

35. Object-Oriented Data Model (cont'd) • Additional Features of the Next-Generation Database • Systems • - Represent and manipulate complex nested objects • - Store and retrieve arbitary long data • - Define and manipulate arbitary data types • - Represent and manipulate various semantic modeling concepts • - Specify rules and extented constraints to support inferencing • and constraint management • - Manage long-duration cooperative transactions

36. Object-Oriented Data Model (cont'd) • Object-Oriented Approach • - in Programming language • from SIMULA-67 (K. Nygard, Norway) • Smalltalk, Eiffel, C++, Java • - in Artificial Intelligence • from introduction of FRAME • KEE, ART • - in Database • from semantic data model • E-R, EE-R, SDM, DAPLEX

37. Object-Oriented Data Model (cont'd) Account object • Core Modeling Concepts • - Object and Object Identifier • object = data + operation • - Attribute and Methods • attribute : specified property to represent an object • method : an operation of an object ( = code) • - Encapsulation and Message passing • encapsulation : the data of an object can only be accessed via • the methods of the objects • message : a request to perform an operation Balance 5000 A# 100 deposit withdraw

38. Object-Oriented Data Model - Class class : a template from which objects with the same representation and the same behavior can be created - Class Hierarchy and Inheritance inheritance : a mechanism which allows a new class to be incrementally defined from an existing class instantiation instantiation class object 1 object 2

39. Object-Oriented Data Model • Object-Oriented Database Concepts EMP Name Age Salary Lee 25 500,000 Lee is an EMP object data part operation part (hire, change Age, change Salary) Account object 기존: all 6% 변경: $1000 -> 5% $1000 -> 6% Balance 5000 A# 100 pay-interest

40. 연습문제 1. 현실 세계와 개념 세게의 차이점을 설명하라. 2. 데이터 모델이란 무엇이며, 개념적 데이터 모델, 논리적 데이터 모델, 물리적 데이터 모델의 차이점을 설명하라. 3. 주변의 어떤 업무를 선택해서 E-R 모델을 만들어 보라. 4. 관계형, 계층형, 네트워크 데이터 모델을 설명하고 장,단점은? 5. E-R 모델에서 관계형 모델로 변환하는 방법을 설명하라. 6. 객체 지향 모델의 개념을 설명하고, 장점도 설명하라.

41. 별을 찾아라 작가 델마 톰슨이 문단에 나오기 전의 일이다. 군인이었던 그녀의 남편이 캘리포니아의 모하비 사막으로 파견을 나가게 되어 남편과 가까이 있고자 그 근처로 이사를 갔다. 그러나 그곳은 폭염과 모래 바람만 불어되는 혹독한 곳이었다. 하루종일 남편을 기다리던 그녀에게 매일매일이 고역이었다. 세찬 모래 바람으로 입 안에는 모래알이 돌아 다녔고 음식은 높은 온도에 쉬어 버렸다. 델마는 바참한 심정으로 고향의 친정 아버지에게 편지를 썼다. “아버지, 더 이상은 못 견디겠어요. 차라리 감옥에 있는 것이 더 나을 것 같아요.”델마의 참담한 생활에 대해 아버지의 답장은 간단했다. “두 명의 죄수가 감옥 창살로 밖을 보았단다. 그런데 한 사람은 진흙을 보았고, 다른 한 사람은 별을 보았단다.”델마는 아버지의 편지에 몹시 부끄러워져서, 별을 찾기로 하였다. 먼저 원주민의 공에품에 관심을 보이며 친구가 되고자 했다. 또 사막의 식물에 관심을 가지고 살펴보니, 그것들은 너무나 매혹적이었고 빨갛게 지는 노을을 바라보며 사막에도 아름다움이 많이 숨겨져 있음을 깨달았다. 델마는 새로운 세계를 발견한 기쁨으로 책을 냈는데 “무엇이 나를 변화시켰는가? 모하비 사막은 변하지 않았다. 나의 마음자세가 변했다. 그럼으로써 비참한 경험이 가장 흥미있는 인생으로 변할 수 있음을 깨달았다. 이후 델마 톰슨은 유명 작가의 길에 들어선다.