Transcript 02_CreateTables - IIS Windows Server

ISM 4212 Lab
02
Creating DB Tables
copyright Lars Paul Linden 2007
Lab Overview
Last Week: Introduction to the SQL Server DBMS
•
•
Creating Databases
Moving Databases
This Week: Creating DB Tables
Next Week: Introduction to SQL SELECT Statements
UCF
ISM 4212 Lab
2
Today’s Agenda

CREATE tables and DROP tables (GUI and SQL)


More about creating tables







UCF
NULL / NOT NULL
IDENTITY keyword
PRIMARY KEY
DEFAULT values
Setting table properties
Create a diagram


Fields (the columns), including setting field data types
Multi-page diagrams
Using the diagram as an editing tool for DB objects
Relationships between tables
ISM 4212 Lab
3
Create Tables
and
Drop Tables
UCF
ISM 4212 Lab
4
Two Ways of Creating Tables


UCF
Graphical User Interface’s (GUI) “New Table” form
Execute SQL in a Query Pane
ISM 4212 Lab
5
The GUI’s “New Table” form








UCF
Using SQL Server Manager Studio, open the your database in
the Object Explorer
Expand the database to see the “Tables” folder
Right click on “Tables” and select “New Table…”
In the pane that opens, enter a “Column Name”, hit tab, use the
drop down to specify a “Data Type”, and check “Allow Nulls”
depending on your design
Complete cells with column metadata
Repeat until all of your columns are specified
When done, “X” out the pane, respond “Yes”
Finally, in the “Choose Name” pop-up, type in the name of
your table, and click “OK”
ISM 4212 Lab
6
Having Created It,
Display Your Table






UCF
Using SQL Server Manager Studio
In the Object Explorer, open the your database
Expand the database to see the “Tables” folder
Expand the “Tables” folder to see the name of your
table
Right click on the name of your table and select
“Modify”
Note: select “Open Table” to see the table’s data
ISM 4212 Lab
7
basic CREATE TABLE syntax
CREATE TABLE Products
(
ProductID
INT,
ProductName CHAR(20),
Price
MONEY
)
Parenthesis around
Column specifications
UCF
Commas between
column specifications
Notice how each line
has a field name
and that field’s data type
ISM 4212 Lab
8
Another way
to create a table with SQL




Open a Query Pane
Open the “Template Explorer”
Under “Table”, select “Create Table”
Replace the <xxxxxxx> as needed


UCF
an example of the syntax is on the next slide
And, of course, execute
ISM 4212 Lab
9
Drop a Table (GUI)





UCF
Using SQL Server Manager Studio, open the your
database in the Object Explorer
Expand the database to see the “Tables” folder
Expand the “Tables” folder to see the name of your
table
Right click on the name of your table and select
“Delete”
Click on “Yes” to confirm
ISM 4212 Lab
10
Drop a Table (SQL)
# Make sure you are using the
correct database
DROP TABLE YourTableName
UCF
ISM 4212 Lab
11
Setting table properties
UCF
ISM 4212 Lab
12
table properties


UCF
In Object Explorer right-click on the table name
Select “Properties”
ISM 4212 Lab
13
More about creating DB tables
UCF
ISM 4212 Lab
14
Look at an existing table


In “Northwind”
Open the “Shippers” table


B.
C.
D.
UCF
C
A
Note the following:
A.

Using Rgt-Click “Modify”
Allow Nulls
Identify Specification
Primary Key icon
Default values (not shown)
B
Now we covers these
four topics…
ISM 4212 Lab
15
A.
CREATE TABLE
with NULL or NOT NULL
CREATE TABLE Products2
(
ProductID
INT
Name
CHAR(20)
Price
MONEY
Description CHAR(100)
)
NOT NULL,
NOT NULL,
NOT NULL,
NULL
After executed, you can view
the table and check what you created
for the Description field:
UCF
ISM 4212 Lab
16
A.




UCF
What is the impact
of the NOT NULL?
In a few weeks, in the lab when we INSERT data into
a table,
we’ll see that if a field is marked as NOT NULL then
data must be inserted into that field or an error
message will result.
That is, there is a constraint on that field in the table
Any row of data in that table, must have data in any
NOT NULL field.
ISM 4212 Lab
17
CREATE TABLE
with an IDENTITY
B.

