1.3.2 Databases

Question 1

Whats a database?

Answer 1

An organised collection of related data

Question 2

What’s a flat file database?

Answer 2

A database in 0NF, where all data is stored in a single table, about a single entity. It’s useful for static (non-changing) data

Question 3

What’s an entity

Answer 3

A category about which data needs to be recorded e.g. a person, or thing. (A table in the conceptional phase)

Question 4

What’s a table

Answer 4

A Two-dimensional representation of data that is stored in rows and columns

Question 5

What’s a field

Answer 5

A single piece of data about an entity

Question 6

What’s a record?

Answer 6

A group of fields about a specific entity (AKA a row)

Question 7

What’s a relational database?

Answer 7

Where data is held in multiple tables that are linked via relationships.

Question 8

What’s a primary key?

Answer 8

A field used to uniquely identify each record in the table

Question 9

What’s a feature of a primary key field?

Answer 9

It’s automatically indexed

Question 10

What’s a composite primary key

Answer 10

When multiple fields are used to uniquely identify each record in the table

Question 11

What are the three types of relationships between entities?

Answer 11

One-to-one,
One-to-many,
Many-to-many

Question 12

What’s a foreign key?

Answer 12

A field that creates a relationship between two tables, appearing as the primary key in another table.

Question 13

What are the benefits of foreign keys?

Answer 13

They maintain the relationship between tables

Question 14

Why do we use junction tables?

Answer 14

To eliminate many-to-many relationships

Question 15

What are junction tables?

Answer 15

A junction table contains the primary key columns of the two or more tables you want to relate.

Question 16

What is data integrity

Answer 16

Maintaining and ensuring the reliability of data

Question 17

What needs to be considered when reinforcing data integrity in a database

Answer 17

Accuracy, completeness, consistency over lifecycle

Question 18

What is Entity integrity

Answer 18

Stipulating that every table must have a primary key

Question 19

What is domain integrity

Answer 19

Ensuring every attribute in a relational database is associated with a domain

Question 20

What’s a domain

Answer 20

The set of allowed values that an attribute can contain, e.g. numerical

Question 21

What is referential integrity

Answer 21

It’s a data quality concept that prevents data redundancy and inaccuracies

It a rule that foreign keys in one table can only refer to primary keys in
another table.

Question 22

What’s data validation?

Answer 22

The process of ensuring the accuracy and quality of data.

Question 23

What’s data verification?

Answer 23

The process of checking data for inconsistencies after it has been moved

Question 24

What are the two types of data verification?

Answer 24

Visual check and Double entry

Question 25

What's Visual Check data verification?

Answer 25

When a person checks that the data being entered from one document to another is the same

Question 26

What's Double Entry data verification?

Answer 26

Requires same info to be input twice into 2 separate fields The two values are then compared to check they're the same

Question 27

What's data accuracy?

Answer 27

Refers to how valid data is, or the processes used to ensure that data is trustworthy

Question 28

What's normalization?

Answer 28

The concept of splitting tables in a database and arranging the data to move it from 1NF - 3NF

Question 29

At what point is a database considered normalized

Answer 29

When it meets third normal form

Question 30

What's 0NF

Answer 30

A flat file prior to normalization

Question 31

Define 1NF

Answer 31

When... Each record has a primary key Data is of a single value type (atomic) There are no repeating groups of attributes Values in fields are from the same domain

Question 32

Define 2NF

Answer 32

No partial dependencies- each field depends on primary key Data in 1NF already

Question 33

Define 3NF

Answer 33

Data already in 2NF No non-key (transitive) dependencies All fields depend on the key, the whole key and nothing but the key

Question 34

What are the steps in eliminating many-to-many relationships?

Answer 34

Create a junction table Assign primary keys to initial tables Create a composite key for junction table (and provide foreign keys) Initial tables will both have one to many relationships with junction table

Question 35

What does SQL stand for?

Answer 35

Structured Query Language

Question 36

What are the different SQL commands and their uses?

Answer 36

