1.3.2 database Flashcards
SQL statements not included here; needs to be revised
relational database
linked tables, each table with record of an entity
flat file
one table
database
organised collection of data
primary key
unique identifier of the entity
foreign key
field that links to a primary key in a second table
secondary key
stored for quick access and query searches
composite key
when 2 unique identifiers are needed to identify the particular record
entity
object, person, event or thing of interest about which data needs to be record
record, attribute
record: row
attribute/field : column
entity relationship modelling
ERM - shows the relationship between entities in a relational database
definition of normalisation
process used to come up with the best possible design for a database
importance of normalisation
- no unnecessary duplication of data
- data integrity
- referential integrity
- small tables→ faster searching and saving storage
- more maintainable
1NF
- no repeating (groups of) ATTRIBUTE
- attributes should be atomic
2NF
- 1NF
- no partial dependencies: information should all be relating to the primary key
3NF
- 2NF
- no non-key dependency: all attributes should be dependent on the key, and nothing but the key
indexing
store the position of each record ordered by a certain
attribute for quick access
primary key are automatically indexed
methods of dealing with data
-capturing
-selecting
-managing
-exchanging
capturing data
inputting data into the database
- Paper-based data capture
- Optical character recognition (OCR)
- Optical mark recognition (OMR)
- Magnetic stripes
- Chip and pin
- Barcodes
- QR codes
- Sensors
selecting data
- SQL: SELECT, FROM, WHERE
- QBE (Query by Example): visual representation
managing data
- DBMS: database management system
- validation rules
- encryption
- manage multiple users at the same time
- DML: data manipulation language (e.g. SQL)
- UPDATE
- DELETE
- INSERT
- DROP
exchanging data
how systems and people exchange the data
formats:
1. CSV
2. XML and JSON
manual methods:
1. Memory stick
2. Optical media
3. Removable hard disk
4. Email
5. Paper-based
automatic methods:
- EDI (Electronic Data Interchange)
- protocol between 2 systems to facilitate the exchange of data (updates every hour)
referential integrity
ensuring changes are consistent across the table
e.g. if a record is removed, all references should also be removed
e.g. there must be a corresponding primary key for the foreign key in the table
transaction processing
- Atomic: transaction size to be as small as possible
- All-or-nothing: entire process must finish for success, else fail
ACID
ACID
the rules a DBMS follows for data integrity during transaction process
atomicity: small as possible, entirely or not
consistency: maintain referential integrity
isolation: not interrupted by other users, record locking
durability: changes must be saved, even when system fails
record locking
only occurs on the relevant record;
to prevent simultaneous access to objects in a database
deadlock could happen->serialisation (timestamp ordering; commitment ordering)
(ACID: consistency)
redundancy
the process of having one or more copies of the data in physically different locations
(ACID: durability)
use of SQL
structured query language: manages relational database
referential integrity
1)changes are consistent
2) must be a corresponding primary key to foreign key