What does the IDENTITY do?





UCF
Automatically increments on insert
Only one per table
By itself, not guaranteed unique
You can turn it on and off
Now, see the syntax…
ISM 4212 Lab
18
B.
CREATE TABLE
with an IDENTITY
CREATE TABLE Products3
(
ProductID
INT
IDENTITY
Name
CHAR(20)
Price
MONEY
Description CHAR(100)
)
After executed, you can view
the table and check
the result at the bottom
under “Column Properties”:
UCF
ISM 4212 Lab
NOT NULL,
NOT NULL,
NOT NULL,
NULL
19
B.



UCF
What is the impact
of the IDENTITY?
In a few weeks, in the lab when we INSERT data into
a table,
we’ll see that if a field has an IDENTITY then the
“seed” automatically increments and is inserted into
that field of the row when the row is inserted into the
table.
This is perfect for some Primary Keys
ISM 4212 Lab
20
B.


One little trick to know
about the IDENTITY
You can specify how the identity is going to behave:
IDENTITY (<seed>, <increment>)
CREATE TABLE Products4
(
ProductID
INT IDENTITY(10,5)
Name
CHAR(20)
Price
MONEY
Description CHAR(100)
)
UCF
ISM 4212 Lab
NOT NULL,
NOT NULL,
NOT NULL,
NULL
21
(con’t)
B.
One little trick to know
about the IDENTITY
When viewing the table,
in the “Column Properties” section,
expand the “Identity Specification”
The details
of the Identity
UCF
ISM 4212 Lab
22
C.
CREATE TABLE
with a PRIMARY KEY
CREATE TABLE Categories
(
CategoryID
int
NOT NULL,
CategoryName
nvarchar(15)
NOT NULL,
Description
ntext
NULL,
CONSTRAINT PK_Categories PRIMARY KEY (CategoryID)
)
the name
of this
constraint
the type
of constraint
UCF
the field
that is the PK
ISM 4212 Lab
23
C.
CREATE TABLE
with a PRIMARY KEY
After executed, you can view
the table and see a check
for the PK icon:
UCF
ISM 4212 Lab
24
C.


UCF
What is the impact
of a PRIMARY KEY?
For starters, it is a constraint
“Enforces uniqueness for the purpose of identifying a
row.”
-Turley, page 331
ISM 4212 Lab
25
D.

Default values
When a new row is inserted into this table, if the
“State” field is not specified in the insert statement,
then the system automatically add the default field of
‘FL’.
CREATE TABLE Locations
(
LocationID
INT IDENTITY NOT NULL,
LocationName
CHAR(20)
NOT NULL,
State
CHAR(2)
NOT NULL DEFAULT 'FL'
)
UCF
ISM 4212 Lab
26
D.
Evidence of the Default values
Select the field
then look below
UCF
ISM 4212 Lab
27
Diagram
UCF
ISM 4212 Lab
28
Create a diagram






UCF
Using SQL Server Manager Studio,
Open the your database in the Object Explorer
Expand the database to see the “Database Diagrams”
folder
Right click on “Database Diagrams and select “New
Database Diagram”
Add Tables
To save, right click on the tab of the pane and enter a
name in the “Choose Name” pop-up
ISM 4212 Lab
29
Miscellaneous Diagram Operations



UCF
On menu, select “Database Diagram” and then
“Arrange Tables”
To zoom, right click on the background and select
“Zoom”
To change the appearance of a table, right click on a
table and select “Table View” and then a new
selection
ISM 4212 Lab
30
From a diagram,
you can alter your tables



UCF
In the diagram, right click on the table
Choose a selection, for example, “Insert Column”
When you are all done, make sure you save the
diagram by right-clicking on the tab and selecting
“Save…”
ISM 4212 Lab
31
Help with Multi-page diagrams



UCF
With the diagram open…
Right click on the background
and select “View Page Breaks”
ISM 4212 Lab
32
Save Your Diagram



