-- Copyright (C) 2001 YesSoftware. All rights reserved.
-- BugTrack_MySQL.sql

DROP TABLE IF EXISTS card_types;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS editorial_categories;
DROP TABLE IF EXISTS editorials;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS orders;

CREATE TABLE card_types (
       card_type_id         int auto_increment primary key,
       name                 varchar(50) NOT NULL
);

insert into card_types (name) values ('Visa');
insert into card_types (name) values ('American Express');

CREATE TABLE categories (
       category_id          int  auto_increment primary key,
       name                 varchar(50) NOT NULL
);

insert into categories (name) values ('Programming');
insert into categories (name) values ('Databases');
insert into categories (name) values ('HTML & Web design');

CREATE TABLE editorial_categories (
       editorial_cat_id     int  auto_increment primary key,
       editorial_cat_name   varchar(50) NULL
);

insert into editorial_categories (editorial_cat_name) values ('What');
insert into editorial_categories (editorial_cat_name) values ('New');
insert into editorial_categories (editorial_cat_name) values ('Weekly');
insert into editorial_categories (editorial_cat_name) values ('General');

CREATE TABLE editorials (
       article_id           int auto_increment primary key,
       editorial_cat_id     int NULL DEFAULT 0,
       article_title        varchar(200) NULL,
       article_desc         text NULL,
       item_id              int NULL DEFAULT 0
);

insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
       values(1, 'A Sharp Combination', 'To get inside C#, Microsoft''s new OO programming language, use A Preview of C# as a guide. It offers a preview of Visual Studio.NET and an overview of the .NET framework, and demonstrates how C# is integrated with ASP+, ADO+, and COM+ in .NET applications. You''ll get examples of C# in action, too.', 22);
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
       values(2, '1001 Web Site Construction Tips and Tricks', '39.95', 21);
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
       values(3, 'Flash 4 Magic', 'If you''re right in the middle of learning (or just jumping into) Flash to create and manipulate animations, music tracks, sound effects, and interface design, try the Flash 4 Magic. Inside are tutorials, graphic presentations, and a CD.', 8);
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
       values(4, '<b><font color="brown">Free Shipping on orders over $40</font></b>', 'For limited time only, until next Sunday, you can enjoy free shipping. Simply order more then $40 worth of books and shipping''s on us.', 0);

CREATE TABLE items (
       item_id              int auto_increment primary key,
       category_id          int NOT NULL,
       name                 varchar(255) NOT NULL,
       author               varchar(100) NULL,
       price                float NOT NULL,
       product_url          varchar(255) NULL,
       image_url            varchar(100) NULL,
       notes                text NULL,
       is_recommended       tinyint NULL DEFAULT 0,
       rating               int default 0,
       rating_count         int default 0
);

insert into items (category_id, name, author, price, product_url, image_url, notes, is_recommended)
       values(2, 'Web Database Development : Step by Step', 'Jim Buyens', 39.99, 'http://www.amazon.com/exec/obidos/ASIN/0735609667/yessoftware', 'images/books/0735609667.jpg', 'As Web sites continue to grow in complexity and in the volume of data they must present, databases increasingly drive their content. WEB DATABASE DEVELOPMENT FUNDAMENTALS is ideal for the beginning-to-intermediate Web developer, departmental power user, or entrepreneur who wants to step up to a database-driven Web site-without buying several in-depth guides to the different technologies involved. This book uses the clear Microsoft(r) Step by Step tutorial method to familiarize developers with the technologies for building smart Web sites that present data more easily. ', 1);