Database Creation With Code |
|
The command used to create a database in SQL uses the
following formula:
CREATE DATABASE DatabaseName
The CREATE DATABASE (remember that SQL is not
case-sensitive) expression is
required. The DatabaseName factor is the name that the new database
will carry. Although SQL is not case-sensitive, you
should make it a habit to be aware of the cases you use to name your
objects. Every statement in SQL can be
terminated with a semi-colon. Although this is a requirement in many
implementations of SQL, in Microsoft SQL Server, you can omit the
semi-colon. Otherwise, the above formula would be
CREATE DATABASE DatabaseName;
Here is an example:
CREATE DATABASE GeneralCensus;
To assist you with writing code, tin the previous
lessons, we saw that you could use the query window.
Practical
Learning: Creating a Database Using the SQL Query Analyzer
|
|
- To open the code editor, in the Object Explorer, right-click the name of
the server and click New Query
- In the empty window, type:
CREATE DATABASE RealEstate1;
GO
|
- To execute the statement, press F5
To specify more options with code, Microsoft SQL
Server ships with various sample codes you can use for different
assignments. For example, you can use sample code to create a database.
The sample codes that Microsoft SQL Server are accessible from the
Template Explorer.
To access the Template Explorer, on the main menu,
you can click View -> Template Explorer. Before creating a database,
open a new query window. Then:
- To create a new database using sample code, in the Template Explorer,
expand the Databases node, then drag the Create Database node and drop it in
the query window. The new database would be created in the server that holds
the current connection
- If you have access to more than one server, to create a database in
another server or using a different connection, in the Template Explorer,
expand the Databases node, right-click Create Database and click Open. In
the Connect to Database Engine dialog box, select the appropriate options,
and can click OK
With any of these actions, Microsoft SQL Server would
generate sample code for you:
-- =============================================
-- Create database template
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'<Database_Name, sysname, Database_Name>'
)
DROP DATABASE <Database_Name, sysname, Database_Name>
GO
CREATE DATABASE <Database_Name, sysname, Database_Name>
GO
You would then need to edit the code and execute it
to create the database. From the previous lessons and sections, we have
reviewed some characters uch as the comments -- and some words or
expressions such as GO, CREATE DATABASE, and SELECT.
We will study the other words or expressions in future lessons and
sections.
If you have created a database but don't need it anymore, you can
delete it. It is important to know, regardless of how you create a
database, whether using SQL Server Management Studio, code in the query
window, or the Command Prompt, every database can be accessed by
any of these tools and you can delete any of the databases using any of
these tools.
As done with creating a database, every tool provides
its own means.
SQL Server Management Studio |
|
To delete a database in SQL Server Management Studio,
in the Object Explorer, expand the Databases node, right-click the
undesired database, and click
Delete. A dialog box would prompt you to confirm your intention. If you
still want to delete the database, you can click OK. If you change your
mind, you can click Cancel.
Practical
Learning: Deleting a Database in the SQL Server Management Studio
|
|
- In the Object Explorer, right-click MotorVehicleAdministration and click Delete
- In the Delete Object dialog box, click OK
Deleting a Database Using SQL |
|
To delete a database in SQL Query Analyzer, you use the DROP DATABASE
expression followed by the name of the database. The formula used is:
DROP DATABASE DatabaseName;
Before deleting a database in SQL, you must make sure
the database is not being used or accessed by some one else or by another
object.
Practical
Learning: Deleting a Database With Code
|
|
- On the Standard toolbar, click the New Query button
- To delete a database, type:
DROP DATABASE RealEstate1;
GO
|
- Press F5 to execute the statement
While writing code in a Query Window, you should
always know what database you are working on, otherwise you may add code
to the wrong database. To programmatically specify the current
database, type the USE keyword followed by the name of the database.
The formula to use is:
USE DatabaseName;
Here
is an example:
USE GovernmentStatistics;
Refreshing the List of Databases |
|
Some of the windows that display databases, like the SQL Server Management Studio, don't update their list immediately if an
operation occurred outside their confinement. For example, if you create a
database in the query windows, its name would not be updated in the Object
Explorer. To view such external changes, you
can refresh the window that holds the list.
In SQL Server Management Studio, to update a list,
you can right-click its category in the Object Explorer and click Refresh. Only
that category may be refreshed. For example, to refresh the list of
databases, in the Object Explorer, you can right-click the Databases node
and click Refresh.
Microsoft SQL Server Primary Settings |
|
When you install Microsoft SQL Server, it also installs 4
databases named master, model, msdb, and tempdb. These databases will be
for internal use. This means that you should avoid directly using them, unless
you know exactly what you are doing.
One of the databases installed with Microsoft SQL Server is
named master. This database holds all the information about the server on which
your MS SQL Server is installed. For example, we saw earlier that, to perform
any operation on the server, you must login. The master database identifies any
person, called a user, who accesses the database, about when and how.
Besides identifying who accesses the system, the master
database also keeps track of everything you do on the server, including creating
and managing databases.
You should not play with the master database; otherwise you
may corrupt the system. For example, if the master database is not functioning right, the system would not work.
A namespace is a technique of creating a series of items
that each has a unique name. For example, if you start creating many databases,
there is a possibility that you may risk having various databases with the same
name. If using a namespace, you can isolate the databases in various namespaces.
In reality, to manage many other aspects of your database server, you use
namespaces and you put objects, other than databases, within those namespaces.
Therefore, a namespace and its content can be illustrated as follows:
Notice that there are various types of objects within a
namespace.
Within a namespace, you can create objects as you wish. To
further control and manage the objects inside of a namespace, you can put them
in sub-groups called schemas. Therefore, a schema is a group of objects within a
namespace. This also means that, within a namespace, you can have as many
schemas as you want:
To manage the schemas in a namespace, you need a way to
identify each schema. B
ased on this, each
schema must have a name. In our illustration, one schema is named Schema1.
Another schema is named Schema2. Yet another schema is named Schema_n.
Inside of a schema, two objects cannot have the same name,
but an object in one schema can have the same name as an object in another
schema. Based on this, if you are accessing an object within its schema, you can
simply use its name, since that name would be unique. On the other hand, because
of the implied possibility of dealing with objects with similar names in your
server, when accessing an object outside of its schema, you must qualify it. To
do this, you would type the name of the schema that contains the object you want
to use, followed by the period operator, followed by the name of the object you
want to use. From our illustration, to access the Something1 object that belongs
to Schema1, you would type:
Schema1.Something1
There are two types of schemas you can use, those built-in
and those you create. When Microsoft SQL Server is installed, it also creates a
few schemas. One of the schemas is called sys.
The sys schema contains a
list of some of the objects that exist in your system. One of these objects is
called databases (actually, it's a view). When you create a database, its
name is entered in the databases object using the same name you gave it.
In the previous lesson, we saw that, before using a
database, you must establish a connection with the server. You do this using a
user account that can use the server. As we saw earlier, once the connection
exists, you can create a database. In Microsoft SQL Server, the user who creates
a database is referred to as the database owner. To identify this user, when
Microsoft SQL Server is installed, it also creates a special user account named dbo.
This account is automatically granted various permissions on the databases of
the server.
Because the dbo account has default access to all databases,
to refer to an object of a database, you can qualify it by typing dbo,
followed by the period operator, followed by the name of the object.
Exercise: Utility Company |
|
- Using Microsoft SQL Server Management Studio, create a database named
UtilityCompany1 (Accept the default settings)
- Perform some research on the Internet to find out about different regions
of the United States. On a piece of paper, create a list of the regions of
the US
- Connect to the server from the Command Prompt
- From the Command Prompt, create a database named UnitedStatesRegions1
- Exit the Command Prompt