Database Systems: Design, Implementation, and Management Flashcards

Fundamental Database Concepts and Data Management Evolution

• Data vs. Information

  • Data consists of raw, unprocessed facts that have not yet been evaluated to reveal meaning to end users.

  • Information is the result of processing raw data to reveal its underlying meaning. Processing requires appropriate context to render data meaningful.

  • Data serves as the foundation for information, which in turn forms the bedrock of knowledge. Knowledge implies familiarity, awareness, and understanding of information.

  • Accurate, relevant, and timely information is essential for effective organizational decision making.

  • Data management is an overarching discipline focusing on the proper generation, storage, and retrieval of data.

• Database and Database Management Systems (DBMS)

  • A database is a shared, integrated computer structure that stores a collection of:

  • End-user data: Raw facts of interest to the end user.

  • Metadata: Data about data, through which end-user data is integrated, described, and managed. Metadata outlines data characteristics and relationships linking data elements within the database.

  • A Database Management System (DBMS) is a collection of programs that manages the database structure and controls access to data stored within the database.

  • Advantages of utilizing a DBMS include:

  • Improved data sharing

  • Improved data security

  • Better data integration

  • Minimized data inconsistency

  • Improved data access

  • Improved decision making

  • Increased end-user productivity

• Evolution from File Systems

  • Manual File Systems: Historical paper-based storage systems utilizing file folders and filing cabinets.

  • Computerized File Systems: Early computer-based systems managed by Data Processing (DP) specialists to track data and generate periodic reports.

  • Spreadsheets in Data Processing: Business productivity tools like Microsoft Excel allow users to enter data in rows and columns. A common misuse of spreadsheets is attempting to use them as database substitutes.

  • Basic File Terminology:

  • Data: Raw facts such as a telephone number, birth date, customer name, or year-to-date (YTD) sales value. Data holds little intrinsic meaning until organized logically.

  • Field: A character or group of characters (alphabetic or numeric) possessing specific meaning, used to define and store data.

  • Record: A logically connected set of one or more fields describing a single person, place, or thing (e.g., a customer record containing name, address, phone number, birth date, credit limit, and unpaid balance).

  • File: A collection of related records (e.g., a file containing records of all enrolled students at Gigantic University).

• Flaws and Problems of File System Data Processing

  • Lengthy Development Times: Creating new file queries or structures requires extensive custom programming.

  • Difficulty of Getting Quick Answers: Ad hoc queries are difficult or impossible without new code.

  • Complex System Administration: Managing file structures across separate departments creates administrative overhead.

  • Lack of Security and Limited Data Sharing: Files maintained in isolated silos restrict secure cross-departmental access.

  • Structural Dependence: Access to a file depends directly on its physical structure. If a file structure changes, all application programs referencing that file must be modified to conform to the new structure.

  • Data Dependence: All data access programs are tied to data storage characteristics. When storage characteristics change, access programs must change accordingly.

  • Structural Independence vs. Data Independence: Structural independence occurs when structural changes do not impact application access capabilities. Data independence occurs when storage characteristics change without affecting program access.

  • Logical vs. Physical Data Format: Logical format is how human users view data; physical format is how the computer system manages and stores data.

• Data Redundancy and Data Anomalies

  • Data redundancy occurs when identical data is unnecessarily stored in multiple physical locations.

  • Scattered data locations lead to isolated "islands of information," increasing the probability of inconsistent data versions across an enterprise.

  • Consequences of uncontrolled data redundancy include poor data security, data inconsistency, data-entry errors, and data integrity issues.

  • A data anomaly develops when uncoordinated changes occur across redundant data. Anomalies fall into three categories:

  • Update Anomalies: Existing data is modified in one location but not updated in all redundant copies.

  • Insertion Anomalies: Inability to insert a record without inserting dummy or unrelated data.

  • Deletion Anomalies: Unintended loss of critical data when a record is deleted.

Database System Architecture, Functions, and Roles

