1.3.2 Databases Flashcards
Define a database
An organised collection of data
Define a table
Stores data about a type of entity
Define an attribute
A column in a table, called a field
Define a primary key
An entirely unique identifier for an entity
Define a composite key
A pair of two attributes to make a primary key
Define a foreign key
A non-primary key attribute in one table which links to the primary key field in another table
Define data integrity
This refers to the accuracy and reliability of data
Define referential integrity
This refers to the data integrity in relational databases. It tidies up any effected records.
What are the types of entity relationships?
1 to 1
1 to many
Many to many
Define a flat-file
A single table which contains all the data, which can have repeating data or data which extends over multiple lines
Define the stages of 1st Normal Form
- Each record has a primary key
- Data is atomic
- No repeating groups of attributes
Define the stages of 2nd Normal Form
- Data is in 1NF
- There are no partial dependancies
Define the stages of 3rd Normal Form
- Data is in 2NF
- There are no non-key dependencies
What is SQL?
The standard tool for working with databases. It is a declarative language
Explain the DELETE SQL command
DELETE FROM tblName
WHERE [criteria];
This deletes from the table any record which matches the criteria
Explain the INSERT SQL command
INSERT INTO tblName (field-list)
VALUES (value-list);
This adds a record into the table with the specified values
Explain the SELECT SQL command
SELECT field-list
FROM tblName
Explain the DROP SQL command
DROP TABLE tblName
or DROP DATABASE dbName
or DROP INDEX field-name ON tblName
This will delete the item named
Explain the JOIN SQL command
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
Explain the ORDER BY SQL command
… ORDER BY field1 [ASC/DESC];
This orders the selected data in either ascending or descending order according to the criteria
What is the SQL wildcard?
*
This states to disregard what comes before/after the * for the given text
What is a client-server database?
A database residing on a server, with client applications written to access the database
What is concurrent requests?
When a request is made at the same time as another request
What is a problem with concurrent requests?
If one request is to update data, while another is to read or update data, then a problem can arise
What is a transaction in regards to client-server databases?
A single logical action / unit of work
What are the features of a good database?
Atomicity
Consistency
Isolation
Durability
Describe atomicity in regards to databases
The components of a transaction are atomic, it must succeed or fail
Describe consistency in regards to databases
Ensuring an illegal transaction is rejected so that the integrity is upheld
Describe isolation in regards to databases
Each transaction is dealt with in a way that doesn’t affect others
Describe durability in regards to databases
Ensuring data is saved once a transaction is completed
Define serialisation
The concept of multiple transactions executing concurrently with the effect of them executing serially
Define record locking
The DBMS (Database Management System) will lock an affected record while it is involved in a transaction
Define a deadlock in regards to databases
When two transactions are trying to access records being used by each other transaction, but both are locked
Describe timestamp ordering in regards to databases
- 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
