1.3.2 Databases

Question 1

Define a database

Answer 1

An organised collection of data

Question 2

Define a table

Answer 2

Stores data about a type of entity

Question 3

Define an attribute

Answer 3

A column in a table, called a field

Question 4

Define a primary key

Answer 4

An entirely unique identifier for an entity

Question 5

Define a composite key

Answer 5

A pair of two attributes to make a primary key

Question 6

Define a foreign key

Answer 6

A non-primary key attribute in one table which links to the primary key field in another table

Question 7

Define data integrity

Answer 7

This refers to the accuracy and reliability of data

Question 8

Define referential integrity

Answer 8

This refers to the data integrity in relational databases. It tidies up any effected records.

Question 9

What are the types of entity relationships?

Answer 9

1 to 1

1 to many

Many to many

Question 10

Define a flat-file

Answer 10

A single table which contains all the data, which can have repeating data or data which extends over multiple lines

Question 11

Define the stages of 1st Normal Form

Answer 11

• Each record has a primary key
• Data is atomic
• No repeating groups of attributes

Question 12

Define the stages of 2nd Normal Form

Answer 12

• Data is in 1NF
• There are no partial dependancies

Question 13

Define the stages of 3rd Normal Form

Answer 13

• Data is in 2NF
• There are no non-key dependencies

Question 14

What is SQL?

Answer 14

The standard tool for working with databases. It is a declarative language

Question 15

Explain the DELETE SQL command

Answer 15

DELETE FROM tblName

WHERE [criteria];

This deletes from the table any record which matches the criteria

Question 16

Explain the INSERT SQL command

Answer 16

INSERT INTO tblName (field-list)

VALUES (value-list);

This adds a record into the table with the specified values

Question 17

Explain the SELECT SQL command

Answer 17

SELECT field-list

FROM tblName

Question 18

Explain the DROP SQL command

Answer 18

DROP TABLE tblName

or DROP DATABASE dbName

or DROP INDEX field-name ON tblName

This will delete the item named

Question 19

Explain the JOIN SQL command

Answer 19

SELECT field-list

FROM tblName1

JOIN tblName2

On join-criteria

This allows data from 2 different tables to be selected, where the tables are linked with a foreign-primary key pair

Question 20

Explain the ORDER BY SQL command

Answer 20

… ORDER BY field1 [ASC/DESC];

This orders the selected data in either ascending or descending order according to the criteria

Question 21

What is the SQL wildcard?

Answer 21

*

This states to disregard what comes before/after the * for the given text

Question 22

What is a client-server database?

Answer 22

A database residing on a server, with client applications written to access the database

Question 23

What is concurrent requests?

Answer 23

When a request is made at the same time as another request

Question 24

What is a problem with concurrent requests?

Answer 24

If one request is to update data, while another is to read or update data, then a problem can arise

Question 25

What is a transaction in regards to client-server databases?

Answer 25

A single logical action / unit of work

Question 26

What are the features of a good database?

Answer 26

Atomicity

Consistency

Isolation

Durability

Question 27

Describe atomicity in regards to databases

Answer 27

The components of a transaction are atomic, it must succeed or fail

Question 28

Describe consistency in regards to databases

Answer 28

Ensuring an illegal transaction is rejected so that the integrity is upheld

Question 29

Describe isolation in regards to databases

Answer 29

Each transaction is dealt with in a way that doesn’t affect others

Question 30

Describe durability in regards to databases

Answer 30

Ensuring data is saved once a transaction is completed

Question 31

Define serialisation

Answer 31

The concept of multiple transactions executing concurrently with the effect of them executing serially

Question 32

Define record locking

Answer 32

The DBMS (Database Management System) will lock an affected record while it is involved in a transaction

Question 33

Define a deadlock in regards to databases

Answer 33

When two transactions are trying to access records being used by each other transaction, but both are locked

Question 34

Describe timestamp ordering in regards to databases

Answer 34

• Each record has timestamps for when it was last read and last updated
• Each transaction has timestamps for when it starts and ends
• The transactions are carried out in timestamp order
• The transaction will not go through if the timestamp of a record being updated is after the timestamp of a transaction starting