LJ

1.0-Pre-Class-Self Study-Introduction to DBMS and Data Modeling I-A2020-2

Pre-Class Self Study

  • Main Reference: Modern Database Management, 11th Edition, Chapter 1 & 2: Modeling Data in the Organization

  • Subject Coordinator and Instructor: Dr. Danna (Fahimeh) Ramezani

Module 1: Self Study on Databases

  • Focus: Why Databases & Introduction to DBMS

  • Note: This section is for additional information and perspective. The information will not be examined.

  • Date: 2/17/2025

Traditional File Processing System

Issues

  • Duplicate Data: In traditional systems, multiple departments may hold separate records of the same data, leading to inconsistencies.

  • Departments Affected: Orders, Accounting, Payroll.

Illustration

  • Figure 1-2: Shows Old file processing systems at Pine Valley Furniture Company with master files like Customer Master File, Employee Master File, etc.

Disadvantages of File Processing

  • Program-Data Dependence: Each program needs to maintain distinct metadata for each file.

  • Data Duplication: Different systems have separate data copies, risking data integrity.

  • Limited Data Sharing: No centralized data control, leading to difficulties in collaboration.

  • Lengthy Development Times: Programmers design custom file formats.

  • Excessive Program Maintenance: 80% of budgets spent on maintaining these systems.

Problems with Data Dependency

  • Maintenance Responsibilities: Each programmer must handle their own data and its associated code.

  • Processing Routines: Programs need unique routines for data operations.

  • Coordination Issues: Lack of central control hampers data handling.

  • Non-standard File Formats: Leads to compatibility issues across systems.

Problems with Data Redundancy

  • Space Waste: Storing duplicate data unnecessarily consumes resources.

  • Maintenance Issues: Duplication causes headache in updates and reports.

  • Integrity Risks: Data inconsistencies arise when updates aren't synchronized across duplicates.

Problems with Spreadsheets

  • Redundancy Issues: Each spreadsheet entry stands alone, leading to repeated data entries.

  • Comparison Example: The BoyGirl spreadsheet versus a relational database highlights redundancy problems.

BoyGirl Relational Database Design

  • Design Flaw Correction: A relational database reduces redundant information about individuals (e.g., Bonnie).

  • Tables Relationship: Demonstrates a One-to-Many relationship linked by a Foreign Key and Primary Key structure.

Issues with Data Updates

  • Delete & Update Problems: Inconsistent states can occur when deleting or updating records with duplicates.

  • Insert Errors: Multiple entries can lead to inconsistencies and higher costs.

Solution: The Database Approach

  • Central Repository: Data shared and managed by a centralized system.

  • Controlling Agent: Ensures systematic access and control over data.

  • Requirement: Utilization of a Database Management System (DBMS).

Database Management System (DBMS)

  • Functionality: Defines, creates, maintains databases, and ensures controlled access to data.

  • Systems Managed: Order filing, invoicing, and payroll data handled by the DBMS.

Advantages of the Database Approach

  • Independence & Consistency: Reduces reliance on individual program data formats, fostering improved consistency and sharing.

  • Enhanced Productivity: Streamlines application development and enforces data management standards.

  • Improved Quality & Accessibility: Enhances decision support and minimizes maintenance burdens.

Costs and Risks of the Database Approach

  • Personnel Needs: Involves hiring specialized workforce.

  • Installation Costs: Complexity in installation and management leads to higher expenses.

  • Organizational Conflicts: Potential resistance from existing staff or systems.

Components of the Database Environment

  • Data Modeling Tools: Automated solutions for database design.

  • Repository: Central storage of metadata information.

  • User Interface: Various methods for user interaction with the database.

  • Roles: Includes data administrators and system developers.

Three-Schema Architecture

Structure

  • External Schema: User views limiting data shown are relevant to the user.

  • Conceptual Schema: Overall structure of data and its interrelations without specifying physical storage.

  • Internal Schema: Physical representation and storage details of the data.

ANSI-SPARC Standards

  • Conceptual specifications stated as technology-independent.

Summary of ANSI-SPARC Architecture

  • External Level: Details user-specific views excluding unauthorized access data.

  • Conceptual Level: Overall description of stored data and relations.

  • Internal Level: Physical description in relation to computer storage systems and data models.