Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 2: Intro to Relational ModelSilberschatz, Korth and Sudarshan2.2Database System Concepts - 6th EditionExample of a Relationattributes(or columns)tuples(or rows)Silberschatz, Korth and Sudarshan2.3Database System Concepts - 6th EditionAttribute TypesnThe set of allowed values for each attribute is called the domain of the attributenAttribute values are (normally) required to be atomic; that is, indivisiblenThe special value null is a member of every domainnThe null value causes complications in the definition of many operationsSilberschatz, Korth and Sudarshan2.4Database System Concepts - 6th EditionRelation Schema and InstancenA1, A2, , An are attributesnR = (A1, A2, , An ) is a relation schemaExample: instructor = (ID, name, dept_name, salary)nFormally, given sets D1, D2, . Dn a relation r is a subset of D1 x D2 x x DnThus, a relation is a set of n-tuples (a1, a2, , an) where each ai DinThe current values (relation instance) of a relation are specified by a tablenAn element t of r is a tuple, represented by a row in a tableSilberschatz, Korth and Sudarshan2.5Database System Concepts - 6th EditionRelations are Unorderedn Order of tuples is irrelevant (tuples may be stored in an arbitrary order)n Example: instructor relation with unordered tuplesSilberschatz, Korth and Sudarshan2.6Database System Concepts - 6th EditionDatabasenA database consists of multiple relationsnInformation about an enterprise is broken up into parts instructor student advisornBad design: univ (instructor -ID, name, dept_name, salary, student_Id, .)results inlrepetition of information (e.g., two students have the same instructor)lthe need for null values (e.g., represent an student with no advisor)nNormalization theory (Chapter 7) deals with how to design “good” relational schemasSilberschatz, Korth and Sudarshan2.7Database System Concepts - 6th EditionKeysnLet K RnK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) lExample: ID and ID,name are both superkeys of instructor.nSuperkey K is a candidate key if K is minimalExample: ID is a candidate key for InstructornOne of the candidate keys is selected to be the primary key.lwhich one?nForeign key constraint: Value in one relation must appear in anotherlReferencing relationlReferenced relationSilberschatz, Korth and Sudarshan2.8Database System Concepts - 6th EditionSchema Diagram for University DatabaseSchema Diagram for University DatabaseSilberschatz, Korth and Sudarshan2.9Database System Concepts - 6th EditionRelational Query LanguagesnProcedural vs.non-procedural, or declarativen“Pure” languages:lRelational algebralTuple relational calculuslDomain relational calculusnRelational operatorsSilberschatz, Korth and Sudarshan2.10Database System Concepts - 6th EditionSelection of tuplesnRelation rnSelect tuples with A=B and D 5n A=B and D 5 (r)Silberschatz, Korth and Sudarshan2.11Database System Concepts - 6th EditionSelection of Columns (Attributes)nRelation r:n Select A and CnProjectionn A, C (r) Silberschatz, Korth and Sudarshan2.12Database System Concepts - 6th EditionJoining two relations Cartesian ProductnRelations r, s:nr x s:Silberschatz, Korth and Sudarshan2.13Database System Concepts - 6th EditionUnion of two relationsnRelations r, s:nr s:Silberschatz, Korth and Sudarshan2.14Database System Concepts - 6th EditionSet difference of two relationsnRelations r, s:nr s:Silberschatz, Korth and Sudarshan2.15Database System Concepts - 6th EditionSet Intersection of two relationsnRelation r, s:nr sSilberschatz, Korth and Sudarshan2.16Database System Concepts - 6th EditionJoining two relations Natural JoinnLet 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:lConsider each pair of tuples tr from r and ts from s. lIf tr and ts have the same value on each of the attributes in R S, add a tuple t to the result, where4t has the same value as tr on r4t has the same value as ts on sSilberschatz, Korth and Sudarshan2.17Database System Concepts - 6th EditionNatural Join ExamplenRelations r, s:nNatural Joinnr sSilberschatz, Korth and Sudarshan2.18Database System Concepts - 6th EditionFigure in-2.1Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use End of Chapter 2Silberschatz, Korth and Sudarshan2.20Database System Concepts - 6th EditionFigure 2.01Silberschatz, Korth and Sudarshan2.21Database System Concepts - 6th EditionFigure 2.02Silberschatz, Korth and Sudarshan2.22Database System Concepts - 6th EditionFigure 2.03Silberschatz, Korth and Sudarshan2.23Database System Concepts - 6th EditionFigure 2.04Silberschatz, Korth and Sudarshan2.24Database System Concepts - 6th EditionFigure 2.05Silberschatz, Korth and Sudarshan2.25Database System Concepts - 6th EditionFigure 2.06Silberschatz, Korth