-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 21, 2015 at 09:09 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3

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: `school`
--

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

--
-- Table structure for table `class`
--

CREATE TABLE IF NOT EXISTS `class` (
`code` int(10) unsigned NOT NULL,
  `teacher_id` int(10) unsigned NOT NULL,
  `subject` varchar(45) NOT NULL,
  `room` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=975 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class`
--

INSERT INTO `class` (`code`, `teacher_id`, `subject`, `room`) VALUES
(908, 7008, 'Data structures', 114),
(926, 7003, 'Java programming', 101),
(931, 7051, 'Compilers', 222),
(951, 7012, 'Software engineering', 210),
(974, 7012, 'Operating systems', 109);

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

--
-- Table structure for table `contact_info`
--

CREATE TABLE IF NOT EXISTS `contact_info` (
`id` int(10) unsigned NOT NULL,
  `email_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `contact_info`
--

INSERT INTO `contact_info` (`id`, `email_address`) VALUES
(1, 'mjane@sjsu.edu'),
(2, 'ksmith@sjsu.edu'),
(3, 'jdoe@sjsu.edu'),
(4, 'tnovak@sjsu.edu'),
(5, 'lklein@sjsu.edu'),
(6, 'trogers@sjsu.edu'),
(7, 'athompson@sjsu.edu'),
(8, 'jlane@sjsu.edu'),
(9, 'mflynn@sjsu.edu');

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

--
-- Table structure for table `student`
--

CREATE TABLE IF NOT EXISTS `student` (
`Id` int(10) unsigned NOT NULL,
  `Last` varchar(45) NOT NULL,
  `First` varchar(45) NOT NULL,
  `contact_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1022 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`Id`, `Last`, `First`, `contact_id`) VALUES
(1001, 'Doe', 'John', 3),
(1005, 'Novak', 'Tim', 4),
(1009, 'Klein', 'Leslie', 5),
(1014, 'Jane', 'Mary', 1),
(1021, 'Smith', 'Kim', 2);

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

--
-- Table structure for table `student_class`
--

CREATE TABLE IF NOT EXISTS `student_class` (
  `student_id` int(10) unsigned NOT NULL,
  `class_code` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student_class`
--

INSERT INTO `student_class` (`student_id`, `class_code`) VALUES
(1001, 908),
(1001, 926),
(1001, 951),
(1005, 908),
(1005, 974),
(1014, 931),
(1021, 926),
(1021, 931),
(1021, 974);

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

--
-- Table structure for table `teacher`
--

CREATE TABLE IF NOT EXISTS `teacher` (
`Id` int(10) unsigned NOT NULL,
  `Last` varchar(45) NOT NULL,
  `First` varchar(45) NOT NULL,
  `contact_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7052 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `teacher`
--

INSERT INTO `teacher` (`Id`, `Last`, `First`, `contact_id`) VALUES
(7003, 'Rogers', 'Tom', 6),
(7008, 'Thompson', 'Art', 7),
(7012, 'Lane', 'John', 8),
(7051, 'Flynn', 'Mabel', 9);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `class`
--
ALTER TABLE `class`
 ADD PRIMARY KEY (`code`), ADD KEY `teacher_id` (`teacher_id`);

--
-- Indexes for table `contact_info`
--
ALTER TABLE `contact_info`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `student`
--
ALTER TABLE `student`
 ADD PRIMARY KEY (`Id`), ADD KEY `contact_id` (`contact_id`);

--
-- Indexes for table `student_class`
--
ALTER TABLE `student_class`
 ADD PRIMARY KEY (`student_id`,`class_code`), ADD KEY `student_id` (`student_id`), ADD KEY `class_code` (`class_code`);

--
-- Indexes for table `teacher`
--
ALTER TABLE `teacher`
 ADD PRIMARY KEY (`Id`), ADD KEY `FK_teacher_1` (`contact_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `class`
--
ALTER TABLE `class`
MODIFY `code` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=975;
--
-- AUTO_INCREMENT for table `contact_info`
--
ALTER TABLE `contact_info`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
MODIFY `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1022;
--
-- AUTO_INCREMENT for table `teacher`
--
ALTER TABLE `teacher`
MODIFY `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7052;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `class`
--
ALTER TABLE `class`
ADD CONSTRAINT `teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`Id`);

--
-- Constraints for table `student`
--
ALTER TABLE `student`
ADD CONSTRAINT `contact_id` FOREIGN KEY (`contact_id`) REFERENCES `contact_info` (`id`);

--
-- Constraints for table `student_class`
--
ALTER TABLE `student_class`
ADD CONSTRAINT `class_code` FOREIGN KEY (`class_code`) REFERENCES `class` (`code`),
ADD CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`Id`);

--
-- Constraints for table `teacher`
--
ALTER TABLE `teacher`
ADD CONSTRAINT `FK_teacher_1` FOREIGN KEY (`contact_id`) REFERENCES `contact_info` (`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 */;
