Fundamentals of Creating a Database
Probably before using a database, you must first have one.
If you are just starting with databases and you want to use one, Microsoft SQL
Server ships with two databases ready for you. One of these databases is called
Northwind and the other is called pubs.
Besides, or instead of, the Northwind and the pubs
databases, you can create your own. A database is primarily a group of computer
files that each has a name and a location. When you create a database using
Microsoft SQL Server, it is located in the Drive:\Program Files\Microsoft SQL
In the previous lesson, we saw that there were different
ways to connect to a server. In the same way, there are different ways to create
SQL Server Enterprise Manager
To create a new database in SQL Server Enterprise
- In the left frame, you can right-click the server or the (local)
node, position your mouse on New, and click Database...
- In the left frame, you can also right-click the Databases node and click New Database
- When the server name is selected in the left frame, on the toolbar
of the window, you can click Action, position the mouse on New, and click
- When the server name is selected in the left frame, you can right-click an
empty area in the right frame, position your mouse on New, and click
- When the Databases node or any node under it is selected in the left frame, on the
toolbar, you can click Action and click New Database...
- When the Databases node or any node under is selected in the left frame,
you can right-click
an empty area in the right frame and click New Database...
Any of these actions causes the Database Properties to
display. You can then enter the name of the database.
Characteristics of a Database
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
- 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, act_52_t
- A name cannot include space, that is, empty characters. If you want
to use a name that is made of various words, start the name with an
opening square bracket and end it with a closing square bracket.
Example are [Full Name] or [Date of Birth]
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 exception, 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
- After the first character, we can use any combination of letters,
digits, or underscores
- A name will not start with two underscores
- A name will not include one or more empty spaces. That is, a name
will be made in one word
- If the name is a combination of words, at least the second word will
start in uppercase. Examples are dateHired, _RealSport, FullName, or
Learning: Creating a Database in the Enterprise Manager
- Start the Enterprise Manager (Start -> (All) Programs -> Microsoft
SQL Server -> Enterprise Manager)
- Expand the Microsoft SQL Servers node, followed by the SQL Server Group,
followed by the name of the server, and followed by the Databases node
- Right-click Databases and click New Database...
- In the Name text box, type StudentPreRegistration
The Primary Data File of a Database
In Microsoft SQL Server, a database is created like a
programming project, in the same approach as those you have probably encountered
in C++, C#, Java, Pascal, or Visual Basic, etc. As such, it contains files,
objects, and other items. As normal objects, the computer needs a way to locate
them when necessary. A database is primarily a file. As such, it must have a
name that allows the operating system to identify it. If you use the Database
Properties dialog box of the SQL Server Enterprise Manager, if you specify the
database name in the General property page and click OK, the interpreter
automatically creates a new file that includes and the name you specified for
the database and adds the _Data string to it: this is the name that will allow
the operating system to identify your database's file.
Learning: Checking the Primary Data File
- Click the Data Files tab and notice that a file name has been
The Location of a Database
As you should be aware already from your experience on using
computer, 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 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, SQL Server
assumed that it would be located at Drive:\Program Files\Microsoft SQL
Server\MSSQL\data folder. If you use the Database Properties dialog box of
the SQL Server Enterprise Manager, if you specify the name in the General
property page and click OK, the interpreter automatically creates a new file,
adds the _Data string to it and appends the .MDF extension to the file: this is
the (main) primary data file of your database.
Learning: Checking the Location of the Data File
- Under the Location header, click the ellipsis button
to open the Locate Database File dialog box
- Notice the file name and the location. Scroll up to the name (or letter)
of the drive to see where the path starts
- Click Cancel
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 Database Properties dialog box of the SQL Server Enterprise
Manager, if you specify the name in the General property page and click OK, the
interpreter automatically specifies that the database would primarily use 1MB.
This is enough for a starting database. Of course, you can either change this
default or later on you can increase it when necessary.
If you want to specify a size different from the default, if
you are using the Database Properties to create your database, in the Data Files
property page and under the Initial Size column, change the size as you wish.
Learning: Setting the Database File Size
- In the Data Files property page, click the box under the Initial Size
column header and type 5
- Click OK
Creating a Database Using the Database Wizard
Another technique you can use to create a database is
by using the Database Wizard. There are two main ways you can launch the
Database Wizard. In the left frame, when the server node or the Databases
folder is selected, on the toolbar, you can click the Tools button and
click Wizards. This causes the Select Wizard dialog box to display. In the Select Wizard dialog box,
you can expand the Database node and click Create Database Wizard:
After clicking Create Database Wizard, you can click OK.
This would start a wizard where the first page is only used to start it
and you can click Next.
Learning: Creating a Database Using the Wizard
- On the toolbar of the SQL Server Enterprise Manager, click Tools ->
- In the Select Wizard dialog box, expand the Database node, click Create
Database Wizard, and click OK
- In the first page of the wizard, read the text and click Next
- In the second page of the wizard and in the Database Name text box,
you can specify the name you want for your database. For this exercise,
- After entering the name, click Next
- In the
third, the fourth, the fifth, and the sixth pages of the wizard, accept the default by clicking Next on each page:
- The last page of the wizard shows a summary of the
database that will be created. If the information is not accurate, you can
click the Back button and make the necessary changes. Once you are
satisfied, you can click Finish.
For this example, click Finish
- If the database is successfully created,
you would receive a message box letting you know:
You can then click OK.
- You should receive a message box asking whether you want to create a
maintenance plan. For this example, click No