Self-test RDBMS concepts
Document: E1034Test.fm
10/02/2010
ABIS Training & Consulting
P.O. Box 220
B-3000 Leuven
Belgium
TRAINING & CONSUL
TRAINING &
TING
CONSUL

---

INTRODUCTION SELF-TEST RDBMS CONCEPTS
With this self-test you can see for yourself if you have enough background on relational
database concepts to follow more advanced courses in this domain. The test is based on
topics treated in the RDBMS concepts course.
This test consists of 20 multiple choice questions. Write down your responses and com-
pare them with the solutions given on the last page. This test will take about fifteen min-
utes.
10/02/2010
Self-test RDBMS concepts
2

---

QUESTIONS SELF-TEST RDBMS CONCEPTS
1.
Indicate whether this statement is true or false.
The domain of a column containing the month number of the date of birth of employees
differs from the domain of a column with the day number of that date.
O
(a)
true
O
(b)
false
Het domein van “maandnummers” is 1..12, dus verschillend van het domein van dagnummers.
Het datatype daarentegen is mogelijk hetzelfde (nl. INT of SMALLINT).
2.
Indicate whether this statement is true or false.
Restricting the domain of a numeric column to non-negative values can be implemented
with a check constraint.
O
(a)
true
O
(b)
false
Dit is zelfs de enige manier (naast een before-trigger), vermits er geen datatype “positive int” bestaat.
3.
Indicate whether this statement is true or false.
The restriction on the domain for the date of birth must be implemented with a check con-
straint when the aim is that for every employee the date of birth must be known.
O
(a)
true
O
(b)
false
Een check constraint kan geen “NOT NULL”-conditie specifiëren: daarvoor dient de NOT NULL-optie.
4.
Indicate whether this statement is true or false.
A NULL value means that a value is not known, or that a feature does not apply to a cer-
tain individual.
O
(a)
true
O
(b)
false
5.
Indicate whether this statement is true or false.
The primary key column must contain unique values.
O
(a)
true
O
(b)
false
Uiteraard in de onderstelling dat de PK uit slechts 1 kolom bestaat; vandaar het bepaald lidwoord.
6.
Indicate whether this statement is true or false.
The primary key column may contain NULL values.
O
(a)
true
O
(b)
false
10/02/2010
Self-test RDBMS concepts
3

---

7.
Indicate whether this statement is true or false.
A foreign key column must contain unique values.
O
(a)
true
O
(b)
false
8.
Indicate whether this statement is true or false.
A foreign key column may contain NULL values.
O
(a)
true
O
(b)
false
9.
Indicate whether this statement is true or false.
A view is an alternative method to store data.
O
(a)
true
O
(b)
false
Een VIEW is een virtuele tabel, dus een alternatief zicht op data, geen alternatieve data-opslag.
10.
Indicate whether this statement is true or false.
A stored procedure is only executed when asked for explicitly.
O
(a)
true
O
(b)
false
Dit in tegenstelling tot b.v. een trigger.
11.
Indicate whether this statement is true or false.
A trigger can be used instead of a foreign key to implement referential integrity.
O
(a)
true
O
(b)
false
Dit is uiteraard minder gebruikelijk: een before-trigger kan een andere tabel ondervragen met een subquery, en de ingevoerde waarde weigeren indien die niet in de output van de subquery voorkomt.
12.
Indicate whether this statement is true or false.
A trigger can be used instead of a check constraint.
O
(a)
true
O
(b)
false
Een before-trigger kan exact dezelfde conditie meegegeven worden als een check-constraint. Omgekeerd echter zijn er trigger-condities die niet met een check-constraint kunnen geïmplementeerd worden: wanneer de conditie verwijst naar andere rijen dan de pas ingevoegde of gewijzigde rij.
10/02/2010
Self-test RDBMS concepts
4

---

13.
Can the following construction be used in a relational system, if we want to assign a single
name (first name and last name) and one or more telephone numbers to a certain person?
person number
name
phone number
1
Wouters Peter
016/549216, 0498/526984
2
Peeters Jan
0488/965235
3
Janssens Wouter
03/5214963, 0478/625398
O
(a)
yes
O
(b)
no
Een veld kan geen lijst bevatten, althans niet in de eerste normaalvorm.
14.
Can the following construction be used in a relational system, if we want to assign a single
name (first name and last name) and one or more telephone numbers to a certain person?
person number
name
phone number
1
Wouters Peter
016/549216
1
Wouters Peter
0498/526984
3
Janssens Wouter
03/5214963
3
Janssens Wouter
0478/625398
2
Peeters Jan
0488/965235
O
(a)
yes
O
(b)
no
Eenzelfde persoonsnummer (en/of naam) wijst op dezelfde “persoon”, telkens met een ander telefoonnummer.
15.
Can the following construction be used in a relational system, if we want to assign a single
name (first name and last name) and one or more telephone numbers to a certain person?
person number
name
phone number
1
Wouters
016/549216
1
Peter
016/549216
2
Peeters
0488/965235
2
Jan
0488/965235
3
Janssens
03/5214963
3
Wouter
03/5214963
O
(a)
yes
O
(b)
no
Nu is er geen mogelijkheid meer om voornaam en familienaam van elkaar te onderscheiden.
10/02/2010
Self-test RDBMS concepts
5

