Databases Information Quality Characteristics of high quality information include Accuracy Completeness Consistency Uniqueness Timeliness Security Costs of Poor Information Management Examples of potential business effects resulting from low quality information include Difficulty identifying valuable customers Inability to identify selling opportunities Marketing to nonexistent customers Difficulty tracking revenue due to inaccurate invoices Inability to build strong customer relationships Basic Database Terminology entities things of interest Major Components of a Database Examples Entities and Attributes Database A single table or collection of related tables representing a structured collection of data and information about Data Warehouse A set of databases designed to support decision making in an organization Data Mart A database focused on addressing a specific problem or business unit Entity A person place thing or event about which data and information are collected Attribute A category of data that describes an entity Summary core database concepts Database collection of tables files each table represents one entity Table a collection of rows records instances of an entity Record a collection of fields columns Field an attribute of an entity Database Design The Conceptual Model E R Entity Relationship Diagrams model the relationships among the entities in the database What is an E R Diagram o A picture of the people places objects things events or concepts their characteristics and relationships o A communication tool o A foundation for designing the database Entity person place object event etc about which data will be maintained labeled with descriptive nouns Relationships An association between instances of entity types labeled with descriptive verbs Components of ER diagrams Relationships cardinality Cardinality the number of instances of one entity type that are associated with a single instance of another entity type in a Minimum if zero then the entity is an optional participant if 1 then the entity is a mandatory participant in the relationship Maximum fixed or many relationship Why Do Conceptual Modeling Understandable representation of organizational data Helps to elicit business rules Communication and validation of requirements and business reality Provide the blueprint for the development process Linking Table Independent of technology Key Database Terminology A Primary Key or key is an attribute or group of attributes that uniquely identifies a row record in a table o Every table must have a primary key o Often numeric e g autonumber No NULL values A Foreign Key is a non key attribute in one table that appears as the primary key in another table What should be in a table Each table should Have a primary key Describe a single entity Not include derived attributes e g calculations like total order price Normalization A technique to make databases more efficient by removing undesirable redundancy Break large tables into several smaller tables Efficient for storage and transaction processing Why Care About Normalization Working with unnormalized data is inefficient and error prone because the data contains unnecessary redundancies Problems caused by redundancy Storage inefficiencies Wasted storage space and Processing inefficiencies Multiple updates Errors Requiring multiple updates inconsistencies likely Three Basic Operations in a Relational Database Select Creates subset of rows that meet specific criteria Example select all orders placed in May 2008 Join Combines tables to provide users with information Example print student course schedules joins info from student registration and course tables Project Enables users to create new tables containing only relevant information Example create a table containing student name and number of credits registered for Data Integrity Integrity constraint A rule that helps ensure the quality of information Referential Integrity Foreign keys must match a primary key in the linked table and cannot be blank Business Rules examples Order quantity cannot be negative Phone number must have an area code
View Full Document
Unlocking...