The Relational Database 1 관계데이터모델 릴레이션의개념 릴레이션의특성 데이터베이스키 Relational Query Languages SQL 데이터정의문 DB Lab. CUK 1
관계 (Relational) 데이터모델 (1/2) Relational database 란? a set of relations Relation: made up of 2 parts? 1) Schema : specifies name of relation, plus name and type of each column E.G. Students(sid: string, name: string, login: string, age: integer, gpa: real) 2) Instance : a table, with rows and columns # of rows = cardinality, # of fields = degree / arity Can think of a relation as a set of rows or tuples (i.e., all rows are distinct) DB Lab. CUK 2
관계 (Relational) 데이터모델 (2/2) Example Instance of STUDENTS Relation 도메인 학생 (STUDENTS) INTEGER CHAR(10) INTEGER CHAR(6) 학번 (Sno) 이름 (Sname) 학년 (Year) DB Lab. CUK 3 학과 (Dept) 100 나수영 4 컴퓨터 200 이찬수 3 전기 300 정기태 1 컴퓨터 400 송병길 4 컴퓨터 500 박종화 2 산공 Cardinality =? 5 Degree =? 4 투플 애트리뷰트 (attribute) 릴레이션스키마 인스턴스 학생 (STUDENTS) 릴레이션
릴레이션의개념 (1/2) 릴레이션의수학적정의카티션프로덕트 (Cartesian product) 의부분집합 R D 1 D 2... D n, 단 D i : i번째도메인즉 n-투플, <d 1, d 2,..., d n > 의집합 d i D i, i = 1, 2,..., n n : R의차수 (degree :1차, 2차, 3차,..., n차 ) 투플의수 : 카디널리티 (cardinality) 학번 (Sno) 과목번호 (Cno) 학번 ⅹ 과목번호 (Sno ⅹ Cno) 릴레이션의개념적정의릴레이션스키마 + 릴레이션인스턴스 DB Lab. CUK 4 100 200 C412 C123 C312 <100,C412> <100,C123> <100,C312> <200,C412> <200,C123> <200,C312>
릴레이션의개념 (2/2) Alternative Terminology Tableoriented Recordoriented Setoriented Table file? Row Record? Column Field? DB Lab. CUK 5
릴레이션의개념 (2/2) Alternative Terminology Tableoriented Recordoriented Setoriented Table file Relation Row Record Tuple Column Field Attribute DB Lab. CUK 6
릴레이션의특성 (1/3) 릴레이션의특성 각릴레이션은오직하나의레코드타입만포함 한애트리뷰트내의값들은모두같은유형 애트리뷰트들의순서는중요하지않음 STUDNET STUDENT sid name age 50000 Dave 19 53666 Jones 18 53688 Smith 18 age sid name 19 50000 Dave 18 53666 Jones 18 53688 Smith DB Lab. CUK 7
릴레이션의특성 (2/3) 릴레이션의특성 ( 계속 ) 동일한투플이두개이상존재하지않음 키가존재함 한투플의각애트리뷰트는원자값을가짐 STUDENT sid name login 50000 Dave dave@cs 53666 Jones jones@cs 53688 Smith smith@ee DB Lab. CUK 8
릴레이션의특성 (3/3) 릴레이션의특성 ( 계속 ) 각애트리뷰트의이름은한릴레이션내에서만고유 투플들의순서는중요하지않음 STUDNET sid name age 50000 Dave 19 53666 Jones 18 53688 Smith 18 STUDENT sid name age 53688 Smith 18 53666 Jones 18 50000 Dave 19 DB Lab. CUK 9
데이터베이스키 (1/8) 릴레이션의키 각투플을고유하게식별할수있는하나이상의애트리뷰트들의모임 수퍼키 (superkey), 후보키 (candidate key), 기본키 (primary key), 대체키 (alternate key), 고유키 (unique key), 외래키 (foreign key) 수퍼키 (superkey) 한릴레이션내의특정투플을고유하게식별하는하나의애트리뷰트또는애트리뷰트들의집합 예 : 신용카드회사의고객릴레이션에서 ( 신용카드번호, 주소 ) 또는 ( 주민등록번호, 이름 ) 또는 ( 주민등록번호 ) 투플들을고유하게식별하는데꼭필요하지않은애트리뷰트들을포함할수있음 DB Lab. CUK 10
데이터베이스키 (2/8) 후보키 (candidate key) 각투플을고유하게식별하는최소한의애트리뷰트들의모임 유일성 (uniqueness), 최소성 (minimality) 예 : ( 신용카드번호, 주소 ) 는신용카드회사의고객릴레이션의후보키가아니지만 ( 신용카드번호 ) 는후보키 모든릴레이션에는최소한개이상의후보키가있음 후보키도두개이상의복합애트리뷰트로이루어질수있음 릴레이션 E 의후보키는? ( sid, cid ) DB Lab. CUK 11 E sid cid grade 53831 Carnatic101 C 53831 History105 B 53650 Topology112 A 53666 History105 B
데이터베이스키 (3/8) STUDENT 릴레이션에서 name 이후보키가될수있는가? STUDENT 릴레이션에서 login 이후보키가될수있는가? STUDENT sid name login age gpa 50000 Dave dave@cs 19 3.3 53666 Jones jones@cs 18 3.4 53688 Smith smith@ee 18 3.2 53650 Smith smith@math 19 3.8 53831 Madayan madayan@music 11 1.8 53832 Guldu guldu@music 12 2.0 DB Lab. CUK 12
데이터베이스키 (4/8) 기본키 (primary key) 한릴레이션에후보키가두개이상있으면설계자또는데이터베이스관리자가이들중에서하나를기본키로선정함 예 : 신용카드회사의고객릴레이션에서신용카드번호와주민등록번호가후보키가될수있음. 이중에서신용카드번호를기본키로선정할수있음 자연스러운기본키를찾을수없는경우에는레코드번호와같이종종인위적인키애트리뷰트를릴레이션에추가할수있음 기본키로지정된애트리뷰트들은모든투플에대해어느때고널 (null) 값을가질수없음 DB Lab. CUK 13
데이터베이스키 (5/8) 대체키 (alternate key) 기본키가아닌후보키 예 : 신용카드회사의고객릴레이션에서신용카드번호를기본키로선정하면주민등록번호는대체키 외래키 (foreign key) 어떤릴레이션의기본키를참조하는애트리뷰트 관계데이터베이스에서릴레이션들간의관계를나타내기위해서사용됨 외래키애트리뷰트는참조되는릴레이션의기본키와동일한도메인을가져야함 자신이속한릴레이션의기본키의구성요소가되거나되지않을수있음 DB Lab. CUK 14
데이터베이스키 (6/8) 외래키의유형 다른릴레이션의기본키를참조하는외래키? STUDENT sid name login age cid 50000 Dave dave@cs 19 101 53666 Jones jones@cs 18 101 53688 Smith smith@ee 18 105 53650 Smith smith@math 19 112 53831 Madayan madayan@music 11 203 53832 Guldu guldu@music 12 203 COURSE cid course_name 101 Operating System 203 Reggae 112 Topology 105 History 기본키 외래키 기본키 DB Lab. CUK 15
데이터베이스키 (7/8) 외래키의유형 ( 계속 ) 자체릴레이션의기본키를참조하는외래키? 기본키 외래키 FacSSN FacFirstName FacLastName FacRank FacSalary FacSupervisor 098-76-5432 LEONARD VINCE ASST $35,000 654-32-1098 543-21-0987 VICTORIA EMMANUEL PROF $120,000 654-32-1098 LEONARD FIBON ASSC $70,000 543-21-0987 765-43-2109 NICKI MACON PROF $65,000 876-54-3210 CRISTOPHER COLAN ASST $40,000 654-32-1098 987-65-4321 JULIA MILLS ASSC $75,000 765-43-2109 DB Lab. CUK 16
데이터베이스키 (8/8) 외래키의유형 ( 계속 ) 기본키의구성요소가되는외래키? 기본키각각외래키 ( 합쳐서기본키 ) 기본키 학생수강과목 학번이름... 학번과목번호학점 과목번호 과목이름 11002 이홍근... 11002 CS310 A0 CS310 데이터베이스 24036 김순미... 11002 CS313 B+ CS313 운영체제 24036 CS345 B0 CS345 자료구조 24036 CS310 A+ DB Lab. CUK 17
Relational Query Languages(1/2) A major strength of the relational model: supports simple, powerful querying of data Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. The key: precise semantics for relational queries Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change DB Lab. CUK 18
Relational Query Languages(2/2) SQL Language: History IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory Renamed Structured Query Language (SQL) ANSI and ISO standard SQL: SQL-86, SQL-89, SQL-92(SQL2), SQL-99(SQL3) SQL-99 (language name became Y2K compliant!) SQL:2003 SQL:2008 Not all examples here may work on your particular system. DB Lab. CUK 19
SQL 데이터정의문 (1/6) Allows the specification of not only a set of relations but also information about each relation, including: The schema for each relation The domain of values associated with each attribute Integrity constraints The set of indices to be maintained for each relations Security and authorization information for each relation The physical storage structure of each relation on disk DB Lab. CUK 20
SQL 데이터정의문 (2/6) Domain Types in SQL char(n) : Fixed length character string, with user-specified length n varchar(n) : Variable length character strings, with user-specified maximum length n Int : Integer (a finite subset of the integers that is machinedependent) Smallint : Small integer (a machine-dependent subset of the integer domain type) numeric(p, d) : Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point real, double precision : Floating point and double-precision floating point numbers, with machine-dependent precision float(n) : Floating point number, with user-specified precision of at least n digits DB Lab. CUK 21
SQL 데이터정의문 (3/6) Built-in Data Types in SQL date : Dates, containing a (4 digit) year, month and date Example: date 2015-9-27 time : Time of day, in hours, minutes and seconds Example: time 09:00:30 time 09:00:30.75 timestamp : date plus time of day Example: timestamp 2015-9-27 09:00:30.75 interval : period of time Example: interval 1 day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values DB Lab. CUK 22
SQL 데이터정의문 (4/6) Large objects (photos, videos, CAD files, etc.) are stored as a large object blob binary large object - object is a large collection of uninterpreted binary data clob character large object - object is a large collection of character data When a query returns a large object, a pointer is returned rather than the large object itself DB Lab. CUK 23
SQL 데이터정의문 (5/6) 기본테이블의생성 Creates the Students relation. Observe that the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL) As another example, the Enrolled table holds information about courses that students take. CREATE TABLE Enrolled (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2)) DB Lab. CUK 24
SQL 데이터정의문 (6/6) 기본테이블의제거와변경 Destroys the relation Students. The schema information and the tuples are deleted DROP TABLE Students The schema of Students is altered by adding a new field; every tuple in the current instance is extended with a null value in the new field ALTER TABLE Students ADD firstyear int; DB Lab. CUK 25
Summary(1/2) 관계데이터모델은테이블형태의데이터표현 릴레이션스키마 + 릴레이션인스턴스 Simple and intuitive, currently the most widely used 릴레이션의특성 한애트리뷰트내의값들은모두같은타입을가짐 동일한투플이두개이상존재하지않음 한투플의각애트리뷰트는원자값을가짐 각애트리뷰트의이름은한릴레이션내에서만고유 애트리뷰트나투플들의순서는중요하지않음 릴레이션의키 수퍼키 (superkey), 후보키 (candidate key), 기본키 (primary key), 대체키 (alternate key), 외래키 (foreign key) DB Lab. CUK 26
Summary(2/2) Powerful and natural query languages exist SQL 데이터정의문 기본테이블의생성 CREATE TABLE 기본테이블의제거 DROP TABLE 기본테이블의변경 ALTER TABLE DB Lab. CUK 27