Addis Ababa University, School of Commerce | Updated: Feb, 2025 1
Chapter - 2
Understanding
Database Languages
SQL + DDL + DML
Addis Ababa University, School of Commerce | Updated: Feb, 2025 2
Learning Objectives
• By the end of this chapter, students will:
• Understand the concept of database sublanguages, including DDL and DML.
• Explain the types and functions of Data Definition Language (DDL).
• Explain the types and functions of Data Manipulation Language (DML).
• Differentiate between procedural and nonprocedural DMLs.
• Define and describe the components of data models.
• Differentiate between object-based, record-based, and physical data models.
• Understand the role of conceptual modeling in database design.
• List and describe key functions of a Database Management System (DBMS).
Addis Ababa University, School of Commerce | Updated: Feb, 2025 3
Database Languages
• Database languages are specialized languages used to interact with
databases. They are broadly divided into two components:
• Data Definition Language (DDL): Defines the structure (schema) of the database.
• Data Manipulation Language (DML): Allows users to insert, retrieve, update, and
delete data.
• Together, these form a data sublanguage.
• Database Language Types
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
Addis Ababa University, School of Commerce | Updated: Feb, 2025 4
Database Languages
• Data Definition Language (DDL)
• Purpose:
• Used by database administrators (DBAs) or users to describe the schema: entities, attributes,
relationships, integrity rules, and security constraints.
• Key Concepts:
• Does not manipulate data, only defines its structure.
• Results in a system catalog (data dictionary), which stores metadata (i.e., data about the data).
• Key Commands:
• CREATE: Build tables, databases.
• ALTER: Modify existing structures.
• DROP: Delete objects.
• TRUNCATE: Remove all data but keep structure.
• Real-World Analogy:
• Think of DDL as an architect’s blueprint: you design the layout (structure) of the house
(database) before building anything inside it
Addis Ababa University, School of Commerce | Updated: Feb, 2025 5
Database Languages
• Data Definition Language (DDL)
• For example, if we're creating a student database, we'd use:
• This defines the structure before we add any actual student data.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 6
Database Languages
• Data Manipulation Language (DML)
• Purpose: Manage data within tables (CRUD operations).
• Key Concepts:
• DML operates on data, not structure
• Key Commands:
• INSERT: Add new data.
• SELECT: Retrieve data.
• UPDATE: Modify data.
• DELETE: Remove data.
• Types of DML:
• Procedural DML
• You specify how to get data.
• Often involves loops, cursors, and control structures.
• Nonprocedural DML (Declarative)
• You specify what data you want, not how to get it.
• Easier for end-users and analysts.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 7
Database Languages
• Data Manipulation Language (DML)
• Example: To add a student and then retrieve computer science students:
• Notice we just declare what we want, not how to find it."
Addis Ababa University, School of Commerce | Updated: Feb, 2025 8
Database Languages
• Data Control Language (DCL)
• DCL is used to control access to data in a database. It defines who can access or
manipulate data and what actions they are allowed to perform.
• Ensures only authorized users can view/edit data.
• Key Commands
• GRANT – Gives a user access privileges to the database.
• REVOKE – Removes user access privileges.
• Example:
• Imagine a university database where:
• The first allows Professor Williams to view student data, while:
• would prevent a teaching assistant from deleting student records.
• Proper DCL usage is crucial for data security.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 9
Database Languages
• Transaction Control Language (TCL)
• TCL manages changes made by DML statements and ensures data integrity. It allows
grouping operations into atomic transactions, meaning either all operations succeed or
none do.
• Key Commands
• COMMIT – Saves all changes made by the transaction.
• ROLLBACK – Undoes changes since the last COMMIT.
• SAVEPOINT – Sets a point within a transaction to which you can later roll back.
• SET TRANSACTION – Sets the properties of a transaction.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 10
Database Languages
• How They Work Together • Analogy:
• DDL sets up the "container" (tables). • DDL = Building a bookshelf.
• DML fills/edits the container (data). • DML = Adding/reading/removing books.
• DCL locks/unlocks the container. • DCL = Who has a key to the room.
• TCL ensures safe changes. • TCL = Undoing a misplaced book.
• SQL
• combines all four types into one language.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 11
Data Models Overview
• What is a Data Model?
• A data model is a conceptual representation that defines:
• What data is stored
• How the data is structured
• How data elements relate to one another
• What constraints exist on the data
• Purpose:
• To help in designing a database by providing a clear structure and rules for
organizing and manipulating data
Addis Ababa University, School of Commerce | Updated: Feb, 2025 12
Data Models Overview
• Key Components of a Data Model
• Structural Component
• Describes the types of data (entities, attributes, relationships).
• Example: "Student" entity with "Name", "ID", and "Course" attributes.
• Manipulative Component
• Defines operations for querying and modifying data.
• Example: SQL statements like SELECT, UPDATE, JOIN.
• Integrity Rules (Constraints)
• Ensure data is accurate and consistent.
• Example: Student ID must be unique.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 13
Data Models Overview
• Types of Data Models
• Object-Based Data Models
• Represent data using entities, attributes, and relationships.
• Focus on logical structure and user understanding.
• Examples:
• Entity-Relationship (ER) Model
• Object-Oriented Data Model
• Real-World Analogy:
• Like blueprints of a house, showing rooms (entities), furniture (attributes), and how rooms connect
(relationships).
• Example:
• Entity: Student
• Attributes: StudentID, Name, Department
• Relationship: Enrolls in → Course
Addis Ababa University, School of Commerce | Updated: Feb, 2025 14
Data Models Overview
• Types of Data Models
• Record-Based Data Models
• Organize data in fixed-format records of various types.
• Focus more on how data is stored and accessed.
• Subtypes:
• Relational Model – Uses tables (most common)
• Network Model – Uses graph structures with records and links
• Hierarchical Model – Uses a tree-like structure
• Real-World Analogy:
• Like a filing cabinet, with drawers (tables), folders (records), and fields inside (attributes).
• Example (Relational):
StudentID Name Age Course
101 Aman 22 DB Systems
102 Fana 21 AI
Addis Ababa University, School of Commerce | Updated: Feb, 2025 15
Data Models Overview
• Types of Data Models
• Physical Data Models
• Describe how data is physically stored on hardware.
• Focus on performance, indexing, storage paths, file formats, etc.
• Examples:
• Unifying Model
• Frame Memory Model
• Real-World Analogy:
• Like the plumbing and wiring behind the walls of a house. End-users don’t see it, but it’s
critical for functionality.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 16
Data Models Overview
• Conceptual, Logical, and Physical Models
Level Description Example
Conceptual High-level design, user-friendly ER Diagram of university DB
Logical Detailed structure, DBMS-specific Relational schema for tables
Physical Implementation in hardware terms Indexes, partitions, data files
Addis Ababa University, School of Commerce | Updated: Feb, 2025 17
Data Models Overview
• Why Data Models Matter
• Help bridge the gap between users and technical design
• Guide database design and implementation
• Enforce data consistency and accuracy
• Aid in communication between developers, analysts, and users
Addis Ababa University, School of Commerce | Updated: Feb, 2025 18
Functions of a DBMS (Database Management System)
• Data Storage, Retrieval, and Update
• Manages data in structured formats (tables, files).
• Supports operations like INSERT, SELECT, UPDATE, and DELETE.
• Ensures efficient data access.
• User-Accessible Catalog (Metadata Management)
• Maintains a data dictionary or system catalog.
• Stores metadata: data about data (e.g., table names, column types, constraints).
• Transaction Management
• Ensures that a group of operations (a transaction) are:
• Atomic (all or nothing)
• Consistent
• Isolated
• Durable
(This is the ACID principle)
Addis Ababa University, School of Commerce | Updated: Feb, 2025 19
Functions of a DBMS (Database Management System)
• Concurrency Control
• Manages simultaneous operations by multiple users.
• Prevents data inconsistency due to concurrent access.
• Recovery Services
• Recovers the database after system crashes or failures.
• Uses backup files and logs to restore the last consistent state.
• Authorization and Security Management
• Controls who can access what data.
• Includes user roles, passwords, permissions, encryption.
• Data Communication Support
• Integrates the database with networked systems or distributed environments.
• Supports client-server communication, APIs, and remote data access.
Addis Ababa University, School of Commerce | Updated: Feb, 2025 20
Functions of a DBMS (Database Management System)
• Integrity Enforcement
• Enforces rules to maintain valid and accurate data.
• Types: Entity integrity, referential integrity, domain constraints.
• Data Independence
• Allows changes in the physical storage without affecting logical structure and vice versa.
• Two types:
• Logical Data Independence
• Physical Data Independence
• Utility Services
• Includes tools for:
• Database backup and restore
• Performance monitoring
• Data import/export
• Statistics and optimization
Addis Ababa University, School of Commerce | Updated: Feb, 2025 21
Practical SQL with SQL Server
• SQL is a nonprocedural, free-format language used to manage data in
relational databases. Instead of writing detailed procedures, users declare
what data they need.
• Objectives of SQL:
• Create databases and table structures
• Perform data management (insert, update, delete)
• Execute simple and complex queries to retrieve data
• SQL Syntax Structure
• Uses standard English-like commands (e.g., SELECT, INSERT, UPDATE)
• Composed of:
• Reserved words (e.g., SELECT, FROM)
• User-defined words (e.g., table and column names)
• Case-insensitive and free-format
Addis Ababa University, School of Commerce | Updated: Feb, 2025 22
Practical SQL with SQL Server
• Understanding Literals in SQL
• Before we create tables or manipulate data, it's important to understand
literals.
• Literals are constant values written directly in SQL statements. SQL supports
different literal types depending on the data type:
• Character/String literals: enclosed in single quotes, e.g., 'John'
• Numeric literals: written without quotes, e.g., 1000, 450.75
• Date literals: enclosed in quotes (format varies by RDBMS)
• Rules for Writing Literals
• Non-numeric values → single quotes: 'House', 'Addis Ababa'
• Numeric values → no quotes: 650.00, 5
Addis Ababa University, School of Commerce | Updated: Feb, 2025 23
Practical SQL with SQL Server: Data Types
• Numbers
Addis Ababa University, School of Commerce | Updated: Feb, 2025 24
Practical SQL with SQL Server: Data Types
• Date and Time
Addis Ababa University, School of Commerce | Updated: Feb, 2025 25
Practical SQL with SQL Server: Data Types
• Text
Addis Ababa University, School of Commerce | Updated: Feb, 2025 26
Practical SQL with SQL Server: Data Types
• Binary
Addis Ababa University, School of Commerce | Updated: Feb, 2025 27
Practical SQL with SQL Server: Data Types
• Geometry
Addis Ababa University, School of Commerce | Updated: Feb, 2025 28
Practical SQL : DDL : Create a New Database
Addis Ababa University, School of Commerce | Updated: Feb, 2025 29
Practical SQL : DDL : Create a New Database
Addis Ababa University, School of Commerce | Updated: Feb, 2025 30
Practical SQL : DDL : Create a Schema
Addis Ababa University, School of Commerce | Updated: Feb, 2025 31
Practical SQL : DDL : Create a Schema