Database Basics

Photo by Markus Winkler on Unsplash

Why Use a Database Instead of the File System?

• Data redundancy — wasted space
• Update issues — every copy of the data needs to be modified
• Data inconsistency — sometimes every copy is not modified
• Data access issues — getting to just the right data
• Data isolation — pulling all the data from disparate sources together
• Integrity constraints buried in application logic — hard to add to or change
• Atomicity problems — what happens when the system crashes during an important operation?
• Concurrency issues — when multiple users work with the same data at the same time
• Security issues — how to give someone access to some, but not all, of the data

In addition to storing data, the DBMS also stores metadata — data about the data — in a data dictionary

Different Data Models

• Relational model
• Entity-relationship model
• Aggregate data models (NoSQL)
  - Key-value data model
  -Document data model
  -Column-family stores
• Graph model
• Other models
  - Object-based
  -Semi-structured (XML) models
  -Hierarchical
  -Network

DBMS

DBMS software primarily functions as an interface between the end-user and the database, simultaneously managing the data, the database engine, and the database schema in order to facilitate the organization and manipulation of data.

Though functions of DBMS vary greatly, general-purpose DBMS features and capabilities should include: a user-accessible catalog describing metadata, DBMS library management system, data abstraction and independence, data security, logging and auditing of activity, support for concurrency and transactions, support for the authorization of access, access support from remote locations, DBMS data recovery support in the event of damage, and enforcement of constraints to ensure the data follows certain rules.

A database schema design technique that functions to increase clarity in organizing data is referred to as normalization. Normalization in DBMS modifies an existing schema to minimize redundancy and dependency of data by splitting a large table into smaller tables and defining the relationship between them. DBMS Output is a built-in package SQL in DBMS that enables the user to display debugging information and output and send messages from subprograms, packages, PL/SQL blocks, and triggers. Oracle originally developed the DBMS File Transfer package, which provides procedures to copy a binary file within a database or to transfer a binary file between databases.

A database management system functions through the use of system commands, first receiving instructions from a database administrator in DBMS, then instructing the system accordingly, either to retrieve data, modify data, or load existing data from the system.

DBMS Components

• Storage manager
  -Interface between the applications and queries using the system and the low-level data
  -Manages interaction with file system
  - Facilitates efficient storing, retrieving, and updating data
• Query processor
  -Parses and executes queries efficiently

RDBMS vs DBMS

A relational database management system (RDBMS) refers to a collection of programs and capabilities that is designed to enable the user to create, update, and administer a relational database, which is characterized by its structuring of data into logically independent tables. There are several features that distinguish a Relational DBMS from a DBMS, including:

• Structure: Where data is structured in the hierarchical form in a DBMS, data is structured in tabular form in an RDBMS.
• User capacity: A RDBMS is capable of operating with multiple users. DBMS can only manage one user at a time.
• Software/hardware requirements: A RDBMS has greater software and hardware requirements.
• Programs managed: DBMS maintains databases within the computer network and system hard disks. An RDBMS manages the relationships between its incorporated tables of data.
• Data capacity: A DBMS is capable of managing small amounts of data and an RDBMS can manage an unlimited amount of data.
• Distributed databases: A DBMS does not provide support for distributed databases while an RDBMS does.
• ACID implementation: A RDBMS bases the structure of its data on the ACID (Atomicity, Consistency, Isolation, and Durability) model.

A Transaction is a Complete Unit of Work with the Database

• Unit of work with the following (ACID) properties
  -Atomic
  -Consistent
  -Isolated
  -Durable
• Transaction management involves coping with system failures as well as concurrent users