People who use a database may do so directly or indirectly, which we come to, but generally we can think of ‘users’ as database administrators, database designers, and database end users:

Database administrators: Database administrators are tasked with overseeing the ‘usage’ of the database. That is, managing the specific way in which various users of the database can access it and, therefore, specifying restrictions on which part of the database a user might have ‘access’ to etc., depending on the scope of the particular use requirements. This will involve, for example, setting up of user profiles and passwords, deleing them if necessary, but also for maintaining the various other admin related tasks, e.g., making sure the current licence (agreed with a software vendor) covers all usages, and that the licence is, of course, updated; the software should be used legally.

Database designers: Designers are familiar with all of the principles of database design, covered thus far in the course, because they actually design the schema, and all of the many details that go hand in hand with this role.

Database end users: Any user of the database who accesses data from the database. A user role can vary greatly. It might, for example, be someone who has a relatively infrequent, but important, interaction with the database on a monthly basis, to obtain a report on the total costs of a given set of company actions. Alternatively, it might be a quantitative scientist who depends on large volumes of data to be transferred on a daily basis for analysis.

For the current course, we are not interested in going into all of the different levels of detail that exist between the use of databases, at the external level, and the internal representation of databases in file storage areas within the hardware of a computer. However, you should be aware that this detail exists and whereabouts the DBMS sits in this broader context.

Therefore, in Figure 1, we distinguish between the basic idea of database interaction (A) and that of the database architecture, which includes the database management system. The architecture is drawn in its most basic form (B) and is elaborated on (C).

There are an number of objectives to this architecture, which known as the ANSI-SPARC three-level architecture. Therese objectives are to:

decouple the view of the data from the data itself. In other words, two different end users of the database might have different views of the data, depending on how they would like to use the data, even though the data itself is a resource that is common to all users (it is the same data).

ensure users do not deal with internal physical storage details.

decouple structures from user views. In other words, if a database administrator alters the representation of data structure, this should not affect the any user views.

separate the structure of the database from the details of the storage device.

Figure 1: Database interaction v database architecture

Depending on the operating system you are working on, you will be either using the <cml.exe> program on windows or, e.g., if you are on Mac OS you will be using the Terminal program.

In order to start the MySQL engine for a specific user who has been given a username and password, you type into Terminal the following commands:

> mysql -u root –p

… and then you will be asked to enter your password:

[ Enter password:

What does this mean? The <mysql> part of the command is the name of the program we are calling on the command line. The program has been coded such that it can accept as arguments a value <root> associated with the username option <-u> and a value <your password> associated with the password option <-p>. Once the password is entered correctly we are then inside the MySQL engine. The way we know this is that the command line is now prefixed with <mysql>:

[mysql> quit

If you enter the quit command, then you will be returned to the control of the Terminal program. This makes sense – we are using Terminal to launch the mysql program, so, when we quit mysql we are returned to the calling program.

We have not actually done anything yet on the command line. We just started mysql, confirmed that we were inside the mysql program, then quit the program. This is fine. We are not intending to do much here other than become a little familiar with our environment (seen from the point of view of the command line). However, let us have a look at what databases exist in ‘on the server’:

[mysql> show databases;
+---------------------+
|  Database           |
+---------------------+
|  information_schema |
|  codepoint_db       |
|  mysql              |
|  performance_schema |
|  sys                |
+---------------------+
5 rows in set (0.01 sec)

It does not matter what these are. We are just demonstrating some very simple commands.

In order to create a database called <temp_db> we execute the following:

[mysql> CREATE DATABASE temp_db;
Query OK, 1 row affected (0.00 sec)

…after which the <show databases;> command will reveal temp_db in the list of databases. To drop < temp_db>, i.e., to delete / get rid of the <temp_db> database we use the command:

mysql> DROP DATABASE IF EXISTS temp_db;
Query OK, 0 rows affected (0.00 sec)

…after which the <show databases;> command will reveal <temp_db> to have been removed from the list of databases.

The preceding set of commands are also given in Figure 1.

Figure 3: Simple set of SQL commands in Terminal

MySQL Workbench is the Graphical User Interface (GUI) interface to MySQL. It is presented in Figure 4. When launched a dark view can be seen. Double clicking on the required connection will prompt a password dialougue. Once entered correctly we are ‘inside MySQL’, equivalent to being:

[mysql>

… on the command line. All of the things we can do from the command line can be done from within the GUI. In fact, the GUI is designed to help MySQL development. Some features are displayed at the bottom of Figure 4:

1. By right-clicking in this area, you can create a schema (i.e., database).

2. This area acts as a command window and a script editor. So, we are able to execute SQL statements from within this window much like we can in the <cmd.exe> or <Terminal> programs.

3. The results of the commands executed are displayed here.

4. When typing commands, a context sensitive help window provides feedback as to which options you might want to include when executing an SQL statement.

Figure 4: MySQL Workbench.