Databases Flashcards
What is the purpose of a primary key in a database?
Uniquely identifies a record in a database (will most likely have No/Ref/Id/Code in its name)
What is the purpose of a foreign key in a database?
Used to link 2 tables together
What are examples of field types?
- Text
- Number
- Date
- Time
- Boolean
What field type would you use for a phone number and why?
Text because if a number starts with 0 then it would have to be stored as text otherwise the 0 will be lost
What is the purpose of a data dictionary?
Used to describe the structure of a database
What is a range check?
Checks that the field is within a certain range
Range Check (>=1 and <= 10)
What is restricted choice?
A drop down menu so that only certain options can be picked
What is a length check?
A field has to be a certain number of characters
What is a presence check?
The field can’t be left blank, it must have a value
What does the crows foot represent?
The many (crows foot/three lines) will always be at the table with the foreign key
What is referential integrity?
A foreign key must always have a matching primary key. This is known as referential integrity.
What is the structure of a SELECT statement?
Returns information from a database
SELECT
FROM
WHERE
ORDER BY
What is the structure of a UPDATE statement?
Used to alter information in a database
UPDATE
SET
WHERE
What is the structure of a INSERT statement?
Used to add a new record
INSERT INTO
VALUES
What is the structure of a DELETE statement?
Deletes a record from a database
DELETE FROM
WHERE
What should you do if you only want to update/delete one record?
Always use the primary key in the search criteria
What is an equi-join?
Using the foreign key to join the tables together
Goes in the search criteria
table1.primarykey = table2.foreign key
Describe how you would evaluate the accuracy of the expected output from an SQL statement
Compare what you expect to get as an output with what you actually get as an output
What is a data subject?
The people who are having their personal data stored
What is a data controller?
The person who decides what personal data is stored and how it is used
What are your rights for the GDPR?
- Access your information
- Have your information changed
- Have your information deleted
State the implications of the GDPR
- Data is stored securely
- Data is accurate
- Data is not kept for longer than needed
What is a functional requirement?
What the database should do
What is an end user requirement?
What the users should be able to do when using the system
