Module: Database Essential II
Assignment 1
Due Date: 28 April, 2023
Instruction to answer the following tasks:
• Take a screen shot of the answers completed using MS Access
• Add the screen shot of the answer under each question for questions 2 to 4.
• Write down your name at the top of the answer sheet.
• Use the databases provided in D2L under Week 2 to solve this assignment
answers.
Task 1 (14 marks): To answer the questions for this task 1 take help from Week 1 and 2
lectures.
Question 1: Define database management system. What does DDL and DML stand for? Why
DDL statements are used?
Question 2: Create a Table “TestTable” using in MS Access with the following data types
and attribute names:
In the above table set “MyAutoNumber” as the primary key. Give screenshot of the query.
Question 3: Write down the SQL commands to do the following changes in the “TestTable”:
a) Add a new column called MyTelephoneNumber of data type number and size 10 to
the “TestTable.”
) Delete all records from the “TestTable”
c) Change the size of MyText column in the “TestTable” table to TEXT and size (80)
Task 2 (4x4 = 16 marks): To answer the questions for this task 1 take help from Week 3
lectures.
Question 4: Use the Li
ary Database to answer the following:
a) List the titles of books in descending order.
) List the titles of books written by Joe Celko.
c) List the titles of books where the title contains the words “medicine” or “medieval”.
d) Produce a list of members and the books they have taken out on loan. Include the
member’s last name, first name, and titles of the books. The information to be
displayed is in 2 tables, but it is necessary to specify 3 tables for this query:
Member joins to Loan
Book joins to loan