1.3.2 Databases Flashcards by Lucy Williams

What is a relational database?

A database that recognises the difference between entities, using a different table for each

How well did you know this?

Not at all

Perfectly

What is an entity?

An item about which information is stored

How well did you know this?

Not at all

Perfectly

What is a flat file database?

A database that consists of a single file, usualy one entity

How well did you know this?

Not at all

Perfectly

What is a primary key?

A unique identifier given to each record in a table

How well did you know this?

Not at all

Perfectly

What is a foreign key?

An attribute that links two tables together

How well did you know this?

Not at all

Perfectly

What is a secondary key?

An index other than the primary key used to search and sort through a database with more convenience and speed

How well did you know this?

Not at all

Perfectly

What is meant by capturing data?

The process of getting information to be stored in a database

How well did you know this?

Not at all

Perfectly

What is meant by selecting data?

The process of removing excess information to extract only the data required

How well did you know this?

Not at all

Perfectly

What is meant by managing data?

Manipulating information collected in any way

How well did you know this?

Not at all

Perfectly

What is the most common language used to manipulate information in databases?

Structured Query Language (SQL)

How well did you know this?

Not at all

Perfectly

What is a one-to-one entity relationship?

Each table can only be linked to one other table

How well did you know this?

Not at all

Perfectly

What is a one-to-many entity relationship?

One table can be associated with many other tables

How well did you know this?

Not at all

Perfectly

What is a many-to-many entity relationship?

One entity can be associated with many other entities and the same applies for the other

How well did you know this?

Not at all

Perfectly

How can a many-to-many relationship be created?

Creating a third table
Using two one to many relationships to third table

How well did you know this?

Not at all

Perfectly

What is normalisation?

Creating the best possible design for a relational database

How well did you know this?

Not at all

Perfectly

Give 4 things normalisation tries to accomplish.

No redundancy (unnecessary duplicates)
Consistent data throughout linked data
Records can be added/removed without issues
Complex queries can be carried out

How well did you know this?

Not at all

Perfectly

How is first normal form achieved?

No attribute can contain more than a single value

How well did you know this?

Not at all

Perfectly

How is second normal form achieved?

In first normal form
No partial dependencies

How well did you know this?

Not at all

Perfectly

How is third normal form achieved?

In second normal form
No non-key dependencies

How well did you know this?

Not at all

Perfectly

What is a partial dependency?

When an attribute depends on part of the primary key

How well did you know this?

Not at all

Perfectly

What is a non-key dependency?

When an attribute depends on the value of the primary key and nothing else

How well did you know this?

Not at all

Perfectly

What is indexing?

Method that stores the position of each record when ordered by a certain attribute

How well did you know this?

Not at all

Perfectly

What is indexing used for?

Looking up & accessing data quickly

State the function of the SELECT statement.

Collects fields from a given table

State the function of the FROM statement.

Specifies which table/tables the information to search is stored

State the function of the WHERE statement.

Specifies search criteria

How can the SELECT, FROM, WHERE statements be used together?

SELECT Attributes FROM Table WHERE Attribute / Criteria

State the function of the ORDER BY statement.

Species whether selected data is to be put in ascending/descending order

How can the ORDER BY statement be used?

ORDER BY Attribute Asc/Desc

State the function of the JOIN statement.

Combines rows from multiple tables based on a common field between them

How can the JOIN statement be used?

SELECT Table1.Attribute1 / Table2.Attribute2 ... FROM Table1 JOIN Table2 ON Table1.Attribute3 = Table2.Attribute3

State the function of the CREATE statement.

Allows new databases to be made

State the function of the SELECT statement.

Collects fields from a given table

What details must be specified for each attribute when using the CREATE function?

Whether or not attribute is primary key Data type of attribute Whether attribute needs to be filled in

State the function of the ALTER statement.

Add, delete or modify columns in a table

How is ALTER used to add a column to a table?

ALTER TABLE TableName ADD Attribute & DataTypes

How is ALTER used to delete a column to a table?

ALTER TABLE TableName DROP COLUMN Attribute

How is ALTER TABLE used to modify a column to a table?

ALTER TABLE TableName MODIFY COLUMN Attribute & DataType

State the function of the INSERT INTO statement.

Inserts a new record into a table

How can the INSERT INTO statement be used?

INSERT INTO Column1, Column2 VALUES Value1, Value2

State the function of the UPDATE statement.

Updates a record in a table

How can the UPDATE statement be used?

UPDATE TableName SET Column1 = Value1, Column2 = Value2 ... WHERE Column = Value

State the function of the DELETE FROM statement.

Deletes a record from a database table

How can the DELETE FROM statement be used?

DELETE FROM TableName WHERE Column = Value

What is referential integrity?

A process that ensures consistency to make sure information isn't removed if required elsewhere in a linked database

What is a transaction?

A single operation executed on data

What is transaction processing?

The processing of information within single transactions in line with ACID

What does ACID stand for?

Atomicity Consistency Isolation Durability

What is meant by Atomicity?

A transaction must be processed in its entirety or not at all

What is meant by Consistency?

A transaction must keep the referential integrity rules between linked tables

What is meant by Isolation?

Simultaneous execution of transactions must lead to the same result as if they were executed one after the other

What is meant by Durability?

One a transaction has been completed it will remain so

What is record locking?

The process of preventing simultaneous access of records in a database

What is record locking used for?

To prevent inconsistencies or a loss of updates within data

What is meant by deadlock?

When two or more transactions are waiting indefinitely for one another to give up locks

What is meant by redundancy?

The process of having one or more copies of data in physically different locations

What is the benefit of redundancy?

If there is any damage to one copy of data, others can be recovered