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.