Databases Flashcards
1
Q
What is a Relational Database
A
A database where data is held in tables (relations) and the tables are linked by common attributes
2
Q
What is a Flat File
A
A database that consists of a single file. The flat file will most likely be based around a single entity and its attributes
3
Q
What are the potential problems of using a flat file database
A
- Data might be inconsistent since data changed in one record may not be changed in another
- Space is wasted through repeated data
4
Q
What is an Entity
A
An item of interest
5
Q
What are the three types of keys
A
- Primary Key
- Secondry Key
- Foreign Key
6
Q
What is a Primary Key
A
A primary key is a unique identifier for each record in the table
7
Q
What a Secondary Key
A
- A secondary key allows a database to be searched quickly
- For example, secondary key can be set up on the surname attribute as it is unlikely someone will remember their ID but will remeber their surname it makes it easier to find specific people in the database
8
Q
What is a Foreign Key
A
- A foreign key is the attribute which links two tables together
- The foreign key will exist in one table as the primary key and act as the foreign key in another
9
Q
What is the name given to a key that is made up of multiple attributes
A
Composite key
10
Q
What are the three Entity Relationship Models
A
- One-to-one- each entity can only be linked to one other entity, such as the relationship between a husband and wife
- One-to-many - one table can be associated with many other tables, such as a doctor having multiple patients
- Many-to-many - one entity can be associated with many other entities and the same applies the other way round. An example is customers and products - each customer can have more than one product and each product can have more than one customer
11
Q
What is Normalisation
A
The process of coming up with the best possible layout for a relational database
12
Q
What does Normalisation try to achieve
A
- No redundancy (unnecessary duplicates)
- Consistent data throughout linked tables
- Records can be added and removed without issues
- Complex queries can be carried out
13
Q
What is First Normal Form
A
- No repeating fields / data
- Each record has a primary key
- Field names should only contain a single value
14
Q
What is Second Normal Form
A
- No partial dependencies (one or more fields is depedent on only part of the primay key)
- The data must be in first normal form
15
Q
What is Third Nomalisation form
A
- Contains no non-key dependencies (A non-key dependency means the attribute only depends on the value of the primary key and nothing else)
- The data must be in second normal form
16
Q
What is Indexing
A
- A method of numbering the position of each record in a database
- This is used to look up and access data quickly
17
Q
What does capturing data mean and give examples
A
- The process of getting the data that will be stored in a database
- Manual methods include transcribing data from a form that has been filled in
- Banks scan cheques using Magnetic Ink Character Recognition (MICR). All of the details excluding the amount are printed in a special magnetic ink which can be recognised by a computer but the amount must be entered manually
- Optical Mark Recognition (OMR) is used for multiple choice questions on a test.
- Other forms use Optical Character Recognition (OCR).
18
Q
What does selecting and managing data mean and give an example
A
- Selecting data involves only selecting data that fits a certain criteria to reduce the volume of input
- For example, a camera catching speeding cars will only select cars going above a certain speed. Then, background information will be removed so only the number plate is added to a record.
19
Q
What does exchanging data mean and give an example
A
- The process of transferring collected data
- One common way of exchanging data is EDI (Electronic Data Interchange). This doesn’t require human interaction and enables data transfer from one computer to another
20
Q
What are the eight types of SQL operations
A
- SELECT – finds data
- INSERT – adds data
- UPDATE – changes data
- DELETE – deletes records from a Table
- DROPS - drops an entire table
- Inner Join - selects records with matching values in both tables
- Order by - sorts the data in ascending or descending order
- * or ‘% - wildcards
21
Q
What is the general form for a SQL SELECT operation
A
SELECT
FROM
WHERE
22
Q
What is the general form for a SQL UPDATE operation
A
UPDATE
SET
WHERE
23
Q
What is the general form of an SQL INSERT operation
A
- INSERT INTO
- VALUES
24
Q
What is the general form for a SQL DELETE operation
A
DELETE
FROM
WHERE
25
What is the general form for a SQL Inner Join operation
SELECT
FROM
INNER JOIN
ON
26
What is the general form for a SQL Order by operation
SELECT
FROM
ORDER BY (ASC|DESC)
27
Describe an example of a nested SQL SELECT statement
SELECT
FROM
WHERE (condition) IN ( SELECT, FROM, WHERE)
28
Why is it a good idea to do a SELECT SQL statement before a DELETE SQL statement
A SELECT SQL returns a value, so the user can see what they are deleting before they delete it. If the user just deleted what they are removing would not be returned
29
What is Referential Integrity
* Referential integrity is the process of ensuring consistency
* It ensures that information is not removed if it is required elsewhere in a linked database
* If two database tables are linked, one of these tables cannot be deleted as the other table requires its contents
30
What is a Transaction
A single operation executed on data
31
What is ACID
* Atomicity – the transaction must be completed fully. If it is not completed fully it will not be recorded
* Consistency - a transaction must maintain the referential integrity rules between linked tables
* Isolation - simultaneous transactions should lead to the same result as if they were executed one after the other
* Durability - once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as in the event of a power cut (must write to non - volatile storage )
32
Give an example of Atomicity in the real world
* In a supermarket they may have a customer who is buying a new product
* Before the stock can decrease the customer may need to be added
* If the system crashes during the sale then a rollback would need to occur which meant that all of the transactions occur again so that they all occur together
* This could be implemented using a try and catch statement
33
What is Record Locking
* The process of preventing simultaneous access to records in a database is called record locking and it is used in order to prevent inconsistencies or a loss of updates
* While one person is editing a record, this ‘locks’ the record so prevents others from accessing the same record
34
What is the potential problem of record locking
If two users are attempting to update two records, a situation can happen where neither can proceed known as deadlock
35
Name a method that can prevent a deadlock from happening
* Time stamp ordering
* Each database object has a timestamp to say when it was last read or written.
* This ensures that records are correctly updated in the right order
36
How can redundancy be helpful
* Redundancy is the process of having one or more copies of the data in physically different locations.
* This means that if there is any damage to one copy the others will remain unaffected and can be recovered
