Database Systems,2,Unit 2 The Relational Model,Relational Model Relational Algebra(关系代数) Relational Calculus(关系演算),3,Relational Model,Laid down in 1969-1970 by E.F.CODD “ A Relational Model of Data for Large Shared Data Bank” , ACM 1970 Mathematical Bases ( Relational Theory) Developed in 1980s Most commercial DBMS are Relational,4,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.,5,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,6,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),7,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),attributes (or columns),tuples (or rows),8,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,9,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!,10,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.,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).,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.,12,Key,Primary key(主码): The candidate key that is selected to identify tuples uniquely within the relation. And the others candidate key is called Alternate Keys(替换码). Example: for relation customer (customer_id, customer_name, customer_street, customer_city) Candidate key : customer_id, customer_name Primary key: customer_id,13,Key,For the banking database, we have several relations to store data:,Foreign Key(外码): An attribute, or set of attributes,within one relation R1 that matches the candidate key of some(possibly the same) relation R2. Example: Attribute customer_name in table depositor is a Foreign Key references to customer_name in customer.,14,Key,Usually, Primary Key and Foreign Key was indicated using undirected line and dashed line. Example: Find out PK and FK of the following schema: Student(sno, sname, ssex, sbirth, sdept) Course(cno, cname, cpno, ccredit) SC(sno, cno, grade),15,Integrity of Relational Model,Entity integrity(实体完整性约束): In a base relation, NO attribute of a primary key can be null. Referential integrity(参照完整性约束): If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation(被参照关系) or the foreign key value must be wholly null. Example: customer (customer_name, customer_street, customer_city) depositor (customer_name, account_number) account (account_number, branch_name, balance),16,Integrity of Relational Model,Null(空值): Represents a value for an attribute that is currently unknown or is not applicable for this tuple. It is possible for tuples to have a null value, denoted by null, for some of their attributes The result of any arithmetic expression involving null is null. Aggregate functions simply ignore null values (as in SQL) For duplicate elimination and grouping, null is treated like any other value, and two nulls are