Databases Flashcards
What is a database
A structured, persistent store of data, organised so that it can be easily accessed, managed and updated
Describe a field
- Stores a single piece of data
- Has a specified data type
Define a record
A collection of fields composing an entry in a database
Define a table
A complete set of records
What do databases make it convenient to do
- Access data
- Update data
- Search for data
- Present data in a suitable way
Define flat-file databases
Databases in which all data is stored in a single table
Define relational databases
Consists of multiple tables linked together. Each table usually represents a ‘thing’ or an entity and the link are their relationships
Define primary key
A field that has a unique value for every record in a table
Define foreign key
The primary key in one table being used as a field in another table, creating a link
Define secondary key
A field by which records are likely to be searched and is therefore indexed
What is OMR
Optical Mark Recognition - A data input method that recognises the positions of marks made on paper
What is OCR
Optical Character Recognition - A data input method that converts images of text to machine editable text
What is a DBMS
Database Management System - Software used to control a database, overseeing amendments to its structure, and used to access and amend the data itself
Examples of DBMS
MySQL, Microsoft Access, PostgreSQL
What is SQL
Structured Query Language - A language used to find and manipulate data within a database
What is normalisation
Normalisation - The process of changing the structure of a relational database to remove the potential for data redundancy
What does atomic mean
A field that cannot be broken down into multiple smaller field
What are the rules for 1NF
- Data in every field is atomic
- No repeating fields
- Must have a primary key
Define composite key
A primary key made of two or more fields
What are the rules for 2NF
- In 1NF
- Every field must only depend on all of the primary key
What are the rules for 3NF
- In 2NF
- No fields must depend on any field other than the primary key
Explain SELECT … FROM
Used to retrieve specified fields from a specified table
Explain WHERE
Used to filter the results that are returned
Explain LIKE
Used with WHERE to select fields that match a given pattern
e.g. WHERE Name LIKE ‘New %’;
Explain DELETE
Used to delete records
e.g DELETE FROM Customers WHERE forename = ‘Jason’
Explain INSERT
Adds records into a table
e.g INSERT INTO Cust (forename, surname)
VALUES (‘Jason’, ‘Smith’)
Explain DROP
Used to delete whole tables
e.g DROP TABLE Customer
Explain JOIN
Used to combine information. It selects data every time it finds a match between the two specified fields
eg SELECT Subject.Subject, Teacher.Teacher
FROM Subject
INNER JOIN Teacher ON Subject.Teacher_Code = Teacher.Teacher_Code;
What is referential integrity
Guaranteeing the consistency of a database by ensuring no record points to another record that doesn’t exist
What is a transaction
A group of actions in a database creating a unit of work
What is ACID
Atomicity, Consistency, Isolation and Durability. These are concepts that underpin database transactions
What is atomicity
A change is performed or not performed. Half-finished changes must not be saved
What is consistency
Databases have sets of rules that must be adhered to by all the data they contain. Transactions should never leave a database in a state where any of these rules are broken
What is isolation
A running transaction must not be able to be affected by a different transaction.
What is durability
Once made, transactions cannot be lost. Changes must be written to storage in order to preserve them
What does record locking do
Prevents anyone accessing a record in use by another person