---

16.
Can the following construction be used in a relational system, if we want to assign a single
name (first name and last name) and one or more telephone numbers to a certain person?
person number
phone number
person number
name
1
016/549216
1
Wouters Peter
1
0498/526984
2
Peeters Jan
2
0488/965235
3
Janssens Wouter
3
03/5214963
3
0478/625398
O
(a)
yes
O
(b)
no
De eerste kolom van elke tabel zorgt voor de koppeling: FK en PK, resp.; de PK van de eerste tabel is uiteraard niet de eerste kolom: hetzij de tweede kolom, hetzij beide kolommen samen.
17.
Can the following construction be used in a relational system, if we want to assign a single
name (first name and last name) and one or more telephone numbers to a certain person?
PK
Foreign Key
Primary Key
ID
person number
phone number
person number
name
1
2
0488/965235
1
Wouters Peter
2
1
016/549216
2
Peeters Jan
3
3
03/5214963
3
Janssens Wouter
4
1
0498/526984
5
3
0478/625398
O
(a)
yes
O
(b)
no
Rijvolgorde is niet relevant. De aanwezigheid van een artificiële PK is niet nodig maar wel mogelijk.
18.
Can the following construction be used in a relational system?
The domain of the “language” column is {N,F,E}.
person number
name
language
1
Wouters Peter
N
2
Peeters Jan
N
3
Janssens Wouter
F
O
(a)
yes
O
(b)
no
Niet elke waarde van het domein hoeft voor te komen.
10/02/2010
Self-test RDBMS concepts
6

---

19.
Can the following construction be used in a relational system?
The delete rule for both primary keys is ‘on delete cascade’.
PK
Foreign Key
Primary Key
ID
person number
phone number
person number
name
1
1
016/549216
1
Wouters Peter
2
1
0498/526984
2
Peeters Jan
3
2
0488/965235
3
Janssens Wouter
4
3
03/5214963
5
3
0478/625398
O
(a)
yes
O
(b)
no
20.
Can the following construction be used in a relational system?
The domain of the “married” column is {Y,N}.
person number
name
married
1
Wouters Peter
N
2
Peeters Jan
NULL
3
Janssens Wouter
Y
O
(a)
yes
O
(b)
no
Een NULL-waarde is mogelijk, onafhankelijk van de domeinbeperkingen.
10/02/2010
Self-test RDBMS concepts
7

---

EVALUATION.
Here are the correct answers to all questions:
1. a
2. a
3. b
4. a
5. a
6. b
7. b
8. a
9. b
10. a
11. a
12. a
13. b
14. a
15. b
16. a
17. a
18. a
19. a
20. a
Give yourself 1 point for each correctly answered question.
If you have less than 14 correct answers, we advise you to follow the RDBMS concepts
course.
If you have more than 17 correct answers, it is not useful anymore to follow this course.
You can immediately register for other database courses (SQL fundamentals, DB2, Ora-
cle, MySQL or SQLServer ...). You may check your SQL background first with our self-
test SQL and our self-test SQL workshop.
If your score is between 14 and 17, you have already some background knowledge on
relational databases, but you will certainly still learn a lot in the RDBMS concepts course.
10/02/2010
Self-test RDBMS concepts
8

---

Document Outline

• Introduction Self-test RDBMS concepts
• Questions Self-test RDBMS concepts
• 1. Indicate whether this statement is true or false.
• 2. Indicate whether this statement is true or false.
• 3. Indicate whether this statement is true or false.
• 4. Indicate whether this statement is true or false.
• 5. Indicate whether this statement is true or false.
• 6. Indicate whether this statement is true or false.
• 7. Indicate whether this statement is true or false.
• 8. Indicate whether this statement is true or false.
• 9. Indicate whether this statement is true or false.
• 10. Indicate whether this statement is true or false.
• 11. Indicate whether this statement is true or false.
• 12. Indicate whether this statement is true or false.
• 13. Can the following construction be used in a relational system, if we want to assign a single ...
• 14. Can the following construction be used in a relational system, if we want to assign a single ...
• 15. Can the following construction be used in a relational system, if we want to assign a single ...
• 16. Can the following construction be used in a relational system, if we want to assign a single ...
• 17. Can the following construction be used in a relational system, if we want to assign a single ...
• 18. Can the following construction be used in a relational system? The domain of the language͎ c...
• 19. Can the following construction be used in a relational system? The delete rule for both prima...
• 20. Can the following construction be used in a relational system? The domain of the married͎ co...
• Evaluation.

---