Data manipulation language or DML is used to edit or retrieve data from a database. This means you can insert, update and query using this language.

DML is what people are more used to. We have covered this in great detail in the section on SQL which you are encouraged to read in order to fully understand queries in SQL. Also you should research UPDATE and INSERT SQL commands to further your knowledge

Metadata may seem strange on the face of it, however it does make more sense as you understand the power of databases more. When we create a table we make entries into the metadata tables. Somewhere in the database will be a data, also known as a system table, which stores this information. It is treated like any other table and can be directly accessed if so wished. This means that we can perform queries such as what tables exists, what are the data types of the fields and also what is the primary key?

Consider the command below

Create TABLE personal {

ID integer PRIMARY KEY,

Forename varchar(30),

Surname varchar(30)

};

When processed it will make an entry into the system table, let us call it SYSTEM_TABLE and SYSTEM_FIELD

SYSTEM_TABLE

SYSTEM_FIELD

SYSTEM_TABLE may seem overly simple but in a real database it would be much more complicated. For example it may store who owns the table, which schema it is part of and also the transactional level of the table. Do not worry if you not understand these terms as they are outside the scope of this course, however it is always good to look up terms you are unfamiliar with.

Access system tables

Microsoft access is what most people will have used for databases at this stage. It would be very unlikely that you will have ever looked at the system tables. In order to see them you need to click on tools - > options. You then need to click on the view tab. You should see the following -

Check the items above and then click on OK. You then will see a bunch of new tables. We shall be looking at the MSysObjects table. Consider a database which has the following table -

To find the table above we can do the following query -

Notice the type is set to 1. Tables have an ID of 1. The MSysObjects does not only store tables but also what reports you have, the forms and anything else you may of created. Running the above query brings the following results.

Here we can see that our DVD table has in ID of 24. The above is an example of how we could list down all of the tables in our database. Unfortunately Microsoft do not like you playing with the internal structures of access and as such getting field information etc is much more difficult. However it does give you an idea of how it works. For more information on system tables and metadata you should download MySQL and investigate them in this.