14 Databases

Question 1

What is a database?

Answer 1

A large store of categorised data

Question 2

What is a flat file database?

Answer 2

A database with only one table

Question 3

What is normalisation?

Answer 3

The process of converting a flat file database into a relational database.

Question 4

Describe how to convert to first normal form?

Answer 4

Split multi valued attributes into two different rows by ensuring atomic values.
E.g. if the field “subject” has values maths,science
It should be split into two different entities one with subject science and one with subject maths

Question 5

Describe how to convert to second normal form?

Answer 5

Remove partial dependencies by splitting tables.

Question 6

Describe how to convert to third normal form?

Answer 6

Remove transistors dependencies. (No column in a table should depend on a non key attribute in said table)

Question 7

What are the three types of relationships between tables in an ERD?

Answer 7

1 to 1
Many to 1
Many to many

Question 8

Show what a database schema would look like:

Answer 8

Customers (CustID, Nam, Town)
Orders (OrderID, CustID)

Question 9

What are the three types of key?

Answer 9

Primary key
Foreign key
Composite key

Question 10

How can you easily tell if there is a many relationship?

Answer 10

If the primary key of table 1 appears as a foreign key in table 2 then table 1 has a 1 to many relationship with table 2.

Question 11

What is the lost update problem?

Answer 11

When two users try to update the same record simultaneously the record will be inconsistent between users after being edited.

Question 12

What are the two methods implemented to combat the lost update problem?

Answer 12

Record locks
Transaction queueing

Question 13

Describe how record locking works:

Answer 13

If another user is accessing a record other users are not able to access it until the user is no longer accessing it.

Question 14

What is transaction queuing?

Answer 14

Database changes are grouped as transactions and queued.
Database software processes the transactions in FIFO order from the queue.

Question 15

Write a generic SQL update statement

Answer 15

UPDATE table name
SET column=value, column2=value2
WHERE condition

Question 16

Write a generic SQL fetch statement

Answer 16

SELECT column1, column2
FROM table name
WHERE condition

Question 17

Write a generic SQL delete statement

Answer 17

DELETE FROM table name
WHERE condition e.g. column=value

Question 18

Write a generic SQL write statement

Answer 18

INSERT INTO table name (column1,…)
VALUES (value1,…)

Question 19

Write a generic parameterised SQL statement

Answer 19

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.sharedColumn=table2.sharedColumn AND condition