1.3.2 - Databases Flashcards
What is a Database?
A structured and persistent store of data for ease of processing; allowing for data to be:
- Retrieved quickly
- Updated easily
- Filtered for different views
What are Records
- Records are made up of fields
- Example: Person could be represented as record with fields as name, age and address
Describe a Flat-File Database
- A simple data structure table that is easy to maintain as only a limited amount of data is stored.
- They are of limited use because they may have redundant data which can waste space and leave inconsistent data.
- No specialist knowledge is needed to operate.
- They are harder to update and the data format is difficult to change.
Describe a Relational database
- Based on tables whose records are linked by certain fields (relation)
- Each table has data on one entity
- Each table has a relationship to other tables using primary and foreign keys
Benefits of a Relational Database over a Flat File Database
- Relational data allows for less redundancy of data (less repeated data)
- Relational databases improve the consistency of data
- Relational databases allow for complex queries and/or searches to be performed
Redundancy
- Redundant data is data that is repeated in a database
- A single table file is inefficient as it is full of redundant data
Primary Key
- A field that is a unique identifier for every record in that table
- Example - ID numbers
What is Concatenated Primary Key?
When more than one field is added together to form a unique primary key for a table.
Foreign Key
- Primary key in one table used as an attribute (foreign key) in another
- Provides a link between tables
- Represents many-to-one relationship
Secondary Key
An attribute that is indexed and allows a group of records to be searched for quickly; usually more memorable than PK
Different types of Entity Relationship Modelling (ERM)
- One to one
- One to many
- Many to Many
One to one relationship
- When one entity is linked to another entity
- Makes no sense to put in separate tables
One to many relationship
- When one entity is linked to several entities
- Used in most well designed RDBs
Many to many relationship
- When several entities are linked to several other entities
- Problematic; will lead to data redundancy
Database management
Handled by the database management system (DBMS) such as:
- MySQL
- Oracle
- Bigtable
Methods of Capturing Data
- Paper-Based Forms - Manual data input involving a human reading and typing the information into a system
- Optical Mark/Character Recognition (OCR/OMR) - Automatically reads text/marks by interpreting the shape of the letters; better for printed text and used for Road Cameras and Multiple Choice Tests
- Chip and Pin/QR Codes
Methods of Exchanging Data
- XML and JSON - Human-readable, open formats for structuring data
- CSV (Comma Separated Value file) - Stores each record on a separate line and each field is separated by a comma
What is Normalisation?
The process of arranging data in tables, and setting their relationships to move them through normal forms.
What is Indexing?
The process of creating a database index, which is a data structure that improves the speed of data retrieval operations on a dataset table at the cost of additional writes and storage space to maintain the index data structure.
What is Normal Forms?
A way of structuring the data in a relational database according to formal rules, in order to avoid problems of efficiency and security in accessing and maintaining the data.
What is 0NF?
A table with no normalisation. All data and all fields in one table.
What is 1NF?
- No Repeating fields; all fields must be unique
- Data must be atomic
- Database has a primary Key
What is 2NF?
Data must be in 1NF and any partial dependencies must be removed
What is 3NF?
Data must be in 2NF and any transitive dependencies must be removed
SQL
Structured Query Language: The language and syntax used to write and run database queries.
What is Referential Integrity?
Refers to the accuracy of and consistency of data across a database
Conditions for Referential Integrity:
- If a record is removed, all references to it are removed
- A foreign key value must have a corresponding Primary key value in another table.
What is Transaction Processing?
Information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit, and it can never be only partially complete.
What is ACID, and what do the terms stand for?
ACID: Atomicity, Consistency, Isolation, Durability.
A set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
Atomicity
A transaction must be processed in its entirety or not at all
Consistency
Any changes in the database must retain the overall state of the database
Isolation
Each transaction shouldn’t affect or overwrite other transactions concurrently being processed
Durability
Once a change has been made to a database, committed data/transactions must not be lost in case of power / system failure
What is Record Locking?
Allows one user to access record level data at any one time so data that is being used elsewhere cannot be modified
