Unit 2, Databases Flashcards
What is meant by the end-user requirements?
the tasks that the user wishes to be able to perform
What is meant by the functional requirements of a database?
the tasks that the database system will be able to perform
What is meant by a one to many cardinality?
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)
What is meant by a primary key?
A field that uniquely identifies a record in a relational database
What is meant by a foreign key?
A foreign key is a primary key in another table that allows two tables to be linked together and ensures referential integrity
What can we do when there is no field that is suited to be a primary key in a table?
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
What is meant by a composite key?
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
What is meant by a compound key?
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
What are the aggregate functions that we use in SQL?
- AVG ()
- SUM ()
- COUNT ()
- MAX ()
- MIN ()
What is the purpose of an AVG () aggregate function?
Returns the average value of a numeric column or expression
What is the purpose of the SUM () aggregate function?
Returns the total sum of a numeric column or expression
What is the purpose of a COUNT () aggregate function?
Returns the number of rows of the database that match the WHERE clause of the SQL
What does the MAX () aggregate function do?
MAX () returns the largest value in the selected column or expression
What does the MIN () aggregate function do?
MIN () returns the lowest value in a selected column or expression
What do aggregate functions require to not produce an error message?
Parameters
Like a pre-defined function an error message will appear if no parameters are given for the aggregate function to act on
What is the range of numbers that the COUNT () aggregate function can return?
Any number that is >= 0 as COUNT should not be able to return any negative numbers
What are the rules about mixing aggregate functions with non-aggregate functions and where do we use aggregate functions in SQL?
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
What is meant by an Alias?
Aliases can be used if we want to temporarily change the name of something
In which situations might an alias be used?
- 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
What is a GROUP BY statement used for in SQL?
GROUP BY is used to group a set of records together by some given criteria
What takes precedence in SQL a GROUP BY or an ORDER BY?
The GROUP BY statement takes precedence and must precede the ORDER BY statement
What is meant by a wild card?
A wild card is a character that can be substituted for another character or another set of characters
How can we use wildcards in SQL?
Wildcards can be used in the WHERE statement and require a separate LIKE statement to be used e.g
WHERE firstName LIKE “L*”
What does a * do in a wildcard?
- is used to denote a set of characters
What does “?” do in a wildcard?
? is used to denote a single character
What would this wildcard do WHERE surname LIKE “M*e” ?
This wildcard would find any surnames that start with an M and end with an e
What do we do when testing a database?
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
