1. Every relation in a database is of the entity type. (a) true (b) false 2. If
ID: 3814577 • Letter: 1
Question
1. Every relation in a database is of the entity type.
(a) true (b) false
2. If you need to use a table multiple times in a query, you have to use aliases to avoid the ambiguity.
(a) true (b) false
3. The set operations can be applied to any two tables.
(a) true (b) false
4. When we apply the UNION ALL operator on any two union-compatible tables with n rows and m rows, respectively, we always get a table with n + m rows.
(a) true (b) false
5. It is possible for a multiset table (with duplicate rows) to have a primary key if we consider the composite type primary keys.
(a) true (b) false
6. Which of the following options is the default option when an integrity violation occurs?
(a) RESTRICT (b) SET NULL (c) SET DEFAULT (d) CASCADE
7. We use two tables in a nested query. In order to understand the query, we think of an imaginary tuple variable. In which table do we use the tuple variable?
(a) only the inner table (b) only the outer table (c) both the inner table and the outer table
8. If we join two tables in which the join condition connects two columns from the two tables with different domains, do we get an error?
(a) yes (b) no
9. When we qualify the attribute names with the aliases of tables to prevent ambiguity, we do it by suffixing the aliases to the attribute names.
(a) true (b) false
10. Which of the following ways can NOT be used to remove the duplicate rows in tables?
(a) use DISTINCT (b) use the GROUP BY clause (c) use the UNIQUE function (d) use the UNION operator
11. Suppose that a table has 9 columns. It is known that we only need to provide values for 4 columns explicitly to insert a new row successfully. Assume that there are n columns with default values and there are m columns with NULL values. What is the minimum value for n + m? (You can consider the auto-increment case for the primary key.)
Ans:
12. Assume that in a table with 4 columns, the first column is the primary key, and the remaining three columns are non-key fields. If we want to construct a composite secondary key from the three non-key fields, how many different possible secondary keys can we have?
Ans:
13. Given a set with six elements: S = {a1, a2, a3, a4, a5, a6}, we consider the type of partitions of S which can be written as S = A B with |A| = |B| = 3. How many such partitions can you find?
Ans:
14. Assume that A, B, C are subsets of S, and S = A B C. We use the notation |S| to represent the cardinality (the number of elements in a set) of set S. If we have |A B| + |B C| + |C A| = 2 (|A| + |B| + |C|), then {A, B, C} must be a partition of set S.
(a) true (b) false
15. The requirement that the MAX and MIN functions can be applied on attributes with a domain is that this domain has an ordering.
(a) true (b) false
In the following questions, use the company database to write all your queries.
16. Retrieve all the employees with names who live in Chicago.
Ans:
17. Retrieve the names of those employees who have teenager children.
Ans:
18. List the name and address for those employees whose addresses satisfy the condition: The street number has exact 4 digits.
Ans:
19. Find the average number of dependents per employee in the company.
Ans:
20. List the first name and the last name of those employees whose supervisors’ supervisors are department managers.
Ans:
21. List the last name, the first name, and the year born for the employee who was born the earliest in the company.
Ans:
22. List the last name, the first name, and the number of projects for the manager whose department controls the maximum number of projects.
Ans:
23. Display the first name, the last name of the employee and the number of projects worked by this employee so that this employee has the maximum number of projects in his or her own department.
Ans:
24. Display the first name and the last name of the employee (who has child(ren)) and the number of children of the employee so that this employee has only son(s) or only daughter(s) .
Ans:
25. Retrieve the name of each employee who works on all the projects controlled by one of the departments.
Ans:
Explanation / Answer
Solution:
1. Every relation in a database is of the entity type.
(a) true
Explanation:
Each row of a relation corresponds to a record that contains data values for an entity.
2. If you need to use a table multiple times in a query, you have to use aliases to avoid the ambiguity.
(b) false.
Explanation:
You can use the same table multiple times within a single query.
The different ways to do the same are:
3. The set operations can be applied to any two tables.
(b) false.
Explanation:
You can combine multiple queries using the set operators UNION,INTERSECT, and MINUS. All set operators have equal precedence.For these operations to be meaningful , it is necessary that the two relations concerned be type compatible, i.e. should have the same number of attributes, and that the corresponding attribute in each relation should have the same data type.
4. When we apply the UNION ALL operator on any two union-compatible tables with n rows and m rows, respectively, we always get a table with n + m rows.
(a) true
Explanation:
UNION ALL is also used to combine the result set of two or more queries into single result set. But the UNION ALL operator also shows the common records in the final result set.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.