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

Data verification?

Answer 22

Ensuring that the information being entered into a system has been entered correctly.

Question 23

What are the two types of data verification?

Answer 23

Visual check and Double entry

Question 24

What’s Visual Check data verification?

Answer 24

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

Question 25

What's Double Entry data verification?

Answer 25

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

Question 26

What's data accuracy?

Answer 26

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

Question 27

What's normalization?

Answer 27

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

Question 28

At what point is a database considered normalized

Answer 28

When it meets third normal form

Question 29

What's 0NF

Answer 29

A flat file prior to normalization

Question 30

Define 1NF

Answer 30

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 31

Define 2NF

Answer 31

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

Question 32

Define 3NF

Answer 32

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

Question 33

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

Answer 33

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 34

What does SQL stand for?

Answer 34

Structured Query Language

Question 35

What are the different SQL commands and their uses?

Answer 35

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 36

Why is a join clause used in SQL?

Answer 36

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

Question 37

Basic syntax for inserting new records into tables?

Answer 37

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

Question 38

Basic syntax for deleting data from a table?

Answer 38

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

Question 39

Datatypes?

Answer 39

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

Question 40

Define a transaction

Answer 40

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

Question 41

Transaction feature?

Answer 41

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 42

Ideal carrying out of transactions?

Answer 42

Transactions would be carried out serially (one after another)

Question 43

Serialization?

Answer 43

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 44

Concurrent processing?

Answer 44

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

Question 45

What methods enable concurrent processing to be usable?

Answer 45

Record locking, Timestamp Ordering

Question 46

What is record locking?

Answer 46

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 47

What can be used to fix deadlocks?

Answer 47

Commitment ordering

Question 48

Commitment ordering?

Answer 48

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 49

Timestamp ordering?

Answer 49

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 50

SQL Commands for transaction processing?

Answer 50

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 51

ACID stands for?

Answer 51

Atomicity Consistency Isolation Durability

Question 52

Atomicity?

Answer 52

Transaction must be process entirely or not at all

Question 53

Consistency?

Answer 53

Transaction execution must maintain referential integrity rules

Question 54

Isolation?

Answer 54

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

Question 55

Durability?

Answer 55

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

Question 56

What is ACID?

Answer 56

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 57

Issues caused by multi-user databases?

Answer 57

Race conditions Deadlocks Data corruption Loss of Isolation

Question 58

How to solve issues caused by multi-user databases?

Answer 58

Locking mechanisms Different isolation levels Atomic transactions

Question 59

What is electronic data interchange?

Answer 59

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.