Unit 2, Databases

Question 1

What is meant by the end-user requirements?

Answer 1

the tasks that the user wishes to be able to perform

Question 2

What is meant by the functional requirements of a database?

Answer 2

the tasks that the database system will be able to perform

Question 3

What is meant by a one to many cardinality?

Answer 3

One-to-many relationships can exist when one entity can be present in many other entities (e.g in an airport database one plane can be capable of flying on many different routes)

Question 4

What is meant by a primary key?

Answer 4

A field that uniquely identifies a record in a relational database

Question 5

What is meant by a foreign key?

Answer 5

A foreign key is a primary key in another table that allows two tables to be linked together and ensures referential integrity

Question 6

What can we do when there is no field that is suited to be a primary key in a table?

Answer 6

We can create what is known as a surrogate key, one such surrogate key would be an auto number generator which generates a unique value for each record

Question 7

What is meant by a composite key?

Answer 7

A composite key is a specific type of primary key that uses the contents of two or more fields from a table to create a unique value

Question 8

What is meant by a compound key?

Answer 8

A compound key is similar to a composite. however, A compound key is a specific type of primary key that uses the contents of two or more fields from different tables

Question 9

What are the aggregate functions that we use in SQL?

Answer 9

• AVG ()
• SUM ()
• COUNT ()
• MAX ()
• MIN ()

Question 10

What is the purpose of an AVG () aggregate function?

Answer 10

Returns the average value of a numeric column or expression

Question 11

What is the purpose of the SUM () aggregate function?

Answer 11

Returns the total sum of a numeric column or expression

Question 12

What is the purpose of a COUNT () aggregate function?

Answer 12

Returns the number of rows of the database that match the WHERE clause of the SQL

Question 13

What does the MAX () aggregate function do?

Answer 13

MAX () returns the largest value in the selected column or expression

Question 14

What does the MIN () aggregate function do?

Answer 14

MIN () returns the lowest value in a selected column or expression

Question 15

What do aggregate functions require to not produce an error message?

Answer 15

Parameters

Like a pre-defined function an error message will appear if no parameters are given for the aggregate function to act on

Question 16

What is the range of numbers that the COUNT () aggregate function can return?

Answer 16

Any number that is >= 0 as COUNT should not be able to return any negative numbers

Question 17

What are the rules about mixing aggregate functions with non-aggregate functions and where do we use aggregate functions in SQL?

Answer 17

Aggregate functions are used in the SELECT statement, you can use two or more aggregate functions in the select statement however you cannot mix aggregate functions with non-aggregate functions as this will produce an error message

Question 18

What is meant by an Alias?

Answer 18

Aliases can be used if we want to temporarily change the name of something

Question 19

In which situations might an alias be used?

Answer 19

• When the field name is too long and we want to shorten it
• When the field name wouldn’t make sense to a user and for this reason we would need to change it to improve usability

Question 20

What is a GROUP BY statement used for in SQL?

Answer 20

GROUP BY is used to group a set of records together by some given criteria

Question 21

What takes precedence in SQL a GROUP BY or an ORDER BY?

Answer 21

The GROUP BY statement takes precedence and must precede the ORDER BY statement

Question 22

What is meant by a wild card?

Answer 22

A wild card is a character that can be substituted for another character or another set of characters

Question 23

How can we use wildcards in SQL?

Answer 23

Wildcards can be used in the WHERE statement and require a separate LIKE statement to be used e.g
WHERE firstName LIKE “L*”

Question 24

What does a * do in a wildcard?

Answer 24

• is used to denote a set of characters

Question 25

What does “?” do in a wildcard?

Answer 25

? is used to denote a single character

Question 26

What would this wildcard do WHERE surname LIKE “M*e” ?

Answer 26

This wildcard would find any surnames that start with an M and end with an e

Question 27

What do we do when testing a database?

Answer 27

We create a table that lists the expected results and the actual results and if the expected results match the actual results then the program is fit for purpose