Chapter 2 Introduction to Relational Model Database System

Chapter 2: Introduction to Relational Model Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Example of a Relation attributes (or columns) tuples (or rows) Database System Concepts - 6 th Edition 2. 2 ©Silberschatz, Korth and Sudarshan

Attribute Types n The set of allowed values for each attribute is called the domain of the attribute n Attribute values are (normally) required to be atomic; that is, indivisible n The special value null is a member of every domain n The null value causes complications in the definition of many operations Database System Concepts - 6 th Edition 2. 3 ©Silberschatz, Korth and Sudarshan

Relation Schema and Instance n A 1, A 2, …, An are attributes n R = (A 1, A 2, …, An ) is a relation schema Example: instructor = (ID, name, dept_name, salary) n Formally, given sets of values 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 n The current values (relation instance) of a relation are specified by a table. n An element t of r is a tuple, represented by a row in a table. Database System Concepts - 6 th Edition 2. 4 ©Silberschatz, Korth and Sudarshan

Relations are Unordered n Order of tuples is irrelevant (tuples may be stored in an arbitrary order) n Example: instructor relation with unordered tuples Database System Concepts - 6 th Edition 2. 5 ©Silberschatz, Korth and Sudarshan

Relations are Unordered n Why is the order of tuples irrelevant? n An unordered collection of elements is a set: {1, 2, 3} = {2, 1, 3}. n An ordered collection of elements is a list: (1, 2, 3) (2, 1, 3). n A set is used to express (expressiveness). n We care you are a student, but we don’t care when you register (the order). n A list is used to process (efficiency). Database System Concepts - 6 th Edition 2. 6 ©Silberschatz, Korth and Sudarshan

Schema Diagram for University Database System Concepts - 6 th Edition 2. 7 ©Silberschatz, Korth and Sudarshan

Relational Operators Database System Concepts - 6 th Edition 2. 8 ©Silberschatz, Korth and Sudarshan

Selection of tuples n Relation r n Select tuples with A=B and D > 5 l σ A=B and D > 5 Database System Concepts - 6 th Edition (r) 2. 9 ©Silberschatz, Korth and Sudarshan

Selection of Columns (Attributes) n Relation r: n Select A and C l Projection l Π A, C (r) Database System Concepts - 6 th Edition 2. 10 ©Silberschatz, Korth and Sudarshan

Joining two relations – Cartesian Product n Relations r, s: n r x s: Database System Concepts - 6 th Edition 2. 11 ©Silberschatz, Korth and Sudarshan

Union of two relations n Relations r, s: n r s: Database System Concepts - 6 th Edition 2. 12 ©Silberschatz, Korth and Sudarshan

Set difference of two relations n Relations r, s: n r – s: Database System Concepts - 6 th Edition 2. 13 ©Silberschatz, Korth and Sudarshan

Set Intersection of two relations n Relation r, s: n r s Database System Concepts - 6 th Edition 2. 14 ©Silberschatz, Korth and Sudarshan

Joining two relations – Natural Join n Let r and s be relations on schemas R and S respectively. Then, the “natural join” of relations R and S is a relation on schema R S obtained as follows: l Consider each pair of tuples tr from r and ts from s. l If tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, where 4 t has the same value as tr on r 4 t has the same value as ts on s Database System Concepts - 6 th Edition 2. 15 ©Silberschatz, Korth and Sudarshan

Natural Join Example n Relations r, s: n Natural Join l r s Database System Concepts - 6 th Edition 2. 16 ©Silberschatz, Korth and Sudarshan