1 / 18

Ch.2 Relational Data Model

Ch.2 Relational Data Model. E. F. Codd (1969, 1970). 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 ( 정규화 )

noel-sweet
Download Presentation

Ch.2 Relational Data Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ch.2 Relational Data Model E. F. Codd (1969, 1970)

  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 (동시성) 데이터베이스시스템

  3. What to Learn • Relational Model • Attribute, Domain, Relation • Characteristics of a relation • Key • Candidate key, primary key, • Foreign key • Constraints • Domain constraints, Key constraints • Entity integrity constraints • Referential integrity constrains 데이터베이스시스템

  4. Relational data model • Data models are different in • Data representation structure (표현구조) • Constraints (제약조건) • Operators (연산자) • Relational data model • Users see the data as the collection of tables • Domain constraint, key constraint, entity integrity constraint,referential integrity constraint • Relational AlgebraSQL operators (insert, delete, update, select) 데이터베이스시스템

  5. Structure (1) • Student table relation table file tuple row record attribute column field 데이터베이스시스템

  6. Structure (2) • Attribute (속성) A characteristic of an object with which we describe the object • Atomic vs. composite (학년, 학과), (주소, 전화번호, 주민등록번호) • Single-valued vs. multi-valued (학년, 주민등록번호), (취미, 전화번호, 부양가족이름, 구독신문) • Null valued different from zero-length string (빈문자열) unknown : address, birthdate not applicable : 군번, spousename 데이터베이스시스템

  7. Structure (3) • Domain the set of all possible values an attribute can have. • Domain is a type. has a meaning 학생 (학번, 학생이름, 학과, 학년, 지도교수이름) 학생이름, 지도교수이름 은 동일한 도메인을 갖는다.학번 2010011201, 2015011214 • Operators are associated with the type학번 + 학과 (???)판매량 + 단가 (???), 학년 * 나이 (???) 판매량 * 단가 = 총매출액 데이터베이스시스템

  8. Structure (4) • Relation The type of relation R is R (A1, A2, . . ., An) and the corresponding domains for the attributes are D1, D2, . . ., Dn. Then the relation state(상태) is a time-varying subset of the cartesian product of all domains. {a,b,c}X{A,B} = {(aA),(aB),(bA),(bB),(cA),(cB)} 학생 (학번, 학생이름, 학과, 학년, 지도교수이름) • degree (차수) : the number of attributes • cardinality : the number of tuples 데이터베이스시스템

  9. Structure (5) • relation scheme (스키마) = relation intension (내포) = relation type = table heading • relation state (상태, instance) = relation extension (외연) = relation value = table body 데이터베이스시스템

  10. Structure (6) p.69 데이터베이스시스템

  11. Structure (7) • Properties of a relation • There are no duplicate attributes (attribute names are unique within the relation). • Attributes are unordered, left to right. • There are no duplicate tuples (every tuple has a unique value). • Tuples are unordered, top to bottom. • Every tuple contains exactly one value for each attribute (atomic , single-valued). 데이터베이스시스템

  12. Constraints (1) • Key a group of attributes identifying a unique tuple in a relation (고유식별자) • Candidate key (후보키) : unique and irreducible학생 (학번, 이름, 주민등록번호, 학과)composite key (복합키) : a key with more than one attributes수강 (학번, 과목번호, 성적) • Super key : a super set of a candidate key • Primary key (기본키) : a chosen candidate key (exactly one) 데이터베이스시스템

  13. Constraints (2) • Key • Alternate key (대체키) : any candidate key which is not chosen as the primary key • Foreign key (외래키, 참조키) : a group of attributes whose values are required to match values of some candidate key of some relation 학생 (학번, 이름, 주소, 전공, 교내친구학번)수강 (학번, 과목번호, 성적) Employee (EmpNo, EmpName, DeptNo,ManagerNo) 데이터베이스시스템

  14. Constraints (3) p.83 데이터베이스시스템

  15. Constraints (4) • Integrity constraintsa boolean expression that is associated with some database and is required to evaluate at all times to be TRUE. • Domain constraint (도메인 제약조건)inserted value of an attribute must be within the range.학년 : 1 – 4. 학번 : 2005011xxx • Key constraint (키 제약조건) There are no duplicate values for a key. 데이터베이스시스템

  16. Constraints (5) • Integrity constraints • Entity integrity constraint (개체 무결성 제약조건)any element of a candidate key cannot have null value. 수강 (학번, 과목번호, 성적) • Referential integrity constraint (참조 무결성 제약조건)The database must not contain any unmatched foreign key values (null value is allowed)학생 (학번, 이름, 전공, 복수전공, 교내친구학번) 데이터베이스시스템

  17. Constraints (6) • Integrity constraints • Referential action 학생 (학번, 이름, 전공, 복수전공, 교내친구학번)create table 수강(학번 varchar(10) references 학생(학번) on delete cascade,과목번호 varchar(5) references 과목(과목번호) on update cascade,성적 integer check ( 성적>=0 and 성적<=100 ) primary key (학번, 과목) ); ON DELETE {CASCADE | NO ACTION}ON UPDATE {CASCASE | NO ACTION} restricted, nullify, default 데이터베이스시스템

  18. Constraints (7) p.91 데이터베이스시스템

More Related