E238 Information Systems 12 TEE Database Questions Combo 1997-2000

---

PART A Multiple Choice Items

Attempt all questions in this part. Each question is worth 1 mark. Each question has only one correct response. For each question select the response that is correct. Signify your answer by circling the label (a, b, c or d) of the correct response. If you change your answer, completely erase your original answer. Questions where two or more responses are selected will score no marks.

---

1997 PART A Multiple Choice Items

11. Which of the following is not a benefit of distributed information processing systems?

(a) Failure of a hardware component has less impact.
(b) Remote sites are able to operate in their own time zones.
(c) Local access to data improves response time.
(d) Data integrity is improved.

12. In database terminology, what is a relation?

(a) A link between two tables
(b) A table
(c) A foreign key
(d) An index

13. Consider the following statements about relational databases:

You can have relations without relationships.

You can have relationships without relations.

With reference to these statements, select the best of the following alternatives.

(a) Both statements are true.
(b) The first is true but the second is false.
(c) The first is false but the second is true.
(d) Both statements are false.

END OF 1997 PART A

1998 PART A Multiple Choice Items

11. Which statement about database models is correct?

(a) Links between tables in relational databases are held by data values in keys (namely, foreign key and primary key values).
(b) Hierarchical databases store data in sets (and run on mainframe computers).
(c) Network databases make use of inexpensive personal computers linked to the Internet.
(d) In a flat file database, the DBMS manages the links between tables.

12. Which statement about a relation in second normal form (2NF) is correct?

(a) The relation has a primary key that may contain a null value.
(b) The relation contains repeating groups.
(c) All attributes that are not part of the key depend on all attributes in the key.
(d) The relation has no unique key. (There is no non-key attribute that depends on another non-key attribute.)

13. Which statement about a data dictionary is correct?

(a) A data dictionary holds definitions of data, that is, data about data.
(b) A data dictionary holds common data that will be shared between computer systems.
(c) The online version of the Australian Macquarie Dictionary is an example of a data dictionary.
(d) A data dictionary allows computer systems to translate code from one programming language to another.

END OF 1998 PART A

1999 PART A Multiple Choice Items

12. The major reason relational databases are made up of normalised tables is to:

(a) Reduce the memory required.
(b) Decrease the response time.
(c) Reduce the storage of redundant data.
(d) Decrease the number of tables required.

13. Which statement is correct? Relations in Third Normal Form must have:

(a) All non-key attributes dependent only on the key.
(b) A key consisting of more than one attribute.
(c) A key consisting of just one attribute.
(d) No attributes which are not part of the key.

END OF 1999 PART A

2000 PART A Multiple Choice Items

13. Which statement about a relation in first normal form is correct?

(a) All non-key attributes are dependent on the whole of the primary key.
(b) The relation has a primary key that may contain a null value.
(c) Data items may not be repeated for a specific attribute.
(d) The relation may not contain repeating groups of data for a specific key.

14. Given the descriptions shown below of the tables ORDER and SALESPERSON, which of the following attributes is a foreign key?

ORDER ORDER_NUMBER ORDER_DATE SALESPERSON_NUMBER AMOUNT

SALESPERSON SALESPERSON_NUMBER SALESPERSON_NAME SALESPERSON_COMMISSION

(a) SALESPERSON_NUMBER
(b) ORDER_NUMBER
(c) AMOUNT
(d) SALESPERSON_NAME

15. Your company wants to send an advertising leaflet to all Kalroojalup residents with at least 2 hectares of property. To select the appropriate records from the database, which selection rules should you use?

(a) Suburb EQUALS Kalroojalup OR Area GREATER THAN 2
(b) Suburb EQUALS Kalroojalup AND Area GREATER THAN 2
(c) Suburb EQUALS Kalroojalup OR Area GREATER THAN OR EQUAL TO 2
(d) Suburb EQUALS Kalroojalup AND Area GREATER THAN OR EQUAL TO 2.

END OF 2000 PART A

---

1997 PART B Short Answers

Question 20. (3 marks)

In a relational database system:

(a) What is a relationship? (2 lines)

(b) What is a tuple? (2 lines)

(c) What is an attribute? (2 lines)

