1.3.2 Databases Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

What is a database?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Define a relational database and the benefits of using them.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Define and give an example of a primary key.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Define and give an example of a foreign key.

A

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

Provides a link between tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define and give an example of a secondary key.

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Define indexing and its associated pros and cons.

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Give some examples of sensors and what they may be used for.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Give some examples of how data can be captured.

A

Web Form

Optical Character Recognition (OCR)

Optical Mark Recognition (OMR)

Sensors

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Give some examples of the methods used to exchange data.

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is referential integrity?

A

Ensures that changes are consistent across a database

Changes to data in one table must also happen to data in linked tables (cascaded).

A foreign key value must have a corresponding primary key value in another table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Why is transaction processing required?

A

Without careful transaction processing, one transaction could accidentally overwrite another or half complete leading to inaccurate data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is record locking, why is it required and what issue can be faced?

A

Records should be locked when in use. If one transaction is amending a record, no other transaction should be able to modify it until the first transaction is complete.

The outcome of concurrent transactions is the same as if transactions were completed sequentially.

Can cause delays as users wait for access and could cause deadlock

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the four rules of transaction processing?

A

Atomicity

Consistency

Isolation

Durability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Define atomicity from the ACID model for transaction processing

A

Transactions should be fully complete, or not at all

17
Q

Define consistency from the ACID model for transaction processing

A

The transaction should only change the database according to the rules of the database (validation).

18
Q

Define isolation from the ACID model for transaction processing

A

Each transaction shouldn’t affect/overwrite other transactions concurrently being processed.

19
Q

Define durability from the ACID model for transaction processing

A

Committed data should be saved in secondary storage, so it is not lost in the case of system failure.

20
Q

What is good and bad redundancy in a database?

A

Good redundancy – if part of a database is lost it should be recoverable from elsewhere.

Bad redundancy – Having duplicate data within tables and records. This is fixed by normalising the database tables.

21
Q

What are the rules for data to be in 1st Normal Form (1NF)

A
  • No repeating fields
  • The data is atomic (Cannot be broken down anymore)
  • The data has a primary Key
22
Q

What are the rules for data to be in 2nd Normal Form (2NF)

A

It is in 1NF and every field is dependent on the primary key.

23
Q

What are the rules for data to be in 3rd Normal Form (3NF)

A

All attributes are dependent on the key, the whole key, and nothing but the key.

24
Q

Why is this table not in 3NF?

A

To be in 3NF all fields must only be dependent on the primary key. In this example the Destination Code and Destination Name have a connection and if one changes, the other must change as a result. This means they are dependent on both the primary key, and each other.

25
Q

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

A

SELECT column_name(s)
FROM table_name

or

SELECT * FROM table_name

26
Q

What is the SQL to delete data from a table?

A

DELETE FROM table_name
WHERE some_column=some_value

Notice that we do not use the asterix as we dont get to choose which columns to delete

27
Q

What is the SQL to insert data into a table?

A

INSERT INTO table_name
VALUES (value1, value2…)

28
Q

What is the SQL to remove a table?

A

DROP TABLE table_name

29
Q

What is the SQL to remove a column?

A

ALTER TABLE table_name
DROP COLUMN column_name

30
Q

What is the SQL to join two tables together?

A

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

31
Q

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

A

SELECT * FROM table_name WHERE column_name LIKE ‘%th’

32
Q

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

A

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