Chapter 2 Intro to Relational Model Database System

Chapter 2: Intro 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. Indicated that the value is “unknown” 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 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

Keys n Let K R n K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) l Example: {ID} and {ID, name} are both superkeys of instructor. n Superkey K is a candidate key if K is minimal Example: {ID} is a candidate key for Instructor n One of the candidate keys is selected to be the primary key. l which one? n Foreign key constraint: Value in one relation must appear in another l Referencing relation l Referenced relation l Example – dept_name in instructor is a foreign key from instructor referencing department 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 Query Languages n Procedural vs. non-procedural, or declarative n “Pure” languages: l Relational algebra l Tuple relational calculus l Domain relational calculus n The above 3 pure languages are equivalent in computing power n We will concentrate in this chapter on relational algebra l Not turning-machine equivalent l consists of 6 basic operations Database System Concepts - 6 th Edition 2. 8 ©Silberschatz, Korth and Sudarshan

Select Operation – selection of rows (tuples) n Relation r ¡ A=B ^ D > 5 (r) Database System Concepts - 6 th Edition 2. 9 ©Silberschatz, Korth and Sudarshan

Project Operation – selection of columns (Attributes) n Relation r: n A, C (r) Database System Concepts - 6 th Edition 2. 10 ©Silberschatz, Korth and Sudarshan

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

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

Set intersection of two relations n Relation r, s: n r s Note: r s = r – (r – s) Database System Concepts - 6 th Edition 2. 13 ©Silberschatz, Korth and Sudarshan

joining two relations -- Cartesian-product n Relations r, s: n r x s: Database System Concepts - 6 th Edition 2. 14 ©Silberschatz, Korth and Sudarshan

Cartesian-product – naming issue B n Relations r, s: n r x s: Database System Concepts - 6 th Edition r. B s. B 2. 15 ©Silberschatz, Korth and Sudarshan

Renaming a Table n Allows us to refer to a relation, (say E) by more than one name. x (E) returns the expression E under the name X n Relations r n r x s (r) Database System Concepts - 6 th Edition r. A r. B s. A s. B α α β β 1 1 2 2 α β 1 2 2. 16 ©Silberschatz, Korth and Sudarshan

Composition of Operations n Can build expressions using multiple operations n Example: A=C (r x s) n rxs n A=C (r x s) Database System Concepts - 6 th Edition 2. 17 ©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. 18 ©Silberschatz, Korth and Sudarshan

Natural Join Example n Relations r, s: n Natural Join n r s A, r. B, C, r. D, E ( r. B = s. B ˄ r. D = s. D (r x s))) Database System Concepts - 6 th Edition 2. 19 ©Silberschatz, Korth and Sudarshan

Notes about Relational Languages n Each Query input is a table (or set of tables) n Each query output is a table. n All data in the output table appears in one of the input tables n Relational Algebra is not Turning complete n Can we compute: l SUM l AVG l MAX l MIN Database System Concepts - 6 th Edition 2. 20 ©Silberschatz, Korth and Sudarshan

Summary of Relational Algebra Operators Symbol (Name) σ (Selection) Example of Use σ salary > = 85000 (instructor) Return rows of the input relation that satisfy the predicate. Π (Projection) Π ID, salary (instructor) Output specified attributes from all rows of the input relation. Remove duplicate tuples from the output. x (Cartesian Product) instructor x department Output pairs of rows from the two input relations that have the same value on all attributes that have the same name. ∪ (Union) Π name (instructor) ∪ Π name (student) Output the union of tuples from the two input relations. (Set Difference) Π name (instructor) -- Π name (student) Output the set difference of tuples from the two input relations. ⋈ (Natural Join) instructor ⋈ department Output pairs of rows from the two input relations that have the same value on all attributes that have the same name. Database System Concepts - 6 th Edition 2. 21 ©Silberschatz, Korth and Sudarshan

End of Chapter 2 Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use