instpubs.sql

,/ k lkl l jlkjk kl lk

/*                                                                        */
/*              InstPubs.SQL - Creates the Pubs database                  */ 
/*                                                                        */
/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

SET NOCOUNT ON
GO

set nocount    on
set dateformat mdy

USE master

declare @dttm varchar(55)
select  @dttm=convert(varchar,getdate(),113)
raiserror('Beginning InstPubs.SQL at %s ....',1,1,@dttm) with nowait

GO

if exists (select * from sysdatabases where name='pubs')
begin
  raiserror('Dropping existing pubs database ....',0,1)
  DROP database pubs
end
GO

CHECKPOINT
go

raiserror('Creating pubs database....',0,1)
go
/*
   Use default size with autogrow
*/

CREATE DATABASE pubs
GO

CHECKPOINT

GO

USE pubs

GO

if db_name() <> 'pubs'
   raiserror('Error in InstPubs.SQL, ''USE pubs'' failed!  Killing the SPID now.'
            ,22,127) with log

GO

execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true'
GO

execute sp_addtype id      ,'varchar(11)' ,'NOT NULL'
execute sp_addtype tid     ,'varchar(6)'  ,'NOT NULL'
execute sp_addtype empid   ,'char(9)'     ,'NOT NULL'

raiserror('Now at the create table section ....',0,1)

GO

CREATE TABLE authors
(
   au_id          id

         CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')

         CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED,

   au_lname       varchar(40)       NOT NULL,
   au_fname       varchar(20)       NOT NULL,

   phone          char(12)          NOT NULL

         DEFAULT ('UNKNOWN'),

   address        varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,

   zip            char(5)               NULL

         CHECK (zip like '[0-9][0-9][0-9][0-9][0-9]'),

   contract       bit               NOT NULL
)

GO

CREATE TABLE publishers
(
   pub_id         char(4)           NOT NULL

         CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED

         CHECK (pub_id in ('1389', '0736', '0877', '1622', '1756')
            OR pub_id like '99[0-9][0-9]'),

   pub_name       varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,

   country        varchar(30)           NULL

         DEFAULT('USA')
)

GO

CREATE TABLE titles
(
   title_id       tid

         CONSTRAINT UPKCL_titleidind PRIMARY KEY CLUSTERED,

   title          varchar(80)       NOT NULL,

   type           char(12)          NOT NULL

         DEFAULT ('UNDECIDED'),

   pub_id         char(4)               NULL

         REFERENCES publishers(pub_id),

   price          money                 NULL,
   advance        money                 NULL,
   royalty        int                   NULL,
   ytd_sales      int                   NULL,
   notes          varchar(200)          NULL,

   pubdate        datetime          NOT NULL

         DEFAULT (getdate())
)

GO

CREATE TABLE titleauthor
(
   au_id          id

         REFERENCES authors(au_id),

   title_id       tid

         REFERENCES titles(title_id),

   au_ord         tinyint               NULL,
   royaltyper     int                   NULL,


   CONSTRAINT UPKCL_taind PRIMARY KEY CLUSTERED(au_id, title_id)
)

GO

CREATE TABLE stores
(
   stor_id        char(4)           NOT NULL

         CONSTRAINT UPK_storeid PRIMARY KEY CLUSTERED,

   stor_name      varchar(40)           NULL,
   stor_address   varchar(40)           NULL,
   city           varchar(20)           NULL,
   state          char(2)               NULL,
   zip            char(5)               NULL
)

GO

CREATE TABLE sales
(
   stor_id        char(4)           NOT NULL

         REFERENCES stores(stor_id),

   ord_num        varchar(20)       NOT NULL,
   ord_date       datetime          NOT NULL,
   qty            smallint          NOT NULL,
   payterms       varchar(12)       NOT NULL,

   title_id       tid

         REFERENCES titles(title_id),


   CONSTRAINT UPKCL_sales PRIMARY KEY CLUSTERED (stor_id, ord_num, title_id)
)

GO

CREATE TABLE roysched
(
   title_id       tid

         REFERENCES titles(title_id),

   lorange        int                   NULL,
   hirange        int                   NULL,
   royalty        int                   NULL
)

GO

CREATE TABLE discounts
(
   discounttype   varchar(40)       NOT NULL,

   stor_id        char(4) NULL

         REFERENCES stores(stor_id),

   lowqty         smallint              NULL,
   highqty        smallint              NULL,
   discount       dec(4,2)          NOT NULL
)

GO

CREATE TABLE jobs
(
   job_id         smallint          IDENTITY(1,1)

         PRIMARY KEY CLUSTERED,

   job_desc       varchar(50)       NOT NULL

         DEFAULT 'New Position - title not formalized yet',

   min_lvl        tinyint           NOT NULL

         CHECK (min_lvl >= 10),

   max_lvl        tinyint           NOT NULL