CIS 2109First SQL Server Lab

Creating Tables & Relationships, Loading Data

Inserting, Updating, and Deleting Records

Table of Contents

1. Objectives

2. Introduction

3.Start MS SQL Server and Connect to a Database

4. Create a Small Database using MS SQL Server Management Studio

4.1. Create Tables using Management Studio (Customers and Orders tables)

4.2. Create Index using Management Studio (on the Customers table)

4.3. Create ER Diagram and Create Relationship (between Customers & Orders tables)

5. Create Tables (Order_Line & Products) using SQL Commands, Update ER-D

6. Modify table Definitions Using SQL (Add Fields to Customer Table)

7. Inserting, Updating, and Deleting Data

7.1. Insert Data and Run Queries using Management Studio

7.2. Insert Data with SQL INSERT Statements

7.3. Backup Tables Using SQL Commands (INSERT INTO, DROP TABLES)

7.4. SQL UPDATE commands

7.5. SQL Delete (Deletes Records but not Table Structure)

8. Loading a Database with Data

8.1. Import database from MSAccess

8.2 Load Database using SQL Script

9. Homework Submission

Goodreference for SQL syntax andexamples:

1. Objectives

In this lab, you will be

Getting started with Microsoft SQL Server

Creating tables, relationships, and ER Diagrams using Microsoft SQL Server Management Studio

Entering data and running Queries using Management Studio

Creating tables and relationshipsusing SQL DDL commands (using a SQL Query Window)

Inserting, updating, and deleting data with SQL commands (using a SQL Query Window)

Importing a database

Creating a database (tables, relationships, and data) by using SQL scripts

As you go through these instructions, you will be asked to copy and paste various things into a word document. You may want to go to section 9 now (“Homework Submission”) to get a list of the items you’ll be submitting and paste this list into a Word document (for later submission to the TA). Then as you encounter items that you need to copy, you’ll have a place to put them. 

2. Introduction

In the previous labs we used Access as the database management system (DBMS) for managing relational databases. In this Lab you will work witha different relational DBMS - Microsoft SQL Server. The lab is based on the SQL lecture material and will enable you to practice with SQL commands. Note, as you create and execute the SQL statements, try also to focus on the database design and the practical use of the statements.

The system configuration we use in this lab is based on a client/server architecture that includes clients, a server and a network as in Fig 2.1. The clients are the individual PCs of the system. Each client is typically loaded with some application software (such as accounting) anddata access software (API) such as ADO.NET. Each client PC, is installed with the client software portion of Microsoft’s SQL Server 2000. The client software includes various user tools that we will use in this lab, such as the Management Studio and the Query Analyzer. The server (aka: database server) is a computer on the network that stores the database files and requires a DBMS software such as SQL Server (or Oracle). The server in our lab is called Dwarf. The network consists of the cabling, communication lines and other components that connect the clients and the server of the system. In our labs the client/server system are part of the CIS LAN.

Figure 2.1 Client/Server Configuration

Note that Windows XP and Microsoft SQL Server 2000 enable you to install the database server on your client, (called MSDE - Microsoft SQL Server Desktop Engine), and run the client/server configuration on a single PC. This way you can run client/server configuration on your home computer and execute SQL commands.

The database is typically stored on:
c:\Program Files\Microsoft SQL Server\MSSQL\Data

and the extension of the Microsoft SQL Server database file is: MDF (Ex: mydemo.MDF)

3.Start MS SQL Server and Connect to a Database

To start MS SQL Server, click:

Start  All Programs  Microsoft SQL Server 2005  SQL Server Management Studio

  • The name of the server for this class is “DWARF”. Select that server name from the pick list (shown below). If the Server Dwarf is not displayed, you may need to register it (if so, ask your TA).
  • Under Authentication, select "SQL Server Authentication" (since your assigned SQL Server user name and password are not the same as your Windows user name and password.)
  • Under the Login and Password fields, enter your assigned user name and password for this class (user name format is like: fa08_c2109xxxx).

Figure 3.1Connecting to a database

Click on the Connect button to see the Microsoft SQL Server Management StudioObject Explorer.

Figure 3.2 SQL Server Management Studio

Underneath the DWARF server, open the ‘Databases’ folder to display all the databases in Dwarf. It may take a few minutes to find and open your database (named the same as your class user name, format like: fa08_c2109XXXX).You always should open your database first when you are working with SQL Server.

Figure 3.3 Opening Your Database in SQL Server Management Studio

Note that you arenot authorized to create new databases. Therefore, all the tables that you will create must be done in a single database schema assigned to you.

4. Create a Small Database usingMS SQL Server Management Studio

In this lab, we will use these features (as shown in figure 3.3 above).

  • Tables– to create tables and enter data.
  • Diagrams – to create an Entity Relationship Diagram (ERD) and create relationships between the tables in your database.
  • Views – to create views.
  • SQL Query Window (by clicking the “New Query” button, top left in menu) – to run SQL commands.

(Some of the other options relate to database security and only the database administrator can use these.)

