Database Management Systems Overview

Database and Information Management

• Database management systems (DBMS) are software solutions for organizing and managing data resources within information systems.
• Data is a crucial asset in information technology as its absence halts organizational operations.
• Effective data management structures data logically for easy access, efficient processing, rapid retrieval, and effective management.

Learning Objectives

• Students will grasp fundamental database concepts and understand traditional data management challenges.
• They will learn how database management systems (DBMS), particularly relational DBMSs, function and their capabilities.
• Students will understand data management system principles and tools used for improving business performance through data analysis and decision-making.

Introduction

• In information technology (IT), data forms the most valuable asset as its absence causes business operations to cease.
• Data organization and logical structure within information systems (IS) is vital for access, efficient processing, swift retrieval, and effective management.
• Data management encompasses the methodologies for overseeing data as a valuable resource.
• A database is a structured collection of organized data.
• DBMSs are used to manage digital databases for data creation, maintenance, searching, and other access operations.

The Database System Environment

• The database system environment encompasses various roles and components, including analysts, programmers, database designers, and end-users, each with responsibilities connected to data management within the system.
• The environment includes procedures and standards, database designers who design the database structure, and database administrators who oversee data management operations (e.g., maintenance of existing databases).

Databases, Data, and Information

• Database: A collection of organized data.
• Data: Raw, unprocessed items (e.g., text, numbers, images, audio, video).
• Information: Processed and organized data with meaning and use.

Databases, Data, and Information (School Example)

• Data collection, processing, and conversion to information can be illustrated by a school setting, wherein student information is collected, stored in a database, processed to generate student schedules and advising details, which is then presented in usable format(e.g., student ID card).

Databases, Data, and Information, DBMS

• DBMSs (database management systems) are software programs (such as Microsoft Access) enabling users to create, update, and access data.

Databases, Data, and Information, Hierarchy

• Databases are constructed hierarchically, starting with bits and bytes, escalating to fields, records, and files
• These layers of data are logically organized for effective storage and retrieval.

Characters, Fields, Records, and Files

• Data is structured in levels.
• Characters, fields, records, and files form a hierarchical structure.

Databases, Data, and Information, Tables

• Tables are the core structures of a relational database.
• Tables comprise rows (records, tuples) and columns (fields, attributes), containing data organized and related through common data items/key fields.

Primary Key

• A primary key is a unique identifier for each record within a table.

File/Table

• A file (or table) is a structured collection of related records (rows).
• For instance, 100 employee records in a file about "Employee Personal Details."
• In a DBMS, files are integrated into a structured database through DBMS software.

Traditional Filing Systems

• Traditional filing systems are manual data management methods for storing and organizing data.
• Data is managed and updated manually until needed.

File Processing Systems and Databases

• File processing systems store data in disparate files, each specialized by the departments or areas they serve.
• Databases unite data from diverse sources.

Five Common Data Models

• Hierarchical, network, relational, multidimensional, and object-oriented are common data models.

Hierarchical Database

• Hierarchical databases store data in a tree-like structure.
• A parent node can have multiple child nodes, but each child node has only one parent node.

Network Database

• In a network database, nodes are connected, allowing a child node to have multiple parent nodes (many-to-many relationships).

Relational Database

• Data in a relational database is organized into tables with rows and columns.
• Relationships between tables are established using common data elements or key fields.

Relational Database Operations

• DBMS operations like SELECT, JOIN, and PROJECT allow the creation of subsets of existing data (rows and columns) based on criteria.

Multidimensional Database

• Multidimensional databases model data as facts, dimensions, and numeric answers.
• They handle vast amounts of data for decision-making in areas like business, utilizing colloquial language for questions and representing complex relationships.

Object-Oriented Database

• Object-oriented databases organize data using objects, classes, attributes, and methods.

Databases and the Web

• Modern companies employ web-based interfaces for accessing internal databases.
• A typical web-based database setup uses a web server, application server, and a database server.

Database Development

• Database development procedures are typically top-down oriented.
• A phased approach spanning data planning, requirements specification, conceptual design, logical design, and physical design steps is characteristic of database development.

Data Dictionary

• A data dictionary acts as a reference catalog for defining and documenting data elements and their associated attributes.

Database Development Process

• Data planning, Requirements specification, Conceptual Design, logical design, physical design are the steps involved in this process.

Logical Data Models

• Logical data models use a standard method e.g , relational,network,hierarchical, multidimensional, or object-oriented models to show data elements and their relationships. This helps understand the structure and connections amongst these elements.

Physical Data Models

• Physical data models offer a visual representation of how data is structured and stored physically on storage devices.

Database Management System (DBMS)

• Database management systems (DBMS) are software tools used to create, access, modify, and manage different types of information.

Data types in DBMS

• Data types (e.g., text, numeric, date, etc.) specify the kind of data a database field can hold.

Query Language (SQL)

• SQL (Structured Query Language) is a standard language for accessing data in a relational database.
• It provides simple, English-like commands for data management, (e.g., retrieve, print, store, update, delete) tasks).

DBMS tools

• Tools like query languages, query by example (QBE), forms, and report writers assist users in managing data in a database system and extracting information (e.g. to present data for print or display).
• Forms and report writers are visual interface components of a DBMS for enhancing and organizing database records.

MS Access 2013 Objects

• Tables, queries, forms, and reports are the core data objects in Microsoft Access 2013.
• Macro and modules are additional objects within Access.

Access Files

• Older Access database files (before 2007) commonly used the *.mdb extension.
• Newer Access 2007 and subsequent versions utilize *.accdb files.