Transcript No Slide Title

Chapter 11
How to use the
Management Studio
for database design
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
 Given a complete database design, use the Management Studio to
create the database, including all tables, relationships, constraints,
and indexes.
Knowledge
 Describe the use of table dependencies.
 Describe the use of scripts when you’re using the Management
Studio.
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 2
The New Database dialog box
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 3
The default directory for SQL Server 2008 databases
C:\Program Files\Microsoft SQL Server\
MSSQL10.SQLEXPRESS\MSSQL\DATA
How to create a new database
 To create a new database, (1) right-click on the Databases folder in the
Management Studio, (2) select the New Database command to display
the New Database dialog box, (3) enter a name for the database, and (4)
click OK to create the database and its files.
How to delete a database
 To delete a database, (1) expand the Databases folder, (2) right-click on
the database, (3) select the Delete command, and (4) click OK to delete
the database and its files.
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 4
The Table Designer for the Invoices table
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 5
How to create or modify the design of a table
 To create a new table, right-click on the Tables folder and select
the New Table command to display a new table in the Table
Designer. Then, when you click on the Save button in the toolbar,
you can supply a name for the table.
 To edit the design of an existing table, expand the Tables folder,
right-click on the table, and select the Modify command to display
the table in the Table Designer.
 To set the basic properties for each column, use the grid at the top
of the Table Designer to specify the column name, data type, and
whether or not the column allows nulls.
 To set other column properties, such as the identity column or a
default value, use the Column Properties pane.
 To set the primary key, select the column or columns and click the
Set Primary Key button in the Table Designer toolbar.
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 6
How to delete a table
 To delete a table, expand the Tables folder, right-click on the
table, and select the Delete command to display the Delete Object
dialog box. Then, click OK to delete the table.
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 7
The Foreign Key Relationships dialog box for the
Invoices table
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 8
The Tables and Columns dialog box
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 9
The Indexes/Keys dialog box
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 10
The Check Constraints dialog box
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 11
The Object Dependencies dialog box for the
Vendors table
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 12
A generated script that creates an Invoices table
USE [New_AP]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoices]
(
[InvoiceID] [int] IDENTITY(1,1) NOT NULL,
[VendorID] [int] NOT NULL,
[InvoiceDate] [smalldatetime] NULL,
[InvoiceTotal] [money] NULL CONSTRAINT
[DF_Invoices_InvoiceTotal] DEFAULT ((0)),
CONSTRAINT [PK_Invoices_1] PRIMARY KEY CLUSTERED
([InvoiceID] ASC)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
ON [PRIMARY]
)
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 13
A generated script that creates an Invoices table
(continued)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[Invoices]
WITH CHECK ADD CONSTRAINT [FK_Invoices_Vendors]
FOREIGN KEY([VendorID])
REFERENCES [dbo].[Vendors] ([VendorID])
GO
ALTER TABLE [dbo].[Invoices]
CHECK CONSTRAINT [FK_Invoices_Vendors]
GO
ALTER TABLE [dbo].[Invoices]
WITH CHECK ADD CONSTRAINT [CK_InvoiceTotal]
CHECK (([InvoiceTotal]>(0)))
GO
ALTER TABLE [dbo].[Invoices]
CHECK CONSTRAINT [CK_InvoiceTotal]
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 14
The Save Change Script dialog box
Murach’s SQL Server 2008, C11
© 2008, Mike Murach & Associates, Inc.
Slide 15