Your first exercise is to create a portion of the schema of the manufacturing database from the textbook. Recall that the normalized manufacturing Entity Relationship Diagram (ERD) looks like this:

Figure 4.1 The Manufacturing System ER DIAGRAM

4.1. Create Tables using Management Studio(Customers and Orders tables)

To create the Customers table,right click the ‘tables’ folderand select ‘New Table’.

Figure 4.2 Creating a New Table

Enter the following column specifications (Column Name, Data Type, and Allow Nulls).

Figure 4.3 Specifying Columns in Creating a New Table

Right click on the Customer_ID column and selectPrimary Key.

Figure 4.4 Setting Primary Key for a Table

When you are done, click the save iconand you will be prompted to name the table (“Customers”).

Next, createthe Orders table (the same way you created the Customers table).

Figure 4.5 Creating the Orders Table

NOTES:

  • If a primary key is made up of multiple attributes (Column Names), youwould select all those attributes (e.g., using the Control key) and then right-click and select Primary Key.
  • A null value indicates the absence of data.
  • Any type of field (e.g., date, numeric, character) can hold a null value, but ONLY if that field specified “ALLOW NULLS” during table definition.
  • A typical use of allowing NULLs is to implement an optional foreign key relationship. For example, you might want to enforce a constraint on Student records – if a Major is specified, that Major must exist in the Department Table. However, a Student may not have any value at all in the Major field.
  • Note other common data types that are listed in the appendix at the end of this document.

If you need to modify the design of one of your tables, you can always, right click on the table, and select Design:

Figure 4.6 To Modify the Design of a Table

IMPORTANT:

  • As you work with SQL, realize that all your changes are NEVER SAVED(e.g., creating tables, modifying data, etc), until you close out the window in which you made those changes. Although you may feel that this is annoying, it is designed to save the database from unwanted mistakes.
  • Also, you may have to right-click on your database, its tables folder, or any table underneath that and select REFRESH in order to see your latest changes.

CLOSE ANY WINDOWS THAT MAY BE OPEN NOW, so that your changes are committed to the database.

4.2. Create Index using Management Studio(on the Customers table)

MS SQL Server provides for clustered and non-clustered indexes. A clustered index defines the sequence in which the rows of the table are stored. Thus each table can contain only one clustered index, and many (over 200) non-clustered indexes. A database can be VERY FAST or VERY SLOW, depending on whether the indexes are appropriate for the usage. If you put lots of indexes, you get faster queries, but slower updates (because a lot of indexes need to be updated whenever data is updated).

When to create an index:

  • When the column is a foreign key or when the column is used frequently in search conditions
  • When the column contains a large number of distinct values
  • When the column is updatedInfrequently (so the index does not slow down data updates)

In order to make things faster, an index must be specified exactly as the search criterion. For example, suppose there are lots of queries that look for names (but converted to upper case – all capital letters), like this:

… WHERE UPPER(Customer_name) = “SMITH”

Create an Index on the Customers table, on field Customer_Name.

To create an index on a table, open up the table, then right click on the “Indexes” folder underneath that table, then select “New Index” as shown in the figure below

Figure 4.7 Creating an Index on a Table

When the following window opens,

  • Give your index a name, such as IX_CUSTOMERS_CUSTOMER_NAME
  • then click on the ADD button.

Figure 4.8 Naming an Index on a Table

  • When the secondary window pops up (showing you all the fields that are in the Customers table), you select the field (or fields) on which you want to index. Select Customer_Name in the window shown below, and then click on OK.
  • Now that you have created an index on the Customers table by Customer_Name, all queries that specify Customer_Name in their condition (WHERE CLAUSE) will run faster. However, updates to the Customers table may be a bit slower since SQL Server will have to update this index whenever any Customer_Name changes.
  • Back in the main window, click on the Unique checkbox. By doing this, the database will not allow two customers with exactly the same Customer_name to be stored in the Customers table.
  • If you wanted to add another Index to the Customers table, you would click on Add again, but we will not do that now.
  • Click OK and OK to complete your specifications of Indexes on the Customers table.

Figure 4.9Selecting Column(s) upon which the Table will be Indexed

When you have finished creating your Index on the Customers table, you should be able to see it in the Object Explorer pane. You may have to play around a bit with refreshing in order to see this (right-click on and select REFRESH for the Indexes folder, the Tables folder, your database folder, closing and opening your database).

Figure 4.10New Index Shown in Object Explorer

4.3. Create ER Diagram and Create Relationship (between Customers Orders tables)

Next create an ERD (Entity Relationship Diagram) that will enable you to specify relationships between the tables in your database by right clicking on the “Database Diagrams” folder (underneath your database in the Object Explorer).

Figure 4.11 Create an Entity Relationship Diagram (ERD)

Add tables to your ER Diagram.

  • When the “Add Table” window pops up, select the Customers table and the Orders table to be added to your diagram. If you do not see these tables as choices, click on the Refresh button so you see the latest list of tables in your database.

At this point, your ERD should look like this:

