1.4.2 Databases (Chp 8) Flashcards
Databases
what is a record?
A row
What is a field?
A column
What is a database?
An organised collection of data
What is a flat file?
A flat file is a database that consists of one entity. Will usually be based around a single table.
What is a relational database?
A relational database are two tables entities/ tables that are linked together by a common item of interest as the primary and foreign key.
Entity Relationship Diagrams
Diagram that shows the relationship between 2 or more tables
1:1
one to one relationship
1:M
one to many
M:M
many to many
Primary key
unique identifier for each record in the table. Allows each record to be uniquely identified.
Secondary key
allows database to be searched quickly. User is unlikely to remember the unique primary key, but will remember a certain field i.e. surname. This makes it possible for the user to search.
Foreign key
attribute that links 2+ tables together. It exists in one table as the primary key and in another table as the foreign key.
Capturing
- method is dependent on the context
- i.e. manually entered in surveys
- Bank scans checks using Magnetic Ink Character Recognition
- Optical Mark Recognition
Selecting and managing data
- selecting the correct data is an important part of data processing.
- This could involve only selecting data that fits a certain criteria.
- Collected data can be managed using SQL to sort, restructure, and select certain sections.
What is normalization?
a technique used to help reduce data duplication in databases. the process of coming up with the best possible layout for a relational database.
Reasons to use normalization
- no redundancy
- consistent data throughout linked tables
records can be removed and added without issues
1NF rules
- all field names are unique
- all records are unique
- each record has a primary key
- the values in the fields are of the same domain/ type
- each value is atomic
2NF rules
- that the database is in 1NF
- remove any partial dependencies (split the table)
3NF rules
-that the database is already in 2NF
-remove any transitive data (everything must depend in the primary key and the primary key only.)
(create 1:M relationship - might need a linking table)
Multi-user databases
Databases can hold vast amount of info and often need to support multiple simultaneous users. Users can be given different access rights to a database. (User Access Levels)
- All these different database queries result in multiple transactions taking place, often at the same time.
Transaction processing
a transaction is defined as a single operation executed on data.
Transactions must be processed in line with ACID.
what does ACID stand for?
Atomicity, Consistency , Isolation, Durability
Atomicity
a transaction must either be processed entirely or not at all.
Consistency
A transaction must keep the referential integrity rules between linked tables. Will ensure that an illegal transaction will be rejected so that the integrity of the database is upheld.
Isolation
Will ensure that each transaction will be self contained and dealt with in a way that doesn’t affect others.
-enforced by record locking where the records not being affected are locked, placing them in a read-only state, which is removed once the transaction is complete.
Durability
Once the transaction has been completed/ executed it will remain. Ensures that data is saved once a transaction has been completed.
Referential integrity
- ensures consistency
- this makes sure that information isn’t removed if it required elsewhere in a linked table
integrity
refers to the validity of the data. The integrity can be damaged by changes to the structure or software bugs.
SELECT
SELECT fieldnames
FROM tablename
WHERE condition
INSERT
INSERT INTO tablename (field1, field2)
VALUES (“”,””)
DELETE (record)
DELETE FROM tablename
WHERE condition
UPDATE
UPDATE tablename
SET fieldname = “value”, fieldname2 = “”
WHERE condition
DELETE (whole table)
DROP TABLE tablename
JOIN
SELECT tablename1.primarykey, tablename2.foreignkey
JOIN tablename1
ON tablename1.primarykey = tablename2.foreignkey
Redundancy
process of having more than one copies of data in physically different locations. This means that if there is any damage to one copy, the others can be recovered.
Indexing
Method to store the position of each record when ordered by a certain attribute.
Used to look up and access data quickly
Primary key is automatically indexed
The index takes up extra space in the database