homework
Homework Assignment 6
Chapter 7
1. (6 points, 2 points ea)
Contrast the following terms:
a. base table; view
b. dynamic view; materialized view
c. catalog; schema
2. (6 points, 2 points ea) Distinguish among data definition commands, data manipulation commands, and data control commands.
3. (4 points) Describe the components and structure of a typical SQL environment.
4. (6 points, 2 points ea)
Drawing on material covered in prior chapters, explain the factors to be considered in deciding whether to create a key index for a table in SQL. (Hint: there are three.)
5. (6 points, 3 points ea)
a. What is the difference between COUNT, COUNT DISTINCT, and COUNT(*) in SQL? b. When will these three commands generate the same and different results?
6. (4 points, 2 points each)
a. What is the evaluation order for the Boolean operators (AND, OR, NOT) in an SQL command? b. How can one be sure that the operators will work in the desired order rather than in this prescribed order?
7. (6 points)
In what order are the clauses of an SQL statement processed?
The following are based on the class scheduling 3NF relations along with some sample data shown ERD depicted in Figure 6-11. Not shown in this figure are the data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.
8. (20 points, 2 points each) Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names as needed for your SQL version). Assume the following attribute data types:
STUDENT_ID (integer, primary key) STUDENT_NAME (25 characters) FACULTY_ID (integer, primary key) FACULTY_NAME (25 characters) COURSE_ID (8 characters, primary key) COURSE_NAME (15 characters) DATE_QUALIFIED (date) SECTION_ID (integer, primary key) SEMESTER (7 characters)
9. (9 points, 3 points ea)
Write SQL definition commands for each of the following queries: a.
Chapter 7
1. (6 points, 2 points ea)
Contrast the following terms:
a. base table; view
b. dynamic view; materialized view
c. catalog; schema
2. (6 points, 2 points ea) Distinguish among data definition commands, data manipulation commands, and data control commands.
3. (4 points) Describe the components and structure of a typical SQL environment.
4. (6 points, 2 points ea)
Drawing on material covered in prior chapters, explain the factors to be considered in deciding whether to create a key index for a table in SQL. (Hint: there are three.)
5. (6 points, 3 points ea)
a. What is the difference between COUNT, COUNT DISTINCT, and COUNT(*) in SQL? b. When will these three commands generate the same and different results?
6. (4 points, 2 points each)
a. What is the evaluation order for the Boolean operators (AND, OR, NOT) in an SQL command? b. How can one be sure that the operators will work in the desired order rather than in this prescribed order?
7. (6 points)
In what order are the clauses of an SQL statement processed?
The following are based on the class scheduling 3NF relations along with some sample data shown ERD depicted in Figure 6-11. Not shown in this figure are the data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.
8. (20 points, 2 points each) Write a database description for each of the relations shown, using SQL DDL (shorten, abbreviate, or change any data names as needed for your SQL version). Assume the following attribute data types:
STUDENT_ID (integer, primary key) STUDENT_NAME (25 characters) FACULTY_ID (integer, primary key) FACULTY_NAME (25 characters) COURSE_ID (8 characters, primary key) COURSE_NAME (15 characters) DATE_QUALIFIED (date) SECTION_ID (integer, primary key) SEMESTER (7 characters)
9. (9 points, 3 points ea)
Write SQL definition commands for each of the following queries: a.