1.3.1 Databases

Question 1

What is a database?

Answer 1

A structured store of data. Usually consists of tables, fields, and records.

Question 2

What is a flat-file database, and the issues they face?

Answer 2

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.

Question 3

Define a relational database and the benefits of using them.

Answer 3

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

Question 4

Define and give an example of a primary key.

Answer 4

A field that has a unique value to act as a unique identifier for every record in that table.

UserID in a table of users

Question 5

Define and give an example of a foreign key.

Answer 5

A field that links to a (primary) key in a second table.

Provides a link between tables

Question 6

Define and give an example of a secondary key.

Answer 6

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.

Question 7

Define indexing and its associated pros and cons.

Answer 7

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.

Question 8

What three types of database relationships exist, and which one is prohibited?

Answer 8

1:1 – One to One
1:M – One to Many
M:M – Many to many

M:M - These need to be resolved using a middle table with many coming out of each side.

Question 9

What would a resolved M:M relationship diagram look like?

Answer 9

Question 10

Give some examples of how data can be captured.

Answer 10

Web Form

Optical Character Recognition (OCR)

Optical Mark Recognition (OMR)

Sensors

Question 11

Give some examples of the methods used to exchange data.

Answer 11

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.

Question 12

What is the SQL to select all data from a table?

Answer 12

SELECT column_name(s)
FROM table_name

or

SELECT * FROM table_name

Question 13

What is the SQL to delete data from a table?

Answer 13

DELETE FROM table_name
WHERE some_column=some_value

Question 14

What is the SQL to insert data into a table?

Answer 14

INSERT INTO table_name
VALUES (value1, value2…)

Question 15

What is the SQL to remove a table?

Answer 15

DROP TABLE table_name

Question 16

What is the SQL to remove a column?

Answer 16

ALTER TABLE table_name
DROP COLUMN column_name

Question 17

What is the SQL to join two tables together?

Answer 17

SELECT column_name(s)
FROM table_name1
JOIN table_name2ON table_name1.column_name = table_name2.column_name

Question 18

What is the SQL to select all fields from a table where the data in a column ends with ‘th’?

Answer 18

SELECT * FROM table_name WHERE column_name LIKE ‘%th’

Question 19

What is the SQL to update a value in a table?

Answer 19

UPDATE table_name
SET column1=value, column2=value
WHERE some_column=some_value