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 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

Database n A database consists of multiple relations n Information about an enterprise is broken up into parts instructor student advisor n Bad design: univ (instructor -ID, name, dept_name, salary, student_Id, . . ) results in l repetition of information (e. g. , two students have the same instructor) l the need for null values (e. g. , represent an student with no advisor) n Normalization theory (Chapter 7) deals with how to design “good” relational schemas Database System Concepts - 6 th Edition 2. 6 ©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 Database System Concepts - 6 th Edition 2. 7 ©Silberschatz, Korth and Sudarshan

Schema Diagram for University Database System Concepts - 6 th Edition 2. 8 ©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 Relational operators Database System Concepts - 6 th Edition 2. 9 ©Silberschatz, Korth and Sudarshan

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

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

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

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

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

Set Intersection of two relations n Relation r, s: n r s Database System Concepts - 6 th Edition 2. 15 ©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. 16 ©Silberschatz, Korth and Sudarshan

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

Figure in-2. 1 Database System Concepts - 6 th Edition 2. 18 ©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

Figure 2. 01 Database System Concepts - 6 th Edition 2. 20 ©Silberschatz, Korth and Sudarshan