$/********************************************************$

$*$ This script creates the database named my$\_$guitar$\_$shop

$*********************************************************/$

DROP DATABASE IF EXISTS my$\_$guitar$\_$shop;

CREATE DATABASE my$\_$guitar$\_$shop;

USE my$\_$guitar$\_$shop;

$/*$ create the tables for the database $*/$

CREATE TABLE categories $($

category$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

category$\_$name VARCHAR$(255)$ NOT NULL UNIQUE

$)$;

CREATE TABLE products $($

product$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

category$\_$id INT NOT NULL,

product$\_$code VARCHAR$(10)$ NOT NULL UNIQUE,

product$\_$name VARCHAR$(255)$ NOT NULL,

description TEXT NOT NULL,

list$\_$price DECIMAL$(10,2)$ NOT NULL,

discount$\_$percent DECIMAL$(10,2)$ NOT NULL DEFAULT $0\mathrm{.}00,$

date$\_$added DATETIME DEFAULT NULL,

CONSTRAINT products$\_$fk$\_$categories

FOREIGN KEY $($category$\_$id$)$

REFERENCES categories $($category$\_$id$)$

$)$;

CREATE TABLE customers $($

customer$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

email$\_$address VARCHAR$(255)$ NOT NULL UNIQUE,

password VARCHAR$(60)$ NOT NULL,

first$\_$name VARCHAR$(60)$ NOT NULL,

last$\_$name VARCHAR$(60)$ NOT NULL,

shipping$\_$address$\_$id INT DEFAULT NULL,

billing$\_$address$\_$id INT DEFAULT NULL

$)$;

CREATE TABLE addresses $($

address$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

customer$\_$id INT NOT NULL,

line$1$ VARCHAR$(60)$ NOT NULL,

line$2$ VARCHAR$(60)$ DEFAULT NULL,

city VARCHAR$(40)$ NOT NULL,

state VARCHAR$(2)$ NOT NULL,

zip$\_$code VARCHAR$(10)$ NOT NULL,

phone VARCHAR$(12)$ NOT NULL,

disabled TINYINT$(1)$ NOT NULL DEFAULT $0,$

CONSTRAINT addresses$\_$fk$\_$customers

FOREIGN KEY $($customer$\_$id$)$

REFERENCES customers $($customer$\_$id$)$

$)$;

CREATE TABLE orders $($

order$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

customer$\_$id INT NOT NULL,

order$\_$date DATETIME NOT NULL,

ship$\_$amount DECIMAL$(10,2)$ NOT NULL,

tax$\_$amount DECIMAL$(10,2)$ NOT NULL,

ship$\_$date DATETIME DEFAULT NULL,

ship$\_$address$\_$id INT NOT NULL,

card$\_$type VARCHAR$(50)$ NOT NULL,

card$\_$number CHAR$(16)$ NOT NULL,

card$\_$expires CHAR$(7)$ NOT NULL,

billing$\_$address$\_$id INT NOT NULL,

CONSTRAINT orders$\_$fk$\_$customers

FOREIGN KEY $($customer$\_$id$)$

REFERENCES customers $($customer$\_$id$)$

$)$;

CREATE TABLE order$\_$items $($

item$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

order$\_$id INT NOT NULL,

product$\_$id INT NOT NULL,

item$\_$price DECIMAL$(10,2)$ NOT NULL,

discount$\_$amount DECIMAL$(10,2)$ NOT NULL,

quantity INT NOT NULL,

CONSTRAINT items$\_$fk$\_$orders

FOREIGN KEY $($order$\_$id$)$

REFERENCES orders $($order$\_$id$),$

CONSTRAINT items$\_$fk$\_$products

FOREIGN KEY $($product$\_$id$)$

REFERENCES products $($product$\_$id$)$

$)$;

CREATE TABLE administrators $($

admin$\_$id INT PRIMARY KEY AUTO$\_$INCREMENT,

email$\_$address VARCHAR$(255)$ NOT NULL,

password VARCHAR$(255)$ NOT NULL,

first$\_$name VARCHAR$(255)$ NOT NULL,

last$\_$name VARCHAR$(255)$ NOT NULL

$)$;

$/*$ Insert data into the tables $*/$

INSERT INTO categories $($category$\_$id$,$ category$\_$name$)$ VALUES

$(1,$ 'Guitars'$),$

$(2,$ 'Basses'$),$

$(3,$ 'Drums'$),$

$(4,$ 'Keyboards'$)$;

INSERT INTO products $($product$\_$id$,$ category$\_$id$,$ product$\_$code, product$\_$name, description, list$\_$price, discount$\_$percent, date$\_$added$)$ VALUES

$(1,1,$ 'strat', 'Fender Stratocaster', 'The Fender Stratocaster is the electric guitar design that changed the world. New features include a tinted neck, parchment pickguard and control knobs, and a $\text{'}\text{'}70$s$-$style logo$.$ Includes select alder body, $21-$fret maple neck with your choice of a rosewood or maple fretboard, $3$ single$-$coil pickups, vintage$-$style tremolo, and die$-$cast tuning keys. This guitar features a thicker bridge block for increased sustain and a more stable point of contact with the strings. At this low price, why play anything but the real thing?$\backslash$r

$\backslash$r

Features:$\backslash$r

$\backslash$r

$*$ New features:$\backslash$r

$*$ Thicker bridge block$\backslash$r

$*3-$ply parchment pick guard$\backslash$r

$*$ Tinted neck', $\text{'}699\mathrm{.}00\text{'},\text{'}30\mathrm{.}00\text{'},\text{'}2014-10-3009$:$32$:$40\text{'}),$

$(2,1,$ 'les$\_$paul', 'Gibson Les Paul', 'This Les Paul guitar offers a carved top and humbucking pickups. It has a simple yet elegant design. Cutting$-$yet$-$rich tone?the hallmark of the Les Paul?pours out of the $490$R and $498$T Alnico II magnet humbucker pickups, which are mounted on a carved maple top with a mahogany back. The faded finish models are equipped with BurstBucker Pro pickups and a mahogany top. This guitar includes a Gibson hardshe