Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Ensuring Database IntegrityEntity IntegrityExamples of Entity IntegrityEnforcing IntegrityReferential IntegritySlide 20Using SQL- Structured Query LanguageSQL ExamplesData RetrievalUPDATE tablesDatabase NormalizationNormalizationEx: Database Normalization (1)Ex: Database Normalization (2)Ex: Database Normalization (3)Ex: Database Normalization (4)Ex: Database Normalization (5)The Normal FormsFirst Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Slide 36What is a Data Warehouse?Slide 38Data MiningDW CharacteristicsData AcquisitionData CleansingDATA DATA RESOURCERESOURCEMANAGEMENTMANAGEMENTData Hierarchy in a Computer SystemEntitities and AttributesData redundancyProgram-Data dependenceLack of flexibilityPoor securityLack of data-sharing and availabilityProblems with the Traditional File EnvironmentTraditional File ProcessingFigure 7-3•Creates and maintains databasesCreates and maintains databases•Eliminates requirement for data definition Eliminates requirement for data definition statementsstatements•Acts as interface between application Acts as interface between application programs and physical data filesprograms and physical data files•Separates logical and physical views of dataSeparates logical and physical views of dataDatabase Management System (DBMS)The Contemporary Database EnvironmentComponents of DBMS•Data definition language: Data definition language: Specifies Specifies content and structure of database and content and structure of database and defines each data elementdefines each data element•Data manipulation language:Data manipulation language:Manipulates data in a databaseManipulates data in a database•Data dictionary:Data dictionary: Stores definitions of Stores definitions of data elements, and data characteristicsdata elements, and data characteristicsSample Data Dictionary ReportFigure 7-6Relational Data ModelThree Basic Operations in a Relational Database•Select:Select: Creates subset of rows that meet Creates subset of rows that meet specific criteriaspecific criteria•Join:Join: Combines relational tables to provide Combines relational tables to provide users with informationusers with information•Project:Project: Enables users to create new tables Enables users to create new tables containing only relevant informationcontaining only relevant informationFigure 7-7Three Basic Operations in a Relational DatabaseFLAT FILE – NOT NORMALIZEDA Normalized Relation of ORDEREnsuring Database IntegrityDatabase integrity involves the maintenance of the logical and business rules of the database.There are two kinds of “DB Integrity” that must be addressed:Entity IntegrityReferential IntegrityEntity IntegrityEntity integrity deals with within-entity rules.These rules deal with ranges and the permission of null values in attributes or possibly between recordsExamples of Entity IntegrityData Type Integrity: very common and most basic. Checks only for “data type” compatibility with DB Schema, such as: numeric, character, logical, date format, etc.Commonly referred to in GIS manuals as:Range and List domainsRanges - acceptable Numeric ranges for inputList - acceptable text entries or drop-down lists.Enforcing IntegrityNot a trivial task!Not all database management systems or GIS software enable users to “enforce data integrity” during attribute entry or edit sessions.Therefore, the programmer or the Database Administrator must enforce and/or check for “Integrity.”Referential IntegrityReferential integrity concerns two or more tables that are related.Example: IF table A contains a foreign key that matches the primary key of table B THEN values of this foreign key either match the value of the primary key for a row in table B or must be null.Necessary to avoid: Update anomaly, Delete anomaly.Basic SQL CommandsSELECT: Specifies columns FROM: Identifies tables or viewsWHERE: Specifies conditions Querying Databases: Elements of SQLUsing SQL- Structured Query LanguageSQL is a standard database protocol, adopted by most ‘relational’ databasesProvides syntax for data:Definition RetrievalFunctions (COUNT, SUM, MIN, MAX, etc)Updates and DeletesSQL ExamplesCREATE TABLE SALESREPItem definition expression(s){item, type, (width)}DELETE tableWHERE expressionData RetrievalSELECT list FROM table WHERE conditionlist - a list of items or * for all itemsWHERE - a logical expression limiting the number of records selectedcan be combined with Boolean logic: AND, OR, NOTORDER may be used to format resultsUPDATE tablesSET item = expressionWHERE expressionINSERT INTO tableVALUES …..Database NormalizationNormalization: The process of structuring data to minimize duplication and inconsistencies. The process usually involves breaking down a single Table into two or more tables and defining relationships between those tables. Normalization is usually done in stages, with each stage applying more rigorous rules to the types of information which can be stored in a table.NormalizationNormalization: a process for analyzing the design of a relational databaseDatabase Design - Arrangement of attributes into entitiesIt permits the identification of potential problems in your database designConcepts related to Normalization:KEYS and FUNCTIONAL DEPENDENCEEx: Database Normalization (1)Sample Student Activities DB TablePoorly DesignedNon-unique recordsJohn SmithTest the Design by developing sample reports and queriesCreated a unique “ID” for each Record in the Activities TableRequired the creation of an “ID” look-up table for reporting (Students Table)Converted the “Flat-File into a Relational DatabaseEx: Database Normalization (2)Ex: Database Normalization (3)Wasted SpaceRedundant data entryWhat about taking a 3rd Activity?Query Difficulties - trying to find all swimmersData Inconsistencies - conflicting pricesEx: Database Normalization (4)Students table is fineElimination of two columns and an Activities Table restructuring, Simplifies the TableBUT, we still have Redundant data (activity fees) and data insertion anomalies.Problem: If student #219 transfers we lose all references to Golf and its price.Ex: Database Normalization
View Full Document