Database expert needed

MOCK PAPER CI402: There is ONLY ONE ANSWER PER QUESTION.

Also available as online test on StudentCentral

1. The main purpose of a database is:

a) to provide a form for data entry

b) to store programming code

c) to hide personal data on the internet

d) to store the persistent data for an application

e) to store the transient data for an application

[3 marks]

2. Which of the following statements about Middleware is FALSE:

a) ODBC is a type of Middleware

b) Middleware could be used to connect Microsoft Access to an XML dataset

c) Middleware is where the database server is located

d) Middleware links applications with databases

e) Middleware can be used to connect to heterogeneous data sources

[3 marks]

3. A foreign key is:

a) an alternative primary key for the table

b) a copy of the primary key from another table

c) the way a DBMS searches tables

d) the link to another database

e) A, B and D

[3 marks]

4. Which of the following statements about a foreign key in a relational database is FALSE: A foreign key:

a) is used to link tables

b) must be unique

c) has a datatype

d) represents a whole record in the linked table

e) must link to a primary key

[3 marks]

5. Database views can:

a) provide applications with suitable data

b) be part of a database security strategy

c) give users access to the data they need

d) use data from more than one table

e) all of the above

[3 marks]

6. The SQL SELECT statement is used to:

a) choose a database to move

b) choose a database to copy

c) create a new table

d) read data from a database

e) write data to a database

[3 marks]

7. Consider the following part of an Entity-Relationship Diagram about a university information system.

Many students take each course; each student can only be enrolled on one course.

a) courseId is added to the STUDENT table as a primary key

b) courseId is added to the STUDENT table as a foreign key

c) courseTitle is added to the STUDENT table as an index

d) studentId is added to the COURSE table as a primary key

e) studentId is added to the COURSE table as a foreign key

[3 marks]

8. In the SQL SELECT statement the * sign is used to:

a) return all records

b) return all databases

c) return all fields

d) change the datatype

e) create a new table

[4 marks]

Consider the following partial database design and table fragments from a vet appointment system for the remaining questions. tVET TABLE PARTIAL RESULT

vetID fName sName surgeryName etc vet1 Annie Animal-Care Horseface House …. vet2 Bill Bonio Horseface House …. vet3 Carly Cat-Fixer Dogford Street ….

tAPPOINTMENT TABLE PARTIAL RESULT

vetID petID dateTime comments etc vet1 1001 17/02/2019 10:30 emergency …. vet2 1006 17/02/2019 10:30 …. vet1 1003 18/03/2019 09:00 …. vet2 1002 18/03/2019 09:30 …. vet1 1006 18/03/2019 09:50

tPET TABLE PARTIAL RESULT

petID petName petSpecies petOwnerId etc 1001 Rover Dog PO134 …. 1002 Tibbles Cat PO125 …. 1003 Hamble Hamster PO134 …. 1004 Sleeky Cat PO155 …. 1005 Trevor Tortoise PO198 …. 1006 Bingo Dog PO154 ….

Figure 1: Partial Entity Relationship Diagram and partial tables for Vet Appointment System

9. This design shows that:

a) an appointment involves a VET and a PET

b) vetID is part of a composite primary key in the tAPPOINTMENT table

c) a PET can see different VETS

d) A and B only

e) A, B and C

[5 marks]

10. Which of the following statements are FALSE about the table fragments shown in figure 1:

a) Trevor has an appointment with Annie

b) Rover has an appointment with Annie

c) Annie has more appointments shown than Bill

d) Hamble has one appointment shown

e) Bingo has two appointments shown

[5 marks]

11. The SQL to find any appointments for Sleeky is:

a) SELECT * FROM tAPPOINTMENT WHERE petID = 1002

b) SELECT * FROM tPET WHERE petID = 1006

c) SELECT * FROM tAPPOINTMENT WHERE petID = 1004

d) SELECT * FROM tAPPOINTMENT WHERE petID = PO155

e) SELECT * FROM tPET WHERE vetID = 1004

[5 marks]

12. The SQL to join the VET and APPOINTMENT tables is:

a) SELECT * FROM tVET INNER JOIN tAPPOINTMENT on tVET.petID = tAPPOINTMENT.petID

b) SELECT * FROM tPET INNER JOIN tAPPOINTMENT on tVET.vetID = tPET.petID

c) SELECT * FROM tVET INNER JOIN tAPPOINTMENT on tVET.vetID = tAPPOINTMENT.vetID

d) SELECT FROM tVET INNER JOIN tAPPOINTMENT on tVET.petID = tAPPOINTMENT.vetID

e) SELECT FROM tVET INNER JOIN tAPPOINTMENT on tVET.vetID = tAPPOINTMENT.dateTime

[5 marks]

13. The SQL to show the appointments in date order (earliest first) is:

a) SELECT FROM tAPPOINTMENT ORDER BY date ASC

b) SELECT * FROM tAPPOINTMENT ORDER BY date DESC

c) SELECT FROM tAPPOINTMENT ORDER BY date DESC

d) SELECT * FROM tAPPOINTMENT ORDER BY dateTime DESC

e) SELECT * FROM tAPPOINTMENT ORDER BY dateTime

[5 marks]

14. The SQL to find appointments for the whole of 2019 is:

