Learn to be Better: SQL Server Management Studio Express

Creating databases using SQL Server Management Studio Express

With the release of SQL Server 2005 Express Edition, TI students and professionals began to have an efficient, professional and cheap solution for creation and management of SQL databases. However, to the surprise of most, the Express distribution is not accompanied by any front-end to manipulate the databases and its objects. This happens because of the fact that all the Visual Studio tools have the capability to manage the SQL Server from inside of its IDEs.

In order for the database administrators to use SQL Express, Microsoft released a free tool called SQL Server Management Studio Express. This program, obtained through download, substitutes the old “Enterprise Manager” and “Query Analyzer” of SQL Server 2000, now centering all of the database management in a single tool.

In this article you will get acquainted with Management Studio Express and will learn to create the database of a small library through the resources offered by the tool. You will also see how to create a Database diagram, insert views and export the database created.

Download and installation of the manager

To obtain the SQL Server Management Studio Express, just download directly from Microsoft’s site at the http://msdn.microsoft.com/vstudio/express/sql link. Having done this, simply install the software. After its installation, a program shortcut is created in the Start menu of Windows, inside the Microsoft SQL Server 2005 folder.

Gaining access to SQL Server 2005 Express

When you open Management Studio Express, a connection window with SQL Server will be opened, as Figure 1 shows. In this window, you will enter the data for connection with SQL Express existent in your machine. A detail to be observed is that you can, through the Management Studio, connect to a server SQL Server which is in another machine in the net. Just click in the Server name box and choose the Browser for more option to search for the other server. In our example, we will connect to the existing SQLEXPRESS instance in the machine itself (that, in this case, has the name PND).

Figure 1. Connection window of the SQL Management Studio Express

Relative to the authentication mode, a warning: in case you have installed the SQL Express along with the installation of an Express Tool or Visual Studio 2005, you will not have the “sa” user password and will not be able to effect the authentication through SQL Server Authentication. You will have to use the Windows Authentication option to gain access to the program. In our example, we will connect using Windows authentication.

Management Studio Work Area

After connected, the work area of Management Studio Express is then presented, as Figure 2 shows. This window possesses the following areas: Menu Bar, Toolbar, Object Explorer and Active Files.

Figure 2. SQL Server Management Studio Express Work Area

Through the Object Explorer box we can navigate among all of the SQL Server parts, especially the Databases folder, where the created databases and their objects are contained (tables, views etc.).In order to create a new database, it is enough to right-click over the Databases folder and choose the New Database option.

The window for the creation of the database is then opened, where you insert the name, choose the owner and configure the initial size of the data and log files. In our example,we call our new database “Library” and leave the initial data file size at 3 MB, as you can verify in Figure 3. In the Options page, you will be able to set some of the new database’sadvanced options, as: Compatibility level, where you choose with which version of the SQL Server the new database will be compatible; Database Read-Only, where you decide if the database created will be read-only; and Restrict Access, where you set the database access restrictions. After performing all the actions, click the OK button to finish our database creation.

Figure 3. The “New Database” Window

Creating tables

Created the database, we will visualize and add the desired tables, diagrams and views. To visualize our database’s folders, we will expand the Databases objects and, after that, Library, through the Object Explorer. Once the database is open, the following folders’ structure is presented:

· Database Diagrams: the entity-relationship diagrams are stored in this location;

· Tables: folder where the database tables are;

· Views: here stay all the views that have been created;

· Synonyms: directory of the synonymous that have been created;

· Programmability: place where you set all of the database’s programming: stored procedures, function, rules etc;

· Security: in this last item, you set all the security parameters, such as Users, Schemes,
Certificates, etc.

We will begin by creating the Books table. To create a table, click the right hand button over Tables and choose the New Table option. The new table’s data structure then opens composed of three parts: the Table Designer toolbar, the spread sheet for creation of the columns and the column’s properties’ box, as in

Figure 4.

Figure 4. Creating a table in data structure mode

To insert a field, you must write the name of the column, choose the kind of data and examine if it will accept null values. After, with the new field selected, you will be able to set all its properties in the Column Properties box. Amongst the existing properties, we can highlight the Identity Specification option, where you can attribute the identity property and set the auto increment of the field, as shown in Figure 4. To attribute a primary

key to a field, just select it and click the Set Primary Key button located in the Table
Designer toolbar.To finish the creation of the Books table, just click over the “X” in the right hand side of the table structure window. The Management Studio Express asks if you wish to save this table and what name should be attributed to this new object. Once it is saved, the table begins to appear in the list of the Tables folder.

For our example, we will create the tables in accordance with Table 1.

Table Name	Columns and Data Types
Categories	· categoryID – int · name – varchar(200)
Publishers	· publisherID – int · name – varchar(200) · email – varchar(MAX)
Authors	· authorID – int · name – varchar(200) · email – varchar(MAX)
Books	· bookID – int · category – int · publisher – int · author – int · title – varchar(200) · summary – text

Table 1. List of tables to be created in the Management Studio