Figure 4.12 ER Diagram with Two Tables Added (no relationships yet)

Add a relationship(from Orders.Customer_ID to Customers.Customer_ID).

  • Drag the grey box before the Orders.Customer_ID attribute to the Customers.Customer_ID attribute.
  • When this window pops up, check that you have the right primary key table and field, the right Foreign key table and field.
  • Then click OK.

Figure 4.13 Columns Specified in a Relationship in ER Diagram

Then, you’ll see the Foreign Key Relationship main window. If you open up “INSERT and UPDATE specifications”, (similar to Access relationships) you have the option to select

  • No action – This is the normal choice. With this selected, the database will not allow a primary key record to be deleted if there are any foreign key records pointing to it.
  • Cascade
  • Set null
  • Set default

Figure 4.14Specifying Cascade Update or Delete for a Relationship in ER Diagram

This is how your final ER Diagram will look. (If you make a mistake, you can right-click on the relationship line, delete it and then try again.)

  • Click the SAVE icon and name your diagram (perhaps “MY_ERD”).
  • It is important to CLOSE OUT of the ERD window and say yes to SAVE – so that your changes will be committed to the database (the addition of the FOREIGN KEY reference from Orders to Customers).

Figure 4.15 ERD (Entity Relationship Diagram) with Relationship

5.Create Tables (Order_Line Products) using SQL Commands, Update ER-D

To run SQL commands, click on New Query in Microsoft SQL Server Management Studio. This opens up a SQL Command window into which you enter the SQL you want to run:

Figure 5.1Opening a SQL Query Window (so you can run SQL Commands)

Type a simple SQL command into the SQL Query Window: SELECT * FROM CUSTOMERS Then, click on the red exclamation point icon to run your SQL. This causes the results window to open up on the bottom. Right now, you only see the column headings (Customer_ID, Customer_Name), but no data since data has not been entered yet.


Figure 5.2Executing SQL Commands from the SQL Query Window

You can also click on the blue checkmark icon to the right of the Execute icon, if you just want to check the syntax of your SQL commands (see if you made any spelling errors, etc) before running it.

Use SQL to create Products and Order_Line tables, by enteringthe following SQL DDL commands into the SQL Query Window and then executing them (red exclamation icon).

CREATE TABLE products
(product_idINTIDENTITY PRIMARY KEY NOT NULL,
product_descriptionCHAR(30) NOT NULL,
product_quantity INT);

CREATE TABLE order_line
(order_id INT NOT NULL REFERENCES orders(order_id),
order_quantity INT NOT NULL CHECK(order_quantity > 2),
product_id INT NOT NULL REFERENCES products(product_id));

The bottom Results window should show you this message:

Command(s) completed successfully.

Just for fun, run those commands again (just click on the red exclamation point again). This time you should get this message:

Msg 2714, Level 16, State 6, Line 1

There is already an object named 'products' in the database.

It’s saying that it can’t create a Products table because one is already created. It’s a good idea to pay careful attention to error messages rather than just try to guess what went wrong.

In the Object Explorer, right click on your Tables folder and select REFRESH. Then, you should see the new tables that you just created by using the above SQL DDL commands.

Figure 5.3New Tables Shown in Object Explorer

Note that there is NO NEED TO SAVE when you close out a SQL Query Window-- unless you think you might want to run that SQL script again (if you do want to save it, give it a good name so you can find it in the future). Generally, for this lab, we do not need to save the SQL scripts, but for the other Windows, it is important to Save successfully.

Using the Obeject Explorer, open the ERDiagram you created earlier (Figure 4.15) and add the two additional tables (order_lines and Products) to your ER Diagram. To add the tables, right-click on any blank space in the diagram. If the relationships are not shown, make these connections now.

Figure 5.4 The complete ERDiagram (with four tables)

Note for your Assignment (that you will print and email to the TA)

If you haven’t already done so, create a word document that will hold the various items that you will be copy/pasting for your homework submission. (See step 9 for more about homework submission.) Into your Word document:

Part A: copy/paste the SQL commands you used in this step -- to create the two tables (products and order_line)

Part B: copy the ER DIAGRAM (as an image, Prt-Sc) and paste into word.

6.Modify table Definitions Using SQL (Add Fields to Customer Table)

You can use SQL DDL (Data Definition Language) to modify table definitions as discussed below.

  • To add a new attribute (phone ) to our customers table, you would run this SQL DDL code:

ALTER TABLE customers
ADD phone CHAR (10);

  • To remove this attribute from the table, you would run this code:

ALTERTABLE customers
DROP COLUMN phone;

  • Add the Phone field back to the Customers table (pretend your client has trouble making up their minds).
  • Similarly, you COULDuse the ALTER command to add primary keys, foreign keys and other constraints to your tables.However, we already have the Primary and Foreign Key indices, so don’t do this now. (Note: IX_cust_orders is just a name you decided to call the index.) You also could drop and re-add indices.

ALTER TABLE orders
ADD FOREIGN KEY (IX_cust_orders) REFERENCES customers(customer_id);