(d) What is a primary key? (2 lines)

(e) What is a foreign key? (2 lines)

(f) What is an index? (2 lines)

END OF 1997 PART B

1998 PART B Short Answers

17. In a distributed database application, you may choose to duplicate data at more than one location. Give one advantage and one disadvantage of doing this. (4 lines) [2 marks]

END OF 1998 PART B

1999 PART B Short Answers

25. “A student can belong to many sports clubs. A sports club has many student members.”

An entity-relationship model of this situation consists of two entities Student and Sports Club with a many to many relationship between them. However, most relational database systems do not directly support “many to many” relationships, but do support “one to many” relationships. Explain, using a diagram or otherwise, how you would change the model to use the commonly supported “one to many” relationship. (0.25 page) (2 marks)

END OF 1999 PART B

2000 PART B Short Answers

21. The secretary of a yacht club wants to keep track in the yacht club database of all boat owners and their boats. Given the two entities BOAT and OWNER draw an entity-relationship diagram showing the attributes they might have and their relationship. (1/3 page) (2 marks)

END OF 2000 PART B

---

1997 PART C Extended Answers

Question 26. (23 marks) Allow (23 * 1.8 = 41.4) 42 minutes for this.

A relational database system has been set up to assist with the management of this year's Tertiary Entrance Examinations (TEE). The following are tables used in this database. Primary keys are underlined.

STUDENT (student-ID, student-name, date-of-birth, school-name, aggregate-score)
SCHOOL (school-name, address, phone, principal)
TEE SUBJECT (subject-ID, subject-name, exam-date)
STUDENT SUBJECT (student-ID, subject-ID, exam-mark, school-mark)

• A student's aggregate-score is derived from the school-mark and exam-mark in all subjects.
  
• Some data is already recorded in the tables and is shown below.
  
• Some records have column values set to 'null'. The 'null' value is not the same as zero or a blank. It means that data is unavailable or unknown. The TEE database does not permit a primary or foreign key to have a value of 'null'. Other fields except for student-name may have a value of 'null'.
  
• Each student enrolled has a student-ID number that is used for all his or her subjects.

STUDENT
student-ID	student-name	date-of-birth	school-name	aggregate-score
1003	JOHN MURPHY	12/04/1980	WA HIGH	null
1452	MARY KANTOR	17/03/1980	ST JUDE’S	null

SCHOOL
school-name	address	phone	principal
WA HIGH	23 Collins Terrace, Perth	08 9236 7890	George Lisson
ST JUDE’S	145 Main St, Castleton	08 9371 5644	June Cotton

TEE SUBJECT
subject-ID	subject-name	exam-date
INFOSYS	Information Systems	12/11/1997
ECON	Economics	15/11/1997
GEOG	Geography	23/11/1997

STUDENT SUBJECT
student-ID	subject-ID	exam-mark	school-mark
1003	GEOG	null	56
1003	ECON	null	78
1452	GEOG	null	80
1452	ECON	null	68

(a) Draw a diagram in the frame provided to represent the database design. Show all tables and their attributes (primary and foreign keys and non-key attributes) and underline the primary keys. Mark the foreign keys with the code 'fk'. (half a page available) (5 marks)

(b) Complete the following grid giving details of the primary and foreign keys in the remaining three database relations (tables). Note that the first row has been completed for you. (3 marks)

 

Table	Primary Key	First Foreign Key	Link is to Table	Second Foreign Key	Link is to Table
STUDENT	student-ID	school-name	SCHOOL
SCHOOL
TEE SUBJECT
STUDENT SUBJECT

(c) This grid shows details about the relationships between the tables. Complete the grid by placing an X in each row. Note that the first row has been completed for you. (1 mark)

TABLE 1	TABLE 2	1 : 1	many : many	1 : many	many : 1
SCHOOL	STUDENT			X
STUDENT	STUDENT SUBJECT
TEE SUBJECT	STUDENT SUBJECT

(d) Examine the STUDENT table: (3 marks)

(i) On which column (attribute) other than the primary key might you put an index. (1 line)

(ii) Why? (2 lines)

(iii) Why must the aggregate-score column be optional? (that is, why must it allow for a null value to be stored?) (3 lines)

