CS432/433

Assignment 4

SQL Assignment

Due: Oct 16, 1998

Must be done individually

INTRODUCTION

On World Wide Web there are more than 100 different web pages dedicated to Star Wars. However, the answer to a specific trivia question is sometimes difficult to find, precisely because of the abundance of information. In this assignment we will be creating a new Star Wars Trilogy database for the trivia lovers. We decided to begin with the time tables - who went where and when. We have started to input the information into the database. Your task will be to create SQL queries that answer some of the often asked questions.(or questions that we found most fascinating and asked often)

A database has been created using Microsoft Access in the UGLab. The database currently contains :

Tables

Characters: contains information about the character's Name (primary key), Race (if known), Homeworld (if known) and Affiliation(rebels/empire/neutral/free-lancer).

Planets: contains information about the planet's Name (primary key), it's Type (gas/swamp/forest/handmade/ice/desert), and it's Affiliation(rebels/empire/neutral)

TimeTable: contains Character's Name, Planet's Name, Movie in which the character visited the planet and the time of arrival and departure from the planet.The primary key is Character's Name, Planet's Name and Movie. Movie 1 represents The Star Wars, Movie 2 represents Empire Strikes Back, Movie 3 represents Return of the Jedi. Each movie has been divided into 10 time chunks and these chunks are used to define time of arrival and departure. So that, if Darth Vader visited Bespin (Cloud City) in Empire Strikes Back from the middle of the movie till it's end, the record of it will look like this:

Character's Name Planet's Name Movie Time of Arrival Time of Departure
Darth Vader Bespin 2 5 10

Queries

Simple Query: a simple example of a SQL Query.

 

THE TASK

Get acquainted with Access and create SQL queries that answer the following questions

  1. Find all characters that have been on all neutral planets
  2. Find distinct names of the planets visited by empire affiliated humans.
  3. For each character and for each neutral planet, how much time total did the character spend on the planet?
  4. On which planets and in which movies has Luke been at the same time on the planet as Darth Vader.
  5. Find humans that visited desert planets and droids that visited swampy planets. List the movies when it happened and the names of the characters. The output should be sorted by the movie and the character's name.

 

What to submit

  1. The hard copy of the results of the queries

    A copy of the SQL queries. As Access does not let you print out the SQL queries, you can submit a handwritten copy or cut and paste into your favorite wordprocessor and print them out.

 

How to use Microsoft Access

  1. Copy the database StarWars from the folder CS432/SQL Assignment/
  2. To start Access from the Start menu go to the Programs folder then off97pro folder and Microsoft Access.
  3. In Access open your database as you would open a file in Microsoft Word.

To look at the existing tables:

To look at the existing queries:

To create a new query:

May the force be with you.