• The Database System Environment

  • A database system is an organized structure of components defining and regulating data collection, storage, management, and usage within a database environment.

  • The database system consists of five major components:

  • Hardware: Physical computers, storage media, and network devices.

  • Software: Operating systems, DBMS software, and application programs.

  • People: System administrators, database administrators (DBAs), database designers, systems analysts, programmers, and end users.

  • Procedures: Instructions and rules governing system design, operation, and usage.

  • Data: The stored collection of raw facts and metadata.

• Key Functions of a DBMS

  • Data Dictionary Management: Stores definitions of data elements and their relationships in an integrated catalog.

  • Data Storage Management: Creates and manages structures required for data storage, utilizing performance tuning to ensure efficient query execution.

  • Data Transformation and Presentation: Transforms input data into required logical data structures to meet user expectations.

  • Security Management: Enforces user access security, authorization levels, and privacy policies.

  • Multiuser Access Control: Implements concurrent access algorithms to prevent data corruption when multiple users access the system simultaneously.

  • Backup and Recovery Management: Provides tools for automated backups and recovery routines to restore database integrity following hardware or software failures.

  • Data Integrity Management: Promotes and enforces integrity rules, minimizing redundancy and maximizing consistency.

  • Database Access Languages and Application Programming Interfaces: Provides query capabilities through standardized access languages. Structured Query Language (SQL) serves as the de facto query language supported by database vendors.

  • Database Communication Interfaces: Accepts and processes end-user requests across diverse network configurations.

• Database System Disadvantages and Challenges

  • Increased software and hardware acquisition costs

  • Higher organizational management complexity

  • Continuous need to maintain system currency

  • Vendor lock-in and dependence

  • Frequent hardware and software upgrade/replacement cycles

• Database Career Roles

  • Database Developer: Creates and maintains database-based applications (Requires: Programming, database fundamentals, SQL).

  • Database Designer: Designs and maintains database structures and schemas (Requires: Systems design, database design, SQL).

  • Database Administrator (DBA): Manages and maintains DBMS operations and databases (Requires: Database fundamentals, SQL, vendor courses).

  • Database Analyst: Develops databases for decision support reporting (Requires: SQL, query optimization, data warehouses).

  • Database Architect: Designs and implements overall conceptual, logical, and physical database environments (Requires: DBMS fundamentals, data modeling, SQL, hardware knowledge).

  • Database Consultant: Helps organizations leverage database technologies to improve processes (Requires: Database fundamentals, data modeling, database design, SQL, DBMS, hardware, vendor-specific technologies).

  • Database Security Officer: Implements security policies for data administration (Requires: DBMS fundamentals, database administration, SQL, data security technologies).

  • Cloud Computing Data Architect: Designs and implements infrastructure for cloud database systems (Requires: Internet technologies, cloud storage, data security, performance tuning, large databases).

Database Types and Emerging Data Models

• Classification of Databases

  • By Number of Users:

  • Single-user Database: Supports one user at a time (e.g., desktop database on a PC).

  • Multiuser Database: Supports multiple concurrent users (e.g., workgroup database for small teams/departments; enterprise database for organization-wide deployment).

  • By Physical Location:

  • Centralized Database: Data is stored at a single physical site.

  • Distributed Database: Data is spread across different physical locations linked by a network.

  • Cloud Database: Created, maintained, and accessed using cloud data service platforms.

  • By Data Type and Purpose:

  • General-purpose Database: Contains a wide variety of data used across multiple operational disciplines.

  • Discipline-specific Database: Contains specialized data focused on a narrow subject area.

  • Operational (Transactional) Database: Designed to support day-to-day transaction processing.

  • Analytical Database: Stores historical data and business metrics exclusively for tactical and strategic decision making. Composed of:

    • Data Warehouse: Stores data in a format optimized for decision support.

    • Online Analytical Processing (OLAP): Tools used for retrieving, modeling, and processing data from a data warehouse.

  • By Data Structure:

  • Unstructured Data: Data in its original, raw state without defined formatting.

  • Structured Data: Result of formatting unstructured data to facilitate storage, querying, and analysis.

  • Semistructured Data: Data processed to a limited extent (e.g., XML documents).

  • Extensible Markup Language (XML): Textual representation language for data elements. XML databases specialize in storing and managing unstructured or semistructured XML formats.

