4.10 Databases Flashcards
What is an entity?
An entity is the object / thing of interest about which data is to be recorded (a table in SQL).
What is an attribute?
An attribute is property of an entity (a field in SQL).
What is a relationship?
A relationship is the link or association between entities (primary keys ←→ foreign keys)
What is a key (in a database)?
- A key is the name given to a special field in a database where the values in the field can be used to identify particular records, e.g. CustomerID.
- Sometimes a field that is already in the database can be used as the key field.
What is a primary key?
A key which has a unique value for each record, and acts as a unique identifier for each record
3 problems with a flat file database
- One small change can involve tediously changing many records
- Deleting the only instance of a record containing a particular category will also delete that category
- You can’t add a new category until you create a record that contains that particular category
How do you solve the problems with flat file databases?
- Relational databases that are made up of two or more linked tables.
- Linked tables can be used to find records which are linked together by using the value of the shared key column. They are a feature of relational databases.
- This shared key column acts as the primary key in the table in which its defined, and as a foreign key in the table it is used.
What is a flat file database?
A simple store of information
- Columns are called fields
- Rows are called records
- A table is a group of records
What is a composite key?
A key composed of two or more attributes that together uniquely identify a record.
When is normalisation (database) achieved?
- When there is no redundant data and all related data is stored together
- (When each attribute depends on the key, the whole key and nothing but the key)
What is the saying for database normalisation?
“Each attribute must depend on the key, the whole key, and nothing but the key”
Requirements for 1st normal form
- Every record has a primary key
- No repeating groups → must include a copy of the primary key in the new table (creating a composite key)
- The data in each field must be atomic (i.e. data cannot be sensibly subdivided e.g. name → fname & sname)
What is a repeating group?
A set of attributes whose structure repeats between rows
Requirements for 2nd normal form
No partial dependencies
this means…
- Do all nonkey attributes depend on all parts of the composite key?
- If not, take them out and create a new entity.
(So check all tables with composite keys)
Requirements for 3rd normal form
No non-key dependencies
- All non-key attributes are checked to see if they are only dependent on the primary key.
How do you identify the key in an entity relationship diagram?
Underlining
SQL: Create a database called school
CREATE DATABASE school
What does DDL stand for (in SQL)?
Data Definition Language
SQL: Create a table called students
(StudentNumber as primary key, forename, surname, and date of birth)
CREATE TABLE students
(
StudentNumber INT(8) PRIMARY KEY,
forename VARCHAR(32),
surname VARCHAR(32),
DateOfBirth DATE
)
SQL: What is the keyword for deleting a database/table?
DROP …
SQL data types
CHAR(size) // → fixed length stringVARCHAR(size) // → string (with a maximum length)ENUM // → string object that has to be chosen from a list of possible valuesBOOL / BOOLEANINT(size) // → size is the number of digitsFLOAT(size,d) // → size is the number of digits , with d digits after the decimal pointDATETIMEDATETIME
What does DML stand for (in SQL)?
Data manipulation language
SQL: all strings beginning with ‘str’
LIKE ‘str%’
SQL: Any strings at least five characters long
LIKE ‘_____%’
SQL: between two numbers
… WHERE StudentNumber BETWEEN 10 AND 100
SQL: Selecting from two tables
SELECT table1.attributeX , table2.attributeY
FROM table1 , table2
WHERE table1.primarykey = table2.foreignkey
AND …
SQL: ordering results
ORDER BY surname
ORDER BY surname ASC
ORDER BY surname DESC
SQL: Inserting data
INSERT INTO students VALUES(__,__),(__,__),(__,__)
INSERT INTO students (surname , forename) VALUES (__…
//if you can’t remember what order you’re supposed to do it in)
SQL: update a forename to ‘Harry’ where…:
UPDATE students SET forename = ‘Harry’ WHERE…
SQL: Delete record
DELETE FROM students WHERE …
What is the purpose of a client server database?
To provide simultaneous access to a database for multiple clients
Why does concurrent access need to be managed in a client server database?
To preserve the integrity of the database
State one problem that concurrent access could result in
Updates could be lost if two clients edit a record at the same time
4 ways to manage concurrent access
- Record locks
- Serialisation
- Timestamp ordering
- Commitment ordering
What does it mean for a schedule of transactions to be serialised?
If the schedule of transactions has the same effect as an equivalent serial schedule
What is a transaction?
A set of operations that are grouped together as a single logical unit
Why is serialisation useful?
It allows us to interleave and overlap operations within multiple transactions, reducing the time taken for transactions to be completed
How does record locking work?
- When a transaction on a record starts, a lock is set on the record
- Other transactions cannot access the record until the lock is released
2 disadvantages of record locking
- Slows the system down slightly
- Can lead to deadlock
How does timestamp ordering work? (2•)
- Timestamps are generated for each transaction. These timestamps indicate the order transactions occurred in
- The database server applies rules to determine if processing a transaction will result in loss of data integrity, and if so aborts the transaction
2 situations in timestamp ordering when a transaction would be aborted
- If transactions is trying to write to a record:
- then when the read or write timestamp on the record is greater that the time at which the transaction started
- If transactions is trying to read a record:
- then when the write timestamp on the record is greater that the time at which the transaction started
When can a transaction be committed?
When all of its operations have been carried out
What is commitment ordering?
Using an algorithm to determine an optimum order to commit half-complete transactions in order to reduce conflicts and deadlocks
What is a foreign key?
The primary key field of one table that appears in another table to make a link between the tables
