Download Relational Databases-Database System Concepts-Lecture 02 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Lecture 2 of 42
Friday, 25 August 2006
William H. Hsu Department of Computing and Information Sciences, KSU
KSOL course page: http://snipurl.com/va Course web site: http://www.kddresearch.org/Courses/Fall-2006/CIS Instructor home page: http://www.cis.ksu.edu/~bhsu
Reading for Next Class: Chapter 2, Silberschatz et al. , 5th^ edition – next week Problem Set 1
Relational Databases
Discussion: Problem Set 1
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Chapter 2: Relational ModelChapter 2: Relational Model
z Structure of Relational Databases z Fundamental Relational-Algebra-Operations z Additional Relational-Algebra-Operations z Extended Relational-Algebra-Operations z Null Values z Modification of the Database
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Example of a Relation Example of a Relation
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Basic Structure Basic Structure
z Formally, given sets D 1 , D 2 , …. Dn a relation r is a subset of D 1 x D 2 x … x Dn Thus, a relation is a set of n -tuples ( a 1 , a 2 , …, an ) where each ai ∈ Di z Example: If customer_name = {Jones, Smith, Curry, Lindsay} customer_street = {Main, North, Park} customer_city = {Harrison, Rye, Pittsfield} Then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield) } is a relation over customer_name x customer_street x customer_city
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Relation Instance Relation Instance
z The current values ( relation instance ) of a relation are specified by a table z An element t of r is a tuple , represented by a row in a table
Jones Smith Curry Lindsay
customer_name
Main North North Park
customer_street
Harrison Rye Rye Pittsfield
customer_city
customer
attributes (or columns)
tuples (or rows)
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Relations are UnorderedRelations are Unordered
Order of tuples is irrelevant (tuples may be stored in an arbitrary order) Example: account relation with unordered tuples
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
DatabaseDatabase
z A database consists of multiple relations
z Information about an enterprise is broken up into parts, with each relation storing one part of the information
account : stores information about accounts depositor : stores information about which customer owns which account customer : stores information about customers
z Storing all information as a single relation such as bank ( account_number, balance, customer_name , ..) results in
repetition of information (e.g., two customers own an account)
the need for null values (e.g., represent a customer without an account)
z Normalization theory (Chapter 7) deals with how to design relational schemas
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
TheThe customercustomer^ RelationRelation
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Query LanguagesQuery Languages
z Language in which user requests information from the database. z Categories of languages Procedural Non-procedural, or declarative z “Pure” languages: Relational algebra Tuple relational calculus Domain relational calculus z Pure languages form underlying basis of query languages that people use.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Relational AlgebraRelational Algebra
z Procedural language z Six basic operators
select: σ
project: ∏ union: ∪ set difference: – Cartesian product: x
rename: ρ
z The operators take one or two relations as inputs and produce a new relation as a result.
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Select Operation – ExampleSelect Operation – Example
Relation r A B C D
α α β β
α β β β
1 5 12 23
7 7 3 10
σA=B ^ D > 5 (r)
A B C D
α β
α β
1 23
7 10
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Select OperationSelect Operation
z Notation: σ (^) p ( r ) z p is called the selection predicate z Defined as:
σ p ( r ) = { t | t ∈ r and p(t) }
Where p is a formula in propositional calculus consisting of terms connected by : ∧ ( and ), ∨ ( or ), ¬ ( not ) Each term is one of: op or where op is one of: =, ≠, >, ≥. <. ≤
z Example of selection:
σ (^) branch_name=“Perryridge” ( account )
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Union Operation – ExampleUnion Operation – Example
z Relations r, s:
r ∪ s:
A B
α
α β
1 2
1
A B
α β
2 3
r
s
A B
α α β β
1 2 1 3
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Union Operation Union Operation
z Notation: r ∪ s z Defined as: r ∪ s = { t | t ∈ r or t ∈ s } z For r ∪ s to be valid.
- r, s must have the same arity (same number of attributes)
- The attribute domains must be compatible (example: 2 nd column of r deals with the same type of values as does the 2 nd column of s )
z Example: to find all customers with either an account or a loan
∏ customer_name ( depositor ) ∪ ∏ customer_name ( borrower)
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Set Difference Operation – Example Set Difference Operation – Example
z Relations r , s :
r – s:
A B
α α β
1 2 1
A B
α
β
2
3
r
s
A B
α
β
1
1
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Set Difference Operation Set Difference Operation
z Notation r – s z Defined as: r – s = { t | t ∈ r and t ∉ s }
z Set differences must be taken between compatible relations. r and s must have the same arity attribute domains of r and s must be compatible
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Composition of OperationsComposition of Operations
z Can build expressions using multiple operations z Example: σA=C( r x s ) z r x s
z σA=C( r x s )
A B
α α α α β β β β 1 1 1 1 2 2 2 2
C D
α β β γ α β β γ 10 10 20 10 10 10 20 10
E a a b b a a b b
A B C D E
α β β
1 2 2
α β β
10 10 20
a a b
Computing & Information Sciences CIS 560: Database System Concepts Friday, 25 Aug 2006 Kansas State University
Rename OperationRename Operation
z Allows us to name, and therefore to refer to, the results of relational-algebra expressions. z Allows us to refer to a relation by more than one name. z Example:
ρ (^) x ( E )
returns the expression E under the name X z If a relational-algebra expression E has arity n , then
returns the result of expression E under the name X , and with the attributes renamed to A 1 , A 2 , …., An.
ρ x ( A 1 , A 2 ,..., An )( E )
