Home

Database Creation

 

Introduction

Before using a database, you must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, in the same way, there are also different ways to create a database.

You can create a new database in Microsoft SQL Server Management Studio, in Microsoft Visual Studio, or on the Command Prompt.

To create a database in Microsoft SQL Server Management Studio, you can right-click the Databases node and click New Database...

Microsoft SQL Server Management Studio

If you are working from Microsoft Visual Studio, to create a new database, in the Server Explorer, you can right-click Data Connections and click Create New SQL Server Database...

Server Explorer

To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object:

private void btnDatabase_Click(object sender, EventArgs e)
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes");
    SqlCommand command = new SqlCommand(Database Creation Code, connection);

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}

CREATE a DATABASE

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, even when you include it in a C# statement) expression is required. The DatabaseName factor is the name that the new database will carry.

As done in C#, 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;

Instead of manually writing all of your code, the Microsoft SQL Server Management Studio provides a code template you can use and customize. 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>'
)

CREATE DATABASE <Database_Name, sysname, Database_Name>
GO

You would then need to edit the code and execute it to create the database. If some sections of this code are not familiar to you, we will cover them in subsequent lessons

The Name 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 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 languages. 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 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 LearningPractical Learning: Creating a Database

  1. Start Microsoft Visual C# and create a new Windows Application named Exercise3
  2. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
  3. In the Server Name of the Create New SQL Server Database, type (local)
  4. In the New Database Name text box, type World Hunger Statistics
     
    Create New SQL Server Database
  5. Click OK (if you receive an error that the database was not created, open the Services in the Administrative Tools, check that SQL Server (server name) and SQL Server (MSSQLSERVER) services had started)
  6. Design the form as follows:
     
    Database Maintenance
    Control Text Name
    Label Database Name:  
    TextBox   txtDatabaseCreate
    Button Create btnCreateDatabase
  7. Double-click the Create button
  8. Implement the Click event of the button as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace Exercise3a
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void btnCreateDatabase_Click(object sender, EventArgs e)
            {
    	    // Make sure the user enters the name of the database
                if (txtDatabaseCreate.Text.Length == 0)
                {
                    MessageBox.Show("You must specify the name of " +
                         "the database you want to create");
                    txtDatabaseName.Focus();
                }
    
                string strConnection =
                    "Data Source=(local);Integrated Security=yes";
    
                using (SqlConnection connection = new SqlConnection(strConnection))
                {
                    string strDatabase = txtDatabaseCreate.Text;
                    SqlCommand command =
                        new SqlCommand("CREATE DATABASE [" + strDatabase + "];",
                        connection);
    
                    connection.Open();
    
                    command.ExecuteNonQuery();
    
                    MessageBox.Show("A database named \"" +
                                    txtDatabaseName.Text +
                                    "\" has been created on the " +
                                    connection.DataSource + " server.");
    
                    txtDatabaseCreate.Text = "";
                }
            }
        }
    }
  9. Execute the application
  10. In the Database text box, type Red Oak High School1
  11. Click the Create button
     
    Database Maintenance
  12. Close the form and return to your programming environment

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 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 assumed that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\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.

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 in the Microsoft SQL Server Management Studio, 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.

This series of lessons is intended for (junior) beginning database developers. The target is for those who are learning to create applications and design graphical database applications, not for those who manage databases. Therefore, in our lessons, unless stated otherwise and unless we have to, we will use the default settings of both Microsoft SQL Server and of a regular database. Microsoft SQL Server is so huge that it would be hard to cover both database development (database creation, graphical application design, functions, stored procedures, parameters, views, etc) and database administration (file locations, file sizes, backups, triggers, distribution, client/server issues, etc) in these lessons.
   
 

Published on Monday 24 December 2007

 

Home Copyright © 2007 FunctionX, Inc. Next