//////////////////////////////////////////////////////////////////Task1(a)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS my_guitar_shop1;
CREATE DATABASE my_guitar_shop1;
USE my_guitar_shop1;  -- MySQL command

-- create the tables
CREATE TABLE categories (
  categoryID artistID      INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryName artistName    VARCHAR(255)   NOT NULL,
  PRIMARY KEY (categoryID)
);

CREATE TABLE products (
  productID    albumID    INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryID     artistID  INT(11)        NOT NULL,
  productCode   albumCode   VARCHAR(10)    NOT NULL   UNIQUE,
  productName    albumName  VARCHAR(255)   NOT NULL,
  listPrice      listPrice  DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (productID)
);

CREATE TABLE orders (
  orderID     trackID   INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID    customerID INT            NOT NULL,
  orderDate     trackDate    DATETIME       NOT NULL,
  PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'HipHop'),
(2, 'House'),
(3, 'Rave');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON products
TO mgs_tester@localhost;


//////////////////////////////////////////////////////////////////Task1(b)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS task;
CREATE DATABASE task;
USE task;  -- MySQL command

-- create the tables
CREATE TABLE modules (
  modulesID       INT(11)        NOT NULL   AUTO_INCREMENT,
  modulesName     VARCHAR(255)   NOT NULL,
  PRIMARY KEY (modulesID)
);

CREATE TABLE lecturer (
  lecturerID        INT(11)        NOT NULL   AUTO_INCREMENT,
  artistID       INT(11)        NOT NULL,
  albumCode      VARCHAR(10)    NOT NULL   UNIQUE,
  albumName      VARCHAR(255)   NOT NULL,
  listPrice        VARCHAR(255)  NOT NULL,
  PRIMARY KEY (lecturerID)
);

CREATE TABLE moderator (
  moderatorID        INT(11)        NOT NULL   AUTO_INCREMENT,
  orderID     INT            NOT NULL,
  moderatorDate      DATETIME       NOT NULL,
  PRIMARY KEY (moderatorID)
);

-- insert data into the database
INSERT INTO modules VALUES
(1, 'modules'),
(2, 'lecturer'),
(3, 'moderator');

INSERT INTO lecturer VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON lecturer
TO mgs_tester@localhost;


//////////////////////////////////////////////////////////////////Task2/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS percy;
CREATE DATABASE percy;
USE percy;  -- MySQL command

-- create the tables
CREATE TABLE artist (
  artistID       INT(11)        NOT NULL   AUTO_INCREMENT,
  artistName     VARCHAR(255)   NOT NULL,
  PRIMARY KEY (artistID)
);

CREATE TABLE album (
  albumID        INT(11)        NOT NULL   AUTO_INCREMENT,
  artistID       INT(11)        NOT NULL,
  albumCode      VARCHAR(10)    NOT NULL   UNIQUE,
  albumName      VARCHAR(255)   NOT NULL,
  listPrice        DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (albumID)
);

CREATE TABLE track (
  trackID        INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID     INT            NOT NULL,
  trackDate      DATETIME       NOT NULL,
  PRIMARY KEY (trackID)
);

-- insert data into the database
INSERT INTO artist VALUES
(1, 'artist'),
(2, 'album'),
(3, 'track');

INSERT INTO album VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON album
TO mgs_tester@localhost;



//////////////////////////////////////////////////////////////////Task3(a)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS task3a;
CREATE DATABASE task3a;
USE task3a;  -- MySQL command

-- create the tables
CREATE TABLE categories (
  categoryID artistID      INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryName artistName    VARCHAR(255)   NOT NULL,
  PRIMARY KEY (categoryID)
);

CREATE TABLE products (
  productID    albumID    INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryID     artistID  INT(11)        NOT NULL,
  productCode   albumCode   VARCHAR(10)    NOT NULL   UNIQUE,
  productName    albumName  VARCHAR(255)   NOT NULL,
  listPrice      listPrice  DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (productID)
);

CREATE TABLE orders (
  orderID     trackID   INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID    customerID INT            NOT NULL,
  orderDate     trackDate    DATETIME       NOT NULL,
  PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'HipHop'),
(2, 'House'),
(3, 'Rave');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON products
TO mgs_tester@localhost;