(e) Add the following data to the four tables: (7 marks)

(i) JOHN MURPHY and MARY KANTOR are enrolled in Information Systems. The have school marks of 50 and 52.

(ii) SAMMY LEE, a student at WA HIGH applies to sit for the TEE. His student-ID will be 1699. He was born on 18th December 1979.

(iii) SAMMY LEE is the first student to be enrolled in the new subject "Asian Culture". The exam date has been set for 11th November 1997. His school mark is currently unavailable.

(iv) JANE SMITH is a mature age student (born 14 March 1964) sitting Information Systems this year but she is not enrolled in any school.

NOTE:

• You may not change the structure of the database.
• You may leave null a column where there are missing details only if this is permitted in the TEE database, otherwise invent suitable data.
• You are not required to complete every blank row in the tables.

STUDENT
student-ID	student-name	date-of-birth	school-name	aggregate-score
1003	JOHN MURPHY	12/04/1980	WA HIGH	null
1452	MARY KANTOR	17/03/1980	ST JUDE’S	null
				|
				|
				|
				|

SCHOOL
school-name	address	phone	principal
WA HIGH	23 Collins Terrace, Perth	08 9236 7890	George Lisson
ST JUDE’S	145 Main St, Castleton	08 9371 5644	June Cotton
			|
			|
			|

TEE SUBJECT
subject-ID	subject-name	exam-date
INFOSYS	Information Systems	12/11/1997
ECON	Economics	15/11/1997
GEOG	Geography	23/11/1997
		|
		|

STUDENT SUBJECT
student-ID	subject-ID	exam-mark	school-mark
1003	GEOG	null	56
1003	ECON	null	78
1452	GEOG	null	80
1452	ECON	null	68
			|
			|
			|
			|

(f) MARY KANTOR telephones the TEE administration office and wants to know the dates of all her TEE examination. Explain how the database tables would be accessed to get the required information. (12 lines) (4 marks)

Your answer should describe:

• the order in which the tables are accessed
• the key(s) used
• the number of records read at each stage
• the data retrieved

Although the question doesn’t suggest it, you could have used a table to put your answers in, or you could write the answer as a series of sentences. The table might have a structure like this.

---

1998 PART C Extended Answers

Question 32 [19 marks]

THE WESTERN DOG ASSOCIATION

General
The Western Dog Association is a fictitious non-profit association looking after the interests of pedigree dogs in Western Australia. The Western Dog Association is concerned only with the interests of pedigree dogs. Western Dog Association is affiliated with the National Dog Association (NDA). Membership of the NDA automatically results in membership of the relevant State body, in this case the Western Dog Association. Up to now, owners of mixed breeds have not been accepted as members. The Western Dog Association has an office in Perth, which requires a relational database to keep track of the people and dogs they deal with. All processing of subscriptions and registration of pedigrees are handled solely by the National Dog Association (NDA) from their head office in Melbourne.

Dogs
A pedigree dog is a dog that is registered with the NDA, having satisfied their strict criteria for registration as pedigreed. All pedigree dogs belong to a Breed (e.g. Dalmatian, German Shepherd, Maltese Terrier, et cetera). Dogs have a pedigree name that may be different from the name by which their owner usually call them (e.g. his owner calls the Dalmatian ‘Count Roger of Dalkinghurst’: ‘Spot’).

Licensed dog breeders hold a kennel licence. A holder of a kennel licence breeds only pedigree dogs. No dog can be considered as pedigreed if it was not bred by someone holding a kennel licence.

The Western Dog Association appoints someone locally as a contact person for each breed and needs to keep their names and telephone numbers on file.

Each pedigree dog registered with the NDA has a unique pedigree dog identifier—dog#. Data such as the dog’s two names, date of birth and gender need to be recorded. A dog has only one registered owner with the NDA at any one time and the NDA wishes to record this but does not need to keep records of a dog’s prior owners.

Membership
Owners pay subscription fees to belong to the NDA and thus the Western Dog Association. These are processed through the NDA’s Melbourne office which allocates them a membership#. One membership subscription covers any number of pedigreed dogs the member might own. Periodically, the NDA sends the Western Dog Association a list of paid-up members, including their names, addresses and phone numbers and the details of the pedigree dogs registered to them. The Western Dog Association office uses this list to update their records.