UCF
Right-click on the tab, and select save
Give your diagram a name
The diagram is then available in the Object Explorer
under “Database Diagrams”
ISM 4212 Lab
33
Create a Relationship
Between Two Tables
UCF
ISM 4212 Lab
34
Inspect
a One-to-Many Relationship
First, lets look at an example
 In Northwind…
 Focus on just the Categories table and the Products table
 Notice that the Categories table has a CategoryID and that the
Products table has a CategoryID
 There exists a relationship between these two tables
 Specifically, Products.CategoryID is a Foreign Key that
references the Categories.CategoryID which is a Primary Key
(con’t on next slide)
UCF
ISM 4212 Lab
35
(con’t)
Inspect
a One-to-Many Relationship
(con’t from previous slide)





To find evidence of this relationship…
Open Northwind’s “Products” table
Right-click on the background and select “Relationships”
In the “Foreign Key Relationships” pop-up window, find the
“Selected Relationships” area that lists the existing
relationships
Highlight the “FK_Products_Categories” and then expand the
part of the window where it says “Tables and Columns
Specification” … this will show the tables and attributes.
(con’t on next slide)
UCF
ISM 4212 Lab
36
(con’t)
Inspect
a One-to-Many Relationship
Lists the details
of the relationship
UCF
ISM 4212 Lab
37
Creating a One-to-Many
Relationship (GUI)

First, create two tables that can be used in the example
CREATE TABLE MyCategories
(
CategoryID int
IDENTITY(1,1) NOT NULL,
CategoryName
nvarchar(15)
NOT NULL,
Description
ntext
NULL,
CONSTRAINT PK_MyCategories PRIMARY KEY (CategoryID)
)
CREATE TABLE MyProducts
(
ProductID
INT,
ProductName
CHAR(20),
Price
MONEY,
CategoryID
INT NOT NULL
)
UCF
ISM 4212 Lab
(con’t)
38
(con’t)
Creating a One-to-Many
Relationship (GUI)
Now create the relationship, where the MyProducts.CategoryID is
the FK and the MyCategories.CategoryID is the PK
 From the Object Explorer, right click and “modify” the
MyProducts table
 Right click on background and select “Relationships”
 Click “Add”
 Under “Identity” where it says (name), enter the name of the
relationships (e.g. FK_MyProducts_MyCategories)
 Under “Tables and Columns Specification” click the button that
has the ellipsis (“…”)
 Select a table and field for the Primary Key and the Foreign Key
 “OK” to save and “Close” to exit “Foreign Key Relationship”
pop-up
UCF
ISM 4212 Lab
39
Creating a One-to-Many
Relationship (SQL)
 First, create two tables that can be used in the example
CREATE TABLE MyCategories2
(
CategoryID int
IDENTITY(1,1) NOT NULL,
CategoryName nvarchar(15)
NOT NULL,
Description ntext
NULL,
CONSTRAINT PK_MyCategories2 PRIMARY KEY (CategoryID)
)
CREATE TABLE MyProducts2
(
ProductID
INT,
ProductName
CHAR(20),
Price
MONEY,
CategoryID
INT NOT NULL
FOREIGN KEY REFERENCES MyCategories2 (CategoryID)
)
UCF
ISM 4212 Lab
40
Bonus
UCF
ISM 4212 Lab
41
Ctrl+K, Ctrl+C to Comment SQL
Ctrl+K, Ctrl+U to Uncomment SQL
UCF
ISM 4212 Lab
42
Another way
to see your table’s metadata
EXEC sp_help YourTableName
Note: the “sp_help” is a stored procedure that comes
with the installation. We’ll learn more about
stored procedures in a few weeks.
UCF
ISM 4212 Lab
43
Next Week
UCF
ISM 4212 Lab
44
Next Topic
1.
Introduction to SQL SELECT Statements
including the following important SQL keywords:



UCF
WHERE
ORDER BY
AS
ISM 4212 Lab
45
To Do List
1.
2.
3.
UCF
Practice
Read Ch. 3 (pp. 41-52): Statements
Read Ch. 4 (pp. 79-89): JOINs
ISM 4212 Lab
46