SQL File to setup the comedyclub database. Some insert statements need fixed, and relations.

comedySetup.sql

--
-- Database: `comedyclub`
--

-- --------------------------------------------------------

--
-- Table structure for table `agent`
--

DROP TABLE IF EXISTS `agent`;
CREATE TABLE IF NOT EXISTS `agent` (
  `company` varchar(25) NOT NULL,
  `contactID` int(11) NOT NULL,
  PRIMARY KEY (`contactID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `agent`
--

INSERT INTO `agent` (`company`, `contactID`) VALUES
('Edinburgh Talent', 1),
('Newcastle Singers', 2);

-- --------------------------------------------------------

--
-- Table structure for table `building`
--

DROP TABLE IF EXISTS `building`;
CREATE TABLE IF NOT EXISTS `building` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `postCode` varchar(7) NOT NULL,
  `address1` varchar(30) NOT NULL,
  `address2` varchar(30) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `building`
--

INSERT INTO `building` (`id`, `postCode`, `address1`, `address2`, `city`) VALUES
(1, 'EH12DJ', 'HMV Picture House', '31 Lothian Road', 'Edinburgh'),
(2, 'NE11SW', 'O2 Academy Newcastle', 'Westgate Road', 'Newcastle'),
(3, 'EH113PZ', 'Main Office', '123 Random Street', 'Edinburgh');

-- --------------------------------------------------------

--
-- Table structure for table `contact`
--

DROP TABLE IF EXISTS `contact`;
CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `phone` varchar(11) NOT NULL,
  `address` varchar(30) NOT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `contact`
--

INSERT INTO `contact` (`id`, `name`, `phone`, `address`, `email`) VALUES
(1, 'Jane Smith', '07564343212', '123 Example Street', '[email protected]'),
(2, 'John Cossack', '0191876564', '34 Northumbria Road', '[email protected]'),
(3, 'The Three Scotsmen', '07425654371', '13 Leith Street', '[email protected]'),
(4, 'Geordie Folkmen', '07425654371', '13 Leith Street', '[email protected]');

-- --------------------------------------------------------

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
CREATE TABLE IF NOT EXISTS `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `address1` varchar(30) DEFAULT NULL,
  `address2` varchar(30) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `creditCardNumber` varchar(16) NOT NULL,
  `dateOfBirth` date DEFAULT NULL,
  `gender` enum('Male','Female','Unspecified') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`id`, `name`, `address1`, `address2`, `city`, `creditCardNumber`, `dateOfBirth`, `gender`) VALUES
(1, 'John Customer', '123 Random Street', NULL, 'Edinburgh', '4444555566667777', '1991-05-06', 'Male'),
(2, 'Ethan Grey', '67 New Street', 'Newfoundland', 'Nottingham', '2222333344445555', '1955-12-04', 'Male'),
(3, 'Jane Customer', '256 Random Street', NULL, 'Edinburgh', '2222333344445555', '1986-03-09', 'Female'),
(4, 'Emma Florence', '23 Wayland Ave', 'High Park', 'Aberdeen', '5555888844446666', '1989-04-13', 'Female'),
(5, 'Tom Grant', '73 Sycamore Drive', NULL, 'Glasgow', '4444555544444444', '1969-04-24', 'Male');

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `dateOfBirth` date NOT NULL,
  `address` varchar(50) NOT NULL,
  `startDate` date DEFAULT NULL,
  `phone` varchar(11) NOT NULL,
  `email` varchar(30) DEFAULT NULL,
  `managerID` int(11) DEFAULT NULL,
  `worksIn` int(11) NOT NULL,
  `payBand` int(2) NOT NULL DEFAULT '3' COMMENT '3 is bottom pay level',
  PRIMARY KEY (`id`),
  KEY `managerID` (`managerID`),
  KEY `worksIn` (`worksIn`),
  KEY `payID` (`payBand`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- RELATIONS FOR TABLE `employees`:
--   `managerID`
--       `employees` -> `id`
--   `payBand`
--       `payBand` -> `payID`
--   `worksIn`
--       `building` -> `id`
--

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `name`, `dateOfBirth`, `address`, `startDate`, `phone`, `email`, `managerID`, `worksIn`, `payBand`) VALUES
(1, 'Daniel Bell', '1992-04-10', '123 Random Address', '2011-07-06', '0765447362', '[email protected]', NULL, 2, 1),
(2, 'Calum Gilchrist', '1987-01-01', '256 Random Address', '2011-08-22', '0131454342', '[email protected]', 0, 2, 4),
(3, 'John Doe', '1986-07-30', '33 Doe street', '2012-02-06', '0131666333', NULL, NULL, 0, 2),
(4, 'Jane Doe', '1991-04-10', '32 Doe street', '2012-01-30', '0876555333', NULL, 2, 0, 3),
(5, 'Steve Doe', '1990-02-23', '33 Doe street', '0000-00-00', '0845333888', NULL, 2, 0, 3),
(6, 'George Best', '1965-07-18', '310 Best street', '2011-06-15', '0987564372', NULL, 1, 0, 2);

-- --------------------------------------------------------

--
-- Table structure for table `entertainer`
--

DROP TABLE IF EXISTS `entertainer`;
CREATE TABLE IF NOT EXISTS `entertainer` (
  `contactID` int(11) NOT NULL,
  `type` varchar(30) DEFAULT NULL,
  `genre` varchar(30) DEFAULT NULL,
  `agentID` int(11) DEFAULT NULL,
  PRIMARY KEY (`contactID`),
  KEY `agentID` (`agentID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `entertainer`
--

INSERT INTO `entertainer` (`contactID`, `type`, `genre`, `agentID`) VALUES
(3, 'Comedy', 'Standup', 0),
(4, 'Band', 'Folk', 1);

-- --------------------------------------------------------

--
-- Table structure for table `equipment`
--

DROP TABLE IF EXISTS `equipment`;
CREATE TABLE IF NOT EXISTS `equipment` (
  `itemCode` int(11) NOT NULL AUTO_INCREMENT,
  `productCode` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `type` varchar(30) DEFAULT NULL,
  `manufacturer` varchar(30) DEFAULT NULL,
  `supplier` varchar(30) DEFAULT NULL,
  `lastSafetyCheck` date DEFAULT NULL,
  `buildingID` int(11) NOT NULL,
  PRIMARY KEY (`itemCode`),
  KEY `buildingID` (`buildingID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `equipment`
--

INSERT INTO `equipment` (`itemCode`, `productCode`, `name`, `type`, `manufacturer`, `supplier`, `lastSafetyCheck`, `buildingID`) VALUES
(1, 111, 'Mic1', 'Microphone', 'Samsung', 'General Electrics', '2010-05-07', 1),
(2, 112, 'Amp1', 'Amplifier', 'Fender', 'Stan''s Amps', '2010-06-23', 1),
(3, 111, 'Mic2', 'Microphone', 'Samsung', 'General Electrics', '2010-05-07', 2),
(4, 112, 'Amp2', 'Amplifier', 'Fender', 'Stan''s Amps', '2010-06-23', 2),
(5, 113, 'AC1', 'Air Conditioner', 'General Air', 'AC Units R Us', '2010-09-13', 3),
(6, 113, 'AC2', 'Air Conditioner', 'General Air', 'AC Units R Us', '2010-11-19', 3);

-- --------------------------------------------------------

--
-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
CREATE TABLE IF NOT EXISTS `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `venueID` int(11) NOT NULL,
  `eventName` varchar(100) NOT NULL,
  `dateTime` datetime NOT NULL,
  `amountOfTickets` int(11) NOT NULL,
  `ageRestriction` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `venueID` (`venueID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `event`
--

INSERT INTO `event` (`id`, `venueID`, `eventName`, `dateTime`, `amountOfTickets`, `ageRestriction`) VALUES
(1, 0, 'The Three Scotsman in Scotland', '2012-02-15 19:00:00', 100, 0),
(2, 0, 'Geordie Folkmen Play Heavy Metal', '2012-02-05 18:00:00', 50, 0);

-- --------------------------------------------------------

--
-- Table structure for table `pay`
--

DROP TABLE IF EXISTS `pay`;
CREATE TABLE IF NOT EXISTS `pay` (
  `paymentID` int(11) NOT NULL AUTO_INCREMENT,
  `employeeID` int(6) DEFAULT NULL,
  `dateOfPayment` date NOT NULL,
  `hoursWorked` int(4) NOT NULL,
  `commissionSales` int(11) NOT NULL,
  `bonus` int(11) NOT NULL,
  `contactID` int(6) DEFAULT NULL,
  PRIMARY KEY (`paymentID`),
  KEY `employeeID` (`employeeID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- RELATIONS FOR TABLE `pay`:
--   `contactID`
--       `contact` -> `id`
--   `employeeID`
--       `employees` -> `id`
--

--
-- Dumping data for table `pay`
--

INSERT INTO `pay` (`paymentID`, `employeeID`, `dateOfPayment`, `hoursWorked`, `commissionSales`, `bonus`, `contactID`) VALUES
(1, 1, '2012-01-01', 40, 0, 0, NULL),
(2, 1, '2012-02-01', 50, 0, 0, NULL),
(3, 2, '2012-01-01', 35, 10, 0, NULL),
(4, 2, '2012-02-01', 40, 0, 50, NULL),
(5, 3, '2012-01-01', 0, 25, 100, NULL),
(6, 3, '2012-02-01', 0, 20, 25, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `payBand`
--

DROP TABLE IF EXISTS `payBand`;
CREATE TABLE IF NOT EXISTS `payBand` (
  `payID` int(11) NOT NULL AUTO_INCREMENT,
  `band` int(2) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  `payPerHour` decimal(4,2) DEFAULT NULL,
  `commission` int(3) DEFAULT NULL,
  PRIMARY KEY (`payID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `payBand`
--

INSERT INTO `payBand` (`payID`, `band`, `salary`, `payPerHour`, `commission`) VALUES
(1, 1, 30000, '0.00', NULL),
(2, 1, NULL, '10.00', NULL),
(3, 2, NULL, '7.50', 5),
(4, 2, 35000, NULL, NULL),
(5, 1, NULL, NULL, 30),
(6, 2, NULL, NULL, 20),
(7, 3, NULL, NULL, 10);

-- --------------------------------------------------------

--
-- Table structure for table `playsAt`
--

DROP TABLE IF EXISTS `playsAt`;
CREATE TABLE IF NOT EXISTS `playsAt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventID` int(6) NOT NULL,
  `entID` int(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `eventID` (`eventID`),
  KEY `entID` (`entID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- RELATIONS FOR TABLE `playsAt`:
--   `entID`
--       `entertainer` -> `contactID`
--   `eventID`
--       `event` -> `id`
--

--
-- Dumping data for table `playsAt`
--

INSERT INTO `playsAt` (`id`, `eventID`, `entID`) VALUES
(1, 1, 3),
(2, 2, 4);

-- --------------------------------------------------------

--
-- Table structure for table `ticket`
--

DROP TABLE IF EXISTS `ticket`;
CREATE TABLE IF NOT EXISTS `ticket` (
  `barCode` int(11) NOT NULL AUTO_INCREMENT,
  `eventID` int(11) NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `seatNumber` int(11) DEFAULT NULL,
  `dateOfPurchase` date NOT NULL,
  `shippingMethod` enum('Delivery','Box Office') NOT NULL,
  `customerID` int(11) NOT NULL,
  PRIMARY KEY (`barCode`),
  KEY `eventID` (`eventID`),
  KEY `customerID` (`customerID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `ticket`
--

INSERT INTO `ticket` (`barCode`, `eventID`, `price`, `seatNumber`, `dateOfPurchase`, `shippingMethod`, `customerID`) VALUES
(1, 1, '18.50', 23, '2012-02-11', 'Box Office', 1),
(2, 1, '18.50', 36, '2012-01-23', 'Delivery', 2),
(3, 1, '18.50', 25, '2012-02-15', 'Box Office', 1),
(4, 1, '18.50', 12, '2012-02-15', 'Box Office', 3),
(5, 1, '18.50', 3, '2012-02-15', 'Box Office', 3),
(6, 1, '18.50', 6, '2012-02-15', 'Box Office', 3),
(7, 1, '18.50', 7, '2012-02-15', 'Box Office', 3),
(8, 2, '21.00', NULL, '2012-01-23', 'Delivery', 2),
(9, 2, '25.00', NULL, '2012-02-05', 'Box Office', 5),
(10, 2, '25.00', NULL, '2012-02-05', 'Box Office', 5),
(11, 2, '25.00', NULL, '2012-02-05', 'Box Office', 1),
(12, 2, '25.00', NULL, '2012-02-05', 'Box Office', 4),
(13, 2, '25.00', NULL, '2012-02-05', 'Box Office', 4),
(14, 2, '25.00', NULL, '2012-02-05', 'Box Office', 2),
(15, 2, '25.00', NULL, '2012-02-05', 'Box Office', 4);

-- --------------------------------------------------------

--
-- Table structure for table `venue`
--

DROP TABLE IF EXISTS `venue`;
CREATE TABLE IF NOT EXISTS `venue` (
  `buildingID` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `alcoholLicense` tinyint(1) NOT NULL,
  `foodLicense` tinyint(1) NOT NULL,
  `performanceLicense` tinyint(1) NOT NULL,
  PRIMARY KEY (`buildingID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `venue`:
--   `buildingID`
--       `building` -> `id`
--

--
-- Dumping data for table `venue`
--

INSERT INTO `venue` (`buildingID`, `capacity`, `alcoholLicense`, `foodLicense`, `performanceLicense`) VALUES
(1, 300, 0, 0, 0),
(2, 450, 0, 0, 0);

-- --------------------------------------------------------
-- Views
-- --------------------------------------------------------

DROP VIEW IF EXISTS viewRestrictedPayTable;
DROP VIEW IF EXISTS viewRestrictedCustomers;

DROP VIEW IF EXISTS viewEmployees;
DROP VIEW IF EXISTS viewEmployeeNames;

DROP VIEW IF EXISTS viewBuildings;
DROP VIEW IF EXISTS viewVenues;

DROP VIEW IF EXISTS viewEquipment;

DROP VIEW IF EXISTS viewEvents;
DROP VIEW IF EXISTS viewTickets;

DROP VIEW IF EXISTS viewAgents;
DROP VIEW IF EXISTS viewEntertainers;

DROP VIEW IF EXISTS viewContact;

-- Manager only views
CREATE VIEW viewRestrictedPayTable AS SELECT * FROM pay;
CREATE VIEW viewRestrictedCustomers AS SELECT * FROM customer;

-- Admin only view for the self join
CREATE VIEW viewEmployeeNames AS SELECT id as empID, name as empName FROM employees;
CREATE VIEW viewEmployees AS SELECT * FROM employees LEFT JOIN viewEmployeeNames ON managerID = empID;

CREATE VIEW viewBuildings AS SELECT * FROM building;
CREATE VIEW viewVenues AS SELECT * FROM building RIGHT JOIN venue ON venue.buildingID = building.id;

CREATE VIEW viewEquipment AS SELECT * FROM equipment;

CREATE VIEW viewEvents AS SELECT * FROM event;
CREATE VIEW viewTickets AS SELECT * FROM ticket;

CREATE VIEW viewAgents AS SELECT * FROM contact RIGHT JOIN agent on contact.id = agent.contactID;
CREATE VIEW viewEntertainers AS SELECT * FROM contact RIGHT JOIN entertainer on contact.id = entertainer.contactID;

-- Creates a Contact View that shows all contacts 
CREATE VIEW viewContact AS SELECT contact.*, agent.contactID AS agentConID , agent.company, entertainer.type, entertainer.contactID AS entertainerID, entertainer.agentID, entertainer.genre FROM contact, agent, entertainer WHERE contact.id=entertainer.contactID OR agent.contactID=contact.id GROUP BY id;