MODULE NO.3_JAYBEE D. MARANAN

 DDL is a standardized language with commands to define the storage groups (stogroups), different structures and objects in a database. DDL statements create, modify and remove database objects, such as tables, indexes and stogroups. DDL is also used in a generic sense to refer to any language that describes data.

DDL includes Structured Query Language (SQL) statements to create and drop databases, aliases, locations, indexes, tables and sequences. It also includes statements to alter these objects and impose or drop certain constraints on tables, such as the following:

  • UNIQUE
  • PRIMARY
  • FOREIGN KEY
  • CHECK

The CREATE group of DDL commands includes the following:

  • CREATE DATABASE defines a logical database under the active location root directory. The database normally consists of a subdirectory of the same name that holds the physical table and index files. Users can use stogroups to implicitly specify different storage directories for individual database objects.
  • CREATE TABLE creates a table by defining its columns and each column's data type and field length. The command can also be used to create primary and foreign keys for the table.
  • CREATE STOGROUP creates a Db2-style stogroup to define a physical directory area for storing database objects. A stogroup is associated with a specific directory path.
  • CREATE TABLESPACE creates a Db2-style tablespace to store tables from the same logical database in multiple directory paths. The tablespace is used with a stogroup.
  • CREATE ALIAS defines an alias for an existing table or view. The alias may be described in a different location as the table or view. This command also records the alias definition in the catalog tables at the current location.
  • CREATE SYNONYM can also be used to create an alternate name for an existing table or view at the current location.
  • CREATE INDEX creates an index on one or more columns of a table for faster data retrieval and to enforce uniqueness constraints on the columns.
  • CREATE LOCATION creates a new XDB server location in a user-specified subdirectory.
  • CREATE SEQUENCE must be used to create a sequence at the application server.
  • CREATE VIEW defines a virtual table that restricts data retrieval and updates to a subset of columns and rows from single- or multibase tables.
  • CREATE GLOBAL TEMPORARY can be used to create a temporary table's description at the current server.

The most common DROP commands are the following:

  • DROP DATABASE does the exact opposite of the CREATE DATABASE It deletes a database defined at a certain location, along with all the objects logically associated with it. It also deletes the database subdirectory even if it is empty and contains no objects logically associated with the database.
  • DROP STOGROUP deletes a stogroup by severing the logical connection between data objects defined using the stogroup and the directory path specified in the deleted stogroup definition. It doesn't delete the objects associated with the stogroup. As with other DROP commands, the DROP STOGROUP command should be used sparingly and with caution.
  • DROP TABLE deletes a database table and all associated indexes, views and synonyms built on it.
  • DROP TABLESPACE deletes a tablespace defined in the current location and all tables logically associated within it.
  • DROP ALIAS can be used to delete an alternate name for a table or view in a location's system catalog.
  • DROP SYNONYM can also be used to delete an alternate name for a table or view.
  • DROP INDEX deletes an index at the current location but only if the index was not created as the result of a UNIQUE, PRIMARY or FOREIGN KEY To drop/delete such indexes, the existing constraint must first be dropped with the ALTER command.
  • DROP LOCATION deletes a user-defined XDB server location and the catalog tables, directory structure and objects associated with it.
  • DROP VIEW deletes a view and all other views defined on it from the system catalog of the current location.

  • The most common ALTER commands are the following:

    • ALTER DATABASE modifies the information parameters of a database under the current XDB server location.
    • ALTER STOGROUP modifies the specifications of a stogroup defined at the current XDB server location.
    • ALTER TABLE adds, removes or alters columns and their data types. It can also enforce referential and domain integrity by creating or dropping UNIQUE, PRIMARY, FOREIGN KEY, and CHECK constraints in XDB mode. In Db2 mode, the command can also be used to enforce uniqueness constraints.
    • ALTER TABLESPACE changes the specifications of a tablespace within the current XDB server location.
    • ALTER VIEW uses an existing view definition at the current server to regenerate a view.
    • ALTER SEQUENCE changes sequence attributes at the current server.
    • ALTER INDEX modifies the configuration of an existing index. The XDB server syntactically supports the command to ensure compatibility with Db2.


