Introduction to Database Management Systems

Introduction to Database Management Systems

  • Instructor: Saiwu Lin

  • Institution: Saunders College of Business, Rochester Institute of Technology

  • Contact: slin@saunders.rit.edu

TAL Distributors Overview

  • Purpose: TAL Distributors serves as the primary example throughout the lectures.

  • Topics:

    • Basic database terminologies.

    • Exploration of Database Management Systems (DBMSs).

    • Advantages and disadvantages of database processing.

    • Evolution of database systems.

    • Overview of various database applications including forms and reports.

Data Management Basics

  • Data: Stored representations of meaningful objects and events.

    • Types of Data:

      • Structured: Numbers, text, dates.

      • Unstructured: Images, videos, documents.

    • Information: Data processed to enhance knowledge for the user.

Difference Between Structured and Unstructured Data

  • Structured Data:

    • Characteristics: Pre-defined data models, mainly text, easy to search.

    • Typical Applications: Relational databases, data warehouses.

    • Examples: Airline reservation systems, inventory control.

  • Unstructured Data:

    • Characteristics: No pre-defined data model, may include various formats.

    • Difficult to search, often necessitating NoSQL databases.

    • Examples: Word processing documents, emails, media files.

Contextual Data Understanding

  • Example: Class Roster for MGT 500 - Spring 2015.

  • Importance of context in understanding data sets, includes detailed descriptors like name, ID, major, and GPA.

Data Visualization

  • Graphical displays enhance data utility for managerial decision-making.

  • Summarized data aids in interpretation and analysis.

Metadata

  • Definitions of properties and characteristics of data including data types, field sizes, allowable values, and data context.

Database Fundamentals

  • Database: A large, integrated collection of data modeling real-world enterprises with entities (e.g., teams, games) and their relationships.

  • DBMS: Software system designed for storing, managing, and facilitating access to databases.

Clarity on Database Purposes

  • Essence: To manage and track various entities across different categories like students, professors, courses, and departments.

TAL Distributors Case Study

  • Background:

    • Wholesaler of wooden toys using spreadsheets for data management.

    • Growth has highlighted inefficiencies with this method.

Problems with Spreadsheet Usage

  • Redundancy and duplication of data,

  • Challenges in accessing related data, security concerns, and size limitations

Essential Information for TAL Distributors

  • Needed Data Points:

    • Sales Rep Information: Rep number, name, address, commission details.

    • Customer Info: Customer number, name, address, balance, credit limits.

    • Inventory Items: Item number, description, units on hand, pricing, and categorization.

Database Structure and Entity Concepts

  • Understanding the structure that can store information about various categories and relationships.

  • Entities: Defined as persons, places, or concepts (customers, orders).

  • Attributes: Characteristics or properties of entities (e.g., names, addresses).

Relationships in Databases

  • Types of Relationships:

    • One-to-Many (1:M): One entity relates to multiple instances of another.

    • Many-to-Many (M:N): Multiple instances relate to multiple others.

    • One-to-One (1:1): One instance corresponds to one instance of another (e.g., dean→college).

Visual Representation: Entity-Relationship (E-R) Diagram

  • Diagrams visually represent databases with rectangles (entities) and lines (relationships).

Database Management System (DBMS)

  • Programs facilitating user interaction with databases; includes relational DBMS like Access, Oracle, MySQL, etc.

  • Purpose of a DBMS: Allows for rapid creation of forms and reports, aiding in data accessibility and queries.

Forms and Reports in DBMS

  • Forms: Interface objects to maintain and view data from databases.

  • Reports: Formatted outputs of database queries for decision-making purposes.

Types of Databases

  • Personal Databases: Sizing in MB, intended for individual users.

  • Departmental Databases: GB size, multiple users, not exceeding 100.

  • Enterprise Databases: Large scale, suitable for extensive user bases.

Multi-Tiered Client/Server Database Architecture

  • Components:

    • Client tier: Interface for users.

    • Application/Web tier: Processes user requests.

    • Enterprise tier: Centralized management of data.

Enterprise Systems and Data Warehousing

  • Enterprise Applications: Involve integrated relational databases for core organizational functions (ERP, CRM, etc.).

  • Data Lakes: Store vast amounts of unstructured data without a predefined schema.

Output Example: Executive Dashboard**

  • Displays key metrics like sales and profit ratio, allowing users to filter and analyze results dynamically.

Conclusion: Issues with Non-Database Approaches

  • Major challenges include redundancy, security weaknesses, and inefficient data management.

  • Understanding entities, attributes, and relationships is fundamental for effective database management.