-- <pre><code>
-- PTD includes referential integrity
-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 26, 2020 at 10:28 PM
-- Server version: 10.2.31-MariaDB
-- PHP Version: 7.2.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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: `textbook`
--

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

--
-- Table structure for table `A_MARINA`
--

CREATE TABLE `A_MARINA` (
  `MARINA_NUM` char(4) NOT NULL,
  `NAME` char(20) DEFAULT NULL,
  `ADDRESS` char(15) DEFAULT NULL,
  `CITY` char(15) DEFAULT NULL,
  `STATE` char(2) DEFAULT NULL,
  `ZIP` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `A_MARINA`
--

INSERT INTO `A_MARINA` (`MARINA_NUM`, `NAME`, `ADDRESS`, `CITY`, `STATE`, `ZIP`) VALUES
('1', 'Alexamara East', '108 2nd Ave.', 'Brinman', 'FL', '32273'),
('2', 'Alexamara Central', '283 Branston', 'W. Brinman', 'FL', '32274');

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

--
-- Table structure for table `A_MARINA_SLIP`
--

CREATE TABLE `A_MARINA_SLIP` (
  `SLIP_ID` decimal(4,0) NOT NULL,
  `MARINA_NUM` char(4) DEFAULT NULL,
  `SLIP_NUM` char(4) DEFAULT NULL,
  `LENGTH` decimal(4,0) DEFAULT NULL,
  `RENTAL_FEE` decimal(8,2) DEFAULT NULL,
  `BOAT_NAME` char(50) DEFAULT NULL,
  `BOAT_TYPE` char(50) DEFAULT NULL,
  `OWNER_NUM` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `A_MARINA_SLIP`
--

INSERT INTO `A_MARINA_SLIP` (`SLIP_ID`, `MARINA_NUM`, `SLIP_NUM`, `LENGTH`, `RENTAL_FEE`, `BOAT_NAME`, `BOAT_TYPE`, `OWNER_NUM`) VALUES
('1', '1', 'A1', '40', '3800.00', 'Anderson II', 'Sprite 4000', 'AN75'),
('2', '1', 'A2', '40', '3800.00', 'Our Toy', 'Ray 4025', 'EL25'),
('3', '1', 'A3', '40', '3600.00', 'Escape', 'Sprite 4000', 'KE22'),
('4', '1', 'B1', '30', '2400.00', 'Gypsy', 'Dolphin 28', 'JU92'),
('5', '1', 'B2', '30', '2600.00', 'Anderson III', 'Sprite 3000', 'AN75'),
('6', '2', '1', '25', '1800.00', 'Bravo', 'Dolphin 25', 'AD57'),
('7', '2', '2', '25', '1800.00', 'Chinook', 'Dolphin 22', 'FE82'),
('8', '2', '3', '25', '2000.00', 'Listy', 'Dolphin 25', 'SM72'),
('9', '2', '4', '30', '2500.00', 'Mermaid', 'Dolphin 28', 'BL72'),
('10', '2', '5', '40', '4200.00', 'Axxon II', 'Dolphin 40', 'NO27'),
('11', '2', '6', '40', '4200.00', 'Karvel', 'Ray 4025', 'TR72');

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

--
-- Table structure for table `A_OWNER`
--

CREATE TABLE `A_OWNER` (
  `OWNER_NUM` char(4) NOT NULL,
  `LAST_NAME` char(50) DEFAULT NULL,
  `FIRST_NAME` char(20) DEFAULT NULL,
  `ADDRESS` char(15) DEFAULT NULL,
  `CITY` char(15) DEFAULT NULL,
  `STATE` char(2) DEFAULT NULL,
  `ZIP` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `A_OWNER`
--

INSERT INTO `A_OWNER` (`OWNER_NUM`, `LAST_NAME`, `FIRST_NAME`, `ADDRESS`, `CITY`, `STATE`, `ZIP`) VALUES
('AD57', 'Adney', 'Bruce and Jean', '208 Citrus', 'Bowton', 'FL', '31313'),
('AN75', 'Anderson', 'Bill', '18 Wilcox', 'Glander Bay', 'FL', '31044'),
('BL72', 'Blake', 'Mary', '2672 Commodore', 'Bowton', 'FL', '31313'),
('EL25', 'Elend', 'Sandy and Bill', '462 Riverside', 'Rivard', 'FL', '31062'),
('FE82', 'Feenstra', 'Daniel', '7822 Coventry', 'Kaleva', 'FL', '32521'),
('JU92', 'Juarez', 'Maria', '8922 Oak', 'Rivard', 'FL', '31062'),
('KE22', 'Kelly', 'Alyssa', '5271 Waters', 'Bowton', 'FL', '31313'),
('NO27', 'Norton', 'Peter', '2811 Lakewood', 'Lewiston', 'FL', '32765'),
('SM72', 'Smeltz', 'Becky and Dave', '922 Garland', 'Glander Bay', 'FL', '31044'),
('TR72', 'Trent', 'Ashton', '922 Crest', 'Bay Shores', 'FL', '30992');

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

--
-- Table structure for table `A_SERVICE_CATEGORY`
--

CREATE TABLE `A_SERVICE_CATEGORY` (
  `CATEGORY_NUM` decimal(4,0) NOT NULL,
  `CATEGORY_DESCRIPTION` char(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `A_SERVICE_CATEGORY`
--

INSERT INTO `A_SERVICE_CATEGORY` (`CATEGORY_NUM`, `CATEGORY_DESCRIPTION`) VALUES
('1', 'Routine engine maintenance'),
('2', 'Engine repair'),
('3', 'Air conditioning'),
('4', 'Electrical systems'),
('5', 'Fiberglass repair'),
('6', 'Canvas installation'),
('7', 'Canvas repair'),
('8', 'Electronic systems (radar, GPS, autopilots, etc.)');

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

--
-- Table structure for table `A_SERVICE_REQUEST`
--

CREATE TABLE `A_SERVICE_REQUEST` (
  `SERVICE_ID` decimal(4,0) NOT NULL,
  `SLIP_ID` decimal(4,0) DEFAULT NULL,
  `CATEGORY_NUM` decimal(4,0) DEFAULT NULL,
  `DESCRIPTION` char(255) DEFAULT NULL,
  `STATUS` char(255) DEFAULT NULL,
  `EST_HOURS` decimal(4,2) DEFAULT NULL,
  `SPENT_HOURS` decimal(4,2) DEFAULT NULL,
  `NEXT_SERVICE_DATE` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `A_SERVICE_REQUEST`
--

INSERT INTO `A_SERVICE_REQUEST` (`SERVICE_ID`, `SLIP_ID`, `CATEGORY_NUM`, `DESCRIPTION`, `STATUS`, `EST_HOURS`, `SPENT_HOURS`, `NEXT_SERVICE_DATE`) VALUES
('1', '1', '3', 'Air conditioner periodically stops with code indicating low coolant level. Diagnose and repair.', 'Technician has verified the problem. Air conditioning specialist has been called.', '4.00', '2.00', '2007-07-12'),
('2', '5', '4', 'Fuse on port motor blown on two occasions. Diagnose and repair.', 'Open', '2.00', '0.00', '2007-07-12'),
('3', '4', '1', 'Oil change and general routine maintenance (check fluid levels, clean sea strainers, etc.).', 'Service call has been scheduled.', '1.00', '0.00', '2007-07-16'),
('4', '1', '2', 'Engine oil level has been dropping drastically. Diagnose and repair.', 'Open', '2.00', '0.00', '2007-07-13'),
('5', '3', '5', 'Open pockets at base of two stantions.', 'Technician has completed the initial filling of the open pockets. Will complete the job after the initial fill has had sufficient time to dry.', '4.00', '2.00', '2007-07-13'),
('6', '11', '4', 'Electric-flush system periodically stops functioning. Diagnose and repair.', 'Open', '3.00', '0.00', NULL),
('7', '6', '2', 'Engine overheating. Loss of coolant. Diagnose and repair.', 'Open', '2.00', '0.00', '2007-07-13'),
('8', '6', '2', 'Heat exchanger not operating correctly.', 'Technician has determined that the exchanger is faulty. New exchanger has been ordered.', '4.00', '1.00', '2007-07-17'),
('9', '7', '6', 'Canvas severely damaged in windstorm. Order and install new canvas.', 'Open', '8.00', '0.00', '2007-07-16'),
('10', '2', '8', 'Install new GPS and chart plotter.', 'Scheduled', '7.00', '0.00', '2007-07-17'),
('11', '2', '3', 'Air conditioning unit shuts down with HHH showing on the control panel.', 'Technician not able to replicate the problem. Air conditioning unit ran fine through multiple tests. Owner to notify technician if the problem recurs.', '1.00', '1.00', NULL),
('12', '4', '8', 'Both speed and depth readings on data unit are significantly less than the owner thinks they should be.', 'Technician has scheduled appointment with owner to attempt to verify the problem.', '2.00', '0.00', '2007-07-16'),
('13', '8', '2', 'Customer describes engine as making a clattering sound.', 'Technician suspects problem with either propeller or shaft and has scheduled the boat to be pulled from the water for further investigation.', '5.00', '2.00', '2007-07-12'),
('14', '7', '5', 'Owner accident caused damage to forward portion of port side.', 'Technician has scheduled repair.', '6.00', '0.00', '2007-07-13'),
('15', '11', '7', 'Canvas leaks around zippers in heavy rain. Install overlap around zippers to prevent leaks.', 'Overlap has been created. Installation has been scheduled.', '8.00', '3.00', '2007-07-17');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `A_MARINA`
--
ALTER TABLE `A_MARINA`
  ADD PRIMARY KEY (`MARINA_NUM`);

--
-- Indexes for table `A_MARINA_SLIP`
--
ALTER TABLE `A_MARINA_SLIP`
  ADD PRIMARY KEY (`SLIP_ID`),
  ADD KEY `MARINA_NUM` (`MARINA_NUM`),
  ADD KEY `OWNER_NUM` (`OWNER_NUM`);

--
-- Indexes for table `A_OWNER`
--
ALTER TABLE `A_OWNER`
  ADD PRIMARY KEY (`OWNER_NUM`);

--
-- Indexes for table `A_SERVICE_CATEGORY`
--
ALTER TABLE `A_SERVICE_CATEGORY`
  ADD PRIMARY KEY (`CATEGORY_NUM`);

--
-- Indexes for table `A_SERVICE_REQUEST`
--
ALTER TABLE `A_SERVICE_REQUEST`
  ADD PRIMARY KEY (`SERVICE_ID`),
  ADD KEY `CATEGORY_NUM` (`CATEGORY_NUM`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `A_MARINA_SLIP`
--
ALTER TABLE `A_MARINA_SLIP`
  ADD CONSTRAINT `A_MARINA_SLIP_ibfk_1` FOREIGN KEY (`MARINA_NUM`) REFERENCES `A_MARINA` (`MARINA_NUM`) ON UPDATE CASCADE,
  ADD CONSTRAINT `A_MARINA_SLIP_ibfk_2` FOREIGN KEY (`OWNER_NUM`) REFERENCES `A_OWNER` (`OWNER_NUM`) ON UPDATE CASCADE;

--
-- Constraints for table `A_SERVICE_REQUEST`
--
ALTER TABLE `A_SERVICE_REQUEST`
  ADD CONSTRAINT `A_SERVICE_REQUEST_ibfk_1` FOREIGN KEY (`CATEGORY_NUM`) REFERENCES `A_SERVICE_CATEGORY` (`CATEGORY_NUM`) ON UPDATE CASCADE;
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 */;