Data Definition Language (DDL)

Data Definition Language (DDL) is responsible for modifying the structure of database objects

Includes orders to modify, delete, or define relationships (tables) in which database data is stored

There are four basic operations:

• CREATE
• ALTER
• DROP
• TRUNCATE

Definition of data

The relationship set for each database must be specified in the system in terms of a data definition language (DDL)

The SQL DDL not only allows the specification of a set of relationships, but also information regarding those relationships, including:

• The outline of each relationship
• The value domain associated with each attribute
• Integrity restrictions
• The set of indexes that must be maintained for each relationship
• The security and authorization information for each relationship
• The physical storage structure of each relationship on disk

Basic types of domains

The SQL standard supports a wide variety of predefined domain types:

• CHAR(n)

  A fixed length character string, with a length n specified by the user. You can also use the full word character

• VARCHAR(n)

  A variable length character string with a maximum length n specified by the user. The complete form, character varying, is equivalent

• INT

  An integer (a finite subset of computer dependent integers). The whole word, integer, is equivalent

• SMALLINT

  A small integer (a computer dependent subset of the entire domain type)

• NUMERIC(p, d)

  A fixed comma number, the accuracy of which is specified by the user. The number is made up of p digits (plus the sign) and those p digits, d belongs to the decimal part

• DOUBLE

  A floating-point number and double precision floating point numbers, with computer dependent precision

• FLOAT(n)

  A floating point number whose accuracy is, at least n digits

• DATE

  Date type data, with an accuracy of up to second

Basic definition of schemes

CREATE TABLE

Relationships are defined by the command CREATE TABLE:

\text{CREATE TABLE r} (\\ A_1 D_1, \cdots, A_n D_n,\\ \{rest-integrity_1\}, \cdots, \{rest-integrity_k\} )

Where r is the name of relationship, every A_i is the name of an attribute in the relationship scheme r and D_i is the domain type of the attribute domain values A_i

There are several valid integrity restrictions

One of the most commonly used is the primary key (primary key), which takes the following form:

\text{primary key}(A_{j1}, \cdots, A_{jm})

The primary key specification determines the attributes A_{j1}, \cdots, A_{jm} form the primary key to the relationship

The attributes of the primary key must be non null and unique; that is, no tuple can have a null value for an attribute of the primary key and no pair of tuples in the relationship can be equal across all attributes of the primary key

Although the primary key specification is optional, it is usually a good idea to specify a primary key for each relationship

CREATE TABLE acount(
number_acount char(10),
name_branch char(15),
balance numeric(12, 2),
primary key(number_acount) )

The example created a relationship called acount in which the attributes have been defined:

number_acount, you can save a word of up to 10 characters
name_branch, you can save a word of up to 15 characters
balance, you can save a decimal number with 12 integers and 2 decimal places
And with the restriction on number_acount as the primary key to the relationship

If a newly inserted or newly modified tuple in a relationship contains null values for any of the attributes that are part of the primary key, or if they have the same value as another tuple in the relationship, SQL will report the error and prevent the update

CREATE TABLE client(
number_acount char(10),
name_client char(15),
primary key(number_acount) )

The example created another relationship called client in which the attributes have been defined:

number_acount, you can save a word of up to 10 characters
name_client, you can save a word of up to 15 characters
And with the restriction on number_acount as the primary key to the relationship

As can be seen we can create more than one relationship

DROP TABLE

The command is used to delete a relationship from the database DROP TABLE

This command removes all information about the relationship from the database

DROP TABLE acount

The example has removed all information about the relationship from the database acount

This command is more drastic than DELETE, which only erases your tuples while maintaining the outline of the relationship

However, DROP, if removed acount, no tuples can be reinserted into that relationship unless it is recreated with the command CREATE TABLE

ALTER TABLE

The command ALTER TABLE is used to add attributes to an existing relationship

As the value of the new attribute, all tuples in the relationship are assigned null value

ALTER TABLE acount ADD
year DATETIME

The example added a new attribute

Where acount is the name of the existing relationship, year is the name of the attribute you want to add and DATETIME is the domain of the added attribute

You can also remove attributes using ALTER TABLE

ALTER TABLE acount DROP
year

The example removed the attribute we added earlier

Where acount is the name of the existing relationship and year is the name of the attribute you want to delete

In this case it is not necessary to declare the domain of the attribute to be deleted

Many database systems do not allow the deletion of attributes, although they do allow the deletion of entire tables

TRUNCATE

This command applies only to tables and its function is to clear the entire contents of the specified relationship

The advantage over the command DELETE, is that if you want to erase all the content of the relationship, it is much faster, especially if the relationship is very large

The downside is that TRUNCATE only serves when you want to remove absolutely all tuples, as the clause is not allowed WHERE

While, at first, this statement would appear to be Data Manipulation Language (DML), it is actually a DDL, because internally, the TRUNCATE command clears the table and recreates it and does not execute any transactions

TRUNCATE TABLE acount

In the example we've deleted all the content of the called relationship acount