USE [mybookstore]

/****** Object:  Table [dbo].[authors] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[authors]') AND type in (N'U'))
DROP TABLE [dbo].[authors]
GO
/****** Object:  Table [dbo].[publishers] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[publishers]') AND type in (N'U'))
DROP TABLE [dbo].[publishers]
GO
/****** Object:  Table [dbo].[titles] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[titles]') AND type in (N'U'))
DROP TABLE [dbo].[titles]
GO
/****** Object:  Table [dbo].[users] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
DROP TABLE [dbo].[users]
GO
/****** Object:  Table [dbo].[users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[users](
	[user_id] [int] IDENTITY(1,1) NOT NULL,
	[user_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[user_password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[user_roles] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_users] PRIMARY KEY NONCLUSTERED 
(
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[users] ON
INSERT [dbo].[users] ([user_id], [user_name], [user_password], [user_roles]) VALUES (1, N'admin', N'admin', N'admin')
INSERT [dbo].[users] ([user_id], [user_name], [user_password], [user_roles]) VALUES (2, N'user', N'user', N'user')
SET IDENTITY_INSERT [dbo].[users] OFF
/****** Object:  Table [dbo].[titles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[titles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[titles](
	[title_id] [int] IDENTITY(1,1) NOT NULL,
	[pub_id] [int] NULL,
	[au_id] [int] NULL,
	[title] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[price] [money] NULL,
	[pubdate] [datetime] NULL,
	[notes] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_titles] PRIMARY KEY CLUSTERED 
(
	[title_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[titles]') AND name = N'titleind')
CREATE NONCLUSTERED INDEX [titleind] ON [dbo].[titles] 
(
	[title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
SET IDENTITY_INSERT [dbo].[titles] ON
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (1, 6, 19, N'Computer Phobic AND Non-Phobic Individuals: Behavior Variations', 21.5900, CAST(0x000092F400000000 AS DateTime), N'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (2, 6, 11, N'The Gourmet Microwave', 2.9900, CAST(0x00008B0D00000000 AS DateTime), N'Traditional French gourmet recipes adapted for modern microwave cooking.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (3, 4, 21, N'Prolonged Data Deprivation: Four Case Studies', 19.9900, CAST(0x000090BC00000000 AS DateTime), N'What happens when the data runs dry?  Searching evaluations of information-shortage effects.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (4, 7, 20, N'Cooking with Computers: Surreptitious Balance Sheets', 11.9500, CAST(0x00008F4C00000000 AS DateTime), N'Helpful hints on how to use your electronic resources to the best advantage.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (5, 4, 1, N'You Can Combat Computer Stress!', 2.9900, CAST(0x000092F800000000 AS DateTime), N'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (6, 7, 22, N'The Busy Executive''s Database Guide', 19.9900, CAST(0x00008DE100000000 AS DateTime), N'An overview of available database systems with emphasis on common business applications. Illustrated.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (7, 6, 3, N'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean', 20.9500, CAST(0x00008B8A00000000 AS DateTime), N'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (8, 7, 14, N'But Is It User Friendly?', 22.9500, CAST(0x00008C8600000000 AS DateTime), N'A survey of software for the naive user, focusing on the ''friendliness'' of each.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (9, 6, 9, N'Fifty Years in Buckingham Palace Kitchens', 11.9500, CAST(0x00008C7400000000 AS DateTime), N'More anecdotes from the Queen''s favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (10, 6, 4, N'Silicon Valley Gastronomic Treats', 19.9900, CAST(0x000092FC00000000 AS DateTime), N'Favorite recipes for quick, easy, and elegant meals.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (11, 6, 15, N'Sushi, Anyone?', 14.9900, CAST(0x00008DE100000000 AS DateTime), N'Detailed instructions on how to make authentic Japanese sushi in your spare time.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (12, 7, 8, N'Secrets of Silicon Valley', 20.0000, CAST(0x000092FF00000000 AS DateTime), N'Muckraking reporting on the world''s largest computer hardware and software manufacturers.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (13, 4, 12, N'Emotional Security: A New Algorithm', 7.9900, CAST(0x00008C7400000000 AS DateTime), N'Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (14, 7, 12, N'Net Etiquette', NULL, CAST(0x00008F8600000000 AS DateTime), N'A must-read for computer conferencing.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (15, 7, 6, N'Straight Talk About Computers', 19.9900, CAST(0x0000923300000000 AS DateTime), N'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (16, 4, 18, N'Life Without Fear', 7.0000, CAST(0x0000929C00000000 AS DateTime), N'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately.')
INSERT [dbo].[titles] ([title_id], [pub_id], [au_id], [title], [price], [pubdate], [notes]) VALUES (17, 4, 18, N'Is Anger the Enemy?', 10.9500, CAST(0x0000922C00000000 AS DateTime), N'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.')
SET IDENTITY_INSERT [dbo].[titles] OFF
/****** Object:  Table [dbo].[publishers] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[publishers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[publishers](
	[pub_id] [int] IDENTITY(1,1) NOT NULL,
	[pub_name] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_publishers] PRIMARY KEY CLUSTERED 
(
	[pub_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[publishers] ON
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (1, N'GGG&G')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (2, N'Ramona Publishers')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (3, N'Five Lakes Publishing')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (4, N'New Moon Books')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (5, N'Scootney Books')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (6, N'Binnet & Hardley')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (7, N'Algodata Infosystems')
INSERT [dbo].[publishers] ([pub_id], [pub_name]) VALUES (8, N'Lucerne Publishing')
SET IDENTITY_INSERT [dbo].[publishers] OFF
/****** Object:  Table [dbo].[authors] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[authors]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[authors](
	[au_id] [int] IDENTITY(1,1) NOT NULL,
	[au_lname] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[au_fname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[phone] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_authors] PRIMARY KEY CLUSTERED 
(
	[au_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[authors]') AND name = N'aunmind')
CREATE NONCLUSTERED INDEX [aunmind] ON [dbo].[authors] 
(
	[au_lname] ASC,
	[au_fname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
SET IDENTITY_INSERT [dbo].[authors] ON
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (1, N'Green', N'Marjorie', N'415 986-7020', N'309 63rd St. #411', N'Oakland', N'CA', N'94618')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (2, N'Dull', N'Ann', N'415 836-7128', N'3410 Blonde St.', N'Palo Alto', N'CA', N'94301')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (3, N'Panteley', N'Sylvia', N'301 946-8853', N'1956 Arlington Pl.', N'Rockville', N'MD', N'20853')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (4, N'del Castillo', N'Innes', N'615 996-8275', N'2286 Cram Pl. #86', N'Ann Arbor', N'MI', N'48105')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (5, N'Smith', N'Meander', N'913 843-0462', N'10 Mississippi Dr.', N'Lawrence', N'KS', N'66044')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (6, N'Straight', N'Dean', N'415 834-2919', N'5420 College Av.', N'Oakland', N'CA', N'94609')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (7, N'O''Leary', N'Michael', N'408 286-2428', N'22 Cleveland Av. #14', N'San Jose', N'CA', N'95128')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (8, N'Hunter', N'Sheryl', N'415 836-7128', N'3410 Blonde St.', N'Palo Alto', N'CA', N'94301')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (9, N'Blotchet-Halls', N'Reginald', N'503 745-6402', N'55 Hillsdale Bl.', N'Corvallis', N'OR', N'97330')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (10, N'McBadden', N'Heather', N'707 448-4982', N'301 Putnam', N'Vacaville', N'CA', N'95688')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (11, N'Ringer', N'Anne', N'801 826-0752', N'67 Seventh Av.', N'Salt Lake City', N'UT', N'84152')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (12, N'Locksley', N'Charlene', N'415 585-4620', N'18 Broadway Av.', N'San Francisco', N'CA', N'94130')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (13, N'Stringer', N'Dirk', N'415 843-2991', N'5420 Telegraph Av.', N'Oakland', N'CA', N'94609')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (14, N'Carson', N'Cheryl', N'415 548-7723', N'589 Darwin Ln.', N'Berkeley', N'CA', N'94705')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (15, N'Yokomoto', N'Akiko', N'415 935-4228', N'3 Silver Ct.', N'Walnut Creek', N'CA', N'94595')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (16, N'Gringlesby', N'Burt', N'707 938-6445', N'PO Box 792', N'Covelo', N'CA', N'95428')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (17, N'DeFrance', N'Michel', N'219 547-9982', N'3 Balding Pl.', N'Gary', N'IN', N'46403')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (18, N'Ringer', N'Albert', N'801 826-0752', N'67 Seventh Av.', N'Salt Lake City', N'UT', N'84152')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (19, N'Karsen', N'Livia', N'415 534-9219', N'5720 McAuley St.', N'Oakland', N'CA', N'94609')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (20, N'MacFeather', N'Stearns', N'415 354-7128', N'44 Upland Hts.', N'Oakland', N'CA', N'94612')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (21, N'White', N'Johnson', N'408 496-7223', N'10932 Bigge Rd.', N'Menlo Park', N'CA', N'94025')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (22, N'Bennet', N'Abraham', N'415 658-9932', N'6223 Bateman St.', N'Berkeley', N'CA', N'94705')
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip]) VALUES (23, N'Greene', N'Morningstar', N'615 297-2723', N'22 Graybar House Rd.', N'Nashville', N'TN', N'37215')
SET IDENTITY_INSERT [dbo].[authors] OFF
GRANT DELETE ON [dbo].[authors] TO [guest] AS [dbo]
GO
GRANT INSERT ON [dbo].[authors] TO [guest] AS [dbo]
GO
GRANT REFERENCES ON [dbo].[authors] TO [guest] AS [dbo]
GO
GRANT SELECT ON [dbo].[authors] TO [guest] AS [dbo]
GO
GRANT UPDATE ON [dbo].[authors] TO [guest] AS [dbo]
GO
GRANT ALTER ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT CONTROL ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT DELETE ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT INSERT ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT REFERENCES ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT SELECT ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT TAKE OWNERSHIP ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT UPDATE ON [dbo].[publishers] TO [guest] AS [dbo]
GO
GRANT DELETE ON [dbo].[titles] TO [guest] AS [dbo]
GO
GRANT INSERT ON [dbo].[titles] TO [guest] AS [dbo]
GO
GRANT REFERENCES ON [dbo].[titles] TO [guest] AS [dbo]
GO
GRANT SELECT ON [dbo].[titles] TO [guest] AS [dbo]
GO
GRANT UPDATE ON [dbo].[titles] TO [guest] AS [dbo]
GO
GRANT DELETE ON [dbo].[users] TO [guest] AS [dbo]
GO
GRANT INSERT ON [dbo].[users] TO [guest] AS [dbo]
GO
GRANT REFERENCES ON [dbo].[users] TO [guest] AS [dbo]
GO
GRANT SELECT ON [dbo].[users] TO [guest] AS [dbo]
GO
GRANT UPDATE ON [dbo].[users] TO [guest] AS [dbo]
GO