//////////////////////////////////////////////////////////////////Task3(b)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS task3b;
CREATE DATABASE task3b;
USE task3b;  -- MySQL command

-- create the tables
CREATE TABLE categories (
  categoryID artistID      INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryName artistName    VARCHAR(255)   NOT NULL,
  PRIMARY KEY (categoryID)
);

CREATE TABLE products (
  productID    albumID    INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryID     artistID  INT(11)        NOT NULL,
  productCode   albumCode   VARCHAR(10)    NOT NULL   UNIQUE,
  productName    albumName  VARCHAR(255)   NOT NULL,
  listPrice      listPrice  DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (productID)
);

CREATE TABLE orders (
  orderID     trackID   INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID    customerID INT            NOT NULL,
  orderDate     trackDate    DATETIME       NOT NULL,
  PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'HipHop'),
(2, 'House'),
(3, 'Rave');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON products
TO mgs_tester@localhost;


//////////////////////////////////////////////////////////////////Task4(a)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS task4a;
CREATE DATABASE task4a;
USE task4a;  -- MySQL command

-- create the tables
CREATE TABLE categories (
  categoryID artistID      INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryName artistName    VARCHAR(255)   NOT NULL,
  PRIMARY KEY (categoryID)
);

CREATE TABLE products (
  productID    albumID    INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryID     artistID  INT(11)        NOT NULL,
  productCode   albumCode   VARCHAR(10)    NOT NULL   UNIQUE,
  productName    albumName  VARCHAR(255)   NOT NULL,
  listPrice      listPrice  DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (productID)
);

CREATE TABLE orders (
  orderID     trackID   INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID    customerID INT            NOT NULL,
  orderDate     trackDate    DATETIME       NOT NULL,
  PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'Artist'),
(2, 'Album'),
(3, 'Track');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON products
TO mgs_tester@localhost;

//////////////////////////////////////////////////////////////////Task4(b)/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- create and select the database
DROP DATABASE IF EXISTS task4b;
CREATE DATABASE task4b;
USE task4b;  -- MySQL command

-- create the tables
CREATE TABLE categories (
  categoryID artistID      INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryName artistName    VARCHAR(255)   NOT NULL,
  PRIMARY KEY (categoryID)
);

CREATE TABLE products (
  productID    albumID    INT(11)        NOT NULL   AUTO_INCREMENT,
  categoryID     artistID  INT(11)        NOT NULL,
  productCode   albumCode   VARCHAR(10)    NOT NULL   UNIQUE,
  productName    albumName  VARCHAR(255)   NOT NULL,
  listPrice      listPrice  DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (productID)
);

CREATE TABLE orders (
  orderID     trackID   INT(11)        NOT NULL   AUTO_INCREMENT,
  customerID    customerID INT            NOT NULL,
  orderDate     trackDate    DATETIME       NOT NULL,
  PRIMARY KEY (orderID)
);

-- insert data into the database
INSERT INTO categories VALUES
(1, 'HipHop'),
(2, 'House'),
(3, 'Rave');

INSERT INTO products VALUES
(1, 1, 'strat', 'Fender Stratocaster', '699.00'),
(2, 1, 'les_paul', 'Gibson Les Paul', '1199.00'),
(3, 1, 'sg', 'Gibson SG', '2517.00'),
(4, 1, 'fg700s', 'Yamaha FG700S', '489.99'),
(5, 1, 'washburn', 'Washburn D10S', '299.00'),
(6, 1, 'rodriguez', 'Rodriguez Caballero 11', '415.00'),
(7, 2, 'precision', 'Fender Precision', '799.99'),
(8, 2, 'hofner', 'Hofner Icon', '499.99'),
(9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', '699.99'),
(10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', '799.99');

-- create the users
CREATE USER IF NOT EXISTS mgs_user@localhost 
IDENTIFIED BY 'pa55word';

CREATE USER IF NOT EXISTS mgs_tester@localhost 
IDENTIFIED BY 'pa55word';

-- grant privleges to the users
GRANT SELECT, INSERT, DELETE, UPDATE
ON * 
TO mgs_user@localhost;

GRANT SELECT 
ON products
TO mgs_tester@localhost;
/////////////////////////////////////////////////////////////////////End/Finish/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////