10: Databases Flashcards
1
Q
What is a database?
A
An organised collection of data
2
Q
What is the benefit of organising data into a database?
A
Allows for easy:
- Adding
- Modification
- Deletion
- Searching
3
Q
What are the benefits of using electronic databases over paper-based systems?
A
- easier to retrieve, add, delete, update and modify data
- Easier to back up and make copies of data
- Can be accessed by multiple people at the same time and from different locations
4
Q
What are the 3 features of a database?
A
- data is stored in a table (entity/file)
- tables contain records (rows/tuples)
- tables contain fields (columns/attributes)
5
Q
Why might someone choose to store data in a flat-file database?
A
- Very simple
- quick to set up
- require little expertise to maintain
- suitable for storing small amounts of data
6
Q
What are some typical uses of flat file databases?
A
- Storing contact details
- Small product database
- Maintaining a game or music collection
7
Q
Why might someone not choose to use a flat file database?
A
- become very inefficient as repeated data takes up more space
- they become slow to query and difficult to maintain
8
Q
What is a primary key?
A
A field in a record that is unique
9
Q
what is a foreign key?
A
If two tables have a relational link, the primary key of one table is the foreign key of the other table
10
Q
What are the 3 types of relationship tables/databases can have?
A
- one to one
- one to many
- many to many
11
Q
What are the 4 ways you can handle data?
A
- Capturing
- Selecting
- Managing
- Exchanging
12
Q
What are the 4 ways of capturing data?
A
- Paper-based forms
- Optical character recognition
- optical mark recognition
- other methods
13
Q
How you use paper-based forms to capture data?
A
- involves a human reading the form and typing the information into a computer-based system
- to reduce human error and make the process as quick as possible, you can make sure:
— every part of the form is clearly labelled
— there are instructions to complete the form in a black pen
— instructions to complete in capital letters
— tick boxes are used
— squares are used for entering each letter separately
14
Q
How is OCR used to capture data?
A
- automatically reads text by interpreting the shape of the letters
- post offices uses OCR to read postcodes and route mail
- road cameras use automatic number plate recognition
15
Q
How is OMR used to capture data?
A
- used for multiple choice tests and lottery tickets
- very fast and efficient
- significantly reduces the possibility of human error
16
Q
What are other methods of capturing data?
A
- magnetic stripes
- chip and pin
- barcodes
- QR codes
- sensors
17
Q
What are 2 ways of selecting data?
A
- SQL
- Query by example
18
Q
How is SQL used to select data?
A
- allows for fast and efficient retrieval, deletion and manipulation of data
- uses commands: SELECT, FROM, WHERE
19
Q
how is Query by Example used to select data?
A
- graphical query language, making use of visual tables where the user would enter commands and conditions
- Once a query is built, it is converted into statements that can be executed against the database
- this means the user doesn’t need to remember the finer details of SQL syntax
20
Q
What can both SQL and QBE allow you to do?
A
- specify tables
- specify fields
- specify criteria
- specify output sorting
- use boolean expressions
21
Q
What commands can you use to manage data in SQL?
A
- UPDATE
- DELETE
- INSERT
22
Q
What is the purpose of a database management system?
A
- provides a layer of abstraction for the user and programmer
23
Q
What are the main features of a DBMS?
A
- preventing the creation of duplicate primary keys
- enforcing validation rules
- providing secure access
- providing encryption
- providing program data independence
- managing multiple users
24
Q
What must be considered before exchanging data?
A
- The common formats used for exchanging data
- manual methods of data exchange
- automatic methods of data exchange
25
What are the common formats of exchanging data?
- XML
- JSON
- Comma-separated values file
26
What is XML and JSON?
- both human-readable, open formats for structuring data
- common standards designed for storing and transporting data
- as long as one system exports data using one format, another system can accept the same data and know the format
27
What are the manual methods of exchanging data?
- memory stick
- optical media
- removable hard disk
- email
- paper-based
28
How do CSV files exchange data?
- each record is stored on a separate line in the file, and each field is separated by a comma
- as the structure is fixed and known, import routines can be written to extract the data from a CSV file
29
What are the automatic methods of exchanging data?
- Electronic Data Interchange (EDI): a protocol between two systems to facilitate the exchange of data
- popular uses include automatic order placement systems for a shop's stocks
30
What are the three types of normal form?
- First normal form (1NF)
- Second normal form (2NF)
- Third normal form (3NF)
31
What is normalisation?
The concept of splitting tables in a database and arranging the data to move it from 1NF -> 2NF -> 3NF
32
What is 0NF
a flat file before any normalisation
33
What are the five rules that have to be followed to get to 1NF?
1. All field names must be unique
2. Values in fields should be from the same domain
3. Values in fields should be atomic
4. No two records can be identical
5. Each table needs a primary key
34
What are the two rules that must be followed to get to second normal form?
1. The data is already in 1NF
2. Any partial dependencies have been removed
35
How do you fix a many-to-many relationship?
- create a linking table
- Assign the primary keys from the tables as the composite key for the new linking table
- this becomes 2 separate 1-to-many relationships joined by the new table
36
What are the two rules that need to be followed to get to 3NF?
1. The data is already in 2NF
2. Any transitive dependencies have been removed
37
What is data integrity?
The maintenance and consistency of data in a data store. The data must reflect the reality it represents
38
What is referential integrity?
The accuracy and consistency of data within a relationship
39
What is transaction processing?
Any information processing that is divided into individual, indivisible operations called transactions
40
What is CRUD?
Refers to the base functionality of a database
C: create
R: read
U: update
D: delete
41
What is ACID?
a set of rules DBMS's must follow to ensure data integrity
A: atomicity
C: consistency
I: isolation
D: durability
42
What is atomicity?
A change to a database is either completely performed or not at all
43
What is consistency?
Any change in the database must retain the overall state of the database
44
What is 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
45
What is durability?
Once a change has been made to a database, it must not be lost due to a system failure
