uLGNwbk2

1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[authors]') AND type in (N'U'))
2DROP TABLE [dbo].[authors]
3GO
4
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE TABLE [dbo].[authors](
12au_id VARCHAR(11) CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]') CONSTRAINT AuKey PRIMARY KEY CLUSTERED,
13au_lname varchar(40) NOT NULL,
14au_fname varchar(20) NOT NULL,
15phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
16address varchar(40) NULL,
17city varchar(20) NULL,
18state char(2) NULL,
19zip char(5) NULL CHECK (zip like '[0-9][0-9][0-9][0-9][0-9]'),
20contract bit NOT NULL
21)
22GO
23
24IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[titles]') AND type in (N'U'))
25DROP TABLE [dbo].[titles]
26GO
27
28CREATE TABLE [dbo].[titles](
29title_id VARCHAR(6) CONSTRAINT TiKey PRIMARY KEY CLUSTERED,
30title varchar(80) NOT NULL,
31type char(12) NOT NULL DEFAULT ('UNDECIDED'),
32pub_id char(4) NULL,
33price money NULL,
34advance money NULL,
35royalty int NULL,
36ytd_sales int NULL,
37notes varchar(200) NULL,
38pubdate datetime NOT NULL DEFAULT (getdate())
39)
40GO
41
42IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[titleauthor]') AND type in (N'U'))
43DROP TABLE [dbo].[titleauthor]
44GO
45
46CREATE TABLE [dbo].[titleauthor](
47au_id VARCHAR(11) REFERENCES authors(au_id),
48title_id VARCHAR(6) REFERENCES titles(title_id),
49au_ord tinyint NULL,
50royaltyper int NULL,
51CONSTRAINT TiAuKey PRIMARY KEY CLUSTERED(au_id, title_id)
52)
53GO
54
55IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sales]') AND type in (N'U'))
56DROP TABLE [dbo].[sales]
57GO
58
59CREATE TABLE [dbo].[sales](
60[stor_id] [char] (4) NOT NULL ,
61[ord_num] [varchar] (20) NOT NULL ,
62[ord_date] [datetime] NOT NULL ,
63[qty] [smallint] NOT NULL ,
64[payterms] [varchar] (12) NOT NULL ,
65[title_id] [varchar](6) NOT NULL,
66CONSTRAINT SaKey PRIMARY KEY CLUSTERED(stor_id, title_id, ord_num)
67)
68GO
69
70ALTER TABLE [dbo].[sales] ADD FOREIGN KEY ([title_id]) REFERENCES [dbo].[titles] ([title_id])
71GO
72