9.1.1     Database hierarchy

This is NOT the latest copy of this book; click here for the latest version.

Each part of a database has a name, and we will be using these names throughout this chapter and much of the rest of this book. As such, you need to fully understand a certain amount of database jargon to survive.

At the top of the database food chain is a Database Management System (DBMS). This is a program that looks after your data, and allows you to ask it questions ("queries") about that data, which it will answer. The DBMS is responsible for actually looking after your data, changing it when necessary, searching through it to answer the questions you ask, optimising your questions to perform as quickly as possible, and so on.

Each DBMS has many databases holding data, and each database is usually entirely independent of every other database. For example, a web hosting service that provides its customers with access to a DBMS might give each customer their own database to use as they wish. Each database holds many tables, which is where the data is actually stored. You can consider a database to be like a directory, and a table to like files inside a directory - files inside a directory are usually related to each other. A DBMS that handles tables being dependent on each other is known as an RDBMS, for Relational Database Management System, but we will be using the term DBMS throughout for ease of reading.

Examples of tables include, "orders" for shopping carts, "members" for a gold club, "clients" for a dentists surgery, etc. Each table can have multiple attributes, for example our golf club members table might have attributes "Name", "Address", "Last Visit Date", etc. Furthermore, each table has multiple records - entries into the table itself. Each record in the members table would be a member, and each member would have space for their own Name attribute, their own Address attribute, their own Last Visit Date attribute, etc.

We end up with something like this:

That is the output from a program called the MySQL Monitor -an incredibly useful program that lets you ask questions to MySQL and get your answers neatly formatted like you can see above.

MySQL stores each of its tables in a file on your hard drive, which means that MySQL running on Unix-like operating systems are case sensitive when it comes to table names - asking a question about table Foo will not work if you named the table foo. There are ways around this, but the easiest and fastest way to handle this situation is simply to always use lowercase table names, whether you are on Unix or Windows.

MySQL, and indeed all DBMSs, have complex privileges systems that require usernames and passwords before you can ask questions. Each user can be configured to only be able to work with certain databases, or even only certain tables - this makes sure that your data is safe in your DBMS.

Many people use different words for each part of a database, but it is nothing to worry about. Some people call records rows, others call them tuples. Some people call attributes fields, others call them columns. In general use "records" and "rows" are very common, as are "attributes", "fields", and "columns". Use whatever you feel most comfortable with, but be aware others may use a different term for the same thing.