Data Manipulation Language (DML) is a crucial part of database management systems. It allows users to interact with and manipulate data within a database. Here are the key points about DML:

  1. Purpose of DML:

    • DML provides commands for retrieving, modifying, and deleting data from a database.
    • It allows users to perform operations like querying, updating, inserting, and deleting records.

  2. Common DML Commands:

    • SELECT: The primary DML command, used to retrieve data from one or more tables. For example:
      SQL
      SELECT column1, column2 FROM my_table WHERE condition;
      AI-generated code. Review and use carefully. More info on FAQ.
    • INSERT INTO: Adds new records to a table. For example:
      SQL
      INSERT INTO my_table (column1, column2) VALUES (value1, value2);
      AI-generated code. Review and use carefully. More info on FAQ.
    • UPDATE: Modifies existing records in a table. For example:
      SQL
      UPDATE my_table SET column1 = new_value WHERE condition;
      AI-generated code. Review and use carefully. More info on FAQ.
    • DELETE FROM: Removes records from a table based on a condition. For example:
      SQL
      DELETE FROM my_table WHERE condition;
      AI-generated code. Review and use carefully. More info on FAQ.

  3. Interactive and Embedded Modes:

    • DML can be used interactively (directly by users) or embedded within host programming languages.
    • In interactive mode, users directly execute DML commands.
    • In embedded mode, DML commands are part of a larger program written in another language (e.g., Java, Python).

  4. Query Language:

    • The term “query language” is often used interchangeably with DML.
    • DML commands resemble simple English language, making them user-friendly.




DML allows users to access and retrieve database contents in both interactive and embedded modes. In the context of databases, DML includes constructs that facilitate the retrieval and manipulation of data. The more commonly used term for DML is the “query language.” Essentially, it provides a way to interact with the data stored in a database.

Here are some key points about DML:

  1. Interactive Mode: Users can directly interact with the database using DML commands. For example, you can write SQL queries to retrieve specific data or update records.

  2. Embedded Mode: DML can also be embedded within a host programming language. This means you can include DML statements directly in your application code. For instance, if you’re building a web application, you might embed SQL queries in your Python or Java code.



  4. Declarative vs. Procedural:

    • Declarative DML: Specifies the properties of the data you want to retrieve (e.g., SQL queries). The database management system (DBMS) converts these declarative statements into a relational algebra expression before optimizing their execution.
    • Procedural DML: Requires users to specify how to access the data. However, with the rise of relational DBMSs, declarative DMLs have become more common.

  5. Popular DML: The ISO standard SQL language is the most widely used DML for relational databases. Other specialized DMLs exist for specific types of databases, such as SPARQL for RDF data, OQL for object databases, and XQuery for XML databases.

  6. Historical Background: DML has been essential since the early days of database system development. Initially, these languages were mostly procedural. However, with the advent of relational DBMSs in the 1970s, declarative DMLs gained prominence.

  7. Applications: DML is used in all types of database applications to access and manipulate data.











Comments

Post a Comment

Popular posts from this blog

FEB.28, 2024(MARANAN, JAYBEE D.)

Image
       WHAT IS INFORMATION MODEL An information model is a conceptual representation of how data is structured, organized, and processed within a system or organization. In other words, it's a way to define and describe various data elements and their relationships. Typically, an information model defines the entities (objects or concepts) relevant to a business or system, the attributes of those entities, and the relationships between them. It describes the flow of information and how data is exchanged between different parts of the system. An information model can be used to support a wide range of activities related to data management, analysis, and design. For example, it can serve as a blueprint for systems development, guide data integration and exchange between systems, or serve as a basis for data analysis and reporting. Different types of information models exist, such as conceptual, logical, and physical models. These models differ in terms of the lev...
Read more

MODULE 4_JAYBEE D. MARANAN

 WHAT IS SQL? SQL (Structured Query Language)  is a standard language used for  storing, manipulating, and retrieving data  in databases. It provides a consistent way to interact with various database management systems (DBMS) such as  MySQL ,  SQL Server ,  Oracle , and more. Here are some key points about SQL: Data Manipulation : SQL allows you to perform various operations on data: Querying : Retrieving specific data from tables using the  SELECT  statement. Inserting : Adding new records into tables using the  INSERT INTO  statement. Updating : Modifying existing records using the  UPDATE  statement. Deleting : Removing records from tables using the  DELETE FROM  statement. Data Definition : SQL includes commands for defining and modifying the  structure  of database objects: Creating Tables : Using the  CREATE TABLE  statement. Altering Tables : Modifying existing table structures with...
Read more

Introduction to Database Management

Image
  A. FUNCTIONS OF DBMS Post Title: The Crucial Role of Database Management Systems in Data Organization Post Content: Database Management Systems (DBMS) are essential tools for managing and organizing data. They provide a structured and efficient way to store, retrieve, and manipulate data. Some of the primary functions of DBMS include:   Data Organization: DBMS helps in organizing data into a structured format. This ensures efficient storage and retrieval of data. Data Storage: DBMS stores data in a secure and efficient manner. It ensures data integrity and prevents unauthorized access. Data Retrieval: DBMS allows users to retrieve data quickly and efficiently. It provides various retrieval methods like simple retrieval, complex retrieval, and advanced retrieval. Data Modification: DBMS allows users to modify data. This includes adding new records, updating existing records, and deleting records. Data Security: DBMS provides robust security measures to protect dat...
Read more