Database Maintenance

A database is primarily a computer file. As such, it has a location (or memory address) where it resides. Most of the time, for this tutorial, you will not need to be concerned with where the file is located since you can access the database from the Command Prompt. By curiosity or for any other reason, you may need to view the database and its components.

There are two primary types of databases you will use: those you create right away or those that exist already. At any time, while you are working, you must know what database you are currently using so that, if you make a change or try accessing a list, you must be aware of its existence or absence. For example, if a database was created already, in order to use it, you must first open it.

In MySQL, when you create a database, by default, a folder (or directory) is created for it under the Drive:\Program Files\MySQL\MySQL Server 4.1\data:

Both Microsoft SQL Server and MSDE store their databases by default in Drive:\Program Files\Microsoft SQL Server\MSSQL\Data.

It will not be unusual that, as you learn more or become highly productive, you would be dealing with various databases, some of which you will create, some others will have been created by someone else. In all cases, whenever you are performing a database task on an existing database, you should make sure you know what database you are currently using.

In SQL, to indicate the name of the database you want to work on, type the following formula:

USE DatabaseName;

The USE keyword is required. The DatabaseName factor is the name of the database you want to open. You must provide the exact name of the database. If you don't remember the name of the database you want to use, you can open Windows Explorer or My Computer and display the contents of the Drive:\Program Files\MySQL\MySQL Server 4.1\data folder for a MySQL database or the Drive:\Program Files\Microsoft SQL Server\MSSQL\Data folder for an MSDE database.

If using MSDE or MS SQL Server, the shortcut to know the names of the databases stored in your computer is to execute sp_databases (sp_databases is a stored procedure).

If using MySQL, to get a list of the databases, you can execute the SHOW DATABASES; statement.

Practical Learning: Creating a Database Using a Sample

1. To view a list of the available databases, if you are using MSDE, execute the following statement
    
   sp_databases
    
   If you are using MySQL, execute the following statement:
    
   SHOW DATABASES;
2. To specify which one to use, execute the following statement:
    

   USE CarRental1;

3. To create another database, execute the following statement:
    
   CREATE DATABASE DogGrooming;

After executing the USE statement, MSDE doesn't let you know that the database has been selected. In MySQL, it displays Database changed

If you have created a database but don't need it anymore, you can delete it. To delete a database using SQL, you use the DROP DATABASE instruction followed by the name of the database. The syntax used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed. On this tutorial, we assume that you are only learning, which means you can control whether your database is being accessed or who is accessing your database.

Practical Learning: Deleting a Database

1. Execute the following statement:
    
   DROP DATABASE DogGrooming
2. Notice that a message lets you know
    
   
3. To attempt to delete a database that doesn't exist, execute the following statement:
    
   DROP DATABASE PublicLibrary1
4. Notice you receive a message that lets you know
    

   1> DROP DATABASE PublicLibrary1; 2> GO Msg 3701, Level 11, State 8, Server TELES, Line 1 Cannot drop the database 'PublicLibrary1', because it does not exist in the system catalog. 1>

5. Type Exit and press Enter
6. Type Exit again and press Enter