1.3.2 Databases Flashcards
What is a relational database?
A database that recognises the difference between entities, using a different table for each
What is an entity?
An item about which information is stored
What is a flat file database?
A database that consists of a single file, usualy one entity
What is a primary key?
A unique identifier given to each record in a table
What is a foreign key?
An attribute that links two tables together
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
What is meant by capturing data?
The process of getting information to be stored in a database
What is meant by selecting data?
The process of removing excess information to extract only the data required
What is meant by managing data?
Manipulating information collected in any way
What is the most common language used to manipulate information in databases?
Structured Query Language (SQL)
What is a one-to-one entity relationship?
Each table can only be linked to one other table
What is a one-to-many entity relationship?
One table can be associated with many other tables
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 can a many-to-many relationship be created?
Creating a third table
Using two one to many relationships to third table
What is normalisation?
Creating the best possible design for a relational database
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 is first normal form achieved?
No attribute can contain more than a single value
How is second normal form achieved?
In first normal form
No partial dependencies
How is third normal form achieved?
In second normal form
No non-key dependencies
What is a partial dependency?
When an attribute depends on part of the primary key
What is a non-key dependency?
When an attribute depends on the value of the primary key and nothing else
What is indexing?
Method that stores the position of each record when ordered by a certain attribute
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
