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

Chapter 6:Logical Database Design and the Relational Model JIANGMIN COMP256 Overview Objective is to translate the conceptual data model into a logical database design that can be implemented on a chosen DBMS Notation for relational data model Integrity Constraints Transforming ERD to relational model Normalization and Normal Form (NF) Relational Data Model Represents data in the form of tables Data Structure Data organized in the form of tables with rows and columns Data Manipulation Operations to manipulate the data stored in the tables Data Integrity Facilities to specify business rules that maintain the integrity of the data when manipulated Relational Data Model Relation Named two dimensional table of data Set of named columns Arbitrary number of rows Relational Data Model Relation showing sample data Correspondence with ER Model Relations (tables) correspond with entity types and with many-to-many relationship types Rows correspond with entity instances and with many-to-many relationship instances Columns correspond with attributes NOTE: The word relation (in relational database) is NOT the same same the word relationship (in ER model) Key Fields Primary key(Ms access) An attribute (or combination) that uniquely identifies each row in a relation Foreign key(eg) Represents relationship between 2 tables Attribute (or combination) in a relation that is the primary key of another relation in same database Composite key Key consists of more than one attribute Functional Dependencies and Keys Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute Candidate Key: A unique identifier. One of the candidate keys will become the primary key E.g. perhaps there is both credit card number and SS# in a table…in this case both are candidate keys Each non-key field is functionally dependent on every candidate key Candidate Key Must satisfy subset of primary key properties: Unique Identification Implies that eac