代做CS 338 Computer Applications in Business: Database Assignment #2 – SQL调试R程序

CS 338 Computer Applications in Business: Database

Assignment #2 – SQL

Assignments and exams (both questions and answers) are intellectual properties of the instructor. Please do NOT share them outside of class or post them outside of Learn. Doing so without permission is a copyright infringement and also an academic misconduct.

Introduction

Assignments are essential in learning the material and preparing for exams. You can ask for help but make sure you write the answers in your own words. We will check for copies. You should submit your work to the dropbox on Learn, by 1pm on Oct. 30. Late submission will NOT be accepted. Only use SQL commands that are discussed in lectures to answer these questions. Questions answered with SQL commands outside of lecture material will be MARKED ZERO. One of the main objectives of this course (and the only objective of this assignment) is to help students learn and master the fundamentals of SQL. Using “advanced” SQL commands to bypass the fundamentals defeats this purpose. Also, DO NOT use VIEW as intermediate steps and DO NOT use INSERT, DELETE or UPDATE as part of your query (you are to search the data, not to modify it in anyway). There should be only one query statement for each question. These restrictions hold truey to both assignments and exams.

In order for Learn to properly render and display your file, when you submit your assignment (or exam) file, please ONLY upload PDF file or image (JPG, JPEG, BMP, PNG) file.

It is recommended that you type the answers. If you are to write the answers, make sure you write them neatly or the TAs will NOT mark them if they cannot read your handwriting.

Questions

All the queries must be formulated with respect to the Capital Ships database whose schema is explained on the last page. A sample database is also provided (CapitalShips.db). Note that the database only contains a very small set of data. Feel free to add more data so that you can properly test your answers. It is recommended that you use DB Browser for SQLite (https://sqlitebrowser.org). The tool is quite easy to use and you can find plenty of tutorials on YouTube. Here is one:https://www.youtube.com/watch?v=eJ- XmWbfeSg. You don’t have to use SQLite or even the sample database to complete the assignment. But if you are to learn SQL, it seems strange not to learn at least one SQL tool and run your codes on an actual database.

Write SQL expressions to answer the following questions. Try to follow the same format style as the examples in the lectures. Proper indentation makes your code much easier to read and understand.

1.   The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tones. List the ships that violated the treaty and participated in at least one WWII battles. Note that this excludes ships launched before 1921. The result should display the ship name, its class, displacement and country,

2.   Find the class(es) where no ship of that class was sunk. The result should list the class and its country.

3.   Find the pair of ships (A, B) that never fought in the same battle (regardless fight on the same side or against each other). Note that (A, B) and (B, A) is the same pair and only one should be showed. The result should display the ship names.

4.   Find the heaviest (in displacement) battleship(s) that survived the war (that is, among all the ships survived the war, find the heaviest bb). The result should display the ship name and its country.

5.   For country(ies) with both battleship and battlecruiser, find the country(ies) whose battleships(s) never engaged in any battle.

6.   Find the luckiest ship(s). That is, they were damaged in every battle they participated in but never sunk. This excludes ships never participated in any battle. The result should display the ship names and their types.

7.   Find the capital ship(s) that participated in all the battles in 1942.

8.   Find the country(s) that had ships participated in every battle. Say a country had two ships and there were total 4 battles. Ship 1 went to battle 1, 3 and 4, and Ship 2 went to battle 2 and 4. So this country had ships went to all 4 battles and this country should be listed in the result.

9.   Find the ships that survived the most brutal battle. Brutality of a battle is measured by the total number of ships sunk from both sides. The result should show ship names and their courtiers.

10. Which country suffered the most naval loss, UK or Japan? To find the answer, list the number of ships UK and Japan lost (sunk) during the war. The result should be displayed as follows.

UK	Jap
26	31

Marking (30 points)

Each question is worth 3 points and is marked according to the following scheme:

•   Good (no mistake or very small one): 3 points;

•   Ok (mostly correct): 2 points;

•   Poor (mostly wrong but has some merit): 1 point;

•   Wrong (totally off or no answer): 0 point.

Submission

The assignment file format should be DOC/DOCX (word document), PDF, TXT (text file), or JPG/JPEG (picture). It should be uploaded to the drop box on Learn: Submit -> Dropbox -> Assignment #2. The submission deadline is Oct 30th, 1pm .

WII Capital Ship Database

The Capital  Ships  database  stores  information  about  WWII  capital  ships.  It  involves  the  following relations:

Classes(className, type, country, numGun, bore, displacement)

Ships(name, class, launched) Battles(name, date)

Outcomes(shipName, battleName, result)

The foreign keys should be easy to deduce from the semantics and the attribute names. A capital ship could either be a battleship or a battlecruiser. Ships are built in “classes” (think of it as the data type of the ship) from the same design, and the class is usually named after the first ship of that class. The relation Classes records the name of the class, the type (bb for battleship and bc for battlecruiser), the country that designed the class, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tones). 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 the battles, and table Outcomes gives the result (sunk, damaged, or ok) for ships participated in battles.

Just in case you might need it, here is how to extract year from a date (%M for month and %D for day): SELECT         strftime ('%Y', date) AS Year

FROM            Battles;

Tables on the last page provide sample data for these four relations. Note that, these are just for your reference and make it easier to understand the semantics of the relations. Information in the tables is incomplete as it will make the tables too big. As such, there are some “dangling tuples” in this data, e.g., ships mentioned in Outcomes that are not mentioned in Ships. Your query should assume the information is complete (no “dangling tuples” in the actual database). A sample capital ship database in SQL is also provided so that you can test your answers.

Classes

className	type	country	numGun	bore	displacement
Bismarck	bb	Germany	8	15	42000
Iowa	bb	USA	9	16	46000
Kongo	bc	Japan	8	14	32000
North Carolina	bb	USA	9	16	37000
Renown	bc	Gt. Britain	6	15	32000
Revenge	bb	Gt. Britain	8	15	29000
Tennessee	bb	USA	12	14	32000
Yamato	bb	Japan	9	18	65000

Battles

name	date
North Atlantic	1941-05-24
Guadalcanal	1942-11-15
North Cape	1943-12-26
Surigao Strait	1944-10-25

Outcomes

shipName	battleName	result
Bismarck	North Atlantic	sunk
California	Surigao Strait	ok
Duke of York	North Cape	ok
Fuso	Surigao Strait	sunk
Hood	North Atlantic	sunk

King George V	North Atlantic	ok
Kirishima	Guadalcanal	sunk
Prince of Wales	North Atlantic	damaged
Rodney	North Atlantic	ok
Scharnhorst	North Cape	sunk
South Dakota	Guadalcanal	damaged
Tennessee	Surigao Strait	ok
Washington	Guadalcanal	ok
West Virginia	Surigao Strait	ok
Yamashiro	Surigao Strait	sunk