DATABASES Flashcards
(30 cards)
State the benefits of using a relational database instead of a flat file database
- Relational data allows for less redundancy of data / less repeated data
- Relational databases improve the consistency of data
- Relational databases allow for complex queries and/or searches to be performed
State the difference between a primary key and a foreign key
- A primary key will only appear once in a table / is a unique identifier
- A foreign key may appear multiple times in a table / may not be unique
- A foreign key links two tables together
Describe a method a streaming service could use to capture a new customer’s personal data
- Web form
- Can use validation to check for common errors
- Can check for duplicate values
- Data can be entered direct into the database / limited manual processing
- Can be done from remote locations
Identify methods of exchanging data with other computer systems
- CSV
- JSON
- XML
- SQL
Describe what is meant by a transaction being durable
- Data / transaction is not lost
- In case of power / system failure
Give one way that durability can be achieved for a completed transaction
- Completed transactions stored in secondary storage (data not stored long-term in RAM/cache)
Explain how record locking can be used to ensure that the ACID principle of isolation is achieved when carrying out multiple transactions
- The outcome of concurrent transactions is the same as if transactions were completed sequentially
- Record locking allows one user / process to access / modify record level data at any one time
- So data that is being used elsewhere cannot be modified / data that is being modified elsewhere cannot be used
What are the disadvantages of record locking
- Can cause delays (as users wait for access)
- Can cause deadlock
Describe what is meant by the term ‘referntial integrity’ and how this could potentially be broken
- Database / relationships are consistent (each foreign key links to an existing / valid primary key)
- If a record is removed all references to it are removed
- A foreign key value must have a corresponding Primary key value in another table
- e.g. If primary key is deleted / updated, foreign keys are no longer valid / changes should be cascaded
- e.g. Cascade delete can cause necessary records to be deleted due to records from associated tables being deleted
Describe what is meant by the term ‘Atomic’ in the context of ACID transactions.
- A transaction / review can only be fully complete or not complete (cannot partially complete)
State what the letters in ACID stand for
Atomicity
Consistency
Isolation
Durability
Describe the different types of format used to exchange data
- CSV (Comma Separated Value)
- A text file/format with values separated by commas (or some other delimiter)
- XML/eXtensible Markup Language
- A markup language that uses tags to denote data
- SQL / Structured Query language
- A language for creating/querying databases
- JSON / JavaScript Object Notation
- Test format that can easily be changed to and from JavaScript Options
Give an advantage and disadvantage of indexing a field in a database
- Advantage: Searches of the field can be performed more quickly
- Disadvantage: The index takes up extra space in the database
What is a secondary key (secondary index)
- Allows a database to be searched quickly
- Makes it possible to order and search attributes, which makes it easier to find specific patterns in the database
What are the advantages of flat file databases
- Quick to set up
- Require little expertise to maintain
(Suitable for storing small amounts of data)
What is Optical Character Recognition and gives examples form where it has been used
- Automatically reads text by interpreting the shape of the letters, which helps to speed up and automate data input
- The Post Office uses OCR software to read postcodes and route mail
- Road cameras use automatic number plate recognition software to handle congestion charging and identify drivers who are speeding
What is Optical Mark Recognition
- Used for multiple-choice tests or lottery tickets
- It is a very fast and efficient way of collecting data and inputting it in a database while significantly reducing human error
What are the advantages of OCR
- Cheaper than paying someone to manually enter large amounts of text
- Much faster than someone manually entering large amounts of text
- The latest software can recreate tables and the original layout
What are the advantages of OMR
- A fast method of inputting large amounts of data
- Only one computer needed to collect and process data
- OMR is much more accurate than data being keyed in by a person
What is meant by transaction processing
Any information processing that is divided into individual, indivisible operations called transactions
What are the base functionalities of relational databases
Create
Read
Update
Delete
What is meant by a transaction being in isolation
- A transaction must not be interrupted by another transaction
- The transaction must occur in isolation so other users or processes cannot access the data concerned
What is meant when a transaction is consistent
Any change in the database must retain the overall state of the database
What are the conditions for a table to be in 1NF
- All field names must be unique
- Values in fields should be from the same domain
- Values in fields should be atomic
- No two records can be identical
- Each table needs a primary key
