1.3.2 - Databases

Question 1

What is a database?

Answer 1

Are structured and persistent stores of data for ease of processing i.e. on secondary storage, non-volatile.

-Easier to add, delete, modify and update data
-Data can be backed up and copied easier
-Multiple users, from multiple locations, can access the same database at the same time

Question 2

What are fields?

Answer 2

A single piece of data in a record

Question 3

What is a record?

Answer 3

A group of related fields, representing one data entry

Question 4

What is a table?

Answer 4

A collection of records with a similar structure

Question 5

What is a primary key?

Answer 5

A unique identifier for each record in a table. Usually, an ID number

Question 6

What is a foreign key?

Answer 6

A field in a table that refers to the primary key in another table. Used to link tables and create relationships

Question 7

What is a secondary key?

Answer 7

An attribute that allows a group of records in a table to be sorted and searched differently from the primary key and data to be accessed in a different order.
Basically an index that the DBMS will look for in order to find the relevant record.

Question 8

What is a database management system?

Answer 8

Software used to manage databases. Examples include MySQL, Oracle, Microsoft SQL Server, PostgreSQL

Question 9

What is a composite key?

Answer 9

A combination of (2 or more) fields that is unique for all records

Question 10

What is a flat-file database?

Answer 10

These are simple data structure tables that are easy to maintain as only a limited amount of data is stored.

They are of limited use because they may have redundant and inconsistent data.

No specialist knowledge is needed to operate.

They are harder to update.

Data format is difficult to change.

Question 11

What is a relational database?

Answer 11

Based on linked tables (relation).

Each table is based on an entity and has rows and columns.

Each row (tuple) in a table is equivalent to a record and is constructed in the same way.

Each column (attribute) is equivalent to a filed and must have just one data type.

One column or combination of columns must be the PK.

Reduces and avoids data duplication and data redundancy to save storage space.

Improves data consistency and data integrity.

Easier to change data and data format.

Data can be added more easily.

Improves levels of data security so easier to control access to data.

Question 12

What is a ERD?

Answer 12

Entity relationship diagram, necessary when planning relational databases. Uses a diagram to show how data tables relate to each other and help with reducing redundancy (Repeated data)

Question 13

What are three relationships that are used in ERDs?

Answer 13

One to One relationship - Makes no sense to put the data in separate tables in this case.
One to Many relationship - Used in most well-designed RDBs
Many to Many relationship - Will lead to data redundancy.

Question 14

What is data redundancy?

Answer 14

Is unnecessary repetition of data that leads to inconsistencies

Data should have redundancy so if part of a database is lost it should be recoverable from elsewhere.

Redundancy can be provided by RAID setup or mirroring servers.

Question 15

What is normalisation in databases?

Answer 15

A formal, methodical process to design data tables optimally.

Goes through distinct stages to lead to at least 3NF.

Resolves m-m (many to many) relationships.

Minimises repetition to reduce data redundancy.

Ensures all attributes in a table depend on one another to avoid the need to update multiple data entries when
changing a single attribute to reduce the chances of mistakes.

Question 16

What is 1NF?

Answer 16

First Normal Form Separates out the multiple items/sets of data in each row

Following actions need to be followed:

1) Each record must never contain more than one value (EI Name cannot have a first name and last name)
2) Each row must be unique (using a primary key)
3) Each column name must be unique (cant have city 1 and city 2)
4) Must be no repeating groups (if there are then the repeated record must be put into a new table)

Question 17

What is 2NF?

Answer 17

All data must depend on the primary key.
Removes data that occurs in multiple rows and puts these data items in a new table.
Creates relationships/links between the tables as necessary by repeated fields.

Question 18

What is 3NF?

Answer 18

The primary key must fully define all columns and each column should not depend on any other key.
Removes non-key dependencies (i.e. transitive relationships) to their own linked table so every non-key attribute/field depends on the key, the whole key and nothing but the key!

Question 19

What is indexing?

Answer 19

Indexing is a technique used to speed up data retrieval in a database

It works in a similar way to the index in a book

If a student had a maths book and wanted to find the section on factorising, they could start at the first page and look at each page in turn until they found the section they wanted

But this would be slow, so it is better to look in the index to find where the factorising section is and just go directly to it

Likewise in a database, certain columns can be indexed so that the DBMS does not have to look at every single record during a search and can just go to the relevant records directly

This can greatly speed up searches

Fields that are indexed are known as secondary keys

Question 20

What are three different types of data files?

Answer 20

Serial files
Sequential files
Indexed sequential files

Question 21

What are serial files?

Answer 21

Are relatively short and simple files.

Data records are stored chronologically i.e. in the order in which they are entered.

New data is always appended to the existing records at the end of the file.

To access a record, you to search from the first item and read each preceding item.

Easy to implement.

Adding new records is easy.

Searching is easy but slow.

Question 22

What are sequential files?

Answer 22

Are serial files where the data in the file is ordered logically according to a key field in the record.

Question 23

What are indexed sequential files

Answer 23

Records are sorted according to a PK

A separate index is kept that allows groups or blocks of records to be accessed directly and quickly

New records need to be inserted in the correct position and the index has to be maintained and updated to be kept
in sync with the data

Is more difficult the manage but accessing individual files is much faster

More space efficient

More suited to large files

Question 24

What doe DBMSs do?

Answer 24

Is software that creates, maintains and handles the complexities of managing a database.

May provide a UI.

May use SQL to communicate with other programs.

Provides different views of the data for different users.

Provides security features.

Finds, adds and updates data.

Maintains indexes.

Enforces referential integrity and data integrity rules.

Manages access rights.

Provides the means to create the database structures: queries, views, tables, interfaces and outputs.

Question 25

What is SQL?

Answer 25

Is a declarative database language that allows the creation, interrogation and alteration of a database.

Question 26

What is referential integrity?

Answer 26

Transactions should maintain referential integrity. This means keeping a database in a consistent state so changes to data in one table must take into account data in linked tables, e.g. you cannot delete data that is linked to existing data in another table. It is often enforced by DBMS.

Question 27

What are transactions?

Answer 27

Are changes in the state of a database:

• Addition of data
• Deletion of data
• Alteration of data

Question 28

What rule must transactions follow?

Answer 28

ACID Rules

Question 29

What are the 4 ACID rules?

Answer 29

Atomicity: They should either succeed or fail but never partially succeed.

Consistency: The transaction should only change the database according to the rules of the database.

Isolation: Each transaction shouldn’t affect or overwrite other transactions concurrently being processed.

Durability: Once a transaction has been started it must remain no matter what happens.

Question 30

What is record locking?

Answer 30

Is the technique of preventing simultaneous access to objects in a database in order to prevent updates from being lost or inconsistencies in the data arising.

A record is locked whenever a user retrieves it for editing or updating.

Anyone else attempting to retrieve the same record is denied access until the transaction is completed or cancelled, e.g. if one transaction is amending a record, no other transaction can until the first transaction is complete.