Transcript Note12
CS3754 Class Note 12 Summery of Relational Database Information Systems Today 3-1 (©2006 Prentice Hall) Database Technology • A collection of related data organized in a way that makes it valuable and useful • Allows organizations to retrieve, store, and analyze information easily • Is vital to an organization’s success in running operations and making decisions Information Systems Today 3-2 (©2006 Prentice Hall) Database Terminology Entities • Things we store information about. (i.e. persons, places, objects, events, etc.) • Have relationships to other entities (i.e. the entity Student has a relationship to the entity Grades in a University Student database Attributes • These are pieces of information about an entity (i.e. Student ID, Name, etc. for the entity Student) Information Systems Today 3-3 (©2006 Prentice Hall) File Processing vs Database Approach Summary File Processing Approach (Old School) • Storage Media: Sequential tapes or files • Data: stored in long sequential files • Organization: redundant data in multiple files • Efficiency: data embedded to support processing • Updates: requires multiple updates in many files • Processing: slower query/faster processing Data Base Approach (New School-TODAY) • Storage Media: Direct Access Storage Device (DASD) • Data: stored in related tables • Organization: redundant data minimized/eliminated • Efficiency: data only stored only in tables • Updates: requires few or one update for a data field • Processing: faster query/slower processing Information Systems Today 3-4 (©2006 Prentice Hall) Advantages of the Database Approach Information Systems Today 3-5 (©2006 Prentice Hall) Roles in Database Development and Use Database Administrator (DBA) • Designs, develops and monitors performance of databases • Enforces policy and standards for data uses and security Systems Programmer • Creates business applications that connect to databases • Tests the new systems and databases before use Information Systems Today 3-6 Systems Analyst • Defines data requirements working with a DBA • Incorporates the database design into new program designs (©2006 Prentice Hall) Designing Databases – Data Model ER or EER Data Model (Conceptual Data Model) • A map or diagram that represents entities and their relationships • Used by Database Administrators to design tables with their corresponding associations Example: ERD (Entity Relationship Diagram) Information Systems Today 3-7 (©2006 Prentice Hall) Designing Databases – Keys Database Keys Mechanisms used to identify, select, and maintain one or more records using an application program, query, or report Primary Key A unique attribute type used to identify a single instance of an entity. Compound Primary Key A unique combination of attributes types used to identify a single instance of an entity Secondary Key An attribute that can be used to identify one or more records within a table with a given value Information Systems Today 3-8 (©2006 Prentice Hall) Designing Databases – Keys (Example) Primary Key ENTITIES - Student ID Entities are translated into Tables (Students and Grades) Secondary Key - Major Entities are joined by common attributes Compound Primary Key - Student ID - Course ID - Sec No. - Term Information Systems Today 3-9 (©2006 Prentice Hall) Designing Databases - Associations Associations • Define the relationships one entity has to another • Determine necessary key structures to access data • Come in three relationship types: - One-to-One - One-to-Many - Many-to-Many Foreign Key • An attribute that appears as a non-primary key in one entity (table) and as a primary key attribute in another entity (table) Information Systems Today 3-10 (©2006 Prentice Hall) Designing Databases - Associations Entity Relationship Diagram (ERD) • Diagramming tool used to express entity relationships • Very useful in developing complex databases Example • Each Home Stadium has a Team (One-to-One) • Each Team has Players (One-to-Many) • Each Team Participates in Games • For each Player and Game there are Game Statistics Information Systems Today 3-11 (©2006 Prentice Hall) Designing Databases - Associations Information Systems Today 3-12 (©2006 Prentice Hall) Designing Databases – Associations (Example) Information Systems Today 3-13 (©2006 Prentice Hall) The Relational Model The Relational Model • The most common type of database model used today in organizations • Is a three-dimensional model compared to the traditional two-dimensional database models - Rows (first-dimension) - Columns (second-dimension) - Relationships (third-dimension) • The third-dimension makes this model so powerful because any row of data can be related to any other row or rows of data Information Systems Today 3-14 (©2006 Prentice Hall) The Relational Model - Example Information Systems Today 3-15 (©2006 Prentice Hall) The Relational Model - Normalization Normalization • A technique to make complex databases more efficient by eliminating as much redundant data as possible • Example: Database with redundant data (below) Information Systems Today 3-16 (©2006 Prentice Hall) The Relational Model - Normalization Normalized Database Information Systems Today 3-17 (©2006 Prentice Hall) The Relational Model – Data Dictionary Data Dictionary • Is a document that database designers prepare to help individuals enter data • Provides several pieces of information about each attribute in the database including: - Name - Key (is it a key or part of a key) - Data Type (date, alpha-numeric, numeric, etc.) - Valid Value (the format or numbers allowed) • Can be used to enforce Business Rules which are captured by the database designer to prevent illegal or illogical values from entering the database. (e.g. who has authority to enter certain kinds of data) Information Systems Today 3-18 (©2006 Prentice Hall) Organizational Use of Databases Operational Informational Extract Data Extract Data Department Databases Data Warehouse • Day to Day Department Transactions • Used primarily by departments • Extracted Department transactions • Used for business analysis Information Systems Today 3-19 Data Mart • Extracted subset of a data warehouse • Used for highly specific business analysis (©2006 Prentice Hall) Data Mining Data Mining • Is a method companies use to analyze information to better understand their customers, products, markets, or any other phase of their business for which they have data • With data mining tools you can graphically drill down, sort or extract data based on certain conditions, perform a variety of statistical analysis • Data mining applications are very powerful and use highly complex algorithms to analyze and to identify opportunities Information Systems Today 3-20 (©2006 Prentice Hall) Data Warehouse Example Information Systems Today 3-21 (©2006 Prentice Hall) Uses of Data Warehousing Information Systems Today 3-22 (©2006 Prentice Hall)