Transcript Using SQL Server to create a table

Microsoft SQL Server 2008
From the Program menu choose:
Microsoft SQL Server 2008 R2  SQL
Server Management Studio.
You may see a window indicating the MS
SQL Server Management Studio is
configuring for first time use. Be patient;
it sometimes takes a while.
In the Connect to Server window, select
or enter:
Database Engine for the Server type
ICSD for the server name
Windows Authentication for Authentication.
Then press the connect button.
In the Object Explorer pane, expand
Databases.
If you don’t see an Object Explorer pane,
select it from the View menu.
For this course there are four databases
(identified by folders):
CS451 Family
CS451 Packers
CS451 Supplier
CS451 University
You should have read-only access to each
one. I will use these during the semester.
To see the tables in one of them, expand
the database folder and then the Tables
folder.
Right click on one of the table names and
select Select Top 1000 Rows
Test this and let me know of any access
problems.
You should not be able to change the data
in any of these tables.
If you want tables you can edit, you
can create tables in your group
database or copy (import) them from
one of the existing databases.
Subsequent slides show how to do
both.
Creating Tables in SQL Server
Your group databases are named
CS451 G1 (or G2, G3, G4, etc.)
Expand the folder for the database
assigned to you and right click on the
Tables folder
select New Table.
In the resulting window you can specify
column names, types, length, and whether
null values are allowed.
A column that has no value may be
assigned null.
Any comparison with data involving that
column will ALWAYS evaluate to false.
Create some columns using whatever
names and types you want.
To create a primary key (unique
identifier):
highlight the appropriate column(s) by
clicking in the area to the left of the column
name.
You can select more than one column
(composite key) by using the Shift key while
selecting additional columns
With the column(s) highlighted, right click
and select Set Primary Key to specify the
highlighted column(s) as the primary key.
To save the table
select FileSave table and specify the table
name.
To add data
right click on the appropriate table name (in
the Tables folder)
select Edit Top 200 Rows and start entering
data.
Click on the red exclamation point (!) in
the toolbar to save the data or simply
close the table
Modifying Tables/Setting Up Table
Constraints
Open a table in design view (Right click
on the table name and select Design).
Can add, remove, or change columns as
needed.
When you select a column you can
change various column properties that
appear in the lower pane.
For this course you don’t need to worry
about most of those properties.
In the Table Designer toolbar there are
some icons associated with managing
check constraints, managing indexes and
keys, and relationships.
Alternatively, you can right click in the
table’s design window and select one of
these options.
To put constraints on specific fields
Select a column
Right click and select Check Constraints.
Click on the Add button.
Each constraint has a default name. You can
provide your own in the (Name) textbox.
Enter an expression that defines a constraint
for a specific attribute.
Save the table.
The next slide has some examples.
Table/Column
Constraint
Meaning
Supplier/S
([status] >= 0 and [status] <= 50)
Status lies between 0 and
50, inclusive
Supplier/S
([S#] like 'S[0-8]')
Supplier must start with
‘S’ and be followed by a
digit that is not 9.
Supplier/SP
([QTY] % 100 = 0)
QTY must be a multiple of
100.
University/Student
([credits] >= 0)
Credits must not be
negative.
University/Student
([gpa] >= 0 and [gpa] <= 4)
GPA must lie between 0
and 4, inclusive.
Anything entered as data must meet the
specified constraint
Takes the burden off of the programmers’
to verify data integrity.
Results in more consistent data.
Setting up referential integrity
constraints (foreign keys)
Assume I have tables A, B, and AB and
that AB should contain foreign keys that
match primary keys in A and B. Here’s
how you might proceed.
Open AB in design view.
Click on the icon (or right click and select
the option) associated with Relationships.
As before click on the Add button and
provide a name for the constraint.
Look for an option Tables and Columns
Specification and click the ellipsis button
(…) associated with it.
In the menu associated with Primary Key
Table choose table A
Below that, choose the appropriate
column. Make sure that column is a
primary key for A.
Under the Foreign key Table
specification, choose the foreign key field
from table AB
Make sure you save the changes.
You will not be allowed to make changes
that violate referential integrity.
Creating ER Diagrams that reflect your
referential integrity rules
Select Database Diagrams under the
specified database.
Right click and select New Database
Diagram.
Select the tables that will appear in the
diagram and click Add and then Close.
The diagram appears and reflects the
referential integrity rules you defined in
the database constraints.
Save the diagram and give it a name.
Importing base tables from one
database to another
NOTE: This will move data to a
destination database but it does NOT
preserve any constraints defined in the
source database table.
Right Click on the destination database
(your group database) and select tasks 
Import Data.
An import Wizard will be activated.
Click Next.
Make the following selections to define
the source database before clicking Next:
Under Data Source select Microsoft OLE DB
Provider for SQL Server.
Under Server name select ICSD. If you don’t
see it, just type it in
Select the Use Windows Authentication radio
button.
Under Database select the database you
want to copy. For example: CS451 Supplier.
Click Next.
Make/verify the following selections for
your destination database:
Under Destination select Microsoft OLE DB
Provider for SQL Server.
Under Server select (or type) ICSD.
Select the Use Windows Authentication radio
button.
Under Database select the database you
want to copy to. For example: CS451_G1.
Click Next.
Select the radio button for Copy data
from one or more tables or views.
Click Next.
Select ONLY the base tables you want to
copy. DO NOT select views.
Click Next.
Check the checkbox for Execute
Immediately and click Next.
Verify that the summary provided is
correct and click finish.
This just copies the tables but does not
preserve the constraints that may exist on
the tables.
To copy the constraints we’ll need to
learn about scripting, which we’ll do
later.
The new tables may not appear in your
list of tables (remember, you’re still
looking at what the client Studio
Management has. It has not yet gotten the
updated information from the server).
However, there is a refresh icon near the
top just under Object Explorer. Click on
it and the new tables should appear in
your object explorer view.