USE [booksdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[author](
	[authorid] [int] IDENTITY(1,1) NOT NULL,
	[lastname] [varchar](100) NOT NULL,
	[firstname] [varchar](100) NOT NULL,
 CONSTRAINT [PK_author] PRIMARY KEY CLUSTERED 
(
	[authorid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[category](
	[categoryid] [int] IDENTITY(1,1) NOT NULL,
	[categorydesc] [varchar](100) NOT NULL,
 CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED 
(
	[categoryid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[subject](
	[subjectid] [int] IDENTITY(1,1) NOT NULL,
	[categoryid] [int]  NOT NULL,
	[subjectdesc] [varchar](50)  NOT NULL,
	[keywords] [varchar](200)  NULL,
 CONSTRAINT [PK_subject] PRIMARY KEY CLUSTERED 
(
	[subjectid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [FK_subject1] FOREIGN KEY([categoryid])
 REFERENCES [dbo].[category] ([categoryid])
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[book](
	[bookid] [int] IDENTITY(1,1) NOT NULL,
	[authorid] [int]  NOT NULL,
	[subjectid] [int]  NOT NULL,
	[title] [varchar](200)  NOT NULL,
        [price] [money]  NOT NULL,
        [pubdate] [char](4) NOT NULL,
 CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED 
(
	[bookid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [FK_book1] FOREIGN KEY([authorid])
 REFERENCES [dbo].[author] ([authorid]),
 CONSTRAINT [FK_book2] FOREIGN KEY([subjectid])
 REFERENCES [dbo].[subject] ([subjectid])
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[customer](
	[customerid] [int] IDENTITY(1,1) NOT NULL,
	[lastname] [varchar](50)  NOT NULL,
	[firstname] [varchar](50)  NOT NULL,
	[address] [varchar](100)  NOT NULL,
	[phone] [varchar](30)  NOT NULL,
 CONSTRAINT [PK_custorder] PRIMARY KEY CLUSTERED 
(
	[customerid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[custorder](
	[orderid] [int] IDENTITY(1,1) NOT NULL,
	[customerid] [int] NOT NULL,
	[orderdate] [datetime]  NOT NULL,
 CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED 
(
	[orderid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [FK_custorder1] FOREIGN KEY([customerid])
 REFERENCES [dbo].[customer] ([customerid])
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[orderdetails](
	[orderid] [int] NOT NULL,
	[bookid] [int]  NOT NULL,
	[quantity] [smallint]  NOT NULL,
	[unitprice] [money] NOT NULL,
        [discount] [smallint],
 CONSTRAINT [FK_orderdetails1] FOREIGN KEY([orderid])
 REFERENCES [dbo].[custorder] ([orderid]),
 CONSTRAINT [FK_orderdetails2] FOREIGN KEY([bookid])
 REFERENCES [dbo].[book] ([bookid])
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[subject] CHECK CONSTRAINT [FK_subject1]
GO
ALTER TABLE [dbo].[book] CHECK CONSTRAINT [FK_book1]
GO
ALTER TABLE [dbo].[book] CHECK CONSTRAINT [FK_book2]
GO
ALTER TABLE [dbo].[custorder] CHECK CONSTRAINT [FK_custorder1]
GO
ALTER TABLE [dbo].[orderdetails] CHECK CONSTRAINT [FK_orderdetails1]
GO
ALTER TABLE [dbo].[orderdetails] CHECK CONSTRAINT [FK_orderdetails2]
GO
CREATE NONCLUSTERED INDEX [IX_author] ON [dbo].[author] 
(
	[lastname] ASC,
	[firstname] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_book] ON [dbo].[book] 
(
	[title] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO