Database Systems: Design, Management, and Implementation, and the Database Design Process

The Database Design Process: Detailed Stages

The database design process is categorized into several distinct stages, transitioning from conceptual requirements to physical implementation and hardware-specific configurations.

• Stage $9-4$: Conceptual Design

  • Focus: This stage is DBMS and hardware independent.
  • Data Analysis and Requirements: Determine end-user views, outputs, and transaction requirements.
  • Entity Relationship (ER) Modeling and Normalization:
    • Define entities, attributes, domains, and relationships.
    • Draw ER diagrams and normalize entity attributes.
  • Data Model Verification:
    • Identify ER modules and validate rules for insert, update, and delete operations.
    • Validate reports, queries, views, integrity, access, and security measures.
  • Distributed Database Design: Define the fragmentation and allocation strategy (explored further in Chapter $12$).

• Stage $9-5$: DBMS Selection

  • Focus: This stage is DBMS dependent.
  • Activities: Determine the specific Database Management System and data model to be utilized.

• Stage $9-6$: Logical Design

  • Focus: This stage is DBMS dependent.
  • Mapping: Map the conceptual model to logical model components.
  • Validation:
    • Validate the logical model using normalization.
    • Validate logical modeling integrity constraints.
    • Validate the model against user requirements.
  • Specifications: Define tables, columns, relationships, and constraints, resulting in a normalized set of tables. Ensure entity and referential integrity and define column constraints.

• Stage $9-7$: Physical Design

  • Focus: This stage is hardware dependent.
  • Data Storage: Define data storage organization and the physical organization of tables, indexes, and views.
  • Integrity and Security: Define integrity measures, security, users, security groups, roles, and access controls.
  • Performance: Determine performance measures and define database and query execution parameters (explored further in Chapter $11$).

Data Modeling Standards and Business Rules

Effective database design relies on established rules and naming conventions to ensure clarity, consistency, and alignment with business needs.

• Business Rules:

  • Must be documented and verified with end users.
  • Must be written precisely, clearly, and simply to help identify entities, attributes, relationships, and constraints.
  • Each rule must have an identified source and be justified, dated, and signed off by an approving authority.

• Naming Conventions:

  • All names must be limited in length according to the specific DBMS size constraints.
  • Entity Names: Should be nouns familiar to the business, short, meaningful, and unique. Abbreviations, synonyms, and aliases must be documented. Composite entities may use combinations of abbreviated names of linked entities.
  • Attribute Names: Must be unique within the entity. Should use the entity abbreviation as a prefix and use suffixes like _ID, _NUM, or _CODE for Primary Keys (PK). They must not be reserved words or contain special characters (@, !, &).
  • Relationship Names: Should be active or passive verbs indicating the nature of the relationship.

• Entity Guidelines:

  • Each entity should represent a single subject with distinguishable entity instances.
  • Required to be in $3NF$ (Third Normal Form) or higher; any lower normalization must be justified.
  • The granularity of entity instances and the PK must be clearly defined.

• Attribute Guidelines:

  • Should be simple and single-valued (atomic data).
  • Nonkey attributes must be fully dependent on the PK.
  • Redundancy is disallowed unless justified for performance, transaction accuracy, or history.

• ER Model Integrity:

  • Validate against expected insert, update, and delete processes.
  • Minimize data redundancy to ensure single-place updates.
  • Minimal Data Rule: "All that is needed is there and all that is there is needed."

Foundations of Data, Information, and Knowledge

Understanding the progression from raw data to actionable knowledge is critical for database systems.

• Data: Raw facts that have not yet been processed to reveal meaning (e.g., a list of faculty IDs and names in a database row).
• Information: The result of processing raw data to reveal meaning through context and organization. For example, a raw list of faculty becomes information when summarized into a chart showing that $20\%$ of the Information Systems (INFS) department consists of adjunct faculty.
• Knowledge: The body of information and facts about a specific subject. It implies familiarity and understanding, allowing the derivation of "new" knowledge from "old" knowledge.
• Data Management: A discipline focusing on the proper generation, storage, and retrieval of data.

The Pervasive Nature of Databases and Daily Life

Databases are ubiquitous (abundant/global) and pervasive (inescapable/persistent), tracking individual activities from birth to death.

• Hypothetical Case: A Day in Susan's Life:

  • Grocery Shopping: Interacts with inventory and sales databases; checks credit card validity.
  • Pharmacy: Accesses prescription and inventory data.
  • Online Shopping (Amazon): Interacts with product data, stock updates, and recommendation systems based on customer data.
  • Travel Planning: Online websites source data from airline and hotel databases.
  • Social Media: Interaction with users, friends, posts, and "likes" databases.

