SQLServerCh12

Chapter 12

How to use
the Management Studio
for database design

Murach's SQL Server 2012, C12

© 2012, 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 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 2

The dialog box for creating a new database

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 3

The default directory
for SQL Server 2012 databases
C:\Program Files\Microsoft SQL Server\
MSSQL11.SQLEXPRESS\MSSQL\DATA

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 4

The design of the Invoices table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 5

The foreign key relationships
for the Invoices table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 6

The tables and columns for the foreign key

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 7

The indexes and keys for the Vendors table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 8

The check constraints for the Invoices table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 9

The object dependencies for the Vendors table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 10

A generated script that creates a 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 [PK_Invoices] PRIMARY KEY CLUSTERED ([InvoiceID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 11

A generated script that creates a table (continued)
ALTER TABLE [dbo].[Invoices] ADD CONSTRAINT
[DF_Invoices_InvoiceTotal]
DEFAULT ((0)) FOR [InvoiceTotal]
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
[CK_InvoiceTotal]
CHECK (([InvoiceTotal]>(0)))
GO

CONSTRAINT

ALTER TABLE [dbo].[Invoices]
CHECK CONSTRAINT [CK_InvoiceTotal]
GO

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 12

A generated change script for the Invoices table

Murach's SQL Server 2012, C12

© 2012, Mike Murach & Associates, Inc.

Slide 13