Chapter 3: Relational Model Structure of Relational Databases Relational Algebra Extended Relational-Algebra-Operations Modification of the database Views Tuple Relational calculus Domain relational calculus 标 Database System Concepts 3.1 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.1 ©Silberschatz, Korth and Sudarshan Chapter 3: Relational Model Structure of Relational Databases Relational Algebra Extended Relational-Algebra-Operations Modification of the Database Views Tuple Relational Calculus Domain Relational Calculus
Example of a Relation account-number branch-name balance A-101 Downtown 500 A-102 Perryridge 400 A-201 Brighton 900 A-215 anus 700 A-217 Brighton 750 A-222 Redwood 700 A-305 Round Hill 350 Database System Concepts 3.2 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.2 ©Silberschatz, Korth and Sudarshan Example of a Relation
Basic Structure Formally, given sets D,, D2, .. D, a relation r is a subset of D X D,x.X Thus a relation is a set of n-tuples(a1, a2,..., an)where each a;∈D Example: if customer-name= Jones, Smith, Curry, Lindsay! customer-street= Main, North, Park Then r=[ (Jones, Main, Harrison,'A customer-city Harrison, Rye ittsfieldy (Smith, North, Rye (Curry, North, Rye) (Lindsay, Park, Pittsfield)y is a relation over customer-name x customer-street X customer-city Database System Concepts 3.3 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.3 ©Silberschatz, Korth and Sudarshan Basic Structure Formally, given sets D1 , D2 , …. Dn a relation r is a subset of D1 x D2 x … x Dn Thus a relation is a set of n-tuples (a1 , a2 , …, an ) where each ai Di Example: if customer-name = {Jones, Smith, Curry, Lindsay} customer-street = {Main, North, Park} customer-city = {Harrison, Rye, Pittsfield} Then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield)} is a relation over customer-name x customer-street x customer-city
Attribute Types Each attribute of a relation has a name 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 divisible E.g. multivalued attribute values are not atomic E.g. composite attribute values are not atomic The special value null is a member of every domain The null value causes complications in the definition of many operations we shall ignore the effect of null values in our main presentation and consider their effect later 你忘利 Database System Concepts 3.4 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.4 ©Silberschatz, Korth and Sudarshan Attribute Types Each attribute of a relation has a name 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 E.g. multivalued attribute values are not atomic E.g. composite attribute values are not atomic The special value null is a member of every domain The null value causes complications in the definition of many operations we shall ignore the effect of null values in our main presentation and consider their effect later
Relation schema Al, A2,..., An are attributes 只=(A1,A2,…,An) is a relation schema E.g. Customer-schema (customer-name, customer-street, customer-city) rR)is a relation on the relation schema R E.g. customer (Customer-schema) 标 Database System Concepts 3.5 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.5 ©Silberschatz, Korth and Sudarshan Relation Schema A1 , A2 , …, An are attributes R = (A1 , A2 , …, An ) is a relation schema E.g. Customer-schema = (customer-name, customer-street, customer-city) r(R) is a relation on the relation schema R E.g. customer (Customer-schema)
Relation Instance The current values(relation instance)of a relation are specified by a table An element t of r is a tuple, represented by a row in a table Attributes or columns customer-name customer-street customer-city Jones Main Harrison smith North Rve tupl Curr North Rve (or rows) Lindsay Park Pittsfield customer 标 Database System Concepts 3.6 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.6 ©Silberschatz, Korth and Sudarshan Relation Instance The current values (relation instance) of a relation are specified by a table An element t of r is a tuple, represented by a row in a table Jones Smith Curry Lindsay customer-name Main North North Park customer-street Harrison Rye Rye Pittsfield customer-city customer attributes (or columns) tuples (or rows)
Relations are Unordered Order of tuples is irrelevant (tuples may be stored in an arbitrary order) E.g. account relation with unordered tuples account-number branch-name balance A-101 Downtown 500 A-215 Mianus 700 A-102 Perryridge 400 A-305 Round Hill 350 A-201 Brighton 900 A-222 Redwood 700 A-217 Brighton 750 Database System Concepts 3.7 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.7 ©Silberschatz, Korth and Sudarshan Relations are Unordered Order of tuples is irrelevant (tuples may be stored in an arbitrary order) E.g. account relation with unordered tuples
Database Adatabase consists of multiple relations Information about an enterprise is broken up into parts with each relation storing one part of the information E.g.: account: stores information about accounts depositor: stores information about which customer owns which account customer: stores information about customers Storing all information as a single relation such as bank(account-number, balance, customer-name,. results in repetition of information(e.g. two customers own an account) the need for null values(e.g. represent a customer without an account) Normalization theory ( Chapter 7)deals with how to design relational schemas Database System Concepts 3.8 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.8 ©Silberschatz, Korth and Sudarshan Database A database consists of multiple relations Information about an enterprise is broken up into parts, with each relation storing one part of the information E.g.: account : stores information about accounts depositor : stores information about which customer owns which account customer : stores information about customers Storing all information as a single relation such as bank(account-number, balance, customer-name, ..) results in repetition of information (e.g. two customers own an account) the need for null values (e.g. represent a customer without an account) Normalization theory (Chapter 7) deals with how to design relational schemas
The customer relation customer-name customer-street customer-city Adams Sprin 8 Pittsfield B roo Ks Senator Brooklyn Curry North Rye Glenn Sand hill Woodside Green Walnut Stamford Ha ayes Main Harrison Johnson Alma Palo alto ones Main Harrison Li lindsay Park Pittsfield Smith North Rye Turner Putnam Stamford Williams Na assau Princeton Database System Concepts 3.9 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.9 ©Silberschatz, Korth and Sudarshan The customer Relation
The depositor Relation customer-name account-number Hayes A-102 ohnson A-101 Johnson A-201 ones A-217 Lindsay A-222 Smith A-215 Turner A-305 Database System Concepts 3.10 OSilberschatz, Korth and Sudarshan
Database System Concepts 3.10 ©Silberschatz, Korth and Sudarshan The depositor Relation