• Mobile Privacy and Tracking:

  • Smartphones use GPS Functionality and Bluetooth pairing to generate data.
  • Phone "Knowledge": Assumes "Home" is where you spend night hours and "Work" is where you spend day hours. It uses Bluetooth to remember where a car was last "parked."
  • Apple Disclaimer: GPS data is collected to "learn places that are significant to the end-user," but is stored locally and not shared.

Key Characteristics and Roles of a DBMS

A Database Management System (DBMS) is an intermediary between the user and the database, which consists of end-user data and metadata.

• Metadata: Data about data. It describes data characteristics and relationships (e.g., name, type, and whether a field can be null). This makes a database "self-describing."
• Advantages of a DBMS:
  • Improved Data Sharing: Multiple users/applications can access the same data.
  • Improved Security: Frameworks for data privacy and security policies.
  • Data Integration: Provides a "big picture" view of organizational operations.
  • Minimized Inconsistency: Reduces risks of different versions of the same data appearing in different places (e.g., different name formats or prices in different offices).
  • Improved Access via Ad Hoc Queries: Provides quick answers to spur-of-the-moment questions (e.g., "What was the dollar volume of sales last month?").
  • Data Quality: Promotes accuracy, validity, and timeliness.

Types and Classifications of Databases

Databases are classified by user count, location, usage, and structure.

• Number of Users:

  • Single-user: Supports one user at a time (e.g., a desktop database).
  • Multiuser: Supports many users simultaneously.
  • Workgroup: Supports fewer than $50$ users or a specific department.
  • Enterprise: Supports more than $50$ users across an entire organization.

• Location:

  • Centralized: Data at a single site.
  • Distributed: Data across several different sites.
  • Cloud Databases: Created/maintained using services like Microsoft Azure or Amazon AWS. Benefits include rapid provisioning (e.g., $3M$ Health Information Systems reduced provisioning time from $10$ weeks to minutes).

• Usage and Time Sensitivity:

  • Operational (OLTP): Supports day-to-day operations (Production/Transactional databases).
  • Analytical: Focuses on historical data and business metrics for strategic decision-making.
    • Data Warehouse: Specialized database for decision support.
    • Online Analytical Processing (OLAP): Tools for processing and modeling data in the warehouse.

• Degree of Structure:

  • Unstructured: Raw state data.
  • Structured: Formatted data ready for processing.
  • Semistructured: Processed to an extent using tags (e.g., XML/HTML).
  • NoSQL (Not Only SQL): Designed for social media and massive data loads. Examples include Twitter (over $500$ million tweets/day) and Facebook (incoming $500$ terabytes/day, storage over $100$ petabytes, scanning $200$ terabytes/hour).

Importance of Database Design: Case Study of Poor vs. Good Design

Poor design leads to data integrity issues and processing inefficiencies.

• Example of Poor Design (Single Table for Skills):

  • Structure: Uses columns like Skill1, Skill2, Skill3 for employee certifications.
  • Problems:
    1. Searching: Difficult to alphabetize by last name if the name field is a single string (e.g., "Brian Oates").
    2. Counting: To count "Basic Database Manipulation" certifications, one must check and sum variables across three separate columns.
    3. Consistency: High risk of spelling variations (e.g., "Basic Database Manipulation" vs. "Basic DB Manipulation").
    4. Scalability: Requires structural changes (adding columns) if an employee earns a $4^{th}$ or $5^{th}$ skill.

• Example of Good Design (Relational Decomposition):

  • Structure: Decomposed into three tables: EMPLOYEE, SKILL, and CERTIFIED (linked by IDs).
  • SQL Advantages:
    • Alphabetical Listing: SELECT * FROM EMPLOYEE ORDER BY EMPLOYEE_LNAME;
    • Efficient Counting: SELECT COUNT(*) FROM SKILL JOIN CERTIFIED ON SKILL.SKILL_ID = CERTIFIED.SKILL_ID WHERE SKILL_NAME = 'Basic Database Manipulation';

Evolution of Data Management

• Manual File Systems: Historically used paper-and-pencil, folders, and filing cabinets. Limitations increased as organizational reporting needs became more complex.
• Computerized File Systems: Early digital organization, now largely outmoded due to structural and data dependency limitations.
• Database Systems: Developed to overcome file system shortcomings, ensuring data is an organizational resource rather than a limitation.