7. Data Definition Language

One of the basic functions of a RDBMS is to provide a method of creating a database from scratch. This is the role of the data definition language (DDL).

The language allows tables to be defined in terms of :

• Field names
• Data type
• Data size / length
• Validation rules
• Default values
• Presence check
• Auto incrementing requirement
• Indexing requirement
• Primary key

As each table is defined and the relationships between them is established, then the overall design emerges. This is called the 'Schema'.

It should be noted that a single large database may have several groups of people using it for different purposes. In which case a different schema is needed for each of them.

Case study

Consider a motorcycle company. They store all the physical details of each motorcycle manufactured in a database - colour, engine etc. But in addition, the database includes order and sales details.

Parts group

One group of staff are only interested in making sure enough bits arrive at the factory to keep the lines going - they have no interest in the sales side of the database.

In this case the designer will set up a 'bill-of-material' schema that only includes the tables containing physical parts information.

Sales group

On the other hand, another group are tracking sales - they have no interest in the physical bits making up the bikes, but they do want to see sales and orders. So the designer makes a schema with only sales and order tables present

Strategy group

This group has the task of planning future production - so they need to understand how sales are going, but they also need to know the physical details of the best-selling bikes. In this case the designer needs to include a schema that includes all the database tables.