💾Intro to Database Systems Unit 5 – SQL Data Definition Language Basics

What's SQL DDL?

• SQL Data Definition Language (DDL) consists of SQL commands used to define and manage the structure of a database and its objects
• Enables database administrators and developers to create, modify, and delete database objects such as tables, indexes, and constraints
• Provides a way to specify the logical schema of a database which defines the structure and organization of data
• Helps ensure data integrity and consistency by enforcing rules and constraints on the data stored in the database
• DDL statements are typically executed during the initial setup and configuration of a database and when changes to the database structure are required
• Common DDL commands include

  CREATE

  ,

  ALTER

  , and

  DROP

  used to manipulate database objects
• DDL statements are processed and executed by the database management system (DBMS) which translates the commands into the appropriate actions on the database

Key Components of DDL

• CREATE

  statement used to create new database objects such as tables, indexes, views, and procedures
  • Specifies the name and structure of the object being created along with any associated properties or constraints

• ALTER

  statement used to modify the structure of existing database objects
  • Enables adding, modifying, or dropping columns, constraints, or other properties of an object
  • Helps adapt the database schema to changing requirements or optimize performance

• DROP

  statement used to remove database objects from the database
  • Permanently deletes the specified object and all its associated data
  • Requires caution as dropped objects cannot be easily recovered
• Data types used to define the type and format of data that can be stored in a column of a table
  • Common data types include

    INT

    ,

    VARCHAR

    ,

    DATE

    ,

    BOOLEAN

    , and

    DECIMAL

  • Choosing appropriate data types ensures efficient storage and processing of data
• Constraints used to enforce rules and restrictions on the data stored in a database
  • Examples include primary key, foreign key, unique, not null, and check constraints
  • Help maintain data integrity, consistency, and relationships between tables

Creating Database Objects

• CREATE TABLE

  statement used to create a new table in the database
  • Specifies the table name, column names, data types, and any constraints
  • Example:

    CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10,2))

• CREATE INDEX

  statement used to create an index on one or more columns of a table
  • Improves query performance by allowing faster data retrieval based on the indexed columns
  • Example:

    CREATE INDEX idx_employee_name ON employees (name)

• CREATE VIEW

  statement used to create a virtual table based on the result of a SELECT statement
  • Provides a customized or simplified view of the data without modifying the underlying tables
  • Example:

    CREATE VIEW employee_info AS SELECT id, name FROM employees

• CREATE PROCEDURE

  statement used to create a stored procedure in the database
  • Encapsulates a set of SQL statements that can be executed repeatedly with different parameters
  • Helps modularize and reuse database logic

• CREATE TRIGGER

  statement used to create a trigger that automatically executes in response to specific database events
  • Enables enforcing complex business rules or performing actions when data is inserted, updated, or deleted
  • Example:

    CREATE TRIGGER update_employee_log AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO employee_log VALUES (OLD.id, NEW.salary); END

Modifying Database Structures

• ALTER TABLE

  statement used to modify the structure of an existing table
  • Enables adding, modifying, or dropping columns, constraints, or indexes
  • Example:

    ALTER TABLE employees ADD COLUMN email VARCHAR(100)

• Renaming database objects using the

  RENAME

  statement
  • Allows changing the name of tables, columns, indexes, or other objects
  • Example:

    ALTER TABLE employees RENAME TO staff

• Modifying column data types or constraints using

  ALTER TABLE

  • Enables changing the data type of a column or adding/removing constraints
  • Example:

    ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2)

• Adding or removing indexes using

  CREATE INDEX

  or

  DROP INDEX

  • Helps optimize query performance by creating or removing indexes on specific columns
  • Example:

    DROP INDEX idx_employee_name ON employees

• Altering stored procedures, views, or triggers using

  ALTER

  statements
  • Allows modifying the definition or behavior of existing database objects
  • Example:

    ALTER VIEW employee_info AS SELECT id, name, email FROM employees

Removing Database Objects

• DROP TABLE

  statement used to remove a table and all its associated data from the database
  • Permanently deletes the table and cannot be undone
  • Example:

    DROP TABLE employees

• DROP INDEX

  statement used to remove an index from a table
  • Helps optimize database performance by removing unnecessary indexes
  • Example:

    DROP INDEX idx_employee_name ON employees

• DROP VIEW

  statement used to remove a view from the database
  • Removes the virtual table definition without affecting the underlying tables
  • Example:

    DROP VIEW employee_info

• DROP PROCEDURE

  statement used to remove a stored procedure from the database
  • Deletes the procedure definition and any associated permissions
  • Example:

    DROP PROCEDURE calculate_bonus

