Data definition language (DDL)
Tables are the primary objects in a relational database because they are where the data is stored. However, there are several secondary objects which perform supporting functions. SQL provides a special set of commands for maintaining these objects known as data definition language (DDL). For each type of object there are three maintenance operations that can be performed:
- CREATE
- ALTER
- DROP
Whereas the CREATE command defines the object and allocates space for it in the database, the ALTER command changes the definition of an existing object. This could be done for example to extend the length of text string that can be stored in a particular column. The DROP command removes the object and any contents from the database.
This set of notes goes through the main types of database object and describes their purpose. Examples of the DDL statements used to maintain the objects are also provided.
Another set of SQL commands known as data manipulation language (DML) allows you to maintain the data in your tables in contrast to DDL which is about maintaining the schema itself. The three basic operations that you can perform on data are INSERT which creates new rows, UPDATE which modifies existing rows and DELETE which removes rows from the database. You will notice that these are very similar to the commands in DDL, so it is important to be clear about the distinction:
| DDL: Schema objects | DML: Table data |
|---|---|
| CREATE | INSERT |
| ALTER | UPDATE |
| DROP | DELETE |
This set of notes also covers a couple of small but important topics for practical database work, the first of which is the use of date and time values. These cause problems in all database platforms because of the way they are stored, the close relationship between dates and times, and the wide range of formats that need to be handled. Without explicit practice with date and time functions, they can be very difficult to use because they do not behave in an intuitive way.
The last topic shows you how to find out more explicit information about a database by querying the schema information directly. Information about schema objects is stored in a set of tables which are owned by the database administrator account, and which are called the data dictionary. Knowing about these tables can be convenient when using the command line to build a query, but more importantly they can be used to personalise the information that a particular user can access. This is further explored in week 11 under the heading of security.