1.3.2 database

Question 1

relational database

Answer 1

linked tables, each table with record of an entity

Question 2

flat file

Answer 2

one table

Question 3

database

Answer 3

organised collection of data

Question 4

primary key

Answer 4

unique identifier of the entity

Question 5

foreign key

Answer 5

field that links to a primary key in a second table

Question 6

secondary key

Answer 6

stored for quick access and query searches

Question 7

composite key

Answer 7

when 2 unique identifiers are needed to identify the particular record

Question 8

entity

Answer 8

object, person, event or thing of interest about which data needs to be record

Question 9

record, attribute

Answer 9

record: row
attribute/field : column

Question 10

entity relationship modelling

Answer 10

ERM - shows the relationship between entities in a relational database

Question 11

definition of normalisation

Answer 11

process used to come up with the best possible design for a database

Question 12

importance of normalisation

Answer 12

• no unnecessary duplication of data
• data integrity
• referential integrity
• small tables→ faster searching and saving storage
• more maintainable

Question 13

1NF

Answer 13

• no repeating (groups of) ATTRIBUTE
• attributes should be atomic

Question 14

2NF

Answer 14

• 1NF
• no partial dependencies: information should all be relating to the primary key

Question 15

3NF

Answer 15

• 2NF
• no non-key dependency: all attributes should be dependent on the key, and nothing but the key

Question 16

indexing

Answer 16

store the position of each record ordered by a certain
attribute for quick access

primary key are automatically indexed

Question 17

methods of dealing with data

Answer 17

-capturing
-selecting
-managing
-exchanging

Question 18

capturing data

Answer 18

inputting data into the database

1. Paper-based data capture
2. Optical character recognition (OCR)
3. Optical mark recognition (OMR)
4. Magnetic stripes
5. Chip and pin
6. Barcodes
7. QR codes
8. Sensors

Question 19

selecting data

Answer 19

• SQL: SELECT, FROM, WHERE
• QBE (Query by Example): visual representation

Question 20

managing data

Answer 20

• 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

Question 21

exchanging data

Answer 21

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)

Question 22

referential integrity

Answer 22

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

Question 23

transaction processing

Answer 23

• Atomic: transaction size to be as small as possible
• All-or-nothing: entire process must finish for success, else fail

ACID

Question 24

ACID

Answer 24

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

Question 25

record locking

Answer 25

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)

Question 26

redundancy

Answer 26

the process of having one or more copies of the data in physically different locations

(ACID: durability)

Question 27

use of SQL

Answer 27

structured query language: manages relational database

Question 28

referential integrity

Answer 28

1)changes are consistent
2) must be a corresponding primary key to foreign key