Computer si
khairul30
MidTermExamIT507DatabaseWinter20_OG.docxMid Term Exam IT 507
Please Note: Submit a word document of your exam answers only DO not repeat the questions please.
For T/F please do not repeat the questions. Only write the question number and the correct answer for example: 1) T, 2) F…..
For Multiple choices only write the question number and the answer for example: 1) A 2) B
T/F (15 questions, 2 points each, total points 30)
1. Redundancy wastes space because you are storing different types of data in the same place.
2. A database will not only hold information about multiple types of entities, but also information about
the relationships among these multiple entities.
3. A relational database handles entities, attributes, and relationships by storing each entity in its own
table.
4. A query is a question represented in a way that the DBMS can recognize and process.
5. In an AND criterion, the overall criterion is true if either of the individual criteria is true.
6. The concept of grouping means that statistics will be calculated for individual records.
7. In many cases, you will need to create queries to select data from more than one table. To do so, it is
necessary to join the tables based on matching fields in corresponding columns.
8. Matrix algebra is a theoretical way of manipulating a database.
9. The union of tables A and B is a table containing all rows that are in either table A or table B or in
both table A and table B.
10. The intersection of two tables A and B is the set of all rows that are in table A but that are not in table
B.
11. The product of two tables is the table created by combining every row in the first table with every row
in the second table.
12. The join operation is the core operation of relational algebra because it is the command that allows
you to extract data from more than one table.
13. The SELECT command will allow you to write a query in SQL.
14. Removal of repeating groups is the starting point in the quest to create tables that are as free of problems as possible.
15. Relational Algebra is the foundation of relational Database.
MC ( 10 questions, 2 points each, total points 20 )
1. An integrity constraint is a rule that:
a. is kept in an external file
b. must be followed by data in the database
c. can be accessed by authorized users
d. can unintentionally be accessed by unauthorized users
2. __ is a property that lets you change the structure of the database without requiring you to change the
programs that access the database.
a. Database design c. Integrity constraint
b. Data independence d. Data dependence
3. The basic form of an SQL retrieval command is:
a. SELECT-WHERE-FROM c. SELECT-WHERE
b. CREATE-SELECT-FROM d. SELECT-FROM-WHERE
d
4. Which of the following queries will list the number, name, credit limit, and balance for all customers
with credit limits that exceed their balances.
|
a. |
SELECT CustomerNum, CustomerName FROM Customer WHERE CreditLimit>Balance ; |
|
b. |
SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer WHERE CreditLimit>Balance ; |
|
c. |
SELECT CustomerNum, CustomerName, Balance FROM Customer WHERE CreditLimit>Balance ; |
|
d. |
SELECT CustomerNum, CustomerName, CreditLimit, Balance FROM Customer ; |
5. The response of the DBMS to a query is the .
a. ad hoc query b. ad hoc response
c. query result set d. integrated view of the data
6. Which of the following queries will list the descriptions of all parts that are located in warehouse 3 or
for which there are more than 20 units on hand, or both.
|
a. |
SELECT Description FROM Customer WHERE Warehouse=’3’ AND OnHand>20 ; |
|
b. |
SELECT Description FROM Part WHERE Warehouse=’3’ OR OnHand>20 ; |
|
c. |
SELECT Description FROM Part WHERE Warehouse=’3’ ; |
|
d. |
SELECT Description FROM Part WHERE OnHand>20 ; |
7. Students and classes have a……. relationship.
a. one-to-one b. one-to-many
c. many-to-one d. many-to-many
8. Referential dictates that the foreign key must contain values that match the primary key in the related table, or must contain null.
a. integrity b. uniqueness
c. model d. attribute
9. The …….. relationship is the “relational model ideal.”
a. 1:1 b. 1:M
c. M:1 d. M:N
10. Information about tables in the database is kept:
|
a. |
in the system catalog |
|
b. |
in each table in the database |
|
c. |
in each row of each table in the database |
|
d. |
in an external file |
Short Essay and conceptual questions
Please Note: You can write your answers in the space provided for each question.
1. Suppose that you are using the Database composed of the two tables shown in the following
figure. Use these tables to answer part a, b.
Theater Database Tables
Table name: DIRECTOR
Table name: PLAY
a. Identify the primary keys for each table. ( 3 points )
b. Identify the foreign key. ( 2 points )
2. Use the following Tables to answer questions A,B,C .
PRODUCT and vendor Database Tables
Table name: PRODUCT
Table name: VENDOR
A. For each table, identify the primary key and the foreign key(s). If a table does
not have a foreign key, write None in the assigned space. ( 5 points )
|
TABLE |
PRIMARY KEY |
FOREIGN KEY(S) |
|
PRODUCT
|
Write your answer here |
Write your answer here
|
|
VENDOR |
Write your answer here |
Write your answer here |
B. Do the tables exhibit entity integrity? Answer Yes or No, and then explain
your answer. ( 5 points )
|
TABLE |
ENTITY INTEGRITY? Type Yes or No here |
EXPLANATION Explain the reason here |
|
PRODUCT |
Write your answer here |
Write your answer here
|
|
VENDOR |
Write your answer here |
Write your answer here
|
C. Do the tables exhibit referential integrity? Answer Yes or No, and then explain
your answer. Write NA (Not Applicable) if the table does not have a foreign
key. ( 5 points )
|
TABLE |
REFERENTIAL INTEGRITY |
EXPLANATION |
|
PRODUCT |
Write your answer here |
Write your answer here
|
|
VENDOR |
Write your answer here
|
Write your answer here
|
3) Define entity integrity and referential integrity the two requirements to ensure integrity of database? Provide an example. (15 points)
4) Explain multivalued attributes with the help of an examples. (15 points)
2