All the ID fields at the beginning of the tables are primary keys. After they are created, fill in the tables with three categories, three editors, three authors and 10 books of your choice. To do this, click the right hand button over the table and choose the Open Table option.

Creating entity-relationship diagrams

One of the most interesting Management Studio’s resources is the creation of entity-relationship

diagrams (the database diagrams). These diagrams, besides favoring the visualization of the

tables and their relations, allow the user to understand the whole database.

With all of the tables created, we will now add a database diagram to our database and create

all the necessary relations. To do so, right click over Database Diagrams and choose the New

Diagram option. Automatically, it will open a blank diagram and ask which tables you wish to

insert in the new diagram. For our example, we will select all the tables available, clicking the

Add button to add them and then the Close button. In your screen you will have all the tables

and their respective fields,

as Figure 5 shows.

Figure 5. Window with the Entity-Relation Diagram

In case it is the first time you are using the database diagram resource in Management Studio Express, you will be asked to install some files necessary to the proper functioning of the diagrams module. Just click Yes so that these files are installed.

In our example, we will now create a relation between the Authors and Books tables. To add a relation between two tables, select the table that contains the foreign key (in our case, the Books table) and click the Relations button

located in the Database Diagram toolbar. Clicking the Add button, a new relation is created with the name FK_Books_Books, according to

Figure 6.

Figure 6. Relations’ Window

In this window, you will be able to set all the properties of the new relation, such as: name of the relation, description, referential integrity rules, the tables that are part of the relation and the respective fields.

To edit

the relations’ definitions, click the Tables and Columns Specifications property and then the button. The box where you will determine which tables and fields will take part in the relation is opened, as

Figure 7 shows.

Figure 7. Determining the tables and fields in the relation

Notice that the Foreign key table field for table choice is disabled, making the alteration impossible.

This is the reason because of which you selected the table with the foreign key to start the relationship window, and not the one that contained the primary key.

Modify the Primary key table field for the Authors table and, after that, choose the authorID field. After, in the Books table, choose the author field and confirm the new relation clicking over the OK button. With this, the relationship is created.

In case it is necessary to set the referential integrity in the relationship, just open the window of relations and expand the INSERT and UPDATE Specification option. When you expand, you will be able to choose the relation’s rules of update and delete. In our example, we will choose, in the Delete Rule field, the Cascade option, this assures that, when removing an author, all the work belonging to this author will be removed from the table Books.

With this, we create the first relation. Create the other relationships so that we have the same diagram as the one in

Figure 8. After that, save the diagram with the name “Library Diagram” and close the window.

Figure 8. Diagram after the creation of all the relations

Tip: You can select a field in the table and drag it until the referenced table primary key,

to visually set the relation.

Creating views

View has always been a very useful and very practical feature for the creation of reports integrating data from the various tables of the database. In our example, we will create a View for a report of book titles organized by author.

Right-click over Views and choose the New View option. Automatically it will open a blank diagram and ask to which tables you wish to insert inside the new diagram. For our example, we will select Books and Authors tables; we click the Add button to add them and finally the Close button. You will have on your screen the Views setting and testing window, as

Figure 9 shows.

Figure 9. Setting and Views test window

The Views window is divided in four parts: Diagram Panel, Criteria Panel, SQL Panel and Results Panel. Through these panels you can assemble its views through SQL language or through mouse selection.

In our report, will be displayed the authors (in alphabetical order), their respective e-mails and books. For this matter, in Diagram Panel, select the fields name and email in the Authors table; and the title field in the Books table. Notice that, as you go along selecting the fields, the Management Studio Express assembles the entire query in the SQL Panel.

Having done this, select the Execute SQL button

in the View Designer toolbar. It will already show, in the Results Panel, the data of both the tables.

But we are not yet finished: we need to put the name field in alphabetical order. In the Criteria Panel, go to the Sort Type field of the line of the name column and choose the Ascending option. When you execute the query, the result will be a listing by order of author, with their respective e-mails and books.

To finish, go to the Alias column and type “Author Name”, “E-mail” and “Book Title” on their respective fields

(see Figure 10). Alias will make these texts appear at the heading of the results table, avoiding the user from visualizing the names of the fields in the report. When finished, your Views window will look like Figure 10. Save with the name “view_books_author” and close the window.

Figure 10. Views window, after field selection and setup

Exporting the database

After creating the database in your machine, there might be the necessity to export it to a SQL Server, or to generate a backup for later use. We will show you here the exportation of the Library database to a Script (*.sql) archive. Click with the right hand button over Library. Go to Task > Generate Scripts. The Management Studio will show a wizard for the script generation. Click Next and choose the Library database. After selected, mark the Script all objects in the selected database option and click the Finish button,

as Figure 11 shows.

Figure 11. Scripts generation assistant

After executing the processing on behalf of the Management Studio, a window with the entire script of the generated database is opened. To save this script, just go to File > Save, choose the folder and name the file.

Conclusion

The Management Studio Express is a Microsoft SQL Server free management tool which makes developers’ and DBAs day to day easier. In this article we saw the visual interface of this tool and show how to create some resources that are present in the entire database (tables, database diagrams and views).