CS 582 — Database Management System II — Spring 2007

A Survey as A Quick Quiz

NAME:

Note: This survey serves as a quiz for me to learn about your background. I expect that you finish this quiz within 15–20 minutes. In what follows, assume that we are talking about relational databases (where a relation is often called a table, tuples are known as rows etc.). Please answer the following:

1. How many keys can a table have?

2. Are there tables without a key?

3. Do you know how to create a web site?

4. Which DBMS did you use in learning SQL?

5. Did you use PHP with Oracle?

6. Assume that we have the following two tables:

        Student(StudID, Name, Address, Status)
        Grade(StudID, Class, Semester, Grade)

where Status can either be “Graduate” or “Undergraduate”, StudID is unique and is the key of the table

  Student StudID, Class, Semester is the key of the table Grade, and StudID (in Grade) is a

foreign key referencing StudID (in Student). Do the following:

• Write a relational algebra expression that list all graduate students.

Answer:

• Write a relational algebra expression that list the classes of “John Murphy” whose student ID is 123. Answer:

• Write a SQL query that finds all students with the GPA 4.0.

Answer:

• Write a SQL query that lists all undergraduate students.

Answer:

7. It is known that the response time to a query (from the DBMS) depends on the number of rows that need to be loaded from the harddisk to the main memory. It is also known that a query can be answered in several ways. Assume that the Student and Grade contain 16,000 and 160,000 rows respectively. Assuming that the time needed to load 4,000 rows is 1s. Estimate the best possible response time to the following queries:

(a) SELECT * FROM STUDENT

Answer:

(b) SELECT * FROM STUDENT WHERE StudID<100

Answer:

If you do not complete this quiz within 20 minutes, please revise the materials. It will be important.