A. Multiple-Choice Questions (30 marks, 30 questions each 1 mark)
B. MS Access Exercise (70 marks)
You are given an MS Access database named BookRetails.accdb which contains the information about a
bookstore. There are 05 tables described the following information:
• Book: Information about books such as ISBN, Title, Author, Year, Binding, RRP, Qty_On_Hand, Min_Stock_Level and Catagory.
• Category: Information about book categories such as Category and Description.
• Customer: Information about customers such as Cust_Code, Name, Street Address, Country,
Post_Code, Email, CC_Number, CC_Type, CC_ExpiryDate.
• S_Invoice_Line: Information about sale invoice lines such as S_Inv_No, ISBN, Qty, Unit_Price.
• Sales_Invoice: Information about sale invoice such as S_Invoice_No, Date and Cust_Code.
You will use the skills learnt in the course to solve the 8 tasks below. The tasks must be done by applying MS Access techniques.
a. Change the database name to the following format: (15%)
StudentFullName_StudentID.accdb (no blank between words_first_character_capitalized)
e.g.: NguyenVanAn_BABAIU12345.accdb
Change five tables’ name to the following format: FirstLastname_Book, FirstLastname_Category, FirstLastname_Customer, FirstLastname_S_Invoice_Line, and FirstLastname_Sales_Invoice, respectively.
e.g.: AnNguyen_Books, AnNguyen_Authors, AnNguyen_Publishers
Then define the field properties of the tables as follows (accept all the warnings from MS Access when
defining the fields = Click Yes as many as you can): Field Data Type Description Requirements
Table1: AnNguyen_Book
Field Size: Long Integer
Validation Rule: (create by yourself) ISBN
Validation Text: ISBN must have at Number ISBN of the book (Primary Key) least 8 digits Required: Yes
Indexed: Yes (No Duplicates)
Field Size: 50, Allow Zero Length: No Title Short Text Title of the book Required: Yes
Indexed: Yes (Duplicates OK)
Field Size: 30, Allow Zero Length: No Author Short Text Author of the book Required: No
Indexed: Yes (Duplicates OK)
Field Size: Integer Year publication of the Year Number Required: Yes book
Indexed: Yes (Duplicates OK)
Field Size: Byte Quantity on hand kept
Default value: 0 Qty_On_Hand Number in the storage Required: Yes
Indexed: Yes (Duplicates OK) Field Size: Byte
Default value: 5
Validation Rule: (create by yourself) Minimum number of Min_Stock Number
Validation Text: Must keep at least 5 books in the storage books Required: Yes
Indexed: Yes (Duplicates OK)
Field Size: 4, Allow Zero Length: No Category Short Text Category of the book Required: Yes
Indexed: Yes (Duplicates OK)
Table2: AnNguyen_Category
Field Size: 4, Allow Zero Length: No Category Short Text Category of the book Required: Yes (Primary Key)
Indexed: Yes (No Duplicates)
Field Size: 30, Allow Zero Length: No Description of the Description Short Text Required: Yes category
Indexed: Yes (Duplicates OK)
Table3: AnNguyen_Customer
Field Size: 8, Allow Zero Length: No Cust_Code Short Text Code of the customer
Required: Yes (Primary Key)
Indexed: Yes (No Duplicates) Field Size: 35
Validation Rule: (create by yourself)
Validation Text: Must enter a Name Short Text Name of the customer Customer Name here Required: Yes
Indexed: Yes (Duplicates OK)
Field Size: 30 Country where the Country Short Text Required: Yes customer lives in
Indexed: Yes (Duplicates OK)
Field Size: 30 Email Short Text Email of the customer Required: Yes
Indexed: Yes (Duplicates OK)
Table4: AnNguyen_S_Invoice_Line Field Size: 8 Number of the Sales S_Inv_No Short Text Required: Yes Invoice
Indexed: Yes (Duplicates OK)
Field Size: Long Integer ISBN Number ISBN of the book Required: Yes
Indexed: Yes (Duplicates OK)
Field Size: Byte Quantity of the book Qty Number Required: Yes
sold in the Sales Invoice Indexed: Yes (Duplicates OK)
Field Size: Single Unit price of the book Unit_Price Number Required: Yes
sold in the Sales Invoice Indexed: Yes (Duplicates OK)
Table5: AnNguyen_Sales_Invoice Field Size: 8 S_Inv_No Number of the Sales Short Text Required: Yes (Primary Key) Invoice
Indexed: Yes (No Duplicates)
Format: dd/mm/yyyy Date the transaction Date Date/Time
Required: Yes occurs
Indexed: Yes (Duplicates OK)
Field Size: 8, Allow Zero Length: No Cust_Code Short Text Code of the customer
Required: Yes
Indexed: Yes (Duplicates OK) ACBSP: Reflective thinking Topic: Table Design
Bloom’s Taxonomy: Remember, Understand, Apply
Relevant Program Learning Outcomes: PLO5
Relevant Course Learning Outcomes: CLO6
Level of Difficulty: 2 (Medium)
b. Create the relationship between these five tables, enforce referential integrity. (5%) ACBSP: Reflective thinking Topic: Relational Database
Bloom’s Taxonomy: Understand, Apply
Relevant Program Learning Outcomes: PLO5
Relevant Course Learning Outcomes: CLO6
Level of Difficulty: 2 (Medium)
c. Create a select query (FirstLastname_queryc) that lists the ISBN, Title, Author, Year and Category
of all the books which title contains the word “Programming” and publication year is since 1993.
Sort in ascending order of publication year (resulting 11 records) (5%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Querying a Database Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 1 (Easy)
d. Create a select query (FirstLastname_queryd) that lists the ISBN, Title, Quantity_On_Hand,
Min_Stock and Category Description of all books which has Quantity_On_Hand less than
Min_Stock (resulting 35 records) (10%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Querying a Database Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6
Level of Difficulty: 2 (Medium)
e. Create a parameter query (FirstLastname_querye) that lists all customers’ information by
entering the country where the customer lives in a dialog box (i.e., “Enter a country name:”, then
enter “Ireland”) (resulting 9 records) (5%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Querying a Database Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 1 (Easy)
f. Create a parameter query (FirstLastname_queryf) that lists S_Invoice_No, Date, Cust_Code and
Total Amount (calculated as Quantity multiplied by Unit Price) while prompting a Sales Invoice
Number in a dialog box (i.e., “Enter a Sales Invoice Number”, then enter “B0001X1”) (10%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Querying a Database Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 3 (Hard)
g. Create a summary query (FirstLastname_queryg) that lists all the customers’ code, name, country
and number of invoices for each customer (resulting 18 records) (5%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Querying a Database Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 1 (Easy)
h. Create a wizard form (FirstLastname_formh) for Customer with Layout = “Tabular” (5%) ACBSP: Reflective thinking
Topic: MS Access 2016 - Forms & Reports Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 1 (Easy)
i. Create a report (FirstLastname_reporti) with the following requirements (10%)
• At the report header, print the Report on Customers
• At the report footer, print the Total Amount of all the transactions by the customers
• Report: Grouping by Cust_Code, Ascending order in “Date”, Layout = “Block”, Orientation = “Portrait”
• Hint: create a summary query before creating the report … ACBSP: Reflective thinking
Topic: MS Access 2016 - Forms & Reports Bloom’s Taxonomy: Apply
Relevant Program Learning Outcomes: PLO6
Relevant Course Learning Outcomes: CLO6 Level of Difficulty: 3 (Hard)
The layout of the database objects at the end...