• DROP TRIGGER

  statement used to remove a trigger from the database
  • Removes the trigger definition and stops its automatic execution
  • Example:

    DROP TRIGGER update_employee_log

• Removing multiple objects using a single

  DROP

  statement
  • Allows dropping multiple tables, indexes, or other objects in a single command
  • Example:

    DROP TABLE employees, departments, locations

Data Types and Constraints

• Numeric data types used to store numeric values
  • Examples include

    INT

    ,

    DECIMAL

    ,

    FLOAT

    , and

    BIGINT

  • Specify the precision and scale for decimal numbers (total digits and decimal places)
• Character data types used to store textual data
  • Examples include

    CHAR

    ,

    VARCHAR

    , and

    TEXT

  • Specify the maximum length of the string
• Date and time data types used to store temporal values
  • Examples include

    DATE

    ,

    TIME

    ,

    DATETIME

    , and

    TIMESTAMP

  • Allow storing and manipulating dates, times, or combinations of both
• Boolean data type used to store true/false values
  • Represented as

    BOOLEAN

    or

    TINYINT

    (0 for false, 1 for true)
• Primary key constraint used to uniquely identify each record in a table
  • Ensures the uniqueness and non-nullability of the specified column(s)
  • Example:

    CREATE TABLE employees (id INT PRIMARY KEY, ...)

• Foreign key constraint used to establish relationships between tables
  • Ensures referential integrity by linking a column to the primary key of another table
  • Example:

    CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id))

• Unique constraint used to ensure the uniqueness of values in a column or set of columns
  • Prevents duplicate values from being inserted into the specified column(s)
  • Example:

    CREATE TABLE users (username VARCHAR(50) UNIQUE, ...)

• Not null constraint used to enforce the requirement of a non-null value in a column
  • Ensures that a column cannot contain null values
  • Example:

    CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, ...)

• Check constraint used to define a condition that must be satisfied for each row in a table
  • Allows specifying custom validation rules for the data
  • Example:

    CREATE TABLE employees (id INT PRIMARY KEY, age INT CHECK (age >= 18), ...)

Best Practices in DDL

• Use meaningful and descriptive names for database objects
  • Choose clear and concise names that reflect the purpose and content of the object
  • Follow a consistent naming convention (e.g., lowercase, snake_case)
• Define appropriate data types for columns
  • Select data types that accurately represent the nature and range of the data
  • Consider storage efficiency and performance implications
• Enforce data integrity through constraints
  • Apply primary key, foreign key, unique, not null, and check constraints as needed
  • Ensure data consistency, accuracy, and relationships between tables
• Normalize the database schema to reduce redundancy and anomalies
  • Follow normalization principles (1NF, 2NF, 3NF) to organize data effectively
  • Minimize data duplication and update anomalies
• Use indexes judiciously to improve query performance
  • Create indexes on columns frequently used in search conditions or join criteria
  • Monitor and optimize indexes based on query patterns and performance requirements
• Document the database schema and changes
  • Maintain a clear and up-to-date documentation of the database structure
  • Include information about tables, columns, relationships, and constraints
  • Track and document any modifications made to the schema over time
• Plan and test DDL changes before applying them to production
  • Carefully review and test DDL statements in a development or staging environment
  • Assess the impact of changes on existing data, applications, and performance
  • Implement a version control system to manage and track DDL scripts

Hands-On DDL Examples

1. Creating a table to store customer information:

   CREATE TABLE customers (
     id INT PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
     email VARCHAR(100) UNIQUE,
     phone VARCHAR(20),
     address VARCHAR(200),
     city VARCHAR(50),
     state VARCHAR(50),
     zip_code VARCHAR(10),
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

2. Adding a new column to the customers table:

   ALTER TABLE customers
   ADD COLUMN date_of_birth DATE;
   

3. Creating an index on the customers table:

   CREATE INDEX idx_customers_last_name ON customers (last_name);
   

4. Creating a table with a foreign key constraint:

   CREATE TABLE orders (
     id INT PRIMARY KEY,
     customer_id INT,
     order_date DATE,
     total_amount DECIMAL(10,2),
     FOREIGN KEY (customer_id) REFERENCES customers(id)
   );
   

5. Dropping a table:

   DROP TABLE orders;
   

6. Renaming a column in the customers table:

   ALTER TABLE customers
   RENAME COLUMN phone TO contact_number;
   

7. Creating a view to retrieve customer details:

   CREATE VIEW customer_details AS
   SELECT id, first_name, last_name, email, city, state
   FROM customers;
   

8. Modifying a column data type:

   ALTER TABLE customers
   MODIFY COLUMN zip_code VARCHAR(20);