9.7. Exercises for Unary Operators¶
Here are some exercises to ensure mastery of what you have read so far. For each one, first try to write down in the short answer box the name of each result relation. Then use the show button to reveal what we called them. If we don’t match, consider whether your version is reasonable or whether the one given is more precise. After that, you can practice determining the identifier and the base of each result relation.
Try as few or as many as you wish for practice, but be sure to try at least one from each operator.
9.7.1. Filter¶
What would be the result relation name of each of the following charts?
Click show to see the answer and then practice knowing what the identifier of the result is.
The following image of 4 precedence charts using Filter will be repeated again below.
Q-1: Write the result relation name for A, B, C, and D
Now check yours with our versions by showing them. Note that there are multiple ways to be precise or accurate, so your answers may be just fine even if they are not just like those you see revealed. Notice haw we even showed different ways of describing equality in A versus C and D. Also, I personally prefer the use of ‘whose’ in A and B revealed below; what about you?
Achievement whose achDate > 2017.
TeamSkill whose teamSize is 3.
Aspiration with its aspiredProficiency being ‘5’.
Achievement with its skillCode being ‘B2’.
Here are the above 4 charts again:
Now practice knowing the base noun and the identifying attributes that are indicative of that base. Show each question to try the exercise for each result relation. (This is so you can hide and effectively collapse them out of the way.)
Result relation A:
Result relation B:
Result relation C:
Result relation D:
Note
The key idea you should be owning after these exercises is that the base of the result relation after a Filter does not change, and thus neither does its identifier, made up of the input relation’s identifying attributes(s).
9.7.2. Project¶
What would be the result relation name of each of the following charts?
Q-6: Write the result relation name for A, B, C, and D
Now check yours with our versions by showing them. Note that there is one straightforward pattern for a precise name for the result with Project, so your answers should be just like ours.
achId and proficiency of Achievement.
creatureId, SkillCode of Aspiration.
SkillCode of TeamSkill.
townName of Town.
Here are the above 4 charts again:
Now practice knowing the base noun and the identifying attributes that are indicative of that base. Show each one to try it.
Result relation A:
Result relation B:
Result relation C:
Result relation D:
Note
The key idea you should be owning after these exercises is that the base of the result relation after a Project does not change, and thus neither does its identifier, made up of the input relation’s identifying attributes(s).
9.7.3. Reduce¶
Next, let us practice some naming of result relations for reduce.
Tip
Because with Reduce we will now be declaring new identifying columns, we must remember this: No columns in an identifier of a result relation can be null. Note below what the achievement data, which will be used in some of the following examples, contains:
achId |
creatureId |
skillCode |
proficiency |
achDate |
test_townId |
|---|---|---|---|---|---|
1 |
1 |
A |
3 |
2020-07-24 21:37:53 |
a |
2 |
1 |
E |
3 |
2017-09-15 15:35:00 |
d |
3 |
1 |
A |
3 |
2018-07-14 14:00:00 |
a |
4 |
1 |
E |
3 |
2020-07-24 21:37:53 |
d |
5 |
5 |
Z |
6 |
2016-04-12 15:42:30 |
t |
6 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
7 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
8 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
9 |
4 |
Z |
3 |
2018-06-10 00:00:00 |
a |
10 |
11 |
PK |
10 |
1998-08-15 00:00:00 |
le |
11 |
12 |
PK |
10 |
2016-05-24 00:00:00 |
le |
12 |
13 |
PK |
10 |
2012-08-06 00:00:00 |
le |
13 |
8 |
PK |
1 |
NULL |
t |
14 |
9 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
15 |
10 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
16 |
7 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
17 |
9 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
18 |
4 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
19 |
5 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
20 |
2 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
21 |
1 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
22 |
9 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
23 |
13 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
24 |
7 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
Q-11: Why is the shape of the result labeled A different than the others? How might your try to name it?
Click Show to compare your answer to our reason.
Since Achievement’s achDate contains some NULLs and identifier data values cannot be null, this is a bad reduce producing a table with no identifier that is tricky to name. Perhaps something like this: “Possibly null achDate of Achievement table”. (If there were not NULLs in the achDate column, A would be achDate of Achievement and would be a square box.)
Q-12: What are the names of result relations B, C, and D?
Click show to see what we suggest.
roleName of Contribution
townNickname of Town
TeamSize of one or more TeamSkills
In the above names for the result relations, notice how we could have stated A and B like D. The version in D makes it easier to see that there may be fewer rows in the result relation than in the original input relation.
Here are the above 4 charts again:
Now practice knowing the base noun and the identifying attributes that are indicative of that base. Show each one to try it.
Result relation B:
Result relation C:
Result relation D:
Note
The concepts to master by doing these exercises are that the base of the result relation always changes from that of the input relation when doing a Reduce operation and there are cases we can try that result in a Bad Reduce if we are not careful about the columns we choose to identify the result relation.
9.7.4. Group¶
With the following Group exercises, we have several cases, so we will display them one at a time, rather than together as we did for the previous examples.
Case 1:
Q-16: The result relation name A is:
The correct answer:
Proficiency, CreatureCount of AchievementProficiency
Case 2:
Q-18: The result relation name B is:
- The correct answer:
-
roleName, roleCount of ContributionRoleName
Case 3:
Q-20: The result relation name C is:
The correct answer:
averageTeamSize of TeamSkill
Case 4:
Q-22: The result relation name D is:
- The correct answer:
-
country, creatureCount of TownCountry
Case 5: Let’s try something just a bit different.
Case 6:
- count of Creatures
- 'count of Creatures' is pithy and reasonably correct way of expressing the result relation, though C and D are nice because it provides a column name that you can use for further analysis, which often happens.
- Creature
- The result relation from a group is never the same as the input relation.
- creatureCount of Creature
- Giving a name of creatureCount to the single resulting column is helpful for future use of that result relation as an input relation.
- creatureCountOfCreature
- Some analysts prefer one single long name, since this is returning a single-column relation.
Q-26: Which of these is a reasonable name for H (there is more than one correct answer)?
- True
- Yes- the point is that the whole name is the base and will be underlined because the result relation contains one column with one row.
- False
- Review the examples of group over nothing in section 9.7 and notice how we underlined the whole name of the result.
Q-27: The base of the result relation is the same as the answer for the previous question.
Case 7:
Q-28: The name I is:
Choose Show to reveal a possible way to name the above result.
This is an example of a bad carry in a Group. This makes it hard to name the resulting table, especially because of what the DBMS will likely produce. Something like this might be somewhat accurate, but not a sensical result to give a client:
creatureId, countOFAchievedSkills, townOfFirstAchievedSkill of AchievingCreature.
You could try the SQL for this bad carry example in the SQL query box below to see what happens for creatures with more than one achieved skill.
achId |
creatureId |
skillCode |
proficiency |
achDate |
test_townId |
|---|---|---|---|---|---|
1 |
1 |
A |
3 |
2020-07-24 21:37:53 |
a |
2 |
1 |
E |
3 |
2017-09-15 15:35:00 |
d |
3 |
1 |
A |
3 |
2018-07-14 14:00:00 |
a |
4 |
1 |
E |
3 |
2020-07-24 21:37:53 |
d |
5 |
5 |
Z |
6 |
2016-04-12 15:42:30 |
t |
6 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
7 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
8 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
9 |
4 |
Z |
3 |
2018-06-10 00:00:00 |
a |
10 |
11 |
PK |
10 |
1998-08-15 00:00:00 |
le |
11 |
12 |
PK |
10 |
2016-05-24 00:00:00 |
le |
12 |
13 |
PK |
10 |
2012-08-06 00:00:00 |
le |
13 |
8 |
PK |
1 |
NULL |
t |
14 |
9 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
15 |
10 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
16 |
7 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
17 |
9 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
18 |
4 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
19 |
5 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
20 |
2 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
21 |
1 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
22 |
9 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
23 |
13 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
24 |
7 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
Case 8:
Tip
Remember: No columns in an identifier of a result relation can be null. Note above when you show the answer, the middle data values tab can remind you what the achievement data contains.
Q-30: The result relation name J is:
- The correct answer:
-
creatureId, test_townId, Count of number of skills achieved by creature in a test_town
For this interesting query, it would be good for you to practice the SQL version. Change the basic SQL query below to match this precedence chart.
achId |
creatureId |
skillCode |
proficiency |
achDate |
test_townId |
|---|---|---|---|---|---|
1 |
1 |
A |
3 |
2020-07-24 21:37:53 |
a |
2 |
1 |
E |
3 |
2017-09-15 15:35:00 |
d |
3 |
1 |
A |
3 |
2018-07-14 14:00:00 |
a |
4 |
1 |
E |
3 |
2020-07-24 21:37:53 |
d |
5 |
5 |
Z |
6 |
2016-04-12 15:42:30 |
t |
6 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
7 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
8 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
9 |
4 |
Z |
3 |
2018-06-10 00:00:00 |
a |
10 |
11 |
PK |
10 |
1998-08-15 00:00:00 |
le |
11 |
12 |
PK |
10 |
2016-05-24 00:00:00 |
le |
12 |
13 |
PK |
10 |
2012-08-06 00:00:00 |
le |
13 |
8 |
PK |
1 |
NULL |
t |
14 |
9 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
15 |
10 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
16 |
7 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
17 |
9 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
18 |
4 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
19 |
5 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
20 |
2 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
21 |
1 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
22 |
9 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
23 |
13 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
24 |
7 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
Note
The concepts to master by doing these exercises are that the base of the result relation always changes from that of the input relation when doing a Group operation and there are cases we can try that result in a Bad Group if we are not careful about the columns we choose to identify the result relation.