-- phpMyAdmin SQL Dump
-- version 4.1.12
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: May 08, 2014 at 05:05 AM
-- Server version: 5.6.16
-- PHP Version: 5.5.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `tvguide`
--
CREATE DATABASE IF NOT EXISTS `tvguide` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `tvguide`;

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

--
-- Table structure for table `channels`
--

CREATE TABLE IF NOT EXISTS `channels` (
  `channel_number` varchar(30) NOT NULL DEFAULT '',
  `channel_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`channel_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `channels`
--

INSERT INTO `channels` (`channel_number`, `channel_name`) VALUES
('2', 'SBS'),
('7', 'Channel 7'),
('72', '7 Two'),
('78', '7mate');

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

--
-- Table structure for table `dayz`
--

CREATE TABLE IF NOT EXISTS `dayz` (
  `slot_id` int(11) NOT NULL,
  `air_day` varchar(30) DEFAULT NULL,
  `guide_slot` int(11) DEFAULT NULL,
  PRIMARY KEY (`slot_id`),
  KEY `guide_slot` (`guide_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dayz`
--

INSERT INTO `dayz` (`slot_id`, `air_day`, `guide_slot`) VALUES
(1, 'Monday', 2),
(2, 'Thursday', 1);

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

--
-- Table structure for table `guide`
--

CREATE TABLE IF NOT EXISTS `guide` (
  `slot_id` int(11) NOT NULL AUTO_INCREMENT,
  `show_id` varchar(30) DEFAULT NULL,
  `time_slot` varchar(30) DEFAULT NULL,
  `show_channel` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`slot_id`),
  KEY `show_channel` (`show_channel`),
  KEY `show_id` (`show_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `guide`
--

INSERT INTO `guide` (`slot_id`, `show_id`, `time_slot`, `show_channel`) VALUES
(1, '1', '0000-0100', '78'),
(2, '1', '0200-0300', '7'),
(3, '2', '0600-0630', '2');

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

--
-- Table structure for table `quality`
--

CREATE TABLE IF NOT EXISTS `quality` (
  `slot_id` int(11) NOT NULL DEFAULT '0',
  `quality` varchar(30) DEFAULT NULL,
  `show_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`slot_id`),
  KEY `show_id` (`show_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `quality`
--

INSERT INTO `quality` (`slot_id`, `quality`, `show_id`) VALUES
(1, 'HD', '1'),
(2, 'SD', '2');

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

--
-- Table structure for table `ratings`
--

CREATE TABLE IF NOT EXISTS `ratings` (
  `slot_id` varchar(30) NOT NULL DEFAULT '',
  `show_id` varchar(30) NOT NULL DEFAULT '',
  `show_rating` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`slot_id`),
  KEY `show_id` (`show_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ratings`
--

INSERT INTO `ratings` (`slot_id`, `show_id`, `show_rating`) VALUES
('1', '1', '5'),
('2', '1', '2'),
('3', '1', '1'),
('4', '2', '5');

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

--
-- Table structure for table `regions`
--

CREATE TABLE IF NOT EXISTS `regions` (
  `broadcast_id` int(11) NOT NULL DEFAULT '0',
  `broadcast_region` varchar(30) DEFAULT NULL,
  `show_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`broadcast_id`),
  KEY `show_id` (`show_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `regions`
--

INSERT INTO `regions` (`broadcast_id`, `broadcast_region`, `show_id`) VALUES
(1, 'Region 1', '1');

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

--
-- Table structure for table `shows`
--

CREATE TABLE IF NOT EXISTS `shows` (
  `show_id` varchar(30) NOT NULL DEFAULT '',
  `show_title` varchar(30) DEFAULT NULL,
  `show_description` varchar(30) DEFAULT NULL,
  `show_classification` varchar(30) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  PRIMARY KEY (`show_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `shows`
--

INSERT INTO `shows` (`show_id`, `show_title`, `show_description`, `show_classification`, `duration`) VALUES
('1', 'Mythbusters', 'they bust shit', 'M', 60),
('2', 'Simpsons', 'crap', 'PG', 32),
('3', 'Avengers', 'more crap', 'M', 120);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `dayz`
--
ALTER TABLE `dayz`
  ADD CONSTRAINT `dayz_ibfk_1` FOREIGN KEY (`guide_slot`) REFERENCES `guide` (`slot_id`);

--
-- Constraints for table `guide`
--
ALTER TABLE `guide`
  ADD CONSTRAINT `guide_ibfk_1` FOREIGN KEY (`show_channel`) REFERENCES `channels` (`channel_number`),
  ADD CONSTRAINT `guide_ibfk_2` FOREIGN KEY (`show_id`) REFERENCES `shows` (`show_id`);

--
-- Constraints for table `quality`
--
ALTER TABLE `quality`
  ADD CONSTRAINT `quality_ibfk_1` FOREIGN KEY (`show_id`) REFERENCES `shows` (`show_id`);

--
-- Constraints for table `ratings`
--
ALTER TABLE `ratings`
  ADD CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`show_id`) REFERENCES `shows` (`show_id`);

--
-- Constraints for table `regions`
--
ALTER TABLE `regions`
  ADD CONSTRAINT `regions_ibfk_1` FOREIGN KEY (`show_id`) REFERENCES `shows` (`show_id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;