1.3.1 Databases Flashcards
What is a database?
A structured store of data. Usually consists of tables, fields, and records.
What is a flat-file database, and the issues they face?
- A database where all data is stored in one table
- Difficult to change the format of the data
- Space is wasted through redundant data
- Data is harder to update as it could be in multiple places
- Data might become inconsistent for example the amount of an item in stock might be reduced in one record but not in other records.
Define a relational database and the benefits of using them.
Multiple related tables
Simpler to change the format of data
Saves space by reducing data duplication
Maintains data consistency/integrity
Improved security as able to control access to data
Define a primary key
A field that has a unique value to act as a unique identifier for every record in that table. e.g. UserID in a table of users.
Define a foreign key
A field that links to a (primary) key in a second table.
Provides a link between tables
Define and give an example of a secondary key.
Not unique but are likely to be the fields that users are likely to want to search by. Surname in a user table.
Indexed allowing for faster searching.
Define indexing and its associated pros and cons.
The index gives the position of each record according to its primary key.
Advantage: Searches of indexed fields can be performed more quickly.
Disadvantage: The index has to be rebuilt on edit and takes up extra space in the database.
What three types of database relationships exist, and which one is prohibited?
1:1 – One to One
1:M – One to Many
M:M – Many to many (prohibited)
What would a resolved M:M relationship diagram look like?
M:M - These need to be resolved using a middle table with many coming out of each side.
Give some examples of how data can be captured.
Online Form
Optical Character Recognition (OCR)
Optical Mark Recognition (OMR)
Sensors
Give some examples of sensors and what they may be used for.
Altimeter: Measures height from sea level
Accelerometer: Measure forces – When it moves.
Gyroscope: Used to measure rotation.
Thermistor: To read the temperature
GPS: To determine geographical location
Magnetometer: To determine direction.
Heart rate sensor: Detects electrical activity in the heart
Give some examples of the methods used to exchange data.
CSV: A format with values separated by commas
JSON: Uses human-readable text to transmit data objects consisting of attribute-value pairs
XML: A mark-up language that uses tags to denote data.
API: A prewritten set of subroutines that provide access to a company’s data. Used by programmers to transfer data between computer systems.
What is the SQL to select all data from a table?
SELECT column_name(s)
FROM table_name
or
SELECT * FROM table_name
What is the SQL to delete data from a table?
DELETE FROM table_name
WHERE some_column=some_value
What is the SQL to insert data into a table?
INSERT INTO table_name
VALUES (value1, value2…)