SELECT- List of fields to be displayed FROM- List the table(s) data will come from WHERE- List of the search criteria (operators used here) ORDER BY- List the fields that data is to be sorted in (e.g. price ASC) * / (Wild Card) = 'All' LIKE- Used to search for a pattern BETWEEN- Used to search in an inclusive range IN- Used to search in a specific column/ field

Question 37

Why is a join clause used in SQL?

Answer 37

To combine data from two or more tables into one data set, by identifying the primary-to-foreign key relationship.

Question 38

Basic syntax for inserting new records into tables?

Answer 38

INSERT INTO "table" (field1, field2,...) VALUES (value1,value2,...);

Question 39

Basic syntax for deleting data from a table?

Answer 39

DELETE FROM table WHERE criteria; if criteria involves primary key- whole record deleted

Question 40

Datatypes?

Answer 40

CHAR VARCHAR BOOLEAN INT FLOAT- decimals DATE- dd/mm/yy (enlosed in hashtags) TIME CURRENCY

Question 41

Define a transaction

Answer 41

A single logical unit of work. A transaction can be a single operation but most transactions have multiple steps.

Question 42

Transaction feature?

Answer 42

All of the required steps need to be successfully executed before the transaction can be considered complete. Either all of the steps must happen, or none of them must happen.

Question 43

Ideal carrying out of transactions?

Answer 43

Transactions would be carried out serially (one after another)

Question 44

Serialization?

Answer 44

Multiple transactions are executed one after another. A transaction can only be serialized if it can be carried out irrespective of any other transaction.

Question 45

Concurrent processing?

Answer 45

The simultaneous execution of multiple tasks or transactions. Improves performance and efficiency of multi-user databases.

Question 46

What methods enable concurrent processing to be usable?

Answer 46

Record locking, Timestamp Ordering

Question 47

What is record locking?

Answer 47

A method of preventing simultaneous access to the same record by different users of the database. The DBMS locks the affected record until the update is completed - an update can be part of a larger transaction Several records may need to be locked to ensure system integrity- causing potential deadlocks

Question 48

What can be used to fix deadlocks?

Answer 48

Commitment ordering

Question 49

Commitment ordering?

Answer 49

A serialization technique Ensures NO transactions are lost if two clients are trying to update a record at the same time Transactions are ordered according to when they were initiated and by their dependencies on one another Can prevent a deadlock by blocking one request until another is completed

Question 50

Timestamp ordering?

Answer 50

Each record has two time stamps: Time last read, Time last updated. Each transaction is assigned a unique timestamp when it starts Newer transactions have higher timestamps The database system executes transactions in the order of their timestamps If a transaction tries to read or write a data item with a lower/ older timestamp than the existing one, the operation is rejected.

Question 51

SQL Commands for transaction processing?

Answer 51

SET TRANSACTION- Initiates transaction COMMIT- Once all steps are written down it makes changes permanent ROLLBACK- Reverts interim changes to the point before the transaction was started

Question 52

ACID stands for?

Answer 52

Atomicity Consistency Isolation Durability

Question 53

Atomicity?

Answer 53

Transaction must be process entirely or not at all

Question 54

Consistency?

Answer 54

Transaction execution must maintain referential integrity rules

Question 55

Isolation?

Answer 55

Simultaneous transactions results remain unaffected; as if they were executed sequentially

Question 56

Durability?

Answer 56

Ensure data is saved once a transaction is completed- irrespective of hardware (multiple copies in different physical locations)

Question 57

What is ACID?

Answer 57

The set of properties of database transactions that will guarantee the integrity of data If a database system is good, it's ACID compliant

Question 58

Issues caused by multi-user databases?

Answer 58

Race conditions Deadlocks Data corruption Loss of Isolation

Question 59

How to solve issues caused by multi-user databases?

Answer 59

Locking mechanisms Different isolation levels Atomic transactions

Question 60

What is electronic data interchange?

Answer 60

The computer-to-computer exchange of documents such as purchase orders, between two companies/ entities. Replaces post or emails. All docs must be in standard form so that a computer can understand them.