Obedience Training Classes
The Western Dog Association sponsors dog-training classes. The Association keeps names and addresses of trainers who conduct obedience classes for dogs. Only paid-up Association members may enrol their dogs in these classes. Trainers run weekly classes at various time and locations around the state and the Association keeps track of enrolments of dogs in these classes. Unfortunately, due to a scarcity of qualified trainers, dogs may only be enrolled in one class at a time.

Each class has a unique ClassID. The Association only keeps records of the current obedience class enrolments. It has no need to be able to look up past enrolments. Any breed of pedigree dogs can take part in these obedience classes but many of the dogs are German Shepherds. Owners who want to enrol their dog in an obedience class first contact the Association. They want to know the cost, and where and when the classes are held. They also need a telephone number to contact the appropriate trainer.

a) Complete the entity relationship diagram (ERD) that represents a design for a relational database to satisfy the needs of the Western Dog Association. Include two extra entities ‘Owner’ and ‘Breed’ and name any relationships they might have. Include the attributes of the ‘Dog’, ‘Owner’ and ‘Breed’ entities on your diagram. Underline Primary keys. Foreign keys show links to other entities (tables). Draw an ellipse around any foreign keys in your design. (1 page available) (9 marks)

b) Give two examples of non-primary key attributes in the Class table that could benefit from an index and explain why. (6 lines) (3 marks)

c) Describe how you would change the database design as it stands at the moment to allow the Association to efficiently answer inquiries from owners who want to know if there is room in a particular class for their dog. Classes are restricted to enrol a maximum of 30 dogs. (4 lines) (2 marks)

d) The Association has been approached by the Police to allow non-pedigree dogs to enrol in their obedience classes. The Committee has decided that this would be an excellent form of community service and is prepared to find extra trainers but does not want to have the expense of changing the design of the database to allow this. They would expect owners of non-pedigreed dogs to become paid-up members of the Association. How would you include training of non-pedigree dogs without changing the design of your database? (6 lines) (3 marks)

e) With the extension of obedience classes to community members, many existing Association members have asked if they can now enrol their dogs in more than one class. Extra trainers have been employed to conduct obedience classes so this should now be possible. The current database makes no allowance for this. Which of the following three design changes is the most suitable to implement in a relational database to solve the new requirement? You must explain your choice to obtain credit. (6 lines) (2 marks)

---

1999 PART C Extended Answers

30. Jigsaw Library (18 marks)

Mrs Stephenson is retired now and spends her time running a jigsaw library for people in her suburb. She requires a database to run on her laptop computer to keep track of her jigsaw library. Your task is to design this database.

She has over 500 jigsaws in stock. She allocates each one a unique jigsaw ID. She keeps details of the number of pieces in a puzzle, its date of addition to her collection, donor name if not purchased, purchase price if purchased, the title of the picture, and the manufacturer’s name. She categorises the condition of each jigsaw as “Good”, “Satisfactory”, “Poor” (1 or 2 pieces missing) or “Unusable”.

Mrs Stephenson lends jigsaws at no charge to her borrowers who are mainly relatives and friends. She allows the borrowers to keep a puzzle for a time they specify up to a maximum of two months. She keeps details of the borrowers’ names and addresses and contact phone numbers. She also records details of each jigsaw borrowed, including the jigsawID, borrower, date borrowed and the date due to be returned. You may assume that each borrower’s name is unique.

When a borrower returns a jigsaw, Mrs Stephenson records the date returned. If the borrower has attempted to complete the puzzle, she asks them what the puzzle’s current conditon is. If this conflicts with the condition she has previously recorded, she changes the condition in her records.

Mrs Stephenson has a problem. Sometimes the borrowed jigsaw, categorised as “Good” or “Satisfactory”, is found to have some pieces missing. The borrower returns the puzzle and complains to Mrs Stephenson who would now like to be able to track the previous borrowers of the puzzle.

Mrs Stephenson would also like to be able to produce a list of all borrowers with overdue loans, their phone numbers, the jigsaw borrowed and the date it was due to be returned.

