Transcript Document

Introduction of DB(1)
What is DB?
한국기술교육대학교
민준기
KUT
Page 1
Definition of Database
• Origin of “database” Term
– 1963.6 The first SDC Symposium
• Development and Management of a Computer-centered
Data Base
– 1965.9 The second SDC Symposium
• Computer-centered Data Base Systems
KUT
Page 2
History of Databases
1960’s early
The first Database, IDS(Integrated Data Store)
1960’s late
Develop IMS(Information Management System) at IBM
May, 1970
Suggested Relational data model by Codd at IBM
1970’s early
System/R, The first relational database
1979
Oracle V1, the first commercial relational DBMS
1982
SQL/DS, IBM’s commercial RDBMS
current
Oracle, DB2, Sybase, SQL Server, etc
KUT
Page 3
History of SQL
1973
SQUARE(Structured Queries As Relational Express)
1974
System/R용 SEQUEL(Structured English QUEry
Language)
1976
SEQUEL-2
1980
Rename SQL(Structured Query Language)
1986
SQL-86, the first SQL standard spec
1988
ANSI, ISO international standard
1988
Publish SQL-1(SQL/89) standard
1992
Publish SQL-2(SQL/92) standard
1999
Publish SQL-3(SQL/99) standard
KUT
Page 4
Definition of DB
• Database
– A collection of interrelated data
– For diverse application systems in a enterprise, a set of
shared, integrated, stored and operational data
• shared data
• integrated data
– minimal redundancy
– controlled redundancy
• stored data
– Stored media accessed by a computer
– Disk, tape, etc
• operational data
KUT
Page 5
Features of Database
• real-time accessibilities
– real-time processing and response for a query
• continuous evolution
– update, insert, delete : dynamic feature
• concurrent sharing
– multi-users use simultaniously
• content reference
– Not location or address of data
– Reference by contents
KUT
Page 6
Components of Database
• logical components
– User view
– Database = {entities, relationships}
• Entity
– Object to be represented
– Unit of information
– Consist of attributes
• The smallest logical unit of data
– entity set
• entity type vs. entity instance
– Correspond to general record
• record type vs. record instance
KUT
Page 7
 Data world(1)
Real world(Entity)
Entity type
entity
featu
re
field
value
abstraction
Conceptual
modeling
Conceptual
structure
Computer World(Data)
Record type
attribute
value
facts
Conceptual world(concept)
value
Tansform
Data
Modeling
KUT
Logical
structure
(Data model)
Page 8
8
 data model
• data model: D
D = <S, O, C>
– S : structure)
• Data’s static properties
• Entity type and relationship
– O : operation
• Data’s dynamic properties
• Processing of entity instance
– C : constraints
• logical constraints
- existential condition of entity instance
: derived from Structure S
:semantic constraints
• Limitation of data manipulation
KUT
Page 9
9
개체 – 관계 모델(E-R model)
• 현실세계의 개념적 표현
• 개체 타입과 관계 타입을 기본 개념으로 현실 세계를
개념적으로 표현하는 방법
• 개체 집합 : 한 개체 타입에 속하는 모든 개체 인스턴스
• 관계 집합 : 한 관계 타입에 속하는 모든 관계 인스턴스
KUT
Page 10
 Entity-Relationship model(E-R model)
• Entity-Relationship model
– conceptual data model
• Conceptual representation of real world
• ER model has three main concepts:
– Entities (and their entity types and entity sets)
– Attributes (simple, composite, multivalued)
– Relationships (and their relationship types and
relationship sets)
KUT
Page 11
11
 entity type(1)
•
entity
– An object distinguished from others
– Entities are specific objects or things in the mini-world that are
represented in the database
•
entity type
– Defined by name and attribute
– entity set
• A set of entity instance
•
Attribute

Attributes are properties used to describe an entity.
i. Simple attribute, composite attribute
ii. Single value attribute , multi value attribute
iii. Derived attribute, stored attribute
iv. Null
KUT
Page 12
12
 relationship type
