Skip to toolbar
Lesson 1 of 5
In Progress

1. Fundamental Concepts of Database System

Tibebu 16/05/2021

Lesson one:

Fundamental Concepts of Database System

Outline

  • Applications of database technology
  • Key definitions
  • Elements of a database system
  • SQL Overview

Applications of database technology

  • Big Data applications (e.g., Walmart)
  • Storage and retrieval of traditional numeric and alphanumeric data in an inventory application
  • Multimedia applications (e.g., YouTube, Spotify)
  • Biometric applications (e.g., fingerprints, retina scans)
  • Wearable applications (e.g., FitBit, Apple Watch)
  • Geographical information systems (GIS) applications (e.g., Google Maps)
  • Sensor applications (e.g., nuclear reactor)
  • Internet of Things (IoT) applications

Key definitions

Database

  • Database is a collection of interrelated data, which
    • Is logically coherent with some inherent meaning
    • Represent some aspects of (a subset of) the real word: Universe of Discourse (UoD)
    • Has an intended group of users and applications

Database Management System (DBMS)

  • A database management system (DBMS) is a software package used to define, create, use, and maintain a database.
    • E.g. Microsoft SQL Server, MySQL, MS-Access, PostgreSQL
  • Popular DBMS vendors are Oracle, Microsoft, and IBM.
  • MySQL is a well-known open-source DBMS.

Database System 

  • The combination of a DBMS and a database is often called a database system.
  • Database + DBMS = database system

Elements of a Database System

Database Model

  • Database model or database schema provides the description of the database data at different levels of detail.
  • It specifies the various data items, their characteristics, and relationships, constraints, storage details, etc.
  • Specified during database design and not expected to change too frequently.
  • Stored in the catalog

Database State

  • Database state represents the data in the database at a particular moment.
  • Begins with an “initial” state
  • Also called the current set of instance
  • Typically changes on an ongoing basis

Data Model

  • A database model comprises different data models, each describing the data from different perspectives
  • A data model provides a clear and unambiguous description of the data items, their relationships, and various data constraints from a particular perspective

A conceptual data model

  • Provides a high-level description of the data items (e.g Student, Course, Sections) with their characteristics(e.g StudID, Name,..) and relationships (e.g. STUDENT take Sections)
  • Communication instrument between information architect and business user
  • Should be implementation-independent, user-friendly, and close to how the business user perceives the data
  • Usually represented using an enhanced-entity relationship (EER) model, or an object-oriented model e.g., UML

Logical data model

  • A logical data model is a translation or mapping of the conceptual data model toward a specific implementation environment

  • Can be a hierarchical, CODASYL, relational, object-oriented, extended relational, XML, or NoSQL model

Internal data model

  • Represents the data’s physical storage details.
  • It clearly describes :
    • which data are stored where,
    • in what format,
    • which indexes are provided to speed up retrieval

Catalog

  • Heart of the DBMS
  • Contains the data definitions, or metadata, of your database application
  • Stores the definitions of the views, logical and internal data models, and synchronizes these three data models to make sure their consistency is guaranteed

Database Users

  • Information architect designs the conceptual data model
    • Closely interacts with the business user
  • Database designer translates the conceptual data model into a logical and internal data model
  • Database administrator (DBA) is responsible for the implementation and monitoring of the database
  • Application developer develops database applications in a programming language such as Java or Python
  • Business user will run these applications to perform specific database operations

Database Languages

  • Data Definition Language (DDL) is used by the DBA to express the database’s external, logical, and internal data models
    • Definitions are stored in the catalog

  • Data Manipulation Language (DML) is used to retrieve, insert, delete, and modify data
    • DML statements can be embedded in a programming language, or entered interactively through a front-end querying tool

  • Structured Query Language (SQL) offers both DDL and DML statements for relational database systems

SQL Overview

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulates databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

  • execute queries against a database
  • retrieve data from a database
  • insert records in a database
  • update records in a database
  • delete records from a database
  • create new databases
  • create new tables in a database
  • create stored procedures in a database
  • create views in a database
  • set permissions on tables, procedures, and views

SQL commands

  • SQL Commands can be grouped into five major categories depending on their functionality.
    1. Data Definition Language (DDL)
    2. Data Manipulation Language (DML)
    3. Data Query Language (DQL)
    4. Transaction Control Language (TCL)
    5. Data Control Language (DCL)

1. Data Definition Language (DDL)

  • These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes, etc.
  • In this category we have CREATE, ALTER, DROP, and TRUNCATE commands.

2. Data Manipulation Language (DML)

  • These SQL commands are used to store, modify, and delete data from database tables.
  • In this category, we have INSERT, UPDATE, and DELETE commands.

3. Data Query Language (DQL)

  • These SQL commands are used to fetch/retrieve data from database tables.
  • In this category, we have only SELECT command.

4. Transaction Control Language (TCL)

  • These SQL commands are used to handle changes that affect the data in the database.
  • Basically, we use these commands within the transaction or to make a stable point during changes in the database at which we can roll back the database state if required.
  • In this category, we have SAVEPOINT, ROLLBACK, and COMMIT commands.

5. Data Control Language (DCL)

  • These SQL commands are used to implement security on database objects like table, view, stored procedure, etc.
  • In this category, we have GRANT and REVOKE commands.

 

Lesson Content