Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Material Type: Exam; Class: DATABASE SYSTEMS I; Subject: Computer Sci & Software En; University: Auburn University - Main Campus; Term: Fall 2007;
Typology: Exams
1 / 9
This page cannot be seen from the preview
Don't miss anything!
(1) Unlike assertions which must be, in principle, checked any time when data is modified, triggers allow users to specify when checking should occur. True False
(2) For any SQL query, there exists a unique translation into relational algebra. True False
(3) In SQL, the value NULL is ignored in any aggregation. True False
(4) In SQL, a view can be used like a stored relation in any operations. True False
(5) The relational data model was proposed by Dr. Edgar Codd in early 1970’s. True False
(6) Schema normalization not only reduces potential data redundancy but also enhances query efficiency. True False
(7) SQL is a declarative query language, in which we simply declare what we want, but not how to compute, in formulating a query. True False
(8) With the five basic operators (selection, projection, union, set-difference, and cross- product), relational algebra can compose most queries. Other operators are just syntactic sugar and can be derived from the basic operations. True False
(9) A natural join is a special case of an equijoin. True False
(10) An aggregation function, e.g., SUM and AVG, returns a value computed from a set of values. Thus, MIN and MAX are not aggregate, since they only return a single value. True False
Consider the following schema:
Suppliers ( sid: integer, sname: string, address: string) Parts ( pid: integer, pname: string, color: string) Catalog ( sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name. Write the following queries in relational algebra.
(1) Find the Supplier names of the suppliers who supply a red part that costs less than 100 dollors and a green part that costs less than 100 dolloars. (5 pts)
(2) Find the pids of the most expensive parts supplied by suppliers named Andy. (5 pts)
(3) Find the pids of parts supplied by at least two different suppliers. (5 pts)
(4) Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. (5 pts)
Consider the relational conceptual database schema used by company Goldfish, Inc. to store information about fish, species and fish tanks (sno is the species number and tno is the tank number):
Fish (fname varchar, fcolor varchar, fweight int, sno int, tno int); Species (sno int, sname varchar, sfood varchar); Tank (tno int, tname varchar, tcolor varchar, tvolume int);
Write SQL statements for the following queries:
(1) Find the names of tanks containing an orange fish heavier than 10 pounds. (5 pts)
(2) Find the names of blue tanks that contain a species that also appears in a green tank. (5 pts)
(3) Find the average fish weight for each species. (5 pts)
(4) Find the average tank volume by species, for those species that appear in two or more tanks. (5 pts)
Consider the following schema
People ( ssn: integer, name: string, phone: string, city: string) Purchase ( buyer-ssn: integer, seller-ssn: integer, store: string, pid: integer) Product ( pid: integer, name: string, price: real, category: string, cid: integer) Company ( cid: integer, name: string, stock-price: real, country: string)
Write the following queries in SQL.
(1) Find the names, stock prices, and countries of companies that have sold products to people living in Auburn but not to people living in Opelika. (5 pts)
(2) Lists the names of all companies that have sold at least two different products (that is, products with different pids ). (5 pts)
