数据库逻辑模型及关系模型.ppt

Chapter 6:Logical Database Design and the Relational Model;Overview;Relational Data Model;Relational Data Model;Relational Data Model;Relational Notation;Correspondence with ER Model;Key Fields;Figure 6-5 -- Schema for four relations (Pine Valley Furniture);Emp_ID;Emp_ID;Emp_ID;Emp_ID;Relational Database;Customer_ID;Textual Notation CUSTOMER(Customer_ID, Customer_Name, Address, City, State, Zip) ORDER(Order_ID, Order_Date, Customer_ID) ORDER_LINE(Order_ID, Product_ID, Quantity) PRODUCT(Product_ID, Product_Description, Product_Finish, Unit_Price, On_Hand) Note Customer_ID is FKey on ORDER Both Order_ID and Product_ID are Fkey on ORDER_LINE, but difficult to show ;Diagram Notation;Integrity Constraints;domain constraint = Specification of the set of values that can be assigned domain name meaning or description data type size (length), decimal places, etc. allowable values (list or range);Entity Integrity;NULL value;Referential Integrity;Figure 5-5: Referential integrity constraints (Pine Valley Furniture);Well-Structured Relations;Example – Figure 5.2b;Anomalies in this Table;Emp_ID;Data Normalization;Functional Dependencies and Keys;Normalization;Normalization;Definition:constraint between two attributes or two sets of attributes The value of one attribute (the determinant) determines the value of another attribute A ? B B is functionally dependant on A A is called the determinant SIN ? Name, Birthdate, Address? ISBN ? Title, Author Emp_ID, Course_Name ? Date_Completed eg;Candidate Key;Candidate Key;Emp_ID;Emp_ID;A relation which contains no multi-valued attributes (no arrays);Figure 5-10: Mapping a multivalued attribute;Emp_ID;2NF – Second Normal Form;Fig 5.23(b) – Functional Dependencies in EMPLOYEE2;Getting it into 2nd Normal Form;Emp_ID;Guarantee 2NF if relation is 1NF and either Primary key is a single attribute No non-key attributes in relation (every attribute is part of the key) ;3NF – Third Normal Form;3NF – Third Normal Form;Figure 5-24(b) Re