I am stuck on this Question for Information Modeling and Relational Databases: 2
ID: 3801962 • Letter: I
Question
I am stuck on this Question for Information Modeling and Relational Databases:
2. Assume the following conceptual schema is stored. Constraints apply to each database state. C1 means that each person referred to in the database must have his/her fitness rating recorded there. C3 says the possible fitness values are whole numbers from 1 to 10. C4 means no person can be recorded as expert at more than one sport, and C5 says a person can be recorded as being an expert at a sport only if that person is also recorded as playing the same sport.
Reference schemes: Person(.firstname); Sport(.name); FitnessRating(nr)
Base fact types: F1 Person has FitnessRating
F2 Person plays Sport
F3 Person is expert at Sport
Constraints: C1 Each Person has some FitnessRating
C2 Each Person has at most one FitnessRating
C3 The possible values of FitnessRating are 1 to 10
C4 Each Person is expert at at most one Sport
C5 Each Person who is expert at some Sport also plays that Sport
Derivation rules: D1 Person is a martial artist if
Person plays Sport ‘judo’ or Person plays Sport ‘karatedo’
D2 nrPlayers of Sport = count each Person who plays that Sport
The database is initially empty. The user now attempts the following sequence of updates and queries. For each update, circle the letter if the update is accepted (based on the cumulative state of the database). In case of rejection, supply a reason (e.g., state which part of the schema is violated). For queries, supply an appropriate response from the CIP.
(a) add: Person ‘Ann’ has FitnessRating 9.
(b) add: Person ‘Fred’ plays Sport ‘tennis’.
(c) add: Person ‘Bob’ has FitnessRating 7.
(d) add: Person ‘Ann’ has FitnessRating 8.
(e) add: Person ‘Chris’ has FitnessRating 7.
(f) add: Person ‘Fred’ has FitnessRating 15.
(g) add: Person ‘Ann’ plays Sport ‘judo’.
(h) add: Person ‘Bob’ is expert at Sport ‘soccer’.
(i) add: Person ‘Ann’ is expert at Sport ‘judo’.
(j) add: Person ‘Ann’ programs in Language ‘SQL’.
(k) add: Person ‘Ann’ plays Sport ‘soccer’.
(l) add: Person ‘Chris’ plays Sport ‘karatedo’.
(m) del: Person ‘Chris’ has FitnessRating 7.
(n) begin
add: Person ‘Bob’ has FitnessRating 8;
del: Person ‘Bob’ has FitnessRating 7.
end
(o) add: Person ‘Ann’ is expert at Sport ‘soccer’.
(p) add: Person ‘Bob’ play Sport ‘soccer’.
(q) Person ‘Ann’ plays Sport ‘judo’?
(r) list each Person who plays Sport ‘karatedo’?
(s) nrPlayers of Sport ‘soccer’?
(t) list each Person who is a martial artist.
(u) list possible values of FitnessRating.
(v) what is the meaning of life?
Explanation / Answer
(a) Update will be accepted
(b) Rejected: As the database is initially empty, Fred is not having any Fitness rating, hence C1 is violated
(c) Update will be accepted
(d) Rejected: Ann is already having a Fitness rating, update is violating constraint C2
(e) Update will be accepted
(f) Rejected: Maximum value of the rating can be 10, violating constraint C3
(g) Update will be accepted
(h) Rejected: To be expert at any sport, person needs to play that sport as well, violating constraint C5
(i) Update will be accepted
(j) Relationship not defined
(k) Update will be accepted
(l) Update will be accepted
(m) Rejected: Cannot delete fitness rating, as there is an entry for the sport for Chris. This will violate constraint C1
(n) Rejected: block will fail at step 1 itself, as there is already one entry of Fitness rating for Bob, violating constraint C2
(o) Rejected: Ann is already marked expert in judo, violating constraint C4
(p) Update will be accepted
(q) True
(r) Chris
(s) 2
(t) Ann, Chris
// Person who plays judo or karatedo
(u) 9, 7
(v) no such rule defined
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.