1.3.2 Databases Flashcards

You may prefer our related Brainscape-certified flashcards:
1
Q

Database and Parts

A

Database : Persistent Organised store of related data

Table - Collection of records
Record - All fields/data relating to one entity
Field - Attribute/data relating to entity under field name
Field name - Identifier for single piece of data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Flat file database

A

Flat file database : Database consisting of a single file / table.
- Many to many entity relationship

Disadvantages :
- Leads to a-lot of data redundancy
- Leads to data inconsistency when data updated
- Harder to update and search as can update one record but not all

Advantages :
- Easier to create (no specialist knowledge needed) so easier to use for inexperienced user
- Easier to import into another system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Relational Data Base

A

Relational Data Base : Database with Multiple tables that link together each relating to single entity.
- One to many entity relationship

Advantages :
- decreased data redundancy (less repeated data)
- Improved data consistency
- easier to update and remove data as only have to update in single place

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Key Fields

A

Primary Key : Field used to uniquely identify each record of a table. (no repeats or blank values)

Secondary Key : Fields that can be indexed to speed up searching and sorting data. (repeats and blank values allowed)

Foreign Key : field that refers to a Primary key from another table used to link data together between tables. (repeats and blank values allowed)

Composite Key : Where multiple fields used together as primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Referential Integrity

A

Referential Integrity : You cannot delete any data being linked to by other tables
- Each foreign key must link to existing valid primary key.
- Ensures Database relationships between tables are consistent
- If record is removed so are all references
- Enforced by DBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Data Integrity

A

Data Integrity : Data should be protected by stopping incomplete or inconsistent transactions causing data corruption.
- Involves Referential Integrity.
- Process that ensures the accuracy, completeness, consistency, and validity of an organization’s data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SQL Commands

A

Delete record :
DELETE FROM table name WHERE condition

Insert a record :
INSERT INTO table name (field1, field2, …) VALUES (values, value2, …)

Delete table from a database :
DROP TABLE table name

Combine data from two or more tables :
SELECT table name. field , table name. field FROM table name JOIN other table name ON table name. field=other table name.field WHERE condition

Update data within fields :
UPDATE table name SET field name = value, … = … WHERE field name = condition

ORDER BY field ASC (at end to return in alphabetical order)

LIKE (“A%”) (Wildcard after where that returns only records starting with “A”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Normalisation

A

Normalisation : Process used to produce the best possible design for a relational database.
- Process of removing redundancy - makes accessing and maintaining data more efficient and easier.
- To maintain Integrity and avoid duplicates or data errors.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Normalisation Stages

A

Unnormalized form :
- Data in any format or configuration (Flat file database)

1st Normal Form :
- Primary Key Identified
- Data in attributes must be atomic (Can’t be split up further e.g. Name –> FN and SN)
- No repeated attributes / fields (multiple items in an attribute)
- Unique attribute names (field names)

2nd Normal Form :
- Be in 1st normal Form
- Every field must be dependant on whole primary key (No partial dependencies)

3rd Normal Form :
- Be in 2nd normal form
- No transitive dependencies (non-key dependencies)
(one to many relationship at this point)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Indexing Database

A

Indexing : Method to store position of each record ordered by certain attribute to look up and access data quickly.
- Primary key automatically indexed but not usually remembered.
- Secondary Key indexed so table faster and easier to search and query through on particular attributes - searching and sorting.
- However Index takes up extra space in database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Capturing Data Techniques

A

Techniques to input data into databases :
- Forms (e.g. for surveys from pedestrians or web forms)

  • Optical Mark Recognition (e.g. MCQ on test)
  • Optical Character Recognition (e.g. Reading Reg plates)
  • Magnetic ink character recognition (e.g. Cheques)
  • Barcode Reader (e.g. Shopping items)
  • Sensors (e.g. Light sensor)
  • Data Mining (Raw data turned into valuable information e.g. Marketing: Customer segmentation, market basket analysis, and targeted advertising)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Selecting and Managing Data

A

Query By Example : Search or sort or join data in database by providing example of desired output.
- Easy to use and learn + user friendly but less powerful + flexible than SQL

SQL - Structured Query Language - for databases to manage data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Exchanging Data

A

Methods to Exchange data :
- Electronic Data Interchange - no human interaction, steam-lines to increase efficiency.
- CSV - Comma separated values - simple and widely supported.
- JSON - Lightweight and common in web applications.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Transaction processing and ACID

A

Transaction Processing : A Transaction is a single operation performed on data in a database, Whole process consists of multiple transactions
ADID : Ensures Integrity of database is maintained by ensuring all transactions are processed correctly and reliably to prevent inconsistencies.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

ACID Components

A

Atomicity : Ensures a transaction (single indivisible unit of work) is processed entirely or not at all - to prevent partially completed transactions that lead to data corruption.
- Rolls back transaction if fails

Consistency : Ensures that no transaction can make changes that break any rules of the database e.g. Referential integrity.
- Ensures data remains in consistent state
- Validates and verifies data entered.

Isolation : Ensures that each transaction doesn’t effect/overwrite other transactions concurrently being processed and updates to data are not affected.
- so data remains consistent.
- Crucial in multi-user database
- Through Record Locking

Durability : One a transaction has been processed it remains so no matter what and results are stored permanently, even if power or system failure.
- Can be achieved by storing on secondary storage (Redundancy) so no data lost once transaction complete

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Record Locking and Redundancy

A

Record Locking : Prevents simultaneous access to the same record in a database by locking record when somebody is editing/accessing so only one person can access at a time.
- Prevents Inconsistencies or loss of updates when data updated.
- Outcome of concurrent transactions is the same as if transactions completed sequentially.
- Mechanism to achieve Isolation
Problems : Deadlocking - Two users waiting to access each others records (Timestamp ordering solution)

Redundancy : Process of storing multiple copies of data in physically different locations (back-ups) - can recover unaffected data if damage to one copy.
- Or accidentally which leads to wasted storage space