• Big Data, Internet of Things (IoT), and NoSQL

  • The Internet of Things (IoT) links internet-connected devices continuously generating data, contributing to daily global creation rates of roughly $2.5 \times 10^{18}$ bytes ($2.5$ quintillion bytes) of data.

  • Big Data refers to movement and technologies designed to manage massive amounts of sensor- and web-generated data to extract business insight.

  • The core characteristics of Big Data are described as the 3 Vs:

  • Volume: Unprecedented size and quantity of stored data.

  • Velocity: Extreme speed at which data is generated and processed.

  • Variety: Diverse formats ranging from structured tables to unstructured text, audio, and video.

  • Big Data Technologies:

  • Hadoop: Java-based, open-source distributed storage and computational framework.

  • Hadoop Distributed File System (HDFS): Fault-tolerant distributed file storage system managing huge datasets at high throughput speeds.

  • MapReduce: Open-source application programming interface (API) providing fast distributed analytics.

  • NoSQL (Not Only SQL): Generation of DBMSs designed for non-relational, highly distributed architectures. Supports sparse data, dynamic schema-less key-value or document models, high availability, and massive horizontal scaling over strict ACID transaction consistency.

• Historical Evolution of Data Models

  • Hierarchical Model (1960s): Organized data into inverted trees of record types called segments. Established parent-child structural relationships with rigid navigational access paths.

  • Network Model (1969): Created to represent complex M:N relationships better than hierarchical models. Introduced standard database concepts:

  • Schema: Conceptual organization of the entire database as viewed by the DBA.

  • Subschema: Database portion seen by specific application programs.

  • Data Definition Language (DDL): Language enabling the DBA to define schema components.

  • Data Manipulation Language (DML): Environment and commands used to manipulate stored data.

  • Relational Model (1970): Created by E. F. Codd. Based on mathematical relations represented visually as two-dimensional tables.

  • Entity Relationship Model (1976): Graphical modeling standard introducing entities, attributes, and relationships.

  • Object-Oriented Data Model (OODM, 1980s-1990s): Represented data and associated behavior (methods) in objects. Supported class hierarchies and inheritance. Object/Relational DBMSs (O/R DBMS) incorporated OO capabilities into relational engines.

Data Abstraction Levels and Building Blocks

• ANSI/SPARC Framework: Degrees of Data Abstraction

  • The American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) defined data abstraction levels to separate user views from physical implementation details.

• External Model:

  • The end user's view of the data environment.

  • Expressed as external schemas/views representing specific departmental subsets of the database.

  • Independent of both DBMS software and hardware.

• Conceptual Model:

  • Global view of the entire database structure as seen by the enterprise.

  • Represented as a conceptual schema (typically using Entity Relationship modeling).

  • Independent of both DBMS software and hardware. Creating this model is termed logical design.

• Internal Model:

  • Representation of the database as seen by the chosen DBMS.

  • Maps conceptual constructs to the specific database constructs supported by the software.

  • Dependent on software; independent of hardware.

  • Logical Independence: Ability to modify the internal model without altering the conceptual model.

• Physical Model:

  • Lowest level of abstraction, detailing physical storage media, record placement, and hardware access paths.

  • Dependent on both software and hardware.

  • Physical Independence: Ability to change physical storage devices or structures without affecting the internal model.

  • Data Model Building Blocks

• Entity: A person, place, thing, concept, or event about which data is collected and stored.

• Attribute: A characteristic or property describing an entity.

• Relationship: An association among entities (1:1, 1:M, M:N).

• Constraint: A restriction placed on data to guarantee integrity (e.g., salary ranges, valid GPA limits).

  • Business Rules

• A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle within an organization.

• Derived from managers, policy documentation, department heads, and operational manuals.

• Business rules standardize data views, facilitate user-designer communication, define entity constraints, and dictate relationship connectivities.

• Translating Rules to Data Model Components: Nouns map to entities; active/passive verbs map to relationships between entities (e.g., "a customer may generate many invoices" defines CUSTOMER and INVOICE entities linked by a 1:M generates relationship).

