1.4.2 Databases (Chp 8)

Question 1

what is a record?

Answer 1

A row

Question 2

What is a field?

Answer 2

A column

Question 3

What is a database?

Answer 3

An organised collection of data

Question 4

What is a flat file?

Answer 4

A flat file is a database that consists of one entity. Will usually be based around a single table.

Question 5

What is a relational database?

Answer 5

A relational database are two tables entities/ tables that are linked together by a common item of interest as the primary and foreign key.

Question 6

Entity Relationship Diagrams

Answer 6

Diagram that shows the relationship between 2 or more tables

Question 7

1:1

Answer 7

one to one relationship

Question 8

1:M

Answer 8

one to many

Question 9

M:M

Answer 9

many to many

Question 10

Primary key

Answer 10

unique identifier for each record in the table. Allows each record to be uniquely identified.

Question 11

Secondary key

Answer 11

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.

Question 12

Foreign key

Answer 12

attribute that links 2+ tables together. It exists in one table as the primary key and in another table as the foreign key.

Question 13

Capturing

Answer 13

• method is dependent on the context
• i.e. manually entered in surveys
• Bank scans checks using Magnetic Ink Character Recognition
• Optical Mark Recognition

Question 14

Selecting and managing data

Answer 14

• 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.

Question 15

What is normalization?

Answer 15

a technique used to help reduce data duplication in databases. the process of coming up with the best possible layout for a relational database.

Question 16

Reasons to use normalization

Answer 16

• no redundancy
• consistent data throughout linked tables
  records can be removed and added without issues

Question 17

1NF rules

Answer 17

• 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

Question 18

2NF rules

Answer 18

• that the database is in 1NF

- remove any partial dependencies (split the table)

Question 19

3NF rules

Answer 19

-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)

Question 20

Multi-user databases

Answer 20

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.

Question 21

Transaction processing

Answer 21

a transaction is defined as a single operation executed on data.
Transactions must be processed in line with ACID.

Question 22

what does ACID stand for?

Answer 22

Atomicity, Consistency , Isolation, Durability

Question 23

Atomicity

Answer 23

a transaction must either be processed entirely or not at all.

Question 24

Consistency

Answer 24

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.

Question 25

Isolation

Answer 25

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.

Question 26

Durability

Answer 26

Once the transaction has been completed/ executed it will remain. Ensures that data is saved once a transaction has been completed.

Question 27

Referential integrity

Answer 27

• ensures consistency

- this makes sure that information isn’t removed if it required elsewhere in a linked table

Question 28

integrity

Answer 28

refers to the validity of the data. The integrity can be damaged by changes to the structure or software bugs.

Question 29

SELECT

Answer 29

SELECT fieldnames
FROM tablename
WHERE condition

Question 30

INSERT

Answer 30

INSERT INTO tablename (field1, field2)

VALUES (“”,””)

Question 31

DELETE (record)

Answer 31

DELETE FROM tablename

WHERE condition

Question 32

UPDATE

Answer 32

UPDATE tablename
SET fieldname = “value”, fieldname2 = “”
WHERE condition

Question 33

DELETE (whole table)

Answer 33

DROP TABLE tablename

Question 34

JOIN

Answer 34

SELECT tablename1.primarykey, tablename2.foreignkey
JOIN tablename1
ON tablename1.primarykey = tablename2.foreignkey

Question 35

Redundancy

Answer 35

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.

Question 36

Indexing

Answer 36

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