Databases

Question 1

State what is meant by the end user requirements of a database?

Answer 1

The end user requirements are the tasks that the database must be able to perform

Question 2

State what is meant by the functional requirements of a database?

Answer 2

The functional requirements of a database relates to the tasks that the database will be able to perform. The functional requirements take into account the inputs processes and outputs that a database is required to perform in order to function correctly.

Question 3

State what is meant by a data dictionary and why one might be used?

Answer 3

Data dictionaries are created to define the structure of a database, they are created during the design phase and act as an efficient reference point during the implementation stages.

Question 4

State the purpose of relational databases?

Answer 4

Relational databases allow information to be stored across multiple tables.

Question 5

Describe issues that using a relational database can mitigate?

Answer 5

Using relational databases that use primary and foreign keys helps us to avoid issues such as insertion, deletion, or update anomalies in a database

Question 6

State the types of relationships that can exist in a database?

Answer 6

• One to One relationship
• One to Many relationship

Question 7

Explain what is meant by a One to Many cardinality in relationships involving entities?

Answer 7

One to Many relationships occur when one entity is able to be present across many other related entities.

Question 8

State the role of an entity occurrence diagram?

Answer 8

Entity occurrence diagrams show the relationship between the occurrences of a particular entity

Question 9

State what is meant by an entity occurrence?

Answer 9

An entity occurrence is an example of a particular entity. E.g Maths, Computing, and Physics are all entity occurrences of the subjects entity

Question 10

State what is meant by a primary key?

Answer 10

A primary key can be defined as a piece of information that uniquely identifies each record in a table or in a relational database

Question 11

State the purpose of a foreign key?

Answer 11

A foreign key is a key that links two tables together and creates the relationship between the two tables.

Care must be taken when making changes to the foreign key as foreign keys being deleted can destroy the relationship between two entities in a relational database

Question 12

Explain what is meant by a surrogate key?

Answer 12

A surrogate key is a type of key that is used when an entity does not have a naturally occuring primary key, in this case it is possible to create a new field that acts as a unique identifier for each record in the table

Typically an autonumber field will be used as a surrogate key

Question 13

Explain what is meant by a composite key?

Answer 13

A composite key is a specific type of primary key that uses information from two fields in a table to create a new field that acts as a primary key.

Question 14

Explain what is meant by a compound key?

Answer 14

A compound key is a type of primary key that is created from two or more primary keys of other tables

Question 15

State the different aggregate functions that you are expected to know for higher?

Answer 15

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

Question 16

Explain where you would use the SUM aggregate function?

Answer 16

The sum aggregate function could be used to return the total sum of a numeric column or expression

Question 17

Explain where you would use the COUNT aggregate function?

Answer 17

The count aggregate function could be used to total the number of values stored in a given column

Question 18

Explain where you could use the AVG aggregate function?

Answer 18

The average aggregate function could be used to calculate the mean average value for a given numerical column or expression.

Question 19

Explain where you could use the MAX aggregate function?

Answer 19

The max aggregate function would display the largest numerical value found in a given column or expression

Question 20

Explain where you could use the MIN aggregate function?

Answer 20

The min aggregate function can be used to find the smallest numerical value in a given column or expression

Question 21

Describe some of the things that you should know about using aggregate functions?

Answer 21

• Aggregate functions can only be used in the SELECT clause of SQL
• You are able to use more than one aggregate function in the same select statement
• When you wish to include the field you are running an aggregate function on in the where clause you must include the complete aggregate function unless you are using multiple queries.

Question 22

State what aliases are used for?

Answer 22

Aliases can be used to temporarily change the name of something this can be useful if

• A field name is too long
• A field name would not make any sense to a user of the database

Question 23

Describe how to use an alias?

Answer 23

An alias uses the function word AS e.g

SELECT forname AS [‘FIRST NAME’]

Question 24

Explain how a GROUP BY statement is used in SQL?

Answer 24

A GROUP BY statement that we can use to group a set of records in our query together based on some set of criteria that depends on the question being asked

Question 25

State which order each SQL statement should be placed in to run a query?

Answer 25

SELECT
FROM
WHERE
GROUP BY
ORDER BY

Question 26

State what is meant by a wildcard?

Answer 26

A wildcard is a character that can be used to substitute for another character or indeed another set of characters

Question 27

What operator must we use when we are using a wildcard in SQL?

Answer 27

We have to use a LIKE operator for example

WHERE forname LIKE ‘L*’

Question 28

State what a * reperesents in SQL?

Answer 28

A * denotes a missing set of characters

Question 29

State what a ? represents in SQL?

Answer 29

A ‘?’ denotes a missing character in SQL

Question 30

Describe when a database can be described as fit for purpose?

Answer 30

A database is fit for purpose when it meets all of the requirements set out for it in the analysis stage meaning that it meets all of its functional requirements and its end user requirements