Database Management Systems Overview

Learning Objectives

• Database Management Systems will be covered
• Data Models will be discussed
• Introduction to SQL will be provided
• SQL Queries will be explored
• XML and JSON will be explained

Data and Management

• Data represents traces of real-world processes
• Data is valuable but hard and costly to manage
• Data management involves collecting, storing, organizing, and maintaining data for reliability, accessibility, and security.

Data Management: Functionalities

• Describe real-world entities in terms of stored data
• Create and persistently store large datasets
• Efficiently query and update
• Handle complex questions and sophisticated updates
• Performance is crucial

Data Management: Further Functionalities

• Change data structures (e.g., add attributes)
• Enable concurrent queries and updates
• Implement crash recovery
• Ensure access control, security, and integrity
• Implementing all these features is challenging and costly.

DBMS

• Relational database management systems (RDBMS)
• Hadoop Distributed File System (HDFS) systems
• Stream management systems (e.g., Apache Kafka)

Database

• A database is a collection of files, storing related data

Database Management System (DBMS)

• DBMS is a software package designed to store and manage databases
• DBMS models real-world enterprises
• DBMS models entities (e.g., students, courses) and relationships (e.g., Jim taking course DA106)

Benefits of Database Management System

• Data independence and efficient access
• Reduced application development time
• Data integrity and security
• Uniform data administration
• Concurrent access and crash recovery

DBMS Concepts: Data Models

• A data model provides a general and conceptual way to structure data
• How should book data (author, title, publisher, publication date, price) be organized in files?

DBMS Concepts: Data Models (types)

• Relational: Data represented as relations
• Semi-structured (JSON): Data represented as trees
• Key-value pairs: Used by NoSQL systems
• Graph: Data represented as graphs
• Object-oriented: Data represented via objects

DBMS Concepts: Elements of Data Model

• Instance: Actual data
• Schema: Description of data being stored
• Query language: How data can be retrieved and manipulated
• Relational model is the most widely used (tables with rows and columns) 
• Each relation has a schema: Describes the columns

DBMS Concepts: Relational Data Models (details)

• Instance: Organized as tables/relations
• Schema: Table name, column name, attribute type/domain/data type
• "Degree" of a relation: Number of attributes
• Example data types: CHAR, VARCHAR, TEXT, INT, SMALLINT, FLOAT, MONEY, DATETIME

DBMS Concepts: Relational Data Models (keys)

• Primary Key: A minimal set of attributes uniquely identifying rows in a table
• Foreign Key: A key linking two tables, a column in one table matching a key in a different table

DBMS Concepts: Relational Data Models (example)

• Tables (e.g., Student, Course) with relational data (rows, columns, primary & foreign keys)

DBMS Concepts: Relational Data Models (example schema)

• Example schema with fields (e.g., company name, country, no. employees, for profit status) and data types

DBMS Concepts: Relational Data Models (key points)

• Tables not ordered; rows can be exchanged
• Tables don't include nested attributes
• Tables maintain data independence
• Applications are insulated
• Protection against changes in logical and physical structure

DBMS Concepts: Relational Data Models (out-of-scope)

• Concurrency control, Atomicity, Logging, Layered architecture (not for the current lectures)

What is SQL?

• SQL stands for Structured Query Language.
• Standard language for working with databases
• SQL is a standard set by ANSI/ISO
• Major commands (e.g. SELECT, UPDATE, DELETE, INSERT, WHERE) can be used in a similar manner

What can SQL do?

• Execute queries
• Retrieve data
• Insert records
• Update records
• Delete records
• Create new databases/tables
• Create stored procedures
• Set permissions on tables & procedures

What is SQL (example usages)

• Building a website showing data extracted from a database needs an RDBMS database program (e.g., MS Access, SQL Server, MySQL) and server-side scripting languages (e.g., PHP, ASP)
• Using SQL to get data and HTML/CSS to format the website
• Different versions of SQL (although commands are generally the same)

Quick SQL Examples

• Code examples of SQL queries (e.g., selecting all records from a Orders table; selecting unique EmployeeID, selecting records from Customers ordered by Country)

SQL Details (Categories)

• Data Definition Language (DDL): Used to define database schema (creation, alteration, deletion of objects)
• Data Query Language (DQL): Used to query and retrieve data, mainly using the SELECT statement.
• Data Manipulation Language (DML): Used to manipulate data (INSERT, UPDATE, DELETE)
• Data Control Language (DCL): Used to control rights and permissions
• Transaction Control Language (TCL): Used to manage transactions (COMMIT, ROLLBACK, SAVEPOINT)