-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jun 15, 2021 at 01:50 AM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.3.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
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 utf8mb4 */;

--
-- Database: `school`
--

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

--
-- Table structure for table `Address`
--

CREATE TABLE `Address` (
  `id` int(10) UNSIGNED NOT NULL,
  `email_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Addressx';

--
-- Dumping data for table `Address`
--

INSERT INTO `Address` (`id`, `email_address`) VALUES
(1, 'mary.jane@state.edu'),
(2, 'kim.smith@state.edu'),
(3, 'john.doe@state.edu'),
(4, 'tim.novak@state.edu'),
(5, 'leslie.klein@state.edu'),
(6, 'pat.rogers@state.edu'),
(7, 'sidneythompson@state.edu'),
(8, 'john.lane@state.edu'),
(9, 'mabel.flynn@state.edu');

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

--
-- Table structure for table `Class`
--

CREATE TABLE `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 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Class`
--

INSERT INTO `Class` (`code`, `teacher_id`, `subject`, `room`) VALUES
(908, 7008, 'English', 114),
(914, 7051, 'Math', 115),
(926, 7003, 'Art', 101),
(931, 7051, 'Social Studies', 101),
(951, 7012, 'History', 120);

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

--
-- Table structure for table `Student`
--

CREATE TABLE `Student` (
  `id` int(10) UNSIGNED NOT NULL,
  `last` varchar(45) NOT NULL,
  `first` varchar(45) NOT NULL,
  `email_address` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Student`
--

INSERT INTO `Student` (`id`, `last`, `first`, `email_address`) 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 `Takes`
--

CREATE TABLE `Takes` (
  `student_id` int(10) UNSIGNED NOT NULL,
  `class_code` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Takes`
--

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

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

--
-- Table structure for table `Teacher`
--

CREATE TABLE `Teacher` (
  `id` int(10) UNSIGNED NOT NULL,
  `last` varchar(45) NOT NULL,
  `first` varchar(45) NOT NULL,
  `email_address` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Teacher`
--

INSERT INTO `Teacher` (`id`, `last`, `first`, `email_address`) VALUES
(7003, 'Rogers', 'Pat', 6),
(7008, 'Thompson', 'Sidney', 7),
(7012, 'Lane', 'John', 8),
(7051, 'Flynn', 'Mabel', 9);

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `Student`
--
ALTER TABLE `Student`
  ADD PRIMARY KEY (`id`),
  ADD KEY `email_address` (`email_address`);

--
-- Indexes for table `Takes`
--
ALTER TABLE `Takes`
  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 `email_address` (`email_address`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Address`
--
ALTER TABLE `Address`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `Class`
--
ALTER TABLE `Class`
  MODIFY `code` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=952;

--
-- 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 `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `Teacher` (`id`);

--
-- Constraints for table `Student`
--
ALTER TABLE `Student`
  ADD CONSTRAINT `fk_student_address` FOREIGN KEY (`email_address`) REFERENCES `Address` (`id`);

--
-- Constraints for table `Takes`
--
ALTER TABLE `Takes`
  ADD CONSTRAINT `fk_class_code` FOREIGN KEY (`class_code`) REFERENCES `Class` (`code`),
  ADD CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `Student` (`id`);

--
-- Constraints for table `Teacher`
--
ALTER TABLE `Teacher`
  ADD CONSTRAINT `fk_teacher_address` FOREIGN KEY (`email_address`) REFERENCES `Address` (`id`);
COMMIT;

/*!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 */;
