Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Edit the following SQL database code to accomplish the following: Run AviationCo

ID: 3870755 • Letter: E

Question

Edit the following SQL database code to accomplish the following:

Run AviationCo.sql, which is the DDL to create the database, AviationCo.

Create a new table, YOURNAME. Include the primary key attribute, CUS_CODE.

-- Host: localhost
-- Generation Time: Oct 30, 2016 at 08:03 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE AVIATIONCO;

USE AVIATIONCO;

--
-- Database: `aviationco`
--

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

-- Table structure for table `aircraft`
--

CREATE TABLE IF NOT EXISTS `aircraft` (
`AC_NUMBER` varchar(5) NOT NULL DEFAULT '',
`MOD_CODE` varchar(10) DEFAULT NULL,
`AC_TTAF` float DEFAULT NULL,
`AC_TTEL` float DEFAULT NULL,
`AC_TTER` float DEFAULT NULL,
PRIMARY KEY (`AC_NUMBER`),
KEY `MOD_CODE` (`MOD_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `aircraft`
--

INSERT INTO `aircraft` (`AC_NUMBER`, `MOD_CODE`, `AC_TTAF`, `AC_TTEL`, `AC_TTER`) VALUES
('1484P', 'PA23-250', 1833.1, 1833.1, 101.8),
('2289L', 'C-90A', 4243.8, 768.9, 1123.4),
('2778V', 'PA31-350', 7992.9, 1513.1, 789.5),
('4278Y', 'PA31-350', 2147.3, 622.1, 243.2);

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

--
-- Table structure for table `charter`
--

CREATE TABLE IF NOT EXISTS `charter` (
`CHAR_TRIP` int(10) NOT NULL DEFAULT '0',
`CHAR_DATE` datetime DEFAULT NULL,
`AC_NUMBER` varchar(5) DEFAULT NULL,
`CHAR_DESTINATION` varchar(3) DEFAULT NULL,
`CHAR_DISTANCE` float DEFAULT NULL,
`CHAR_HOURS_FLOWN` float DEFAULT NULL,
`CHAR_HOURS_WAIT` float DEFAULT NULL,
`CHAR_FUEL_GALLONS` float DEFAULT NULL,
`CHAR_OIL_QTS` int(3) DEFAULT NULL,
`CUS_CODE` int(6) DEFAULT NULL,
PRIMARY KEY (`CHAR_TRIP`),
KEY `AC_NUMBER` (`AC_NUMBER`),
KEY `CUS_CODE` (`CUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `charter`
--

INSERT INTO `charter` (`CHAR_TRIP`, `CHAR_DATE`, `AC_NUMBER`, `CHAR_DESTINATION`, `CHAR_DISTANCE`, `CHAR_HOURS_FLOWN`, `CHAR_HOURS_WAIT`, `CHAR_FUEL_GALLONS`, `CHAR_OIL_QTS`, `CUS_CODE`) VALUES
(10001, '2014-02-05 00:00:00', '2289L', 'ATL', 936, 5.1, 2.2, 354.1, 1, 10011),
(10002, '2014-02-05 00:00:00', '2778V', 'BNA', 320, 1.6, 0, 72.6, 0, 10016),
(10003, '2014-02-05 00:00:00', '4278Y', 'GNV', 1574, 7.8, 0, 339.8, 2, 10014),
(10004, '2014-02-06 00:00:00', '1484P', 'STL', 472, 2.9, 4.9, 97.2, 1, 10019),
(10005, '2014-02-06 00:00:00', '2289L', 'ATL', 1023, 5.7, 3.5, 397.7, 2, 10011),
(10006, '2014-02-06 00:00:00', '4278Y', 'STL', 472, 2.6, 5.2, 117.1, 0, 10017),
(10007, '2014-02-06 00:00:00', '2778V', 'GNV', 1574, 7.9, 0, 348.4, 2, 10012),
(10008, '2014-02-07 00:00:00', '1484P', 'TYS', 644, 4.1, 0, 140.6, 1, 10014),
(10009, '2014-02-07 00:00:00', '2289L', 'GNV', 1574, 6.6, 23.4, 459.9, 0, 10017),
(10010, '2014-02-07 00:00:00', '4278Y', 'ATL', 998, 6.2, 3.2, 279.7, 0, 10016),
(10011, '2014-02-07 00:00:00', '1484P', 'BNA', 352, 1.9, 5.3, 66.4, 1, 10012),
(10012, '2014-02-08 00:00:00', '2778V', 'MOB', 884, 4.8, 4.2, 215.1, 0, 10010),
(10013, '2014-02-08 00:00:00', '4278Y', 'TYS', 644, 3.9, 4.5, 174.3, 1, 10011),
(10014, '2014-02-09 00:00:00', '4278Y', 'ATL', 936, 6.1, 2.1, 302.6, 0, 10017),
(10015, '2014-02-09 00:00:00', '2289L', 'GNV', 1645, 6.7, 0, 459.5, 2, 10016),
(10016, '2014-02-09 00:00:00', '2778V', 'MQY', 312, 1.5, 0, 67.2, 0, 10011),
(10017, '2014-02-10 00:00:00', '1484P', 'STL', 508, 3.1, 0, 105.5, 0, 10014),
(10018, '2014-02-10 00:00:00', '4278Y', 'TYS', 644, 3.8, 4.5, 167.4, 0, 10017);

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

--
-- Table structure for table `crew`
--

CREATE TABLE IF NOT EXISTS `crew` (
`CHAR_TRIP` int(6) DEFAULT NULL,
`EMP_NUM` int(4) DEFAULT NULL,
`CREW_JOB` varchar(20) DEFAULT NULL,
KEY `CHAR_TRIP` (`CHAR_TRIP`),
KEY `EMP_NUM` (`EMP_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `crew`
--

INSERT INTO `crew` (`CHAR_TRIP`, `EMP_NUM`, `CREW_JOB`) VALUES
(10001, 104, 'Pilot'),
(10002, 101, 'Pilot'),
(10003, 105, 'Pilot'),
(10003, 109, 'Copilot'),
(10004, 106, 'Pilot'),
(10005, 101, 'Pilot'),
(10006, 109, 'Pilot'),
(10007, 104, 'Pilot'),
(10007, 105, 'Copilot'),
(10008, 106, 'Pilot'),
(10009, 105, 'Pilot'),
(10010, 108, 'Pilot'),
(10011, 101, 'Pilot'),
(10011, 104, 'Copilot'),
(10012, 101, 'Pilot'),
(10013, 105, 'Pilot'),
(10014, 106, 'Pilot'),
(10015, 101, 'Copilot'),
(10015, 104, 'Pilot'),
(10016, 105, 'Copilot'),
(10016, 109, 'Pilot'),
(10017, 101, 'Pilot'),
(10018, 104, 'Copilot'),
(10018, 105, 'Pilot');

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

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

CREATE TABLE IF NOT EXISTS `customer` (
`CUS_CODE` int(6) NOT NULL DEFAULT '0',
`CUS_LNAME` varchar(15) DEFAULT NULL,
`CUS_FNAME` varchar(15) DEFAULT NULL,
`CUS_INITIAL` varchar(1) DEFAULT NULL,
`CUS_AREACODE` varchar(3) DEFAULT NULL,
`CUS_PHONE` varchar(8) DEFAULT NULL,
`CUS_BALANCE` float DEFAULT NULL,
PRIMARY KEY (`CUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `customer` (`CUS_CODE`, `CUS_LNAME`, `CUS_FNAME`, `CUS_INITIAL`, `CUS_AREACODE`, `CUS_PHONE`, `CUS_BALANCE`) VALUES
(10010, 'Ramas', 'Alfred', 'A', '615', '844-2573', 0),
(10011, 'Dunne', 'Leona', 'K', '713', '894-1238', 0),
(10012, 'Smith', 'Kathy', 'W', '615', '894-2285', 896.54),
(10013, 'Olowski', 'Paul', 'F', '615', '894-2180', 1285.19),
(10014, 'Orlando', 'Myron', '', '615', '222-1672', 673.21),
(10015, 'O''Brian', 'Amy', 'B', '713', '442-3381', 1014.56),
(10016, 'Brown', 'James', 'G', '615', '297-1228', 0),
(10017, 'Williams', 'George', '', '615', '290-2556', 0),
(10018, 'Farriss', 'Anne', 'G', '713', '382-7185', 0),
(10019, 'Smith', 'Olette', 'K', '615', '297-3809', 453.98);

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

--
-- Table structure for table `earnedrating`
--

CREATE TABLE IF NOT EXISTS `earnedrating` (
`EMP_NUM` int(4) NOT NULL,
`RTG_CODE` varchar(5) NOT NULL,
`EARNRTG_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`EMP_NUM`,`RTG_CODE`),
KEY `earnedrating_ibfk_2` (`RTG_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `earnedrating`
--

INSERT INTO `earnedrating` (`EMP_NUM`, `RTG_CODE`, `EARNRTG_DATE`) VALUES
(101, 'CFI', '1998-02-18 00:00:00'),
(101, 'CFII', '2005-12-15 00:00:00'),
(101, 'INSTR', '1993-11-08 00:00:00'),
(101, 'MEL', '1994-06-23 00:00:00'),
(101, 'SEL', '1993-04-21 00:00:00'),
(104, 'INSTR', '1996-07-15 00:00:00'),
(104, 'MEL', '1997-01-29 00:00:00'),
(104, 'SEL', '1995-03-12 00:00:00'),
(105, 'CFI', '1997-11-18 00:00:00'),
(105, 'INSTR', '1995-04-17 00:00:00'),
(105, 'MEL', '1995-08-12 00:00:00'),
(105, 'SEL', '1994-09-23 00:00:00'),
(106, 'INSTR', '1995-12-20 00:00:00'),
(106, 'MEL', '1996-04-02 00:00:00'),
(106, 'SEL', '1994-03-10 00:00:00'),
(109, 'CFI', '1998-11-05 00:00:00'),
(109, 'CFII', '2003-06-21 00:00:00'),
(109, 'INSTR', '1996-07-23 00:00:00'),
(109, 'MEL', '1997-03-15 00:00:00'),
(109, 'SEL', '1996-02-05 00:00:00'),
(109, 'SES', '1996-05-12 00:00:00');

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

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
`EMP_NUM` int(3) NOT NULL DEFAULT '0',
`EMP_TITLE` varchar(4) DEFAULT NULL,
`EMP_LNAME` varchar(15) DEFAULT NULL,
`EMP_FNAME` varchar(15) DEFAULT NULL,
`EMP_INITIAL` varchar(1) DEFAULT NULL,
`EMP_DOB` datetime DEFAULT NULL,
`EMP_HIRE_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`EMP_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`EMP_NUM`, `EMP_TITLE`, `EMP_LNAME`, `EMP_FNAME`, `EMP_INITIAL`, `EMP_DOB`, `EMP_HIRE_DATE`) VALUES
(100, 'Mr.', 'Kolmycz', 'George', 'D', '1942-06-15 00:00:00', '1987-03-15 00:00:00'),
(101, 'Ms.', 'Lewis', 'Rhonda', 'G', '1965-03-19 00:00:00', '1988-04-25 00:00:00'),
(102, 'Mr.', 'VanDam', 'Rhett', '', '1958-11-14 00:00:00', '1992-12-20 00:00:00'),
(103, 'Ms.', 'Jones', 'Anne', 'M', '1974-10-16 00:00:00', '2005-08-28 00:00:00'),
(104, 'Mr.', 'Lange', 'John', 'P', '1971-11-08 00:00:00', '1996-10-20 00:00:00'),
(105, 'Mr.', 'Williams', 'Robert', 'D', '1975-03-14 00:00:00', '2006-01-08 00:00:00'),
(106, 'Mrs.', 'Duzak', 'Jeanine', 'K', '1968-02-12 00:00:00', '1991-01-05 00:00:00'),
(107, 'Mr.', 'Diante', 'Jorge', 'D', '1974-08-21 00:00:00', '1996-02-01 00:00:00'),
(108, 'Mr.', 'Wiesenbach', 'Paul', 'R', '1966-02-14 00:00:00', '1994-11-18 00:00:00'),
(109, 'Ms.', 'Travis', 'Elizabeth', 'K', '1961-06-18 00:00:00', '1991-04-14 00:00:00'),
(110, 'Mrs.', 'Genkazi', 'Leighla', 'W', '1970-05-19 00:00:00', '1992-12-01 00:00:00'),
(818, 'Mr', 'Anderson', 'Richard', 'P', '1994-03-06 00:00:00', '2014-10-26 00:00:00');

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

--
-- Table structure for table `model`
--

CREATE TABLE IF NOT EXISTS `model` (
`MOD_CODE` varchar(10) NOT NULL DEFAULT '',
`MOD_MANUFACTURER` varchar(15) DEFAULT NULL,
`MOD_NAME` varchar(20) DEFAULT NULL,
`MOD_SEATS` float DEFAULT NULL,
`MOD_CHG_MILE` float DEFAULT NULL,
PRIMARY KEY (`MOD_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `model`
--

INSERT INTO `model` (`MOD_CODE`, `MOD_MANUFACTURER`, `MOD_NAME`, `MOD_SEATS`, `MOD_CHG_MILE`) VALUES
('C-90A', 'Beechcraft', 'KingAir', 8, 2.67),
('PA23-250', 'Piper', 'Aztec', 6, 1.93),
('PA31-350', 'Piper', 'Navajo Chieftain', 10, 2.35);

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

--
-- Table structure for table `pilot`
--

CREATE TABLE IF NOT EXISTS `pilot` (
`EMP_NUM` int(4) NOT NULL DEFAULT '0',
`PIL_LICENSE` varchar(25) DEFAULT NULL,
`PIL_RATINGS` varchar(25) DEFAULT NULL,
`PIL_MED_TYPE` varchar(1) DEFAULT NULL,
`PIL_MED_DATE` datetime DEFAULT NULL,
`PIL_PT135_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`EMP_NUM`),
KEY `EMP_NUM` (`EMP_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pilot`
--

INSERT INTO `pilot` (`EMP_NUM`, `PIL_LICENSE`, `PIL_RATINGS`, `PIL_MED_TYPE`, `PIL_MED_DATE`, `PIL_PT135_DATE`) VALUES
(101, 'ATP', 'SEL/MEL/Instr/CFII', '1', '2013-04-12 00:00:00', '2014-06-15 00:00:00'),
(104, 'ATP', 'SEL/MEL/Instr', '1', '2013-06-10 00:00:00', '2014-03-23 00:00:00'),
(105, 'COM', 'SEL/MEL/Instr/CFI', '2', '2013-02-25 00:00:00', '2014-02-12 00:00:00'),
(106, 'COM', 'SEL/MEL/Instr', '2', '2013-04-02 00:00:00', '2014-12-24 00:00:00'),
(109, 'COM', 'SEL/MEL/SES/Instr/CFII', '1', '2013-04-14 00:00:00', '2014-04-21 00:00:00');

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

--
-- Table structure for table `rating`
--

CREATE TABLE IF NOT EXISTS `rating` (
`RTG_CODE` varchar(5) NOT NULL DEFAULT '',
`RTG_NAME` varchar(50) DEFAULT NULL,
PRIMARY KEY (`RTG_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rating`
--

INSERT INTO `rating` (`RTG_CODE`, `RTG_NAME`) VALUES
('CFI', 'Certified Flight Instructor'),
('CFII', 'Certified Flight Instructor, Instrument'),
('INSTR', 'Instrument'),
('MEL', 'Multiengine Land'),
('SEL', 'Single Engine, Land'),
('SES', 'Single Engine, Sea');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `aircraft`
--
ALTER TABLE `aircraft`
ADD CONSTRAINT `aircraft_ibfk_1` FOREIGN KEY (`MOD_CODE`) REFERENCES `model` (`MOD_CODE`);

--
-- Constraints for table `charter`
--
ALTER TABLE `charter`
ADD CONSTRAINT `charter_ibfk_1` FOREIGN KEY (`AC_NUMBER`) REFERENCES `aircraft` (`AC_NUMBER`),
ADD CONSTRAINT `charter_ibfk_2` FOREIGN KEY (`CUS_CODE`) REFERENCES `customer` (`CUS_CODE`);

--
-- Constraints for table `crew`
--
ALTER TABLE `crew`
ADD CONSTRAINT `crew_ibfk_1` FOREIGN KEY (`CHAR_TRIP`) REFERENCES `charter` (`CHAR_TRIP`),
ADD CONSTRAINT `crew_ibfk_2` FOREIGN KEY (`EMP_NUM`) REFERENCES `employee` (`EMP_NUM`);

--
-- Constraints for table `earnedrating`
--
ALTER TABLE `earnedrating`
ADD CONSTRAINT `earnedrating_ibfk_1` FOREIGN KEY (`EMP_NUM`) REFERENCES `employee` (`EMP_NUM`),
ADD CONSTRAINT `earnedrating_ibfk_2` FOREIGN KEY (`RTG_CODE`) REFERENCES `rating` (`RTG_CODE`);

--
-- Constraints for table `pilot`
--
ALTER TABLE `pilot`
ADD CONSTRAINT `pilot_ibfk_1` FOREIGN KEY (`EMP_NUM`) REFERENCES `employee` (`EMP_NUM`);

Explanation / Answer

Hi,

Below is the code to create the table 'YOURNAME'. I have created some imaginary columns as those are relevent to the entity. You can add the contraint at the last to create a foreign key in the charter table.

-- Table structure for table `YOURNAME`
CREATE TABLE IF NOT EXISTS `YOURNAME` (
`CUS_CODE` int(6) NOT NULL DEFAULT '0',
`YOURLNAME` varchar(15) DEFAULT NULL,
`YOURFNAME` varchar(15) DEFAULT NULL,
`YOURINITIAL` varchar(1) DEFAULT NULL,
`YOURAREACODE` varchar(3) DEFAULT NULL,
`YOURPHONE` varchar(8) DEFAULT NULL,
`YOURBALANCE` float DEFAULT NULL,
PRIMARY KEY (`CUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `charter`
ADD CONSTRAINT `charter_ibfk_3` FOREIGN KEY (`CUS_CODE`) REFERENCES `YOURNAME` (`CUS_CODE`);

Related Questions

Edit View History People Window Help xe C| secure | https://www.awh.a ALEKS Lear
Question #554409
Edit View History Window Help 42% 17 Tue 2:18 AM a : gen wileyplus.com Assignmen
Question #2394495
Edit View History Window Help 423 Bowie State U r Four Three Major Classes of Ch
Question #590473
Edit View History Window Help Thu 7:28 PM Nasser A ani chegg.com Item 16 kinetic
Question #1786326
Edit View History Window Help triton,physics.ndsu.nodak.edu LON-CAPA Electric Fl
Question #2306477
Edit View History arks Window Help sapling learning.com 10 PM Question 3 of 32 S
Question #530179
Edit View History ezto.mheducation.com 2 Ha Mariya Company uses a job-order cost
Question #2556064
Edit View History marks Window Help proprofs.com Study Questi... Microsoft Of...
Question #188221
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Edit the following MATLAB code using interpolation Write a helper function, time
Next
Edit the following for ordering the references in a reference list. Choose the s

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.