CAD/MCSE/MCDBA Self-Paced Training Kit [Electronic resources]: Microsoft SQL Server 2000 Database Design and Implementation, Second Edition (Exam 70-229) نسخه متنی

It is sometimes difficult to predict exactly what length you should use for data types such as char and varchar. You can get a feel for lengths, however, by reviewing column properties in existing databases, such as the Pubs database or the Northwind database.

Notice that the State column in the Customers table uses the varchar(7) data type rather than char(2), as in the Employees table. Because a value is not required for this column in the Customers table, a default value of 'unknown' will be defined (rather than permitting a null value). Nullability and default values are discussed in more detail in Lesson 3.

USE bookshopdb
CREATE TABLE Books
(
TitleID CHAR(8) NOT NULL,
Title VARCHAR(70) NOT NULL,
Publisher VARCHAR(50) NOT NULL DEFAULT 'N/A',
PubDate CHAR(4) NOT NULL DEFAULT 'N/A',
Edition CHAR(4) NOT NULL DEFAULT 'N/A',
Cost MONEY NOT NULL,
SRP MONEY NOT NULL,
ConditionID TINYINT NOT NULL,
Sold BIT NOT NULL DEFAULT '0',
)
CREATE TABLE BookOrders
(
OrderID SMALLINT NOT NULL,
TitleID CHAR(8) NOT NULL 
)
CREATE TABLE Positions
(
PositionID TINYINT NOT NULL,
Title VARCHAR(30) NOT NULL,
JobDescrip VARCHAR(80) NOT NULL DEFAULT 'N/A'
)
CREATE TABLE Employees
(
EmployeeID SMALLINT IDENTITY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Addrees1 VARCHAR(60) NOT NULL,
Address2 VARCHAR(60) NOT NULL DEFAULT 'N/A',
City VARCHAR(15) NOT NULL,
State CHAR(2) NOT NULL,
Zip VARCHAR(12) NOT NULL,
Phone VARCHAR(24) NOT NULL,
DOB DATETIME NOT NULL,
HireDate DATETIME NOT NULL,
PositionID TINYINT NOT NULL
)
CREATE TABLE Customers
(
CustomerID SMALLINT IDENTITY(10,1) NOT NULL,
FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',
LastName VARCHAR(30) NOT NULL DEFAULT 'unknown',
Phone VARCHAR(24) NOT NULL DEFAULT 'unknown',
Address1 VARCHAR(60) NOT NULL DEFAULT 'unknown',
Address2 VARCHAR(60) NOT NULL DEFAULT 'unknown',
City VARCHAR(15) NOT NULL DEFAULT 'unknown',
State VARCHAR(7) NOT NULL DEFAULT 'unknown',
Zip VARCHAR(12) NOT NULL DEFAULT 'unknown'
)
CREATE TABLE Orders
(
OrderID SMALLINT IDENTITY NOT NULL,
CustomerID SMALLINT NOT NULL,
EmployeeID SMALLINT NOT NULL,
Amount MONEY NOT NULL DEFAULT 0,
OrderDate DATETIME NOT NULL,
DeliveryDate DATETIME NULL, 
PaymentID TINYINT NOT NULL,
StatusID TINYINT NOT NULL
)
CREATE TABLE OrderStatus
(
StatusID TINYINT NOT NULL,
StatusDescrip VARCHAR(25) NOT NULL 
)
CREATE TABLE FormOfPayment
(
PaymentID TINYINT NOT NULL,
PaymentDescrip VARCHAR(12) NOT NULL
)