Database Creation

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 Server\MSSQL\Data folder.

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 a database.

To create a new database in SQL Server Enterprise Manager:

• 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 Database...
• 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 Database...
• 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.

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, 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 underscore
• 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 DriversLicenseNumber

1. Start the Enterprise Manager (Start -> (All) Programs -> Microsoft SQL Server -> Enterprise Manager)
2. 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
3. Right-click Databases and click New Database...
    
   
4. In the Name text box, type StudentPreRegistration
    
   

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.

• Click the Data Files tab and notice that a file name has been automatically created:
   
  
   
  

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.

1. Under the Location header, click the ellipsis button to open the Locate Database File dialog box
    
   
2. Notice the file name and the location. Scroll up to the name (or letter) of the drive to see where the path starts
3. Click Cancel

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.

1. In the Data Files property page, click the box under the Initial Size column header and type 5
    
   
2. Click OK

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.

1. On the toolbar of the SQL Server Enterprise Manager, click Tools -> Wizards...
2. In the Select Wizard dialog box, expand the Database node, click Create Database Wizard, and click OK
3. In the first page of the wizard, read the text and click Next
4. 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, enter NationalCensus

   

5. After entering the name, click Next
6. In the third, the fourth, the fifth, and the sixth pages of the wizard, accept the default by clicking Next on each page:

   

7. 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
8. If the database is successfully created, you would receive a message box letting you know:
    
   You can then click OK.
9. You should receive a message box asking whether you want to create a maintenance plan. For this example, click No