Database Management Systems Overview

Learning Objectives

• Database Management Systems are a central topic
• Data Models are key to understanding databases
• Introduction to SQL is crucial for working with databases
• SQL Queries are used to extract and manipulate data
• XML and JSON are data formats

Data and Management

• Data records real-world processes
• Data is valuable but costly to manage
• Data management involves collecting, storing, organizing, and maintaining data
• Data management aims for data reliability, accessibility, and security

Data Management: Functionalities

• Data management needs to describe real-world entities using stored data
• Creating and storing large datasets efficiently is essential
• Handling complex queries and sophisticated updates is vital
• Performance is critical and needs to be considered

Data Management: More Functionalities

• Database structures need to be adaptable (e.g., adding attributes)
• Handling concurrent operations (queries and updates) is necessary
• Features like crash recovery and access control are important, but these can be costly

DBMS

• Relational database management systems (RDBMS) are a common type
• Hadoop Distributed File System (HDFS)-based systems are a particular type of system
• Stream management systems (like Apache Kafka) are another type of system

Database

• A database is a collection of files containing related data

Database Management System (DBMS)

• A DBMS is software for storing and managing databases
• A DBMS stores a large, integrated collection of data
• DBMS models real-world enterprises using entities and relationships (e.g., students, courses)

Benefits of DBMS

• Data independence and efficient access are crucial benefits
• Database systems reduce application development time
• Data integrity and security are core components
• Uniform data administration is a major benefit
• Concurrent access and recovery from crashes are also beneficial

DBMS Concepts: Data Models

• A data model provides a conceptual way to structure data
• Example data (like book data—author, title, publisher) needs to be well organized
• Several important data models exist
• There are relational models, semi-structured models (like JSON), key-value models, graph models, and object-oriented models

DBMS Concepts: Data Models (Specifics)

• Data can be organized as relations, in trees, as key-value pairs, as graphs, and object-oriented
• Relational data is represented as relational tables.
• Semi-structured data is often represented by trees.

DBMS Concepts: Data Models (Elements)

• The "instance" refers to the actual data stored.
• The "schema" details the data structure itself, describing how the data is stored.
• The "query language" shows how to access the data.
• The relational data model is a common and widely used method

DBMS Concepts: Relational Data Models (Details)

• Data is organized in tables with rows (records) and columns (attributes).
• Tables or relations do not have an inherent ordering of rows
• Tables are not structured with nested attributes
• Rows can be moved
• Data independence is important for relational tables

DBMS Concepts: Relational Data Models ("Degree" of a relation)

• The "degree" of a table/relation refers to the number of attributes (columns).
• Specific data types for each attribute exist, (like strings, numbers, dates, time stamps, monetary values) and are usually vendor specific
• enforcement of data types is often strict

DBMS Concepts: Relational Data Models (Keys)

• Primary keys are minimal attributes that uniquely identify rows.
• Foreign keys link different tables.
• Foreign keys contain values that match a primary key in another table

DBMS Concepts: Relational Data Models (Examples)

• Example Tables for a simplified database. (Students, Courses, Shippers)

DBMS Concepts: Relational Data Models (Example: Schema - "type")

• Example of data types and related attributes like "cname", "country", "no_employees," and "for_profit"

DBMS Concepts: Relational Data Models (Key Points)

• Tables in RDBMS do not have an order
• Rows can be moved
• Data in a table is independent
• Relational models provide insulation from how data is stored.

DBMS Concepts (Out of Scope)

• Concurrency control, Atomicity, Logging, and Layered architecture are not covered in this introductory overview.

What is SQL

• SQL stands for Structured Query Language
• SQL is a standard language for accessing and manipulating databases
• SQL became a standard supported by American National Standards Institute (ANSI) in 1986 and by the International Organization for Standardization (ISO) in 1987.

What Can SQL Do?

• SQL can execute queries to retrieve data
• SQL can add records into databases
• SQL can modify existing records in databases
• SQL can remove database records
• SQL can create new tables
• SQL can create stored procedures (a set of instructions stored as a single unit in a database).
• SQL can set permissions on tables and procedures

What is SQL (Alternative Versions)

• Although SQL is a standard, different versions of the language exist.
• Various commands exist in SQL (e.g., SELECT, UPDATE , DELETE, INSERT, WHERE).
• Database programs often feature proprietary commands

What is SQL (Example)

• To build a website needing database data, an RDBMS database system (e.g. MS Access, SQL Server, or MySQL), a server-side scripting language (e.g. PHP or ASP.NET), and proper SQL commands are needed along with HTML and CSS

Quick SQL Examples

• Specific SQL queries to demonstrate retrieving data

SQL Details (Categories)

• SQL commands are grouped into DDL, DQL, DML, and TCL categories

SQL: DDL (Data Definition Language)

• DDL commands create, alter, modify, or delete database structures.

SQL: DQL (Data Query Language)

• DQL commands get data; including a SELECT command.

SQL: DML (Data Manipulation Language)

• DML commands modify data records such as INSERT, UPDATE, and DELETE commands.

SQL: TCL (Transaction Control Language)

• TCL commands manage transactions. These include commands like COMMIT and ROLLBACK.

SQL: DCL (Data Control Language)

• DCL commands deal with rights and permissions in databases and include commands such as GRANT and REVOKE.