Databases Flashcards
Exchanging data
What is a database
A structured, persistent (held for as long as required) store of data, organised so that it can be easily accessed managed and updated
What is a field
A single piece of data (represented by columns on a table)
- Has a specified data type
What is a record
A collection of fields (rows in a table )
What is a table
Complete set of records
What is a flat-file database
A database in which all data is stored in a single table
- Good for small amounts of data (prone to data redundancy)
Define data redundancy
When the same data is stored in different locations
- Wastes space
- Can lead to errors ( If all instances of the data aren’t changed during updates)
What are relational databases
Multiple tables linked together.
Each table represents an entity
How are relational databases linked
They are linked using primary and foreign keys
What is an entity
An instance of a class (anything which we can store data about)
What is a primary key
A field that has a unique value (Identifier) for every record in the table
Eg. registration plate (no two cars have the same one
What is a foreign key
The primary key in one table being used as a field in another table, creating a link
What is a secondary key
A field by which records are likely to be searched and is therefore indexed
Used when we want to search by a field that isn’t the primary key
What is an entity relationship diagram
A diagram showing the tables, which hold the entities and their links
What does it mean when entities have a one-to-one relationship
This is when each instance of an entity connects to a single instance of another entity
Eg. one student will have one mentor
What does it mean when entities have a one-to-many relationship
This is when a single instance of an entity may be linked to many instances of another entity. (a good way to resolve redundancy)
Eg. A computer science teacher may have many classes
What does it mean when entities have a many-to-many relationship
This is when multiple instances of one entity may be linked to multiple instances of another entity
Eg. Each order may have many items and each item can be ordered many times
How can we get rid of many-to-many relationships
Creating a link table containing its primary key and two primary keys from the other tables used as foreign keys (composite key)
What is optical mark recognition (OMR)
A data input method that recognises the position of marks made on paper
- Allows you to collect data from printed forms
What is Optical character recognition (OCR)
It can be used to convert documents into readable text
Eg. When speed cameras scan car number plates
What is the DBMS
Database management system - a layer of software that ensures the database is manipulated in a consistent way while ensuring integrity and security
Examples of the DBMS
- MySQL
- Microsoft Access
- PostgreSQL
What are the two main methods DBMSs offer to manage a database
- Structured query language (SQL)
- Query by example (QBE)
what is SQL
Structured query language - Used for selecting and updating data within a database.
How does QBE work
The user has a graphical interface into which required fields can be dropped, as well as allowing the user to set up conditions to filter the results
Give some examples of standard data formats that are used to exchange data
- SQL standard query language
- CSV comma-separated value
- XML eXtensible markup language
- JSON JavaScript Object Notation
How does SQL work
A DBMS can generate a sequence of SQL instructions that when run on a receiving DBMS, will create the relevant tables, relationships and insert data
How do CSV files work
This is a simple way of exchanging data in a CSV file where each field can be separated by a comma
How does XML work
This markup language uses tags to designate different things within a document.
In the case of a database, each record could be enclosed within tags, and inside those, each field enclosed within further tags
How does JSON work
A format that represents things as objects.
In the case of a database, each record is an object that can contain an array of objects that represent the fields.
JSON is beneficial when sending data to a JavaScript program, for example, on a webpage, as JavaScript can read the data directly
What is normalisation
The process of changing the structure of a relational database to remove the potential for data redundancy
What are the stages of normalisation
- Unnormalised form (UNF) - This is before any normalisation
- First normal form
- Second normal form
- Third normal form
What is first normal form
Data after the first stage of normalisation. Every record has a primary key, no repeating fields, and all are atomic.
What is a composite key
A primary key is made of two or more fields. While on their own, they may not be unique, each record’s combination of them will be
What is second normal form
Data after the second stage of normalisation. In 2NF, all data is in 1NF and has no field depending on only part of the primary key.
- Every field must only depend on all of the primary keys (only applies to tables with composite keys) - we can use a link table to do this
What is third normal form 3NF
Data in a relational database after the third stage of normalisation. In addition to being in 2NF, every field depends solely on the primary key and no other field.
What are the SQL commands you need to know
- DELETE
- INSERT
- DROP
- JOIN
- AND and OR
- LIKE
- WHERE
- SELECT … FROM
what is SELECT … FROM used for (SQL)
This is used to retrieve specified fields from a specified table
What is FROM used for (SQL)
Used to filter the results that are returned
What is LIKED used for (SQL)
This is used with WHERE to select fields that match a given pattern. It is used with the % wildcard which represents 0 or more characters
What is DELETE used for (SQL)
Used to delete records
What is INSERT used for (SQL)
Adds records into a table
What is DROP used for (SQL)
Used to delete whole tables
What is JOIN used for (SQL)
Used to combine information. It selects data every time it finds a match between the specified fields
What is referential integrity
Guaranteeing the consistency of a database by ensuring no record points to another record that doesn’t exist.
DBMS ensures that a change wasn’t made to one table that would impact another table
What does referential integrity ensure
- A record cannot be deleted if its primary key appears as a foreign key on another table
- A value cannot be entered into a foreign key field if there isn’t a corresponding primary key value
What is a transaction
A group of actions in a database creating a unit of work
What is ACID
Properties that a transaction must adhere to, to ensure the data within the database remains valid
What does ACID stand for
- Atomicity
- Consistency
- Isolation
- Durability
what does atomicity refer to (ACID)
A change is performed or not performed. Half-finished changes must not be saved
What does consistency refer to (ACID)
Databases have rules (called constraints) that must be adhered to by all the data they contain.
eg. values representing money must not go below 0
What does isolation refer to (ACID)
Transactions can only run simultaneously if their end result is the same as if they were run one after another.
Any new transaction is locked until the change is committed, then the new transaction is released.
What does durability refer to (ACID)
Once made transactions cannot be lost. Changes must be written to storage to preserve them.
Why do we need record-locking
It is usual for databases to be accessed at the same time.
This can cause issues if they are trying to access the same record at the same time as one person could overwrite the other’s changes.
Record locking avoids this by preventing anyone from accessing a record in use by another person.
What is a redundant system
One that duplicated the work of another. Eg. a mirror database
This is needed so that if the primary server with the database fails, the mirror can be switched to, preventing loss of service
What is a mirror database
An identical instance of a database kept on a separate server