• relationship type
– Correspondence between entity set(type), i.e., mapping
student
enroll
course
“enroll” relationship
type
KUT
Page 13
13
▶ relationship type(1)
• mapping cardinality
– 1 : 1 (one to one: )
fx : x → y
and
fy : y → x
marriage : bridegroom ↔ bride
– 1 : n (one to many)
not fx : x → y
but fy : y → x
mothership : mother ← children
KUT
Page 14
14
▶ relationship type(1)
– n : 1 (many to one)
fx : x → y
not fy : y → x
faculty : professor → department
– n : m (many to many)
not fx : x → y
not fy : y → x
enrollment : student ↔ course
☞ Note : functionality
KUT
Page 15
15
▶ relationship type (2)
- participation constraint
• total participation
– All entities in A participate in A-B relationship
– ex) professor – department
• partial participation)
– Some entities in A participate in A-B relationship
– ex) marriage relationship
KUT
Page 16
16
▶ E-R diagram(1)
• 1976. Peter Chen
• E-R Diagram
– entity type
– relationship type
– attribute
– link
– label : mapping
• 1:1, 1:n, n:1, n:m
KUT
Page 17
17
▶ E-R diagram(2)
• example
professor
1
pno
advice
pname
major
dept
n
sname
teach
n
n
student
sno
time
1
address
year
enroll
m
course
cno
grade
KUT
location
cname
cpoint
Page 18
18
▶ key attributes
• An attribute of an entity type for which each
entity must have a unique value is called a
key attribute of the entity type.
– For example, SSN of EMPLOYEE.
• A key attribute may be composite.
– VehicleTagNumber is a key of the CAR entity
type with components (Number, State).
• An entity type may have more than one key.
– The CAR entity type may have two keys:
• VehicleIdentificationNumber (popularly called VIN)
• VehicleTagNumber (Number, State), aka license plate
number.
• Each key is underlined
KUT
Page 19
19
Weak Entity Types
• A weak entity must participate in an identifying relationship type
with an owner or identifying entity type
• weak entity type
– An entity that does not have a key attribute
– dominant entity – strong entity type,
subordinate entity – weak entity type
• discriminator
– Attribute identifying the particular weak entity who
are related to a strong entity type
• identifying relationship type
KUT
Page 20
Slide 3- 20
▶ E-R diagram
Entity type
attribute
Weak entity
type
Key attribute
Partial key
attribute
Multi-valued
attribute
relationship
identifying
relationship
type
total
participation
Composite
attribute
KUT
Derived
attribute
Page 21
21
▶ weak entity type
loan
loanNum
1
loan_repay
amount
n
repaynum
repay
date
repayamm
E-R diagram of “loan_repay” relationship
KUT
Page 22
22
Relational Database
• Relational Database
KUT
Page 23
Relational Model Concepts
• A Relation is a mathematical concept based
on the ideas of sets
• The model was first proposed by Dr. E.F.
Codd of IBM Research in 1970 in the
following paper:
– "A Relational Model for Large Shared Data Banks,"
Communications of the ACM, June 1970
• The above paper caused a major revolution in
the field of database management and earned
Dr. Codd the coveted ACM Turing Award
KUT
Page 24
Slide 5- 24
Informal Definitions
• Informally, a relation looks like a table of values.
• A relation typically contains a set of rows.
• The data elements in each row represent certain facts
that correspond to a real-world entity or relationship
– In the formal model, rows are called tuples
• Each column has a column header that gives an
indication of the meaning of the data items in that
column
– In the formal model, the column header is called an
attribute name (or just attribute)
KUT
Page 25
Slide 5- 25
Example of Relational Data model
STUDENT Table : Relation
학생
(STUD
ENT)
학번
(Sno)
100
200
300
400
500
이름
(Sname)
나수영
이찬수
정기태
송병길
박종화
학년
(Year)
4
3
1
4
2
KUT
학과
(Dept)
컴퓨터
전기
컴퓨터
컴퓨터
산공
Page 26
26
E-R Model vs. Relational Data Model
• Entity Type  Relation
• Relation Type  Relation
• Attribute  Attribute
•
domain)
– A set of values that an attribute can get
•
attribute)
– Action name of domain
– Each name of attribute is unique
•
simple domain
→ atomic value
•
composite domain
→ composite value
ex)yy, mm, dd ⇒ date:<yy,mm,dd>
KUT
Page 27
Relation
• relation
– 2 dimensional table structure
– A set of tuples and attributes
key
STUDENT relation
STUDNO
tuple
NAME
USERID
Attribute name
GRADE
……
HEIGHT
WEIGHT
DEPTNO
PROFNO
9903
10101
전인하 jun123
4
……
176
72
101
20101
이동훈 Dals
1
……
172
64
201
………
………
10203
윤진욱 Samba
3
……
171
70
102
9905
10107
이광훈 huriky
4
……
175
92
101
9903
………
………
………
………
……
……
………
………
attribute
KUT
Page 28
Relation
schema
instances
Formal Definitions - Summary
• Formally,
– Given R(A1, A2, .........., An)
– r(R)  dom (A1) X dom (A2) X ....X dom(An)
• R(A1, A2, …, An) is the schema of the relation
n : R’s degree :1차, 2차, 3차, ... , n차)
num of tuples : cardinality
• R is the name of the relation
• A1, A2, …, An are the attributes of the relation
• r(R): a specific state (or "value" or “population”) of
relation R – this is a set of tuples (rows)
– r(R) = {t1, t2, …, tm} where each ti is an n-tuple
– ti = <v1, v2, …, vn> where each vj element-of dom(Aj)
KUT
Page 29
Slide 5- 29
Formal Definitions - Example
• Let R(A1, A2) be a relation schema:
– Let dom(A1) = {0,1}
– Let dom(A2) = {a,b,c}
• Then: dom(A1) X dom(A2) is all possible combinations:
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }
• The relation state r(R)  dom(A1) X dom(A2)
• For example: r(R) could be {<0,a> , <0,b> , <1,c> }
– this is one possible state (or “population” or “extension”) r of
the relation R, defined over A1 and A2.
– It has three 2-tuples: <0,a> , <0,b> , <1,c>
• Degree: 2
• Cardinality: 3
KUT
Page 30
Slide 5- 30
Characteristics of Relation(1)
1). uniqueness of tuples
Relation ＝Set of tuples
2). no ordering of tuples
Relation: Intension  set of tuple
Table : Extension
3). no ordering of attributes
Relation schema→ set of attributes
Tuple : set of <attribute: value> pairs
KUT
Page 31
Characteristics of Relation(2)
4). Atomicity of attribute
– atomic value
• indivisible
– normalized relation
• Relation with atomic attributes
• No-normalized relation=> decomposition=>Normalized relation
– null value is also atomic value
• unknown, inapplicable
KUT
Page 32
32
Relational Integrity Constraints
• Constraints are conditions that must hold on all
valid relation states.
• There are three main types of constraints in the
relational model:
– Entity integrity constraints
– Referential integrity constraints
– Another implicit constraint is the domain constraint
• Every value in a tuple must be from the domain of its
attribute (or it could be null, if allowed for that attribute)
• In order to explain Integrity constraints, we should
know KEY.
KUT
Page 33
Key
• Key of a Relation:
– Each row has a value of a data item (or set of
items) that uniquely identifies that row in the
table
• Called the key
– In the STUDENT table, SNO is the key
– Sometimes row-ids or sequential numbers are
assigned as keys to identify the rows in a table
• Called artificial key or surrogate key
KUT
Page 34
Key
• Superkey of R:
– Is a set of attributes SK of R with the following
condition:
• No two tuples in any valid relation state r(R) will have the
same value for SK
• That is, for any distinct tuples t1 and t2 in r(R), t1[SK] 
t2[SK]
• This condition must hold in any valid state r(R)
• Candidate Key of R:
– A "minimal" superkey
– That is, a candidate key is a superkey K such that
removal of any attribute from K results in a set of
attributes that is not a superkey (does not possess the
superkey uniqueness property)
KUT
Page 35
Key
• If a relation has several candidate keys, one is
chosen arbitrarily to be the primary key.
– The primary key attributes are underlined.
• alternate key
– Candidate keys except primary key
KUT
Page 36
Key (continued)
•
Example: Consider the STUDENT relation schema:
– STUDENT(SNO, sNAME, SocialNo, Dept)
– STUDENT has several super keys:
•
•
•
•
Key1 = {SNO,sNAME, SocialNo, Dept}
Key2 = {SNO,sNAME}
Key3 = {SocialNo,sNAME}
Key4 ={SNO}
• {sNANE,Dept} is not a super key
– {SNO, sNAME} is a superkey but not a key.
•
In general:
– Any key is a superkey (but not vice versa)
– Any set of attributes that includes a key is a superkey
– A minimal superkey is also a key
•
SNO and SocialNo are condidate keys
– We choose SNO as a primary key
– SocialNo is an alternative key
KUT
Page 37
Slide 5- 37
Foreign Key
• foreign key
– When Relation R’s attribute set FK is relation S’s
primary key, FK is called as R’s foreign key
– FK’s domain = Domain of S’s primary key
– FK’s value is in S or null
– R is referencing relation, S is referenced relation
KUT
Page 38
Integrity Constraints
(1) entity integrity
– Primary key value is not null.
☞ Notes : null value
① unknown value
② inapplicable
(2) referential integrity
– The value in the foreign key column (or columns) FK of the
the referencing relation R1 can be either:
• (1) a value of an existing primary key value of a corresponding
primary key PK in the referenced relation R2, or
• (2) a null.
– In case (2), the FK in R1 should not be a part of its own
primary key.
(3) Domain integrity
– Every value in a tuple must be from the domain of its attribute
KUT
Page 39