Probably the most important requirement of
creating a
database is to give it a name. The SQL is very
flexible when it comes to names. In fact, it is very less
restrictive than
most other computer languages.
Still, there are rules you must follow when naming the objects in
your databases:
- A name can start with either a letter (a, b, c, d, e, f, g,
h, i, j,
k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E,
F, G,
H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a
digit
(0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a
non-readable
character. Examples are _n, act, %783,
Second
- After the first character (letter, digit, underscore, or
symbol),
the name can have combinations of underscores, letters,
digits, or
symbols. Examples are _n24 or act_52_t
- A name can include spaces.
Example are c0untries st@ts, govmnt (records),
or gl0b#
$urvey||
Because of the flexibility of SQL, it can be
difficult
to maintain names in a database. Based on this, there are
conventions we
will use for our objects. In fact, we will adopt the rules used in
C/C++,
C#, Pascal, Java, and Visual Basic, etc. In our databases:
- Unless stated otherwise (we will mention the exceptions, for
example
with variables, tables, etc), a name will start with either a
letter (a, b, c, d, e, f, g, h, i,
j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D,
E, F,
G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z)
or an
underscore
- After the first character, we will use any combination of
letters,
digits, or underscores
- A name will not start with two underscores
- If the name is a combination of words, at least the second
word will
start in uppercase. Examples are Countries Statistics, Global
Survey, _RealSport, FullName, or
DriversLicenseNumber
After creating an object whose name includes space,
whenever
you use that object, include its name between [ and ]. Examples are [Countries
Statistics],
[Global
Survey], or [Date of Birth]. Even if you had
created an
object with a name that doesn't include space, when using that name, you
can
still include it in square brackets. Examples are [UnitedStations],
[FullName],
[DriversLicenseNumber], and [Country].
Practical
Learning: Starting the Management Studio
|
|
- In the Object Explorer, right-click Databases and click New
Database...
- In the Name text box, type MotorVehicleAdministration
Whenever a new database is created, the server
wants to keep
track of who created that database. This is known as the database owner.
By
default, Microsoft SQL Server creates a special account named dbo
(for database
owner). When you create a database but do not specify the owner, this
account is
used. The dbo account is also given rights to all types of
operations that can
be performed on the database. This is convenient in most cases. Still,
if you
want, you can specify another user as the owner of the database. Of
course, the
account must exist, which means you should have previously created it or
you can
use an existing one.
To visually specify the owner of a database
you are
creating, you can click <default> in the Owner text box, type the
name of
the domain, followed by the back slash, and followed by the user
name who
will own the database. Alternatively, you can click the ellipsis
button on the right side of the Owner text box.
This would open the Select Database Owner dialog box:
In the Enter the Object Names to Select dialog box,
enter
the full name or the username of the user to whom you want to assign the
database. After doing that, click Check Names. If the name is right, the
dialog
box would accept it. If the name is not right, you would receive an
error. You
can click the Browse button. This would open the Browse For Objects
dialog box.
If you see the user object you want to use, click its check box and
click OK.
Practical
Learning: Specifying the Database Owner
|
|
- In the Owner dialog box, click <default> and type DomainName\pkatts
(replace DomainName with the name of your domain; otherwise, skip
this step)
(you can also click the browser button on the right side of Owner to
locate
and select the desired username)
The Primary Size of a Database
|
|
When originally creating a database, you may or may
not know
how many lists, files, or objects the project would have. Still, as a
user of
computer memory, the database must use a certain portion, at least in
the
beginning. The amount of space that a database is using is referred to
as its
size. If you use the New Database dialog box, after specifying the name
of the
database and clicking OK, the
interpreter automatically specifies that the database would primarily
use 2MB.
This is enough for a starting database. Of course, you can either change
this
default later on or you can increase it when necessary.
If you want to specify a size different from the
default, if
you are using the New Database to create your database, in the Database
Files
section and under the Initial Size column, change the size as you wish.
Practical
Learning: Setting the Database File Size
|
|
- In the Database Files section, click the box under the Initial
Size
column header, click the up arrow of the spin button and increase
its value
to 5
The Location of a Database |
|
As you should be aware of already from your
experience on using
computers, every computer file must have a path. The path is where the
file is
located in one of the drives of the computer. This allows the operating system to know
where the file is, so that when you or another application calls it, the
operating system would not be confused.
By default, when you create a new database,
Microsoft SQL Server
assumes that it would be located at Drive:\Program
Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA folder. If you
use the
New Database dialog box of
the SQL Server Management Studio, if you specify the name of the
database and click OK, the interpreter automatically creates a new file,
and appends the .MDF extension to the file: this is
the (main) primary data file of your database.
If you do not want to use the default path, you can
change
it. If you are using the New Database dialog box, to change the path,
under the
Path header, select the current string:
Replace it with an appropriate path of your choice.
Practical
Learning: Checking the Location of the Data File
|
|
- Scroll to the right side and, under the Path header, notice the
location
of the file
- Start Windows Explorer
- In the left frame, click the C: drive
- Right-click a blank area in the right frame -> New -> Folder
- Type Microsoft SQL Server Database Development as the name
of the
new folder
- Return to the New Database dialog box.
Under Path, click the browse button - Locate the Microsoft SQL Server Database Development folder you
created
and select it
- Do the same for the other path
- Click OK
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.
Database Creation With Code |
|
To assist you with creating and managing databases,
including their object, you use a set of language tools referred to as
the Data
Definition Language (DDL). This most includes commands. For example, the
primary
command to create a database uses the
following formula:
CREATE DATABASE DatabaseName
To assist you with writing code, in the
previous
lessons, we saw that you could use the query window.
The CREATE DATABASE (remember that SQL
is not
case-sensitive) expression is
required. The DatabaseName factor is the name that the new
database
will have. 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 NationalCensus;
This formula is used if you do not want to provide
any
option. We saw previously that a database has one or more files and we
saw where
they are located by defauft. We also saw that you could specify the
location of
files if you want. To specify where the primary file of the database
will be
located, you can use the following formula:
CREATE DATABASE DatabaseName ON PRIMARY ( NAME = LogicalName, FILENAME = Path )
The only three factors whose values need to be
changed from
this formula are the database name that we saw already, the logical
name, and
the path name. The logical name can be any one-word name but should be
different
from the database name. The path is the directory location of the file.
This
path ends with a name for the file with the extension .mdf. The path
should be
complete and included in single-quotes. Here is an example:
CREATE DATABASE NationalCensus ON PRIMARY ( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf') GO
Besides the primary file, you may want to create and
store a
log file. To specify where the log file of the database would be
located, you
can use the following formula:
CREATE DATABASE DatabaseName ON PRIMARY ( NAME = LogicalName, FILENAME = Path.mdf ) LOG ON ( NAME = LogicalName, FILENAME = Path.ldf )
The new factor in this formula is the path of the
log file.
Like the primary file, the log file must be named (with a logical name).
The
path ends with a file name whose extension is .ldf. Here is an example:
CREATE DATABASE NationalCensus ON PRIMARY ( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf') LOG ON ( NAME = DataLog, FILENAME = 'C:\Exercises\NationalCensus.ldf') GO
Practical
Learning: Creating a Database Using SQL
|
|
- 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 ON PRIMARY ( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\RealEstate1.mdf') LOG ON ( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\RealEstate1.ldf') 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 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.
Introduction to Namespaces |
|
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:
Notice that, just like a namespace can contain
objects
(schemas), a schema can contain objects also (the objects we will create
throughout our lessons).
To manage the schemas in a namespace, you need a way
to
identify each schema. Based 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.
A schema is an object that contains other objects.
Before
using it, you must create it or you can use an existing schema. 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.
To access the schemas of a database, in the Object
Explorer,
expand the Databases node, expand the database that will hold or own the
schema,
and expand the Security node.
To create a schema, right-click Schemas and click
New
Schema...
This would open the Schema - New dialog box. In the
Schema
Name text box, enter a one-word name. Here is an example:
After providing a name, you can click
OK.
We will see a practical example of creating a
schema
in Lesson 7.
Accessing an Object From a Schema |
|
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 |