SLR10 Flashcards
What is a database
An organised collection of data
What does a database need to be able to do
Add data
Modify data
Delete data
Find data
Where were databases stored before the developments of technology
Physical paper format in filing cabinets
What did technology do to make the functionality of databases better
Made it easier to retrieve data, back up data, access data and modify data
How is a flat file data base stored
In a table
What are the names of the columns and rows in a flat file database
Fields and records
What are the benefits of a flat file database (3)
- They are really easy to set up
- They are really simple
- They require little maintenance
What are the losses of a flat file database (3)
- Can become inefficient quickly due to repeated data
- Making them slow
- Using up unnecessary space
What are the benefits of a flat file database compared to relational database
- Simple to manage
- Great for a small database
- In expensive
What are the drawbacks of a flat file database compared to relational database (2)
- Takes up a lot of unnecessary spade on the computer
- Increased data redundancy
What are the benefits of a relational database compared to flat file database (2)
- More data efficient
- Much better for expansion
What are the drawbacks of a relational database compared to flat file database (2)
- Very time consuming to set up
- Much more expensive to set up
What is a relational database
A database that has been split over multiple tables
What are the three different ways relational databases can be linked
- One to One
- One to Many
- Many to Many
When linking relational databases which is considered to be the worst
Many to Many
What is the most important thing in making a relational database effective
You are able to find things quickly
What is a primary key in a relational database
An item of data that keeps record in every table unique in some way
How can secondary index be made in relational databases
From a piece of data (field) this will be used as the search key
When working in a database what are the 4 ways of handling data
- Capturing
- Selecting
- Managing
- Exchanging
In a database what is the process of capturing
Getting the data into the database
In a database what is the process of selecting
Being able to query or search for the data
In a database what is the process of managing
This is the action of manipulating the data (add, edit and delete)
In a database what is the process of exchanging
Moving the data from one device to another
What effect will data being in a paper format have on the data itself
The data has to be imputed into a computer, to avoid human error and save time Data Capture Forms allow a computer to scan the data off of a paper without anyone having to input any information into the computer
What are the 2 different types of Data Capture Form
OCR (Optical Character Recognition)
OMR (Optical Mark Recognition)
What is Optical Character Recognition
A process that reads text by interpreting the shapes of the letters (works much better with printed text over handwritten as each letter is exactly the same whereas everyone’s handwriting is different)
Give an example of Optical Character Recognition
Speed cameras
What is Optical Mark Recognition
Reads markings then converts this into the correct data and files it
Give an example of Optical Mark Recognition
Multiple choice questions
Name the 5 manual methods of exchanging data
- Memory sticks
- Paper based
- Optical media
- Removable hard disk
How do databases exchange data automatically
Through an interface that deals with both databases known as an EDI (Electronic Data interchange) which takes data and sends it to a set destination
What is XML and JSON
XML = Extensible Markup Language
JSON = JavaScript Object Notation
These are both open formats for structuring data but JSON is hardly used as XML has mostly replaced it. Both used for transporting data
What is CSV
Comma-Separated Values
The structure of the database is known as it will always be the same due to each record being stored on a separate line and each field being separated by a comma. This make is it easy to extract data from a CSV file
What is normalisation
When a database is created with everytime a record or field is added there is a high chance of repetition this can lead to large amounts of unnecessary space being taken up. This can be fixed by rearranging the data this is known as the three normal forms (normalisation)
How do we get to the first normal form
All basic databases start at 0NF this is also known as a flat file database to turn this into a 1NF database we must
1. Make sure all fields are unique
2. The values in the fields should be from the same domain
3. Fields in values all need to be atomic (only one in each)
4. No two records can be identical
5. Each table needs a primary key
How do we get to second normal form
- First we need to make sure that the database is in 1NF
- Then remove all partial dependency’s (a partial dependency is when one or more of the fields depends on only one part of the primary key)
How do we get to the third normal form
- Make sure the database is in 2NF
- Remove any transitive dependencies
- Remove any none key dependencies
- Each key must only have one purpose
Can a database in 3NF be denormalized
Yes if normalised correctly
What happens when you use indexing in a database that has been normalised
Sacrifices a small amount of space to maintain the index data structure
What does SQL stand for
Structured Query Language
What it SQLs purpose
Fast / efficient retrieval, deletion and manipulation of data held in rational databases
What commands are used in SQL (10)
- SELECT
- OR
- FROM
- DELETE
- WHERE
- INSERT
- LIKE
- DROP
- AND
- JOIN
In SQL what does the SELECT command do
Allows you to select which field in a table you are referring to
In SQL what does the OR command do
Allows you to combine two statements into one in your WHERE broadening your field of search;
G% OR H% means any record beginning with “G” or “H”
In SQL what does the FROM command do
Allows you to select which table you are referring to
In SQL what does the DELETE command do
Also known as DELETE FROM. Allows you too delete a full record from a table
In SQL what does the WHERE command do
Allows you to select which record in a table you are referring to
In SQL what does the INSERT command do
Also known as INSERT INTO. Allows you to add / create an entirely new record into a table
In SQL what does the LIKE command do
Allows you to narrow your field of search in WHERE whilst not just choosing one record
In SQL what does the DROP command do
Also known as DROP TABLE which means the process of deleting an entire table
In SQL what does the AND command do
Allows you to add another statement to your WHERE further narrowing your field of search
In SQL what does the JOIN command do
Allows you to join two tables into one
How is information stored in a database
In records
How does SQL differ from a procedural language
SQL describes what needs to be achieved. A procedural language describes how something should be achieved.
What is SQL used for
Database creation and manipulation
What is transaction processing
Any information processing that is divided into individual, indivisible operations
In transaction processing can a transaction be partially complete
No
All transactions must either be fully completed or completely fail
What does CRUD stand for and what does it link up with in SQL
Create = INSERT / CREATE
Read = SELECT
Update = UPDATE
Delete = DELETE
What set of rules does the DBMS (Database Management System) follow
ACID
What does ACID stand for
Atomicity
Consistency
Isolation
Durability
What does Atomicity mean (ACID) (2)
- A change to a database must be either completely performed or not at all.
- It is known as Atomicity from its derivative word atom meaning it cannot be halved
What does consistency mean (ACID) (2)
- Referential integrity is where any change in the database must retain the overall state of the database
- A good example of consistency is moving money from one bank account into another. The money that has left the first account must equal the money that will be paid into the second account
What does isolation mean (ACID) (3)
- One transaction cannot be interrupted by another transaction
- A database management system enforces isolation by implementing a system of record locking
- The lock is only removed once the transaction is complete
What is record locking
When records in a transactions are placed in a read-only state
What does durability mean (ACID) (2)
- Once a change has been made to a database it must not be lost due to system failure
- To make sure all data is safe it is kept in a non-volatile storage