10: Databases Flashcards
What is a database?
An organised collection of data
What is the benefit of organising data into a database?
Allows for easy:
- Adding
- Modification
- Deletion
- Searching
What are the benefits of using electronic databases over paper-based systems?
- 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
What are the 3 features of a database?
- data is stored in a table (entity/file)
- tables contain records (rows/tuples)
- tables contain fields (columns/attributes)
Why might someone choose to store data in a flat-file database?
- Very simple
- quick to set up
- require little expertise to maintain
- suitable for storing small amounts of data
What are some typical uses of flat file databases?
- Storing contact details
- Small product database
- Maintaining a game or music collection
Why might someone not choose to use a flat file database?
- become very inefficient as repeated data takes up more space
- they become slow to query and difficult to maintain
What is a primary key?
A field in a record that is unique
what is a foreign key?
If two tables have a relational link, the primary key of one table is the foreign key of the other table
What are the 3 types of relationship tables/databases can have?
- one to one
- one to many
- many to many
What are the 4 ways you can handle data?
- Capturing
- Selecting
- Managing
- Exchanging
What are the 4 ways of capturing data?
- Paper-based forms
- Optical character recognition
- optical mark recognition
- other methods
How you use paper-based forms to capture data?
- 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
How is OCR used to capture data?
- 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
How is OMR used to capture data?
- used for multiple choice tests and lottery tickets
- very fast and efficient
- significantly reduces the possibility of human error
What are other methods of capturing data?
- magnetic stripes
- chip and pin
- barcodes
- QR codes
- sensors
What are 2 ways of selecting data?
- SQL
- Query by example
How is SQL used to select data?
- allows for fast and efficient retrieval, deletion and manipulation of data
- uses commands: SELECT, FROM, WHERE
how is Query by Example used to select data?
- 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
What can both SQL and QBE allow you to do?
- specify tables
- specify fields
- specify criteria
- specify output sorting
- use boolean expressions
What commands can you use to manage data in SQL?
- UPDATE
- DELETE
- INSERT
What is the purpose of a database management system?
- provides a layer of abstraction for the user and programmer
What are the main features of a DBMS?
- preventing the creation of duplicate primary keys
- enforcing validation rules
- providing secure access
- providing encryption
- providing program data independence
- managing multiple users
What must be considered before exchanging data?
- The common formats used for exchanging data
- manual methods of data exchange
- automatic methods of data exchange
What are the common formats of exchanging data?
- XML
- JSON
- Comma-separated values file
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
What are the manual methods of exchanging data?
- memory stick
- optical media
- removable hard disk
- paper-based
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
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
What are the three types of normal form?
- First normal form (1NF)
- Second normal form (2NF)
- Third normal form (3NF)
What is normalisation?
The concept of splitting tables in a database and arranging the data to move it from 1NF -> 2NF -> 3NF
What is 0NF
a flat file before any normalisation
What are the five rules that have to be followed to get to 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
What are the two rules that must be followed to get to second normal form?
- The data is already in 1NF
- Any partial dependencies have been removed
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
What are the two rules that need to be followed to get to 3NF?
- The data is already in 2NF
- Any transitive dependencies have been removed
What is data integrity?
The maintenance and consistency of data in a data store. The data must reflect the reality it represents
What is referential integrity?
The accuracy and consistency of data within a relationship
What is transaction processing?
Any information processing that is divided into individual, indivisible operations called transactions
What is CRUD?
Refers to the base functionality of a database
C: create
R: read
U: update
D: delete
What is ACID?
a set of rules DBMS’s must follow to ensure data integrity
A: atomicity
C: consistency
I: isolation
D: durability
What is atomicity?
A change to a database is either completely performed or not at all
What is consistency?
Any change in the database must retain the overall state of the database
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
What is durability?
Once a change has been made to a database, it must not be lost due to a system failure
