1、Database Lab work assignment 1(1) Write an SQL database schema for your database, using the CREATE TABLE commands. Pick suitable datatypes for each attribute. Page 85 of the text gives you the principal options regarding types, but Oracle likes you to use VARCHAR2 for character strings. Show the res
2、ponse of sqlplus to a request to describe each of your relation schemas. For example, to see the schema for relation Foo type DESCRIBE Foo;(2) Execute some INSERT commands to insert tuples into one of your relations. Show the response of sqlplus and the relation that results when you issue a SELECT
3、* command. (3) When writing a program to fabricate data, there are two important points to keep in mind: Although you have not (yet) declared keys in your relations, in many cases you probably know that an attribute or set of attributes in a relation will serve as a key. If so, be sure not to genera
4、te duplicate values for these attributes. Your database almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNo thats expected to join with attribute number in relation Course. In generating data, be sure to
5、generate values that actually do join-otherwise all of your interesting queries will have empty results! Problem Set1. Consider a database with the following relations: Dish盘菜 (food 食物, cuisine 烹调风格)Ingredients成分表 (food 食物, ingredient 成分, quantity 数量)ShoppingList采购表 (cuisine 烹调风格, ingredient 成分, qua
6、ntity数量)卡路里表 Calories (ingredient成分, quantity 数量, calories卡路里)Write the following queries. (a) Find the foods that include more than 16 units of butter. (b) Find the cuisines that use cumin as an ingredient (in at least one dish). (c) Change the ingredients for foods so that they include no more tha
7、n 16 units of butter. (If they already include more than 16 units of butter, then change it to 16 units of butter.) (d) Remove beef from all Indian (cuisine) dishes. (e) Create a summary table, ShoppingList that contains the total quantity of each ingredient used by all the dishes for each cuisine.
8、(f) For each dish, set the number of calories equal to the total number of calories for the entire dish. Note that the quantity in relation Calories need not match the quantity in Ingredients, so you need to compute calories per unit2. Consider a database with the following relations: Cars (license,
9、 owner, model)Motorcycle (license, owner, model)Ownership(owner, numberOfCars, numberOfMotorcycles)Write the following queries. (a) List people who own a car but do not own a motorcycle. (b) List people who own a car and also own a motorcycle. (c) List people who own a car but do not own a motorcycle. (d) List the number of cars owned by each person.
