Description
1. You’re developing a database to keep track of your video game collection. You want to store information
about the gaming systems you own, including the manufacturer, the name of the system, whether it
connects to the internet, and how many controllers you have for it.
You want to store information about
the games you own, including the name of the game, the year it was released, the publisher, which
system it’s for, and how much you paid for it. And you want to store information about the genres of
each game, keeping in mind that some games might fall into multiple genres (for example, ”Battlefield:
1942” might be in both the ”historical” genre, and the ”first-person shooter” genre), up to a potentially
unlimited number of genres for any given game.
(a) (6 points) Define three relations to store data about the gaming systems you own (System), the
games you own (Game), and which games are which genres (Genre). Make sure to define keys for
each relation (if appropriate).
(b) (2 points) Give two example tuples for the System relation
(c) (3 points) Give three example tuples for the Game relation
(d) (2 points) Give example tuples from the Genre relation for a game that is in both the ”Fantasy”
and the ”Adventure” genres
(e) (15 points) For each of the tables you created in part (a) above, write a CREATE TABLE statement
in SQL to create the table, including appropriate keys. (5 points each)
Page 2 CSCI 4380
2. Assume the existence of a database with the following relations:
Ingredient(name, calories, cost, containsNuts)
Recipe(name, ingredientName, amount)
Menu(recipeName, season)
which is used by a restaurant to manage its menu items. Assume that Recipe.ingredientName is a
foreign key to Ingredient.name, and that Menu.recipeName is a foreign key to Recipe.name. Ingredient.containsNuts is a boolean, indicating whether the given ingredient contains nuts (e.g., Peanut Oil).
Assume all amounts are in the same unit (e.g., grams).
Write relational algebra expressions for the following:
(a) (3 points) List the names of all recipes on the menu for the Fall season
(b) (3 points) List the names of all of the ingredients in Pizza
(c) (3 points) List all of the recipes that are nut-free
(d) (3 points) List all of the recipes on the Spring menu that contain nuts
Page 3 CSCI 4380
