Outline Structure of Relational Databases Database Schema Keys Schema Diagrams Relational Query Languages The Relational Algebra Database System Concepts-7th Edition 2.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.2 ©Silberschatz, Korth and Sudarshan th Edition Outline ▪ Structure of Relational Databases ▪ Database Schema ▪ Keys ▪ Schema Diagrams ▪ Relational Query Languages ▪ The Relational Algebra
Example of a Instructor Relation attributes (or columns) ID name dept name salary 10101 Srinivasan Comp.Sci. 65000 tuples 12121 Wu Finance 90000 (or rows) 15151 Mozart Music 40000 22222 Einstein Physics 95000 32343 El Said History 60000 33456 Gold Physics 87000 45565 Katz Comp.Sci. 75000 58583 Califieri History 62000 76543 Singh Finance 80000 76766 Crick Biology 72000 83821 Brandt Comp.Sci. 92000 98345 Kim Elec.Eng. 80000 Database System Concepts-7th Edition 2.3 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.3 ©Silberschatz, Korth and Sudarshan th Edition Example of a Instructor Relation attributes (or columns) tuples (or rows)
Relation Schema and Instance A1,A2,...,An are attributes R=(A,A2,...,A is a relation schema Example: instructor =(ID,name,dept name,salary) A relation instance r defined over schema R is denoted by r(R) The current values a relation are specified by a table An element t of relation ris called a tuple and is represented by a row in a table Database System Concepts-7th Edition 2.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.4 ©Silberschatz, Korth and Sudarshan th Edition Relation Schema and Instance ▪ A1 , A2 , …, An are attributes ▪ R = (A1 , A2 , …, An ) is a relation schema Example: instructor = (ID, name, dept_name, salary) ▪ A relation instance r defined over schema R is denoted by r (R). ▪ The current values a relation are specified by a table ▪ An element t of relation r is called a tuple and is represented by a row in a table
Attributes The set of allowed values for each attribute is called the domain of the attribute Attribute values are(normally)required to be atomic;that is,indivisible The special value null is a member of every domain.Indicated that the value is“unknown” The null value causes complications in the definition of many operations Database System Concepts-7th Edition 2.5 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 2.5 ©Silberschatz, Korth and Sudarshan th Edition Attributes ▪ The set of allowed values for each attribute is called the domain of the attribute ▪ Attribute values are (normally) required to be atomic; that is, indivisible ▪ The special value null is a member of every domain. Indicated that the value is “unknown” ▪ The null value causes complications in the definition of many operations
Relations are Unordered Order of tuples is irrelevant(tuples may be stored in an arbitrary order) Example:instructor relation with unordered tuples ID name dept name salary 22222 Einstein Physics 95000 12121 Wu Finance 90000 32343 El Said History 60000 45565 Katz Comp.Sci. 75000 98345 Kim Elec.Eng. 80000 76766 Crick Biology 72000 10101 Srinivasan Comp.Sci. 65000 58583 Califieri History 62000 83821 Brandt Comp.Sci. 92000 15151 Mozart Music 40000 33456 Gold Physics 87000 76543 Singh Finance 80000 Database System Concepts-7th Edition 2.6 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.6 ©Silberschatz, Korth and Sudarshan th Edition Relations are Unordered ▪ Order of tuples is irrelevant (tuples may be stored in an arbitrary order) ▪ Example: instructor relation with unordered tuples
Database Schema Database schema--is the logical structure of the database. ■ Database instance--is a snapshot of the data in the database at a given instant in time. Example: ·schema: instructor(ID,name,dept name,salary) ·Instance: ID name dept name salary 22222 Einstein Physics 95000 12121 Wu Finance 90000 32343 El Said History 60000 45565 Katz Comp.Sci. 75000 98345 Kim Elec.Eng. 80000 76766 Crick Biology 72000 10101 Srinivasan Comp.Sci. 65000 58583 Califieri History 62000 83821 Brandt Comp.Sci. 92000 15151 Mozart Music 40000 33456 Gold Physics 87000 76543 Singh Finance 80000 Database System Concepts-7th Edition 2.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.7 ©Silberschatz, Korth and Sudarshan th Edition Database Schema ▪ Database schema -- is the logical structure of the database. ▪ Database instance -- is a snapshot of the data in the database at a given instant in time. ▪ Example: • schema: instructor (ID, name, dept_name, salary) • Instance:
Keys ■LetKcR K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) Example:{ID}and {ID,name}are both superkeys of instructor. ■ Superkey K is a candidate key if K is minimal Example:{ID)is a candidate key for Instructor One of the candidate keys is selected to be the primary key. ·Vhich one? Foreign key constraint:Value in one relation must appear in another Referencing relation ·Referenced relation Example:dept name in instructor is a foreign key from instructor referencing department Database System Concepts-7th Edition 2.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 2.8 ©Silberschatz, Korth and Sudarshan th Edition Keys ▪ Let K R ▪ K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) • Example: {ID} and {ID,name} are both superkeys of instructor. ▪ Superkey K is a candidate key if K is minimal Example: {ID} is a candidate key for Instructor ▪ One of the candidate keys is selected to be the primary key. • Which one? ▪ Foreign key constraint: Value in one relation must appear in another • Referencing relation • Referenced relation • Example: dept_name in instructor is a foreign key from instructor referencing department
Schema Diagram for University Database takes student I D name course id sec id dept name semester tot_cred vear grade section course course id course id department advisor sec id title dept name s id semester dept_name building iid vear credits building time slot budget room number time slot id time slot id dav start time end time prereg instructor classroom course id D building prereg id name room number dept_name capacity teaches salary D course id sec id semester vear Database System Concepts-7th Edition 2.9 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 7 2.9 ©Silberschatz, Korth and Sudarshan th Edition Schema Diagram for University Database
Relational Query Languages Procedural versus non-procedural,or declarative ■“Pure”languages: ·Relational algebra Tuple relational calculus Domain relational calculus The above 3 pure languages are equivalent in computing power We will concentrate in this chapter on relational algebra Not Turing-machine equivalent Consists of 6 basic operations Database System Concepts-7th Edition 2.10 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 2.10 ©Silberschatz, Korth and Sudarshan th Edition Relational Query Languages ▪ Procedural versus non-procedural, or declarative ▪ “Pure” languages: • Relational algebra • Tuple relational calculus • Domain relational calculus ▪ The above 3 pure languages are equivalent in computing power ▪ We will concentrate in this chapter on relational algebra • Not Turing-machine equivalent • Consists of 6 basic operations
Relational Algebra A procedural language consisting of a set of operations that take one or two relations as input and produce a new relation as their result ■Six basic operators ·select:o project:Π 。union:U ·set difference:- 。 Cartesian product:x 。rename:p Database System Concepts-7th Edition 2.11 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 2.11 ©Silberschatz, Korth and Sudarshan th Edition Relational Algebra ▪ A procedural language consisting of a set of operations that take one or two relations as input and produce a new relation as their result. ▪ Six basic operators • select: • project: • union: • set difference: – • Cartesian product: x • rename: