Database Concepts: Data, DBMS, and Systems

• Data are facts that are recorded and can be accessed.
• Data formats include text, numbers, figures, graphics, images, audio/video recordings, and more.
• Information are data accessed by a user for a specific purpose, often requiring processing or manipulation.

Database Definition

• A database is a structured collection of related data stored on a computer medium.
• Databases organize data for efficient access to information.

Database Management System (DBMS)

• A DBMS is software used for creating, inserting, storing, retrieving, updating, and deleting data in the database.
• DBMS software also maintains databases.

Database System

• A database system is a computer-based system which purpose is to enable efficient interaction between users and databases.
• It includes the DBMS software with the data itself and sometimes applications.

Database Metadata

• A DBMS catalog stores metadata, which is the description of a database, including data structures, types, and constraints.
• Database Metadata represents the structure, content, and characteristics of the data.
• Metadata is essential for proper understanding and use of data.

Database Schema vs Database State

• A database schema includes descriptions of the database structure, data types, and constraints, and is also called intension.
• Database schema changes very infrequently.
• A database state refers to the content at a particular point in time, and is also called an instance, occurrence, snapshot, or extension.
• Database State changes every time the database is updated
• A valid state satisfies the structure and constraints of the database.

DBMS Languages

• A Data Definition Language (DDL) is used to specify the conceptual schema of a database, and often used to define internal and external schemas (views)
• A Data Manipulation Language (DML) is used to specify database retrievals and updates.
• Stand-alone DML commands are also called a query language.

Typical Database System Architecture

• Front-End Applications interact with users.
• The DBMS manages the database.
• The database contains the stored data.

Database Entities (University Registration System Example)

• common entities include: STUDENTS, COURSES, SECTIONS (of COURSES), DEPARTMENTS (academic), and INSTRUCTORS.

Database Relationships (University Registration System Example)

• SECTIONS are specific to COURSEs.
• STUDENTs enrolls in SECTIONs.
• COURSEs require prerequisite COURSEs.
• INSTRUCTORs teach SECTIONs.
• COURSEs comes from DEPARTMENTS.
• STUDENTs major in DEPARTMENTS.
• These entities and relationships are typically expressed in an Entity-Relationship data model.

Typical DBMS Functionality

• Defining a database in terms of data types, structures, and constraints.
• Constructing or loading the initial database contents on a secondary storage medium.
• Manipulating the database through retrieval (querying, generating reports), modification (insertions, deletions, updates), and web applications.
• Processing and sharing by a set of concurrent users and application programs while maintaining data validity and consistency.

Additional DBMS Functionality

• Protection or Security measures prevent unauthorized access.
• Data Presentation and Visualization.
• Backup and Restoring the database.

Interaction with Databases

• Front-end applications provide a mechanism for easy interaction between users and the DBMS.
• End-users (business-users) use a database system to support their tasks and processes.
• Indirect interaction involves interacting with the database through front-end applications.
• Direct interaction involves interacting with the database directly through the DBMS.

Steps in the Development of Database Systems

• Database Requirements: Collection, Definition, and Visualization.
• Database Modeling: Logical Modeling.
• Database Implementation.
• Database Deployment.
• Database Use: Direct and/or indirect use.
• Database Administration and Maintenance.
• The database requirements collection, definition, and visualization process is iterative.
• Collected requirements are clearly defined, written, and visualized, often using a conceptual database model like entity-relationship (ER) modeling.

Database Modeling

• Database modeling (logical database modeling) creates a database model that is implementable by the DBMS software
• Logical database modeling follows conceptual database modeling

Database Implementation

• Database implementation: using a DBMS to implement the database model as an actual database.
• Most modern databases are implemented using a relational DBMS (RDBMS) software.
• SQL (Structured Query Language) is a language used by most RDBMS packages.

Developing Front-End Applications

• Designing and creating applications for indirect use by the end-users.
• Front-end applications are based on the database model and the requirements specifying the front-end functionalities.
• Front-end applications contain interfaces (such as forms and reports) accessible via a navigation mechanism (such as a menu).

Database Deployment

• Releasing the database system for use by the end users.

Database Use

• Data insertion, modification, deletion, and retrieval.

Database Administration and Maintenance

• Performance activities include supporting the database end user and dealing with technical issues
• Providing security for the information contained in the database
• Ensuring sufficient hard-drive space for the database content
• Implementing the backup and recovery procedures

People Involved with Database Systems

• Database analysts: Involved in the requirements collection, definition, and visualization stage.
• Database designers (a.k.a. database modelers or architects): Involved in the database modeling stage.
  • Responsible for defining the content, structure, constraints, and functions/transactions.
  • Must communicate with end-users and understand their needs.
• Database developers: In charge of implementing the database model as a functioning database using DBMS software.
• Front-end application analysts: In charge of collecting and defining requirements for front-end applications.
• Front-end application developers: Creating the front-end applications
• Database administrators (DBAs): Perform tasks related to maintenance and administration of a database system.
  • Responsible for authorizing access, coordinating use, acquiring resources, controlling usage, and monitoring efficiency.
• Database end users: Use the data for queries and reports, and some update the database content.
  • Support work or life-related tasks and processes
  • Categorized into Casual, Naïve/Parametric, and Sophisticated.

End-User Categories

• Casual users: Access a database occasionally when needed.
• Naïve or Parametric users: Large section of users using previously defined functions.
  • Examples include mobile app users, bank tellers, and reservation clerks.
• Sophisticated users: Use business analyst, scientist, engineers tools and have thorough familiarity of the system capabilities.
• Stand-alone users: Maintain personal databases with ready-to-use packaged applications
• Examples include users of tax programs and personal media databases.

Operational vs. Analytical Databases

• Operational information (transactional information): Collected and used for day-to-day operational needs.
• Operational database: Collects and presents operation information.
• Analytical information: Collected and used to support analytical tasks.
  • Analytical information is based on operational (transactional) information
  • Analytical database: Collects and presents analytical information.

Advantages of Using the Database Approach

• Reduced data redundancy in storage, development, and maintenance.
• Restricted unauthorized data access.
• Efficient query processing via storage structures (e.g., indexes).
• Optimized queries for efficient processing.
• Backup and recovery services.
• Multiple interfaces for different user classes.
• Enforced integrity constraints.