a) SELECT * FROM tAPPOINTMENT WHERE dateTime BETWEEN '2019-01-01' AND '2019-12-31'

b) SELECT * FROM tAPPOINTMENT WHERE YEAR IN 2019

c) SELECT * FROM tAPPOINTMENT WHERE dateStart AFTER '2019-01-01' AND BEFORE '2019-12-31'

d) SELECT * FROM tAPPOINTMENT WHERE YEAR WITHIN 2019

e) SELECT * FROM tAPPOINTMENT WHERE dateTime > '2019-01-01'

[5 marks]

15. The SQL to find appointments for Bill Bonio is:

a) SELECT * FROM tVET INNER JOIN tAPPOINTMENT ON tVET.vetID = tAPPOINTMENT.petID WHERE fName = 'Bonio'

b) SELECT * FROM tVET INNER JOIN tAPPOINTMENT ON tVET.vetID = tAPPOINTMENT.vetID WHERE sName = 'Bonio'

c) SELECT * FROM tPET INNER JOIN tAPPOINTMENT ON tVET.petID = tAPPOINTMENT.vetID WHERE sName = 'Bonio';

d) SELECT * WHERE sName = 'Bonio' FROM tVET INNER JOIN tAPPOINTMENT ON tVET.vetID = tAPPOINTMENT.vetID

e) SELECT WHERE fName = 'Bonio' FROM tVET INNER JOIN tAPPOINTMENT ON tVET.vetID = tAPPOINTMENT.petID

[6 marks]

16: The SQL to show how many appointments for each pet is:

a) SELECT petID, SUM(*) FROM tAPPOINTMENT GROUP BY petID

b) SELECT petID, SUM(*) FROM tAPPOINTMENT GROUP BY vetID

c) SELECT vetID, SUM(*) FROM tAPPOINTMENT GROUP BY vetID

d) SELECT petID, COUNT(*) FROM tAPPOINTMENT GROUP BY vetID

e) SELECT petID, COUNT(*) FROM tAPPOINTMENT GROUP BY petID

[6 marks]

17. The SQL to find all appointments for dogs and cats is:

a) SELECT * FROM tPET INNER JOIN tAPPOINTMENT ON tPET.petID = tAPPOINTMENT.vetID WHERE tPET.petID IN ('Dog','Cat')

b) SELECT * FROM tPET INNER JOIN tAPPOINTMENT ON tPET.vetID = tAPPOINTMENT.vetID WHERE tPET.petID IN ('Dog','Cat')

c) SELECT * FROM tPET INNER JOIN tAPPOINTMENT ON tPET.petID = tAPPOINTMENT.petID WHERE tPET.petSpecies IN ('Dog','Cat')

d) SELECT * FROM tPET INNER JOIN tVET ON tPET.vetID = tVET.vetID WHERE tPET.petSpecies IN ('Dog','Cat')

e) SELECT * FROM tVET INNER JOIN tAPPOINTMENT ON tPET.vetID = tVET.vetID WHERE tPET.petSpecies WITHIN ('Dog','Cat')

[7 marks]

18. The SQL to show pets who have more than 1 appointment booked is:

a) SELECT petName, p.petID, SUM(*) FROM tAPPOINTMENT a INNER JOIN tPET p ON p.petID = a.vetID GROUP BY p.petID, petName HAVING SUM(*) >=1

b) SELECT petName, p.petID, SUM(*) FROM tAPPOINTMENT a INNER JOIN tPET ON a.petID = p.petID GROUP BY p.petID, petName HAVING SUM(*) >1

c) SELECT petName, p.petID, COUNT(*) FROM tAPPOINTMENT a INNER JOIN tPET p ON a.petID = p.petID GROUP BY a.petID, petName HAVING COUNT(*) =1

d) SELECT petName, p.petID, COUNT(*) FROM tAPPOINTMENT a INNER JOIN tPET p ON a.petID = p.vetID GROUP BY p.petID, petName HAVING COUNT(*) >=1

e) SELECT petName, p.petID, COUNT(*) FROM tAPPOINTMENT a INNER JOIN tPET p ON a.petID = p.petID GROUP BY p.petID, p.petName HAVING COUNT(*) >1

[8 marks]

19. The petOwner field in tPET:

a) could link to an OWNER table, showing owner information

b) shows that an owner can have more than one pet

c) is a primary key

d) could be of the datatype CHARVAR

e) A and B only

[9 marks]

20: Consider the following 3 SQL statements.

1. SELECT * FROM tPET p INNER JOIN tAPPOINTMENT a

ON p.petID = a.petID where p.petName LIKE 'R%'

2. SELECT * FROM tPET p INNER JOIN tAPPOINTMENT a ON p.petID = a.petID

where petName IN (SELECT petName FROM tPET WHERE petName LIKE 'R%' )

3. SELECT * FROM tPET p INNER JOIN tAPPOINTMENT a ON p.petID = a.petID where petName IN (SELECT petName FROM tPET WHERE petName LIKE 'R%')

Which of the following statements is / are TRUE?

a) These statements will all return the same result as each other (assuming the data is as shown in the tables in figure 1)

b) These statements will all return the same result as each other if there is an additional appointment for a pet named ‘Riggler’)

c) These statements will all show an appointment that Rover has with Bill (assuming the data is as shown in figure 1)

d) A and B only

e) A, B and C

[9 marks]