,5.,*,2007 by Prentice Hall,5,Chapter,Foundations of Business Intelligence:Databases and Information Management,The Database Approach to Data Management,Database:,Collection of related files containing records on people,places,or things,Prior to digital databases,business used file cabinets with paper files,Entity,Generalized category representing person,place,thing on which we store and maintain information,E.g.SUPPLIER,PART,Attributes:,Specific characteristics of each entity,e.g.:,SUPPLIER name,address,PART description,unit price,supplier,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,The Database Approach to Data Management,Relational database,Organize data into two-dimensional tables(relations)with columns and rows,One table for each entity,E.g.(CUSTOMER,SUPPLIER,PART,SALES),Fields,(columns)store data representing an attribute,Rows store data for separate,records,Key field:,Uniquely identifies each record,Primary key:,One field in each table,Cannot be duplicated,Provides unique identifier for all information in any row,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,A Relational Database Table,Figure 5-1,A relational database organizes data in the form of two-dimensional tables.Illustrated here is a table for the entity SUPPLIER showing how it represents the entity and its attributes.Supplier_Number is the key field.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,The PART Table,Figure 5-2,Data for the entity PART have their own separate table.Part_Number is the primary key and Supplier_Number is the foreign key,enabling users to find related information from the SUPPLIER table about the supplier for each part.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,The Database Approach to Data Management,Establishing relationships,Entity-relationship diagram,used to clarify table relationships in a relational database,Relational database tables may have:,One-to-one relationship,One-to-many relationship,Many-to-many relationship,Requires creating a table(join table,Intersection relation)that links the two tables to join information,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,Entity-relationship,ONE-TO-ONE:,CLASS,MONITOR,ONE-TO-MANY:,CLASS,STUDENT,A,STUDENT,B,STUDENT,C,MANY-TO-MANY:,STUDENT,A,STUDENT,B,STUDENT,C,COURSE,1,COURSE,2,A Simple Entity-Relationship Diagram,Figure 5-3,This diagram shows the relationship between the entities SUPPLIER and PART.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,The Database Approach to Data Management,Normalization,Process of streamlining complex groups of data to:,Minimize redundant data elements,Minimize awkward many-to-many relationships,Increase stability and flexibility,Referential integrity rules(数据参照完整性规那么,Used by relational databases to ensure that relationships between coupled tables remain consistent,E.g.When one table has a foreign key that points to another table,you may not add a record to the table with foreign key unless there is a corresponding record in the linked table,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,Sample Order Report,Figure 5-4,The shaded areas show which data came from the SUPPLIER,LINE_ITEM,and ORDER tables.The database does not maintain data on Extended Price or Order Total because they can be derived from other data in the tables.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,The Final Database Design with Sample Records,Figure 5-5,The final design of the database for suppliers,parts,and orders has four tables.The LINE_ITEM table is a join table that eliminates the many-to-many relationship between ORDER and PART.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,Entity-Relationship Diagram for the Database,with Four Tables,Figure 5-6,This diagram shows the relationship between the entities SUPPLIER,ART,LINE_ITEM,and ORDER.,The Database Approach to Data Management,Essentials of Business Information Systems,Chapter 5 Foundations of Business Intelligence:Databases and Information Management,Specific type of software for creating,storing,organizing,and accessing data from a database,Separates the logical and physical views of the dat