IPT - A Virtual Approach - eXercises CSD X #1

Database Design

eXercise #1

Introduction to Information Science

A Stipulation involves specifying a naming convention ans an instruction indicates some sort of action to be performed with a fact, classify each of the following sentences according to whether they represent propositions, instructions, stipulations or queries. For any that represent propositions, indicate whether they are atomic (or single valued).
1. Add the fact that William lives in Maryborough
2. Where does William live.
3. Let a person be denoted by the letter 'p'.
4. William lives in Maryborough.
5. This table has headings 'person' and 'Subject'.
6. Sandra studies French and German.
7. John doesn't study Graphics.
8. This is an instruction.
Choosing suitable identifiers for predicates (or roles), entities and label types, cast each of the following sentences in deep sentence structure form using the format:
```
predicate(entity-category label-type label-instance,
          entity-category label-type label-instance)

example:  eats(Person FirstName 'Fred', Food FoodName 'Tripe')
```
1. Fred lives in Springwood.
2. Goodna is a suburb of Brisbane
3. Mavis is a citizen of Australia
4. Douglas Adams wrote both 'The Meaning of Liff' and 'The Hitch Hikers Guide to the Galaxy'
5. William Shatner played Captian 'James Tiberius Kirk'
6. Fred is brother to Lucy
We may indicate permissible transitions between various states with diagrams. Suppose we have just 4 states denoted by 'a', 'b', 'c' and 'd'. At left the possible transitions between states is indicated and a Transition diagram is drawn beside, using circled states linked by allowable transition arrows.

Draw transition diagrams for the following situations:
1. Each year has 4 seasons: Summer, Winter, Autumn and Spring.
2. A swinging pendulum may be in 4 states, two of which are stationary, one moving left the other moving right
3. A swinging pendulum may be in one of three distinct states: stationary, speeding up or slowing down
4. matter can be in 3 states: solid, liquid and gas
5. a process can be in 4 states: waiting to be processed, being processed, temporarily suspended and completed
6. A person may have one of 5 marital states: married, single, divorced, separated and widowed
7. People are either not enrolled in a high school, currently enrolled, or previously enrolled. If they are currently enrolled, they are enrolled in exactly one year level 8 through 12. Students may enter at any year level, repeat any year level, be promoted only one year level at a time but not demoted.
Consider the following Universe description (conceptual schema):

Stored Fact Types:
- F1: Person owns CarType
- F2: Person has Age
- F3: Person drives CarType
Constraints:
- C1: Each person has exactly one age recorded (ie. at least one and at most one)
- C2: Each person owns at most one car
- C3: X drives CarType IF X owns CarType AND Age >=17
Derivation Rules:
- D1: Count(owns X) returns the # of owners of CarType X
- D2: Avg (Age) gives the average age
- D3: X holdsLicence IF X drives
Your job is to decide (by starting with an initially EMPTY information base), what the cumulative effect of each of the following transactions are. For each transaction, suggest the CIP response, and indicate the current state of the information base (ie. where the data is stored and what the fact tables look like).
1. Add: Person 'Chris' has Age 17.
2. Add: Person 'Sally' owns CarType 'Ferrari'.
3. Add: Person 'Chris' owns CarType 'Range Rover'.
4. Add: Person 'Sally' has Age 15.
5. Add: Person 'Sally' owns CarType 'Ferrari'.
6. Add: Person 'Cassy' owns MotorBike 'Harley Davidson'.
7. Add: Person 'Travis' has Age 14.
8. Add: Person 'Travis' drives CarType 'Mini'
9. Add: Person 'Sally' owns CarType 'BMW'
10. Add: Person 'Sally' has Age 15
11. Add: [ Del:Person 'Sally' has Age 15 Add:Person 'Sally' has Age 17]
12. Add: Person 'Jan' has Age 20
13. Add: Person 'Jan' drives CarType 'BMW'
14. Avg (Age) ?
15. What type of car does Person 'Chris' drive?
16. What type of car does Person 'Sally' own?
17. What does Person 'Chris' drive?

Consider the following Universe description (conceptual schema):

Stored Fact Types:

F1: Student isEnrolledIn Course
F2: Student has EnrollmentStatus

Constraints:

C1: Each Student is enrolled in exactly one course
C2: Each Student has at most one enrolment status
C3: Only the following enrolment states are permitted: active, deferred, suspended, graduated

C4: Only the following enrolment transitions are allowed:

From/To	Active	Deferred	Supspended	Graduated
Active	0	0	1	1
Deferred	1	0	0	0
Suspended	1	0	0	0
Graduated	0	0	0	1

('1' indicates allowed)

Derivation Rules:

D1: Count(isEnrolledIn X) returns the number of students enrolled in course X

Your job is to decide (by starting with an initially EMPTY information base), what the cumulative effect of each of the following transactions are. For each transaction, suggest the CIP response, and indicate the current state of the information base.

Add: Student 'Sarah' isEnrolledIn Course 'Science'
Add: Student 'Sam' has EnrollmentStatus 'Active'
Add: Student 'Sam' isEnrolledIn Course 'Arts'
Add: Student 'Sue' has EnrollmentStatus 'Deferred'
Add: [ Add: Student 'Sam' has EnrollmentStatus 'Deferred' Del: Student 'Sam' has EnrollmentStatus 'Active' ]
Add: Student 'Fred' isEnrolledIn Course 'English'
Add: Student 'Sarah' isEnrolledIn Course 'English'
What is Student 'Sam''s' EnrollmentStatus ?
Add: Student 'Sally' isEnrolledIn Subject 'Latin'
Add: Student 'Fred' has EnrollmentStatus 'Active'
Add: Student 'Sarah' has EnrollmentStatus 'Active'
Add: Student 'Sarah' has EnrollmentStatus 'Deferred'
Add: Student 'Sam' has EnrollmentStatus 'Suspended'
What are the possible EnrollmentStatuses?
Add: Student 'Simon' isEnrolledIn Course 'Science'
Add: Count('Science')
Who is enrolled in Subject 'English'?

Solutions