Assume the following conceptual schema is stored. Constraints apply to each data
ID: 3805139 • Letter: A
Question
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) This record will be inserted as it satisfies the rule C1 that the value of FitnessRating is from 1 to 10.
(b) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(c) This record will be inserted as it satisfies the rule C1 that the value of FitnessRating is from 1 to 10.
(d) This record will be inserted as it satisfies the rule C1 that the value of FitnessRating is from 1 to 10.
(e) This record will be inserted as it satisfies the rule C1 that the value of FitnessRating is from 1 to 10.
(f) This record will not be inserted as it violates the rule C1 that the value of FitnessRating is from 1 to 10.
(g) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(h) This record will be inserted as it satisfies the rule C4 that the person is expert at most one sport.
(i) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(j) This record will not be inserted as it violates the rule C4 that the person is expert at most one sport.
(k) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(l) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(m) This record will be deleted as it satisfies the rule C1 that the value of FitnessRating is from 1 to 10.
(n) add: Perosn 'Bob' has fitnessRating 8 - This record will be inserted as it satisfies the rule C2 that person has atmost one fitnessRating.
delete: Perosn 'Bob' has fitnessRating 7 - This record will be deleted as it satisfies rule C1 that the value of FitnessRating is from 1 to 10.
(o) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(p) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(q) This record will be inserted as it satisfies the rule C5 that the person who is expert in a particular sport also plays that sport.
(r) Chris plays Sport 'karatedo'.
(s) There are 2 players who play the sport 'Soccer'.
(t) Ann and Chris are martial artists because Ann plays sport 'judo' and Chris plays sport 'karatedo' which satisfies the derativation rule D1 that a person is a martial artist if person plays sport 'judo'or person plays the sport 'karatedo'.
(u) The possible values of FitnessRating are from 1 to 10.
(v) The meaning of life is that each person should be active and fit and should actively participate in sports activites such as Soccer, Judo, Karatedo, etc to live a long, happy and healthy life.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.