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