• Naming Conventions: Entity names are uppercase descriptive nouns. Attribute names should prefix the entity name or abbreviation (e.g., CUS_CREDIT_LIMIT in the CUSTOMER entity).

The Relational Database Model and Relational Algebra

• Relational Table Characteristics

  • A relational table (relation) is a logical two-dimensional structure composed of intersecting rows and columns.

  • The characteristics of a relational table include:

  1. Perceived as a two-dimensional structure composed of rows and columns.

  2. Each table row (tuple) represents a single entity occurrence within an entity set.

  3. Each table column represents an attribute with a distinct name.

  4. Each row/column intersection represents a single data value.

  5. All values in a column must conform to the same data format.

  6. Each column has a specific range of values known as the attribute domain.

  7. The order of rows and columns is immaterial to the DBMS.

  8. Each table must have an attribute or combination of attributes that uniquely identifies each row.

• Relational Formal Terminology

  • Relation: A logical table structure storing data.

  • Relvar (Relation Variable): A variable container holding a relation data structure, comprising a Heading (attribute definitions) and a Body (tuples).

• Relational Algebra Set Operators

  • Relational algebra provides mathematical principles for manipulating relational table contents through set operations:

  • SELECT (RESTRICT): Unary operator yielding a subset of rows meeting specified logical conditions.

  • PROJECT: Unary operator yielding a subset of specific columns, removing duplicate rows.

  • UNION: Merges all rows from two union-compatible tables into a single table, dropping duplicates.

  • INTERSECT: Yields only rows common to two union-compatible tables.

  • DIFFERENCE: Yields all rows present in the first table that are not present in the second union-compatible table.

  • PRODUCT (Cartesian Product): Yields all possible pairs of rows formed by combining every row of the first table with every row of the second table.

  • JOIN: Combines related tuples from two or more tables based on matching column conditions.

  • Natural Join: Performs a Cartesian Product, executes a SELECT matching common attribute values, and executes a PROJECT to retain a single copy of common attributes.

  • Equijoin: Links tables based on an explicit equality condition comparing specific columns.

  • Theta Join: Links tables using explicit inequality comparison operators.

  • Inner Join: Returns only matching records present in both joined tables.

  • Outer Join: Retains matched pairs and fills unmatched rows from one side with nulls:

    • Left Outer Join: Retains all rows from the left (first) table regardless of matches in the right table.

    • Right Outer Join: Retains all rows from the right (second) table regardless of matches in the left table.

  • DIVIDE: Takes a binary table and a unary table to yield values in the primary attribute of the binary table associated with all values in the unary table.

Relational Keys, Integrity Rules, and Codd's Rules

• Functional Dependencies and Determination

  • Determination is the state in which knowing the value of one attribute determines the value of another (e.g., $\text{revenue} - \text{cost} = \text{profit}$).

  • Functional Dependence exists when an attribute's value uniquely identifies the value of another attribute ($A ightarrow B$).

  • Determinant: The attribute whose value determines another (the left side, $A$).

  • Dependent: The attribute whose value is determined (the right side, $B$).

  • Full Functional Dependence: Occurs when the dependent attribute is determined by the complete collection of composite determinant attributes, rather than a subset.

• Relational Key Definitions

  • Key Attribute: An attribute that forms part of a key.

  • Superkey: Any attribute or combination of attributes that uniquely identifies any row in a table.

  • Candidate Key: A minimal superkey (a superkey lacking any redundant attributes).

  • Primary Key (PK): A candidate key chosen to uniquely identify all row occurrences within a table. Cannot be null.

  • Foreign Key (FK): An attribute in one table whose values match the primary key values in a related table, or are null.

  • Secondary Key: An attribute used strictly for data retrieval purposes (does not enforce functional dependencies).

  • Composite Key: A key composed of more than one attribute.

