Contact Manager - Creating the SQL Database
The main purpose of the Contact Manager is to store information regarding contacts. To be able to do this we need to store the data in some sort of data source. A data source can be one of many things including text files, XML files, SQL databases, MySQL databases and many more database and file formats.
For these tutorials we will be using SQL Server Express to store the data in, using a SQL Database. In the first tutorial we will be covering how to create the database using the visual tools provided for SQL Server Express.
There are various methods in which one can approach project design. These tutorials are not specifically targeted at a design methodology, but rather as a guideline for complete beginners to get started with Development. Projects will generally go through a complete development live cycle, including Functional and Technical Specifications phases in which decisions regarding technologies to be used and data store options will be decided.
Once you have SQL Express and SQL Management Studio Express installed from Part 1, we need to connect to the SQL Server and create the actual database which we will use to store the contact information in.
A database general consists of various tables, each containing rows of information (Similar to an Excel spreadsheet). Within the database tables can have relationships, linking different tables together (Linked sheets and cells in Excel). We can however also create read-only views to display specific information and also create Stored Procedures to manipulate the data inside these tables and apply business logic should it be required.
Step 1: Connect to SQL Server Express
1. Click on Start - Programs - SQL Server Express - SQL Server Management Studio. (The location may differ dependant on your installation)
2. A login screen represented in figure 1.1 will be displayed once the Management Studio starts.
Server Type
SQL Server 2005 provides different server types, which can be accessed via the Management Studio. These include the Database Engine, Reporting Services and Analysis Services. For this tutorial we will be connecting to the Database Engine, which is selected by default.
Server Name
The server name represents the machine we want to connect to. It is possible to connect to other SQL Servers hosted on other machines or even the Internet. We will be connecting to the local machine, referred to as LocalHost. The Studio should have the local server selected by default. Alternatively you can enter your <machinename>.
Authentication
SQL Server provides two means of authentication. SQL Server Authentication uses usernames and passwords created inside SQL Server. Windows Authentication uses the local machine user to log into the database. SQL Server Express is by default configured to allow the local machine user access and therefore we will keep the default setting of Windows Authentication for this tutorial.
3. Click on Connect.
If all goes well this will successfully connect to the SQL Server and the default Studio layout should come up. This consists of the Object Explorer on the left hand side and the Object Explorer Details pane on the right.
Should you encounter any problems with any of the steps of the tutorial please use the comment option at the bottom to post the error(s) or any questions you may have regarding the specific tutorial. Feedback will be provided to the best of the Author's knowledge and this will also provide a reference for other readers that may be experiencing the same or similar problems with certain steps in the tutorials.
Step 2: Create the Database
1. In the Object Explorer, expand the Server, and then expand Databases, if not expanded already.
2. Right Click on the Databases option and select New Database. (See Figure 1.2)
3. Give the database a name, for the purposes of the tutorial we will use ContactManager.
4. Click OK and the Object Explorer should refresh to reflect the new Database under Databases.
5. Expand Contact Manager, which will display the various options for the selected database. (See Figure 1.3)
When creating a Database using the Studio, a physical database file is created with a .mdf extension. This file is the physical data store attached to SQL Server. A second .ldf file is created, generally known as the Log File. All changes to the .mdf file is stored inside the .ldf file. These files can be backed up, either to be moved from one machine to another, or alternatively to allow for rollback should anything go wrong. A separate series of tutorials are being planned to covered the various options inside SQL Server.
All commands issued through the Studio can be run and created as actual SQL Queries. The Studio effectively generates a SQL Script in the background which is executed against the SQL Server or Database. For example, the script to create the SQL Database would be:
CREATE DATABASE [ContactManager] ON PRIMARY
( NAME = N'ContactManager', FILENAME = N'D:\Databases\MSSQL\ContactManager.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ContactManager_log', FILENAME = N'D:\Databases\MSSQL\ContactManager_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
This brings us to the end of the first tutorial. Short and sweet. In the next tutorial we will create the table we need to store the Contact details in, and work with the Table Designer inside the Management Studio. We will also look at the different data types available and what they do.