

-- 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');





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

GRANT SELECT 
ON lecturer
TO mgs_tester@localhost;
