Advanced level 2024 CASPA mock ICT 3

Advanced level 2024 CASPA mock ICT 3

Advanced level 2024 CASPA mock ICT 3

TASK A- DATABASE (25 MARKS)
The library of a school has just been filled with new books. The library attendant has a major problem
keeping track of the books in shelves and those on loan. A Reader in this college may have more than one
book out on loan. Apart from the Reader ID (identity), a Reader may also have the following information of
obvious meaning: surname, first name, title, address, town, sex, and a Book: book ID, title, Author,
publisher and date of pub (i.e. date of publication).
1) In your answer booklet, describe these tables in terms of relation and attribute names. (2marks)
2) Create a database file named LIBRARY. (1 mark)
3) (a) Create a table named READER with the following attributes as defined below. (2 marks)
Field Name Data type Properties
ReaderID Text Field size =6
Surname Text Field size =20
Firstname Text Field size = 20
PhoneNo Number Field size = 9
Town
Sex Field size =1
(b) Do a screen capture that shows the structure in design view including the property section. Paste in a
word document and save as Reader attributes in your candidate folder. (1 mark)
(c) Populate the table using data from appendix A. Save and close. (2 marks)
4) (a) Create a table named BOOK with the following attributes as defined below. (2 marks)
(b) Populate the table using data from appendix A. Save and close. (2 marks)
5) (a) Create another table named LOAN with the following attributes. (2 marks)
Field Name Data type Properties
BookID Text Field size =6
ReaderID Text Field size =6
DateTaken Date/Time
DateDue Date/Time
(b) Populate the table using data from appendix A. Save and close. (2 marks)
(c) Print all three tables (1 mark)
6) Create a relationship for the three entities. Do a screen capture of the relationship window, paste in a
word document and save as Relationship. (2 marks)
7) Create a query for each of the following, save and print a copy: