Databases Flashcards
State what is meant by the end user requirements of a database?
The end user requirements are the tasks that the database must be able to perform
State what is meant by the functional requirements of a database?
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.
State what is meant by a data dictionary and why one might be used?
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.
State the purpose of relational databases?
Relational databases allow information to be stored across multiple tables.
Describe issues that using a relational database can mitigate?
Using relational databases that use primary and foreign keys helps us to avoid issues such as insertion, deletion, or update anomalies in a database
State the types of relationships that can exist in a database?
- One to One relationship
- One to Many relationship
Explain what is meant by a One to Many cardinality in relationships involving entities?
One to Many relationships occur when one entity is able to be present across many other related entities.
State the role of an entity occurrence diagram?
Entity occurrence diagrams show the relationship between the occurrences of a particular entity
State what is meant by an entity occurrence?
An entity occurrence is an example of a particular entity. E.g Maths, Computing, and Physics are all entity occurrences of the subjects entity
State what is meant by a primary key?
A primary key can be defined as a piece of information that uniquely identifies each record in a table or in a relational database
State the purpose of a foreign key?
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
Explain what is meant by a surrogate key?
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
Explain what is meant by a composite key?
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.
Explain what is meant by a compound key?
A compound key is a type of primary key that is created from two or more primary keys of other tables
State the different aggregate functions that you are expected to know for higher?
- SUM()
- AVG()
- MAX()
- MIN()
- COUNT()
Explain where you would use the SUM aggregate function?
The sum aggregate function could be used to return the total sum of a numeric column or expression
Explain where you would use the COUNT aggregate function?
The count aggregate function could be used to total the number of values stored in a given column
Explain where you could use the AVG aggregate function?
The average aggregate function could be used to calculate the mean average value for a given numerical column or expression.
Explain where you could use the MAX aggregate function?
The max aggregate function would display the largest numerical value found in a given column or expression
Explain where you could use the MIN aggregate function?
The min aggregate function can be used to find the smallest numerical value in a given column or expression
Describe some of the things that you should know about using aggregate functions?
- 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.
State what aliases are used for?
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
Describe how to use an alias?
An alias uses the function word AS e.g
SELECT forname AS [‘FIRST NAME’]
Explain how a GROUP BY statement is used in SQL?
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
State which order each SQL statement should be placed in to run a query?
SELECT
FROM
WHERE
GROUP BY
ORDER BY
State what is meant by a wildcard?
A wildcard is a character that can be used to substitute for another character or indeed another set of characters
What operator must we use when we are using a wildcard in SQL?
We have to use a LIKE operator for example
WHERE forname LIKE ‘L*’
State what a * reperesents in SQL?
A * denotes a missing set of characters
State what a ? represents in SQL?
A ‘?’ denotes a missing character in SQL
Describe when a database can be described as fit for purpose?
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
