Order ready-to-submit essays. No Plagiarism Guarantee!
Note: All our papers are written from scratch by human writers to ensure authenticity and originality.
CIS 363 – Enterprise Database Management – Assignment #2 – More SQL
NEED HELP WRITING AN ESSAY
Tell us about your assignment and we will find the best writer for your project.
Get Help Now!Grading: The weight of this assignment is 10% of the course grade
Consider the same database schema of Assignment #1, which is concerned with World War II capital ships. For your convenience, Assignment 1 Specifications are reproduced here again. The DB Schema involves the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class. The relation Classes records the name of the class, the type (’bb’ for battleship or ’be’ for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle.
Some sample data for these relations is shown on the last two pages of this document.
Furthermore, assume that the data types of the above attributes are consistent with the following Standard SQL declarations:
CREATE TABLE Classes (
class CHAR(20),
type CHAR(5),
country CHAR(20),
numGuns INTEGER,
bore DECIMAL(3,1),
displacement INTEGER
);
CREATE TABLE Ships (
name CHAR(30),
class CHAR(20),
launched INTEGER
);
CREATE TABLE Battles (
name CHAR(30),
date DATE
);
CREATE TABLE Outcomes (
ship CHAR(30),
battle CHAR(30),
result CHAR(10)
);
CIS 363 Spring 2019
Write the following queries and apply them to the above database:
a) Find those countries that have both battleships and battlecruisers.
b) Find those ships that were damaged in one battle, but later fought in another.
c) Find the classes of ships, at least one of which was sunk in a battle.
d) Find the names of the ships whose number of guns was the largest for those ships of the same bore.
e) Find for each class the year in which the first ship of that class was launched. Clearly, the above five queries weigh at least as much as the ten queries of Assignment 1.
Create one pdf document demonstrating your answers to the above ten MySQL queries and show the results of those queries when applied to the sample data given below. Submit this pdf file into the “Assignment 2 Drop Box” of the course’s Canvas website.
CIS 363 Spring 2019
CIS$132( ( Summer(2015(
(
( (
CIS 363 Spring 2019 CIS$132( ( Summer(2015(
( (d)(Sample data for relation Ships
(
