1.3.2 Databases

Question 1

What is an entity

Answer 1

An item of interest in which data is stored

Question 2

Flat File

Answer 2

() Single file about single entity and its attributes

Question 3

Primary key

Answer 3

() A unique identifier
() Field that does not repeat

Question 4

Foreign key

Answer 4

Attribute that links two tables

Question 5

Secondary Key

Answer 5

() Enables data base to search quickly through indexing

Question 6

What does normalisation aim to do

Answer 6

() No redundancy
() Consistent data through linked tables
() Records changed without issue (add and delete)
() Complex queries can be done

Question 7

1NF:

Answer 7

Contains atomic values (no more than a single value)
Unique column names
Primary key present

Question 8

2NF:

Answer 8

No partial dependancies (Compostite key)
in 1NF

Question 9

3NF:

Answer 9

() In 2NF
() No non-key dependencies

Question 10

Indexing

Answer 10

() Method use to store the position of each record when ordered by a certain attribute
() Used to look up data quickly
() Primary key is automaticlly indexed

Question 11

Capturing data methods:

Answer 11

Forms
OMR
OCR
Sensors
Barcodes
Data Mining

Question 12

All sensors

Answer 12

OCR
OMR
Temperature
Pressure
Proximity
Light
Motion
Humidity
Gas
Force
Acoustic
Magnetic

Question 13

Exchanging Data:

Answer 13

CSV: Plain text, commas to seperate
EDI: Doesn’t require human interaction
Memory sticks
Email

Question 14

Selecting and managing data:

Answer 14

SQL

Question 15

Select data SQL

Answer 15

SELECT Attributes
FROM Tablename
WHERE Attribute BETWEEN/LIKE Something
ORDERBY Attribute

Question 16

Combine rows from databases based on common field

Answer 16

SELECT Attributes
FROM Tablename
JOIN Othertablename
ON Tablename.attribute = Othertablename.attribute

Question 17

SQL Create

Answer 17

CREATE TABLE Tablename
(
Attribute 1 INTEGER NOT NULL, PRIMARY KEY,
Attribute 2 VARCHAR(20) NOT NULL
)

Is it primary key
Its data type
Does it need to be filled in?

Question 18

ALTER

Answer 18

Adding column:
ALTER TABLE Tablename
ADD Attribute and their data types

Deleting a column:
ALTER TABLE Tablename
DROP COLUMN Attribute

Modify column:
ALTER TABLE Tablename
MODFIY COLUMN Attribute Newdatatype

Question 19

Data types:

Answer 19

CHAR(n): Fixed length
VARCHAR(n): Upper limit
DATE
TIME
CURRENCY

Question 20

Insert record

Answer 20

INSERT INTO (column1, column2)
VALUES (value1, value2)

Question 21

DELETE

Answer 21

DELETE FROM Tablename
WHERE Attribute = value

Question 22

Nested Select Example

Answer 22

SELECT*
FROM Employees
WHERE Salary > (SELECT AVG(salary) FROM Employees)

Question 23

Referential integrity

Answer 23

() Process of ensuring consistency
() Makes sure information isnt removed if it is needed elsewhere in a linked database

Question 24

Transaction

Answer 24

A single operation executed on data
Must be in line with ACID

Question 25

ACID:

Answer 25

Atomicity: () No partial transactions (Must be completely processed or not at all) Consistency: () Keep database rules before and after transaction Isolation: () Simultaneous actions lead to result as if one was after another Durability: Once a transaction has been executed it will remain so even in case of system failiures

Question 26

Record Locking:

Answer 26

() Preventing simultaneous access of records () To prevent inconsistencies or loss of updates () Data being modified cannot be used elsewhere Can lead to deadlock: Simultaneous locking

Question 27

Redundancy:

Answer 27

() Copies of daata in different physical locations () Damage to one location been others can be recovered