DataBase System 沙Key Primary key(EEig): The candidate key that is selected to identify tuples uniquely within the relation. And the others candidate key is called alternate Keys(替换码) A Example: for relation customer(customer id, customer name, customer street customer city Candidate key: customer id,,customer name) Primary key:customer id Haichang Gao, Software School, Xidian University 13
DataBase System Haichang Gao , Software School , Xidian University 13 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}
DataBase System 沙Key For the banking database. we have several relations to store data b account depositor customer branchiae account number Custoher1ae customer-name branch cit branch name account number customer street assets balance customer-citi/ loan borrower oa1numbe1 customer-name branch_name loan number amount c Foreign Key(]3): An attribute, or set of attributes, within one relation RI that matches the candidate key of some(possibly the same) relation R2 H Example: Attribute customer name in table depositor is a Foreign Key references to customer name in customer Haichang Gao, Software School, Xidian University 14
DataBase System Haichang Gao , Software School , Xidian University 14 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
DataBase System 沙Key G Usually, Primary Key and foreign Key was indicated using undirected line and dashed line E 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 Haichang Gao, Software School, Xidian University 15
DataBase System Haichang Gao , Software School , Xidian University 15 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)
DataBase System 6 Integrity of Relational Model Entity integrit ty(实体完整性约束): In a base relation,NO attribute of a primary key can be null G 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 E Example: customer(customer name, customer street, customer city depositor (customer name, account number accountaccount number, branch name, balance Haichang Gao, Software School, Xidian University 16
DataBase System Haichang Gao , Software School , Xidian University 16 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)
DataBase System 6 Integrity of Relational Model Null(E: Represents a value for an attribute that is currently unknown or is not applicable for this tuple. w It is possible for tuples to have a null value, denoted by null for some of their attributes H The result of any arithmetic expression involving null is null. E Aggregate functions simply ignore null values(as in SQL) H For duplicate elimination and grouping, null is treated like any other value and two nulls are assumed to be the same (as in SQL H Null value is noto of integer, NOT empty string, it is JUST NULL Haichang Gao, Software School, Xidian University 17
DataBase System Haichang Gao , Software School , Xidian University 17 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 assumed to be the same (as in SQL) Null value is NOT 0 of integer, NOT empty string , it is JUST NULL