DATABASE MANAGEMENT SYSTEM

  DATABASE MANAGEMENT SYSTEM (DATA DEFINITION LANGUAGE) By Michael Bush Department of Library and Information Science DELTA STATE UNIVERSITY, ABRAKA, DELTA STATE, NIGERIA. INTRODUCTION A data definition language or data description language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas. Programming language: this is a formal computer language or constructed language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs to control the behavior of a machine or to express algorithms. Data structure: In computer science, a data structure is a particular way of organizing data in a computer so that it can be used efficiently. Database schema: A database schema of a database system is its structure described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. Before we go into DDL, we will first look at Structured Query Language STRUCTURED QUERY LANGUAGE (SQL) Many data description languages use a declarative syntax to define columns and data types. Structured query language (e.g., SQL), however, uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other elements. These statements can be freely mixed with other SQL statements, making the DDL not a separate language. Types of SQL Statements The tables in the following sections provide a functional summary of SQL statements and are divided into these categories: Data Definition Language (DDL) Statements Data Manipulation Language (DML) Statements Transaction Control Statements Session Control Statements System Control Statement Embedded SQL Statements In this paper, we will be focusing on data definition language. Data Definition Language (DDL) Statements DDL stands for "Data Definition Language". A DDL is a language used to define data structure and modify data. For example, DDL command can be used to add, remove or modify tables within a database. DDL used in database application are considered a subset of SQL (structured query language). However a DDL may also define other types of data such as XXL. A Data definition language has a predefined syntax for describing data. For example, to build a new table using SQL, syntax the CREATE command is used followed by parameters for the table name and column definition. The DDL can also define the name of each column and associated data type. Once a table is created it can be modified using the ALTER command. If the table is no longer needed, the DROP command can be used to delete the table. History of Database Definition Language The concept of the data definition language and its name was first introduced in relation to the Codasyl database model, where the schema of the database was written in a language syntax describing the records, fields, and sets of the user data model. Later it was used to refer to a subset of Structured Query Language (SQL) for creating tables and constraints. SQL-92 introduced a schema manipulation language and schema information tables to query schemas. These information tables were specified as SQL/Schemata in SQL: 2003. The term DDL is also used in a generic sense to refer to any formal language for describing data or information structures. Structure of Data Definition Language (DDL) Data Definition Language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify and remove database objects such as tables, indexes and users. Data Definition Language is a vocabulary used to define data structures in SQL (structure query language) Server. Use these statements to create, alter, drop and truncate data structures in an instance of SQL Server. Create: creates an object (a table, for example) in the database. Alter: database modifies the structure of an existing object in various ways (Eg: adding a column to an existing table) Drop: deletes an object in the database, usually irretrievably. Truncate: remove all records from a table, including all spaces allocated for the records are removed Data definition language (DDL) statements let you to perform these tasks: Create, alter, and drop schema objects Grant and revoke privileges and roles Analyze information on a table, index, or cluster Establish auditing options Add comments to the data dictionary The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table. The GRANT, REVOKE, ANALYZE, AUDIT , and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table. Oracle Database implicitly commits the current transaction before and after every DDL statement. Many DDL statements may cause Oracle Database to recompile or reauthorize schema objects. For information on how Oracle Database recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this, see Oracle Database Concepts. DDL statements are supported by PL/SQL with the use of the DBMS_SQL package. CREATE statement: The CREATE command is used to establish a new database, table, index, or stored procedure. The CREATE statement in SQL creates a component in a relational database management system (RDBMS). In the SQL 1992 specification, the types of components that can be created are schemas, tables, views, domains, character sets, collations, translations, and assertions. Many implementations extend the syntax to allow creation of additional elements, such as indexes and user profiles. Some systems, such as PostgreSQL, allow CREATE, and other DDL commands, inside a database transaction and thus they may be rolled back. CREATE TABLE statement: A commonly used CREATE command is the CREATE TABLE command. The typical usage is: CREATE TABLE [table name] ( [column definitions] ) [table parameters] The column definitions are: A comma-separated list consisting of any of the following Column definition: [column name] [data type] {NULL | NOT NULL} {column options} Primary key definition: PRIMARY KEY ( [comma separated column list] ) Constraints: {CONSTRAINT} [constraint definition] RDBMS specific functionality An example statement to create a table named employees with a few columns is: CREATE TABLE employees ( ID INTEGER PRIMARY KEY, first_name VARCHAR(50) not null, last_name VARCHAR(75) not null, fname VARCHAR(50) not null, date of birth DATE not null ); Some forms of CREATE TABLE DDL may incorporate DML (data manipulation language)-like constructs, such as the CREATE TABLE AS SELECT (CTAS) syntax of SQL.[2] DROP statement: The DROP statement destroys an existing database, table, index, or view. A DROP statement in SQL removes a component from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL commands to occur inside of a transaction and thus be rolled back. The typical usage is simply: DROP object type object name. For example, the command to drop a table named employees is: DROP TABLE employees; The DROP statement is distinct from the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement removes the entire table from the database. ALTER statement: The ALTER statement modifies an existing database object. An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depend on which RDBMS is being used. The typical usage is: ALTER object-type object-name parameters. For example, the command to add (then remove) a column named bubbles for an existing table named sink is: ALTER TABLE sink ADD bubbles INTEGER; ALTER TABLE sink DROP COLUMN bubbles; RENAME statement: The RENAME statement is used to rename a database table. RENAME TABLE old_name TO new_name; Referential integrity statements Another type of DDL sentence in SQL is used to define referential integrity relationships, usually implemented as primary key and foreign key tags in some columns of the tables. These two statements can be included in a CREATE TABLE or an ALTER TABLE sentence. Other languages XML Schema is an example of a DDL for XML. Conclusion SQL is a computer programming language that uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other elements. DDL is a vital statement in SQL. It deals with data description in a predefined syntax. For example, to build a new table using SQL, syntax the CREATE command is used followed by parameters for the table name and column definition. The DDL can also define the name of each column and associated data type. Once a table is created it can be modified using the ALTER command. If the table is no longer needed, the DROP command can be used to delete the table. REFERENCES A concise definition and introduction of DDL. techterms.com Allen, Grant (2010). The Definitive Guide to SQLite. Apresspod. Mike Owens (2 ed.). Apress. pp. 90–91.ISBN 9781430232254. Retrieved 2012-10-02. http://blog.sqlauthority.com/2008/01/15/sql-server-what-is-dml-ddl-dcl-and-tcl-introduction-and-examples/ http://culturalview.com/books/sql.pdf http://msdn.microsoft.com/en-us/library/bb510625.aspx http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands Olle, T. William (1978). The Codasyl Approach to Data Base Management. Wiley. ISBN 0-471-99579-7. www​.geekinterview.com/question.../7928 DATA DEFINITION LANGUAGE 6 | SYNERGY (GROUP E)