U

Lecture3B

Lecture Overview

  • Instructor: Dr. Abhijit Sengupta

  • Contact: abhijit.sengupta@surrey.ac.uk

  • Institution: University of Surrey

Learning Objectives

  • Understand the challenges of managing data in traditional file environments.

  • Explore the capabilities of Database Management Systems (DBMS) and assess the strengths of relational DBMS.

Issues in Traditional File Environment

Data Management Problems

  • Redundancy: Same data stored in multiple locations, leading to inefficiencies.

  • Inconsistency: Different versions of the same data create discrepancies.

  • Quality Control:

    • Challenges in ensuring authenticity, accuracy, and timeliness of data.

  • Flexibility: Limited ability to generate ad-hoc reports or accommodate unexpected data needs.

  • Security concerns: Difficult to track who accesses or alters data.

  • Sharing Limitations: Hindered flow of information across various departments.

Database Solutions

Benefits of Database Approach

  • Centralized Data Storage: All data is logically integrated, reducing redundancy.

  • Minimized Inconsistency: Consistency is maintained across the database due to centralized control.

  • Multi-Application Support: One database can serve multiple applications.

  • Data Integrity: Implementation of standards, access rules, and constraints enhances data quality.

Database Components

Entity-Relationship Model

  • Entities: Defined objects in the database (e.g., Customer, Employee).

  • Primary Keys (PK): Unique identifiers for records in a table (e.g., emplID).

  • Foreign Keys (FK): Primary keys utilized in another table, allowing the creation of relationships between tables.

Example Tables

  • Customer Table:

    • Fields: custID, custName, custCity.

  • Employee Table:

    • Fields: emplID, emplName, emplPhone.

ER Diagram Insights

  • Relationships among entities accessed using PKs and FKs.

  • Data integrity maintained through careful definition of keys (underlined in diagrams).

Database Normalization

Definition & Purpose

  • Normalization: Process of organizing data to eliminate redundancy and ensure integrity.

  • Goals:

    • Save storage space by storing each piece of data only once.

    • Avoid data inconsistency by updating data in one place to reflect all necessary views of that data.

    • Facilitate rapid updates and maintain data integrity.

Normalization Levels

  • Un-normalised Data: Displays potential redundancies and inconsistencies.

  • Normalised Tables: Structured in a way to represent data without redundancy, organized efficiently.

Good Practices in Database Design

  • Resolve many-to-many relationships by converting attributes into identifiable entities.

  • Break data into smaller, manageable units for accuracy. For example, separating detailed address components.

Conclusion

  • The discussion emphasizes the transition from traditional file-based data management to a structured database approach to enhance efficiency, accuracy, and security within organizational frameworks.