(a) Produce an Entity Relationship (E-R) diagram modelling a physical design for a relational database to satisfy Mrs Stephenson’s requirements outlined above. On your diagram:

(i) Name all entities, attributes and relationships.
(ii) Identify Primary Keys by underlining them (e.g. PersonID).
(iii) Identify Foreign Keys linking with other tables with the characters -FK after the attribute name (e.g. department-FK).
(iv) Identify any attributes that have null values permitted by including -NP after their name (e.g. Spouse-NP).
(v) Identify any attributes that have values restricted to a set of just a few possible values by including -R after their name (e.g. Month-R).
(vi) Combinations of primary keys, foreign keys, allowed nulls and restricted values may be valid (e.g. SportsClub-FK-R-NP). (0.75 page) (13 marks)

(b) Should Mrs Stephenson delete the record of a jigsaw’s loan after it has been returned? Explain why or why not. (4 lines) (2 marks)

(c) Explain exactly how you would use the database to query the name and telephone number of a borrower with an overdue loan for jigsawID = 98 which was due back yesterday and still has not been returned. (6 lines) (3 marks)

END OF 1999 PART C

---

2000 PART C Extended Answers

29. (20 marks)

BrekkyFeast Breakfast Cereals

BrekkyFeast is a new company in the breakfast cereal market. They recently set up a relational database system to assist in storing nutritional information about their products. Some sample data has been recorded in the tables and is shown below.

PRODUCT
PRODUCT_ID	PRODUCT_NAME	SERVING_SIZE	ENERGY	PROTEIN	FAT	CARBOHYDRATE	FIBRE
111	Honey Pops	40	1730	5.1	1.0	88.8	3.6
112	Wheat Flakes	30	1550	7.1	0.6	6.5	6.5
113	Crispy Rice	45	1680	6.8	1.1	4.8	4.8
114	Rice Balls	35	1460	6.7	0.8	5.3	5.3

INGREDIENT
INGREDIENT_ID	INGREDIENT_NAME	SUPPLIER_NAME
001	Rice	Ord Rice Company
002	Sugar	Sweetness Unlimited
003	Honey	Forest Products
004	Wheat	Western Wheat Board

PRODUCT-INGREDIENT
PRODUCT_ID	INGREDIENT_ID
111	001
111	002
112	002
112	003
112	004

PACKAGING
BARCODE	PRODUCT_ID	PACKAGE_SIZE	SERVINGS
9 310333 061237	111	400	13
9 310333 092345	111	600	18
9 310333 021173	111	750	21
9 300743 006933	112	600	16
9 300743 003444	112	800	20

LEGEND
Attribute	Unit of Measurement
Energy	kilojoules/100 grams
Protein	grams/100 grams
Fat	grams/100 grams
Carbohydrate	grams/100 grams
Fibre	grams/100 grams
Serving-Size	grams
Package-Size	grams
Servings	per package

(a) Draw an entity-relationship diagram in the frame below to represent the database design, including attributes and relationships. It is not necessary to identify the foreign keys.

You may assume that any product can be in more than one size. (space of one page) (7 marks)

(b) For each table listed below, complete the grid giving details of the primary key, each foreign key (if any) and the table(s) it links to. (5 marks)

Table	Primary Key	Foreign Key 1	Linking to Table	Foreign Key 2	Linking to Table
PRODUCT
INGREDIENT
PRODUCT- INGREDIENT
PACKAGING

(c) If supplies of ingredients were to be obtained from more than one supplier, the current database design would have to change. Describe clearly how you would change the current database design to allow an ingredient to be supplied by more than one supplier, and for a supplier to supply more than one ingredient. (10 lines) (4 marks)

(d) The Australian Health Society has recently asked BrekkyFeast for information about products containing wheat. BrekkyFeast wish to produce a report listing the PRODUCT_ID, PRODUCT_NAME and SERVING_SIZE for all products that contain wheat, to make this information available to people with gluten intolerance. Explain how the database tables would be accessed to get the required information, supporting your explanation with data from the table. (10 lines) (4 marks)

END OF 2000 PART C

---

2001 PART C Extended Answers