SLR 10 - DATABASES

Question 1

Define relational database

Answer 1

allows the user to specify information about multiple tables and the relationship between those tables

Question 2

Define flat file

Answer 2

a database that allows the user to specify data attributes (columns, databases etc.) for only one table at a time, storing those attributes independently

Question 3

Define primary key

Answer 3

a field that uniquely identifies a record in a table

Question 4

Define foreign key

Answer 4

• the linking field in the foreign table formed when a relationship is made
• the foreign key becomes by default the primary key of the new table

Question 5

Define composite primary key

Answer 5

when more than one field is added together to form a unique primary key for a table

Question 6

What is indexing?

Answer 6

the process of creating an index of primary keys such that the location of any record can be retrieved given its primary key

Question 7

Define secondary key

Answer 7

a key field which can be used to access a table in a different way

Question 8

Define entity relationship modelling (ERM)

Answer 8

the process of producing a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a data such as a relation database

Question 9

What is normalisation?

Answer 9

the formal process of optimally designing data tables by reducing data redundancy and repetition by converting them into normal forms

Question 10

What is 1st normal form (1NF)?

Answer 10

• no repeating attributes/groups of attributes
• all attributes must be atomic (a single attribute can’t consist of 2 data items)

Question 11

What is 2nd normal form (2NF)?

Answer 11

• table is in 1NF
• contains no partial dependencies

Question 12

What is 3rd normal form (3NF)?

Answer 12

• table is in 2NF
• contains no non-key dependencies (all attributes are dependent on the key, the whole key and nothing but the key)

Question 13

Advs of normalisation

Answer 13

• easier to maintain and change a normalised database
• no unnecessary duplication of data
• data integrity is maintained
• having smaller tables with fewer fields means faster searches and savings in storage

Question 14

What is referential integrity?

Answer 14

the idea of keeping a database consistent by ensuring that any changes made to data or relationships associated with a table are accounted for in all the linked tables

Question 15

What are the SQL commands/statements?

Answer 15

• SELECT
• FROM
• WHERE
• LIKE
• AND
• OR
• JOIN
• ON
• INSERT INTO
• UPDATE
• SET
• DELETE

Question 16

How do you select everything from a section?

Answer 16

asterix (wildcard)

Question 17

What are the possible data types when creating a new table?

Answer 17

• CHAR(n): this is a string of fixed length n
• VARCHAR(n): this is a string of variable length with
  upper limit n
• BOOLEAN: TRUE or FALSE values
• INTEGER/INT: integer
• FLOAT: number with a floating decimal point
• DATE: the date in the format Day/Month/Year (#dd/mm/yyyy#)
• TIME: the time in the format Hour/Minute/Second
• CURRENCY: sets the number as a monetary amount

Question 18

How do you create a new table in SQL?

Answer 18

CREATE TABLE tblname
(
tblID CHAR(4) NOT NULL PRIMARY KEY
description VARCHAR(20)
)

Question 19

How do you add a new column to a table?

Answer 19

ALTER TABLE tblname
ADD FieldName DATA_TYPE

Question 20

How do you delete a column from a table?

Answer 20

ALTER TABLE tblname
DROP FieldName

Question 21

How do you change the data type of a column in a table?

Answer 21

ALTER TABLE tblname
MODIFY COLUMN FieldName newDATA_TYPE

Question 22

How do you insert a new record into a table?

Answer 22

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

Question 23

How do you update a record in a table?

Answer 23

UPDATE TableName
SET column1 = value1, column2 = value2 …
Where columnX = value

Question 24

How do you delete a record from a table?

Answer 24

DELETE FROM TableName
WHERE columnX = value

Question 25

What does a database management system (DBMS) do?

Answer 25

ensures that data stored in a database remains consistent

Question 26

What is data integrity?

Answer 26

- the maintenance and consistency of data in a data store - the data store must reflect the reality that it represents

Question 27

What is transaction processing?

Answer 27

The idea of making sure that any logical operation or change in state of a database (transaction) conforms to ACID (Atomicity, Consistency, Isolation, Durability) rules for reliable processing

Question 28

What does ACID stand for?

Answer 28

- Atomicity - Consistency - Isolation - Durability

Question 29

What does atomicity mean?

Answer 29

all transactions should either succeed or fail, and never only partially processed

Question 30

What does consistency mean?

Answer 30

each transaction must obey the defined validation rules of the database to maintain referential integrity

Question 31

What does isolation mean?

Answer 31

- no transaction should overwrite other transactions that are simultaneously occurring - the same results must be obtained if transactions are concurrently or sequentially processed

Question 32

How does the DBMS enforce isolation?

Answer 32

- implements a system of record locking - record(s) affected by the transaction are locked effectively placing them in a read only state - when the transaction is fully complete the lock will be removed

Question 33

What does durability mean?

Answer 33

- once a transaction has begun, it must be completed under all circumstances - as each part of the transaction is completed, its held in a buffer/cache on a disk

Question 34

What are some methods of capturing data?

Answer 34

- paper based forms - optical character recognition (OCR) - optical mark recognition (OMR) - barcodes - QR codes - sensors

Question 35

How is data exchanged?

Answer 35

- electronic data interchange (EDI) - computer-to-computer exchange for documents - all docs must be in standard format