西安电子科技大学：《数据库系统 DataBase System》课程教学资源（PPT课件讲稿）Unit 2 The Relational Model

tE Unit 2 The Relational Model A Relational model H Relational algebra(关系代数) A Relational calculus(关系演算) Haichang Gao, Software School, Xidian University 3

DataBase System Haichang Gao , Software School , Xidian University 3 Unit 2 The Relational Model Relational Model Relational Algebra(关系代数) Relational Calculus(关系演算)

DataBase System Relational model A Laid down in 1969-1970 by Ef.codd A Relational Model of data for large shared Data Bank C aCM 1970 Mathematical Bases( relational Theory) E Developed in 1980s t Most commercial dbms are relational Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 4 Relational Model Laid down in 1969-1970 by E.F.CODD “ A Relational Model of Data for Large Shared Data Bank” C ACM 1970  Mathematical Bases ( Relational Theory)  Developed in 1980s  Most commercial DBMS are Relational

DataBase System Mathematical relations E Formally, given sets D,, D2,.. Dn, a relation r is a subset of Cartesian product笛卡尔积)D1×D2×…,×Dn Thus a relation(关系) is a set of n-tuples(a1,a2,…,an) where a;∈D H EXample: customer-name=Jones, Smith, Curry, Lindsay customer-Street=(Main, North, Park) customer-city =( Ha arrison. Rye, Pittsfield) Then r=(ones, Main, Harrison) (Smith, North, rye) (Curry, North, Rye) Lindsay, Park, Pittsfield)) is a relation over customer-name x customer-street x customer-citi Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 5 Mathematical Relations  Formally, given sets D1 , D2 , …. Dn , a relation r is a subset of Cartesian product(笛卡尔积) D1 × D2 × … × Dn Thus a relation(关系) is a set of n-tuples (a1 , a2 , …, an ) where 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 × customer-street × customer-city

DataBase System Mathematical relations g Each attribute of a relation has a name g The set of allowed values for each attribute is called the domain(tax) of the attribute Attribute values are(normally required to be atomic; that is indivisible Note: multivalued attribute(多值属性) values are not atomic H Note: composite attribute(组合属性) values are not atomic G The special value null is a member of every domain E The null value causes complications in the definition of many operations Haichang Gao, Software School, Xidian University 6

DataBase System Haichang Gao , Software School , Xidian University 6 Mathematical Relations  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  Note: multivalued attribute(多值属性) values are not atomic  Note: 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

DataBase System K Mathematical Relations H Al, A2,..., An are attributes R=(A1,A2,…,An) is a relation schem(关系模式) E.g. Customer-schema customer-name, customer-street, customer-city H r(R)is a relation on the relation schema r E. g. customer( Customer-schema) Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 7 Mathematical Relations  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)

DataBase System K Mathematical Relations a The current values( relation instance,关系实例) of a relation are specified by a table E An element t of ris a tuple(T-a), represented by a row in a table H Order of tuples is irrelevant(元组是无序的 tuples may be stored in an arbitrary order) attributes (or columns) customer_namecustomer_street customer_city Jones Main Harrison Smith North Rye tuples Curry North Rye y(or rows) Lindsay Park Pittsfie/d customer Haichang Gao, Software School, Xidian University 8

DataBase System Haichang Gao , Software School , Xidian University 8 Mathematical Relations  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  Order of tuples is irrelevant (元组是无序的 tuples may be stored in an arbitrary order) 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)

DataBase System Relational database E A Relational database consists of multiple relations E Information about an enterprise is broken up into parts, with each relation storing one part of the information G Example customer_id I customer_ namel customer street customer cit 19283-7465J ohnson 12 Alma st Palo alto 677-89-9011 Hayes 3 Main st H arrison 182-73-6091 Turner 123 Putnam Ave. Stamford 321-12-3123 Jones 100 Main St Harrison 336-66-9999 Lindsay 175 Park Ave Pittsfield 019-28-3746 Smith 72 North st R ve (a) The customer table The customer table stores information about customers Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 9 Relational Database  A Relational database consists of multiple relations  Information about an enterprise is broken up into parts, with each relation storing one part of the information  Example: The customer table stores information about customers

DataBase System Relational database account_number branch_name balance customer_id I account-number A-101 Downtown500 192-83-7465 A-101 A-215 Mianus 700 19283-7465A-201 A-102 Perryridge 400 019-28-3746 A-215 A-305 Round Hill 350 677-89-9011 A-102 A-201 Brighton 900 182-73-609 A-305 A-222 Redwood 700 321-12-3123 A-217 A-217 Brighton 750 336-66-9999 A-222 019-283746 A-201 (b) The account table (c) The depositor table The account table stores information about accounts The depositor table containing information about which customer owns which account Storing all information as a single relation is NoT a good idea! Haichang Gao, Software School, Xidian University 10

DataBase System Haichang Gao , Software School , Xidian University 10 Relational Database (b) The account table (c) The depositor table The account table stores information about accounts. The depositor table containing information about which customer owns which account Storing all information as a single relation is NOT a good idea!

DataBase System 沙Key Let Kcr e K is a superkey(ig)of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) custoer id customer name Customer street customer cit 192-83-7465 Johnson 12 Alma St Palo alto 677-89-9011 Hayes 3 Main st Harrison 182-73-609 Turner 123 Putnam Ave Stamford 321-12-3123 ones 100 Main St Harrison 336-66-9999 Lindsay 175 Park Ave Pittsfield 019-28-3746 Smith 72 North St Rve (a) The customer table E Example: customer id, customer name) and customer id are both superkeys of Customer a by possible r” ve mean a relation r that could exist in the enterprise we are modeling Haichang Gao, Software School, Xidian University 11

DataBase System Haichang Gao , Software School , Xidian University 11 Key  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: {customer_id, customer_name} and {customer_id} are both superkeys of Customer.  by “possible r ” we mean a relation r that could exist in the enterprise we are modeling

DataBase System 沙Key Let Kcr e K is a superkey(ig)of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) g K is a candidate key(候选码) if K is minimal customer-id customer-name custommer street customer -city 192-83-7465 Johnson 12 Alma St Palo alto 677-89-9011 laves 3 Main st H larrison 182-73-6091 Turner 123 Putnam Ave.Stamford 321-12-3123Jo ones 100 Main St Harrison 336-66-9999 Lindsay 175 Park Ave Pittsfield 019-28-3746 Smith 72 North St Rve (a) The customer table H Example: customer id, is a candidate key for Customer since it is a superkey and no subset of it is a superkey H customer name is a candidate key also, assuming no two customers can possibly have the same name Haichang Gao, Software School, Xidian University 12

DataBase System Haichang Gao , Software School , Xidian University 12 Key  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).  K is a candidate key(候选码) if K is minimal.  Example: {customer_id} is a candidate key for Customer, since it is a superkey, and no subset of it is a superkey.  {customer_name} is a candidate key also, assuming no two customers can possibly have the same name