• Relational Integrity Rules

  • Entity Integrity:

  • Requirement: All primary key entries must be unique, and no part of a primary key may be null.

  • Purpose: Guarantees that every row has a known, distinct identity so foreign keys can properly reference primary keys.

  • Referential Integrity:

  • Requirement: A foreign key entry must either be null (if not part of its table's primary key) or match a primary key value in the related parent table.

  • Purpose: Guarantees valid references across tables, preventing orphan records and ensuring invalid primary key deletions are blocked when matching foreign keys exist.

• Dr. E. F. Codd's 12 Relational Database Rules

  • Rule 0 (Rule Zero): A relational DBMS must manage databases exclusively through its relational capabilities.

  • Rule 1 (Information): All information must be logically represented as column values in table rows.

  • Rule 2 (Guaranteed Access): Every value is guaranteed accessible via a combination of table name, primary key value, and column name.

  • Rule 3 (Systematic Treatment of Nulls): Nulls must be treated systematically, independent of data type.

  • Rule 4 (Dynamic Online Catalog): Metadata must be stored and queried as ordinary relational tables using standard SQL.

  • Rule 5 (Comprehensive Data Sublanguage): Must support at least one declarative language supporting DDL, DML, view definitions, integrity constraints, authorizations, and transactions (begin, commit, rollback).

  • Rule 6 (View Updating): Any view that is theoretically updatable must be updatable by the system.

  • Rule 7 (High-Level Insert, Update, and Delete): Supports set-level insert, update, and delete operations.

  • Rule 8 (Physical Data Independence): Applications are logically unaffected when physical access methods or storage structures change.

  • Rule 9 (Logical Data Independence): Applications are logically unaffected when structural table changes preserve original data values.

  • Rule 10 (Integrity Independence): Integrity constraints must be definable in SQL and stored in the system catalog, not application code.

  • Rule 11 (Distribution Independence): Applications are unaffected by physical distribution of data across sites.

  • Rule 12 (Nonsubversion): Low-level data access interfaces cannot bypass relational integrity constraints.

Entity Relationship (ER) Modeling Mechanics

• ER Diagram Notations

  • Entity Relationship Diagrams (ERDs) depict conceptual database structures using graphical notations:

• Notations include Chen Notation, Crow's Foot Notation, and UML Class Diagram Notation.

  • Entity Types vs. Entity Instances

• Entity Type: The generic structural collection/table (e.g., EMPLOYEE).

• Entity Instance (Occurrence): A specific individual row within a table.

  • Attribute Classifications

• Required Attribute: Must contain a value; cannot be left null.

• Optional Attribute: May be left empty/null.

• Simple Attribute: Cannot be subdivided into components.

• Composite Attribute: Can be subdivided to yield additional attributes (e.g., EMP_NAME split into EMP_FNAME, EMP_LNAME).

• Single-valued Attribute: Holds only one value per entity instance.

• Multivalued Attribute: Holds multiple values for a single entity instance (e.g., multiple college degrees or color options).

  • Resolving Multivalued Attributes: (1) Create several simple attributes within the entity, or (2) Create a separate related entity type to store the multivalued components.

• Derived Attribute: Calculated from other stored attributes (e.g., AGE calculated from BIRTH_DATE).

  • Relationship Characteristics

• Connectivity: Relationship classification (1:1, 1:M, M:N).

• Cardinality: Expressed as a tuple $(x,y)$ representing minimum ($x$) and maximum ($y$) entity occurrences associated with one occurrence of the related entity.

• Existence Dependence: An entity is existence-dependent if it can exist in the database only when linked to another entity occurrence (has a mandatory foreign key).

• Existence Independence: An entity can exist apart from related entities (strong or regular entity).

• Relationship Strength:

  • Weak (Non-identifying) Relationship: Foreign key of the related entity does NOT contain a primary key component of the parent entity (dashed line in Crow's Foot).

  • Strong (Identifying) Relationship: Foreign key of the related entity contains a primary key component of the parent entity (solid line in Crow's Foot).

• Weak Entity Definition: An entity that meets two conditions:

  1. Existence-dependent on its parent entity.

  2. Inherits part or all of its primary key from its parent entity.

• Participation:

  • Optional Participation: One entity occurrence does not require a corresponding entity occurrence (minimum cardinality 0).

  • Mandatory Participation: One entity occurrence requires a corresponding entity occurrence (minimum cardinality 1).

  • Relationship Degree

• Relationship degree indicates the number of participating entity types:

• Unary Relationship: Association within a single entity type.

• Binary Relationship: Association between two entity types.

• Ternary Relationship: Association among three entity types.

• Recursive Relationship: A unary relationship where occurrences of an entity set associate with other occurrences of the same entity set (e.g., EMPLOYEE manages EMPLOYEE).

• Associative (Composite/Bridge) Entities: Used to transform M:N relationships into two 1:M relationships. Primary key is composed of the primary keys of linked parent entities.

Extended Entity Relationship (EER) Modeling and Design Patterns

• Supertypes, Subtypes, and Specialization Hierarchies

  • Entity Supertype: Generic entity containing common attributes shared across subtypes.

  • Entity Subtype: Specialized entity containing unique attributes and unique relationships.

  • Criteria for Subtyping: (1) Identifiable distinct kinds of the entity exist in the problem domain, and (2) Different kinds have unique attributes or participate in unique relationships.

  • Specialization Hierarchy: Arrangement of supertypes and lower-level subtypes forming "is-a" relationships:

• Inheritance and Discriminators

  • Subtypes inherit all attributes and relationships from their supertypes.

  • Subtypes inherit primary keys from the supertype, maintaining a 1:1 relationship at implementation.

  • Subtype Discriminator: An attribute in the supertype determining the subtype association (defaults to equality, e.g., EMP_TYPE = 'P').

• Disjoint vs. Overlapping Constraints

  • Disjoint Subtypes (Nonoverlapping): An entity instance can belong to at most one subtype (indicated by 'd' in category circle).

  • Overlapping Subtypes: An entity instance can belong to multiple subtypes simultaneously (indicated by 'o' in circle; uses independent discriminator attributes per subtype).

• Completeness Constraints

  • Partial Completeness: Supertype instance is not required to be a member of any subtype (single line under circle).

  • Total Completeness: Every supertype instance must belong to at least one subtype (double line under circle).

  • Specialization Constraint Combinations:

  • Partial Disjoint: Optional subtypes; unique sets; null discriminator allowed.

  • Partial Overlapping: Optional subtypes; non-unique sets; null discriminators allowed.

  • Total Disjoint: Mandatory subtype membership; unique sets; null discriminator disallowed.

  • Total Overlapping: Mandatory subtype membership; non-unique sets; null discriminators disallowed.

  • Specialization vs. Generalization: Specialization is top-down design grouping unique characteristics into subtypes. Generalization is bottom-up design consolidating common characteristics into a supertype.

• Entity Clusters

  • An entity cluster is a virtual entity type combining interrelated entities and relationships in complex ERDs to simplify visual modeling.

• Primary Key Selection Criteria

  • Desirable PK characteristics: Unique values, Non-intelligent (no embedded semantics), Static over time, Single-attribute, Numeric, Security-compliant.

  • Natural vs. Surrogate Keys: Natural keys are real-world business identifiers (e.g., SSN). A Surrogate Key is a system-generated primary key created when natural keys are missing, semantically sensitive, or overly long.

  • Composite Primary Keys: Appropriate as identifiers for composite entities (M:N links) or weak entities in identifying relationships.

• Foreign Key Placement in 1:1 Relationships

  • Case I (1 Mandatory, 1 Optional): Place the PK of the mandatory side into the optional side as an FK, making the FK mandatory.

  • Case II (Both Optional): Place the FK where it causes the fewest nulls, or in the entity playing the relationship role.

  • Case III (Both Mandatory): Combine entities or follow Case II rules.

• Time-Variant Data

  • Refers to data whose values change over time requiring historical retention (e.g., salary histories, management tracking).

  • Modeled by creating a new entity in a 1:M relationship with the primary entity, storing effective dates and historical values.

• Design Traps: Fan Traps

  • A design trap occurs when an ERD represents relationships incorrectly.

  • A Fan Trap occurs when one entity is in two 1:M relationships with other entities, creating an improper association between those entities:

• Resolved by reordering entities into a proper linear relationship chain.