Databases

Question 1

How can a many-to-many relationship be expressed?

Answer 1

By both tables in the relationship having one-to-many relationships to a common table

Question 2

What is an entity?

Answer 2

A thing about which data is to be stored, for example: a customer. Each row in the table is an entity

Question 3

What is an attribute?

Answer 3

Characteristics or other information about entities, for example: the customer’s name or address: Each column in the table is an attribute

Question 4

What is a composite primary key?

Answer 4

A primary key made up of multiple attributes

Question 5

What is a foreign key?

Answer 5

A key in a table which is the primary key in another table, used to link those two tables

Question 6

What is a primary key?

Answer 6

A primary key is an attribute that provides an unique identifier for every entity in a table

Question 7

[PPQ] What does it mean when a database is normalised?

Answer 7

The data is atomic and has no transitive dependencies [?}

Question 8

What are the advantages of normalised databases?

Answer 8

Faster to search and sort, easier to manage, number of update, insertion and deletion anomalies reduced

Question 9

What is first normal form?

Answer 9

When a database conforms to first normal form, it contains no repeating attributes. The database’s data can be referred to as atomic (meaning that no single column contains more than one value).

Question 10

[PPQ] Describe an example of a problem that could occur if no system were in place to
manage concurrent access to the database

Answer 10

Two users read and edit the same record simultaneously. They both edit the record and then send it back and save, meaning only one set of changes is kept

Question 11

What is second normal form?

Answer 11

Ensuring the database is in 1NF and removing attributes that depend upon part but not all of the primary key by creating additional tables

Question 12

What is third normal form?

Answer 12

Ensuring the database is in 2NF and removing non-key attributes that depend on other non-key attributes

Question 13

What is a client-server database?

Answer 13

A database that is stored on a server and can be accessed by various users from their workstations

Question 14

How can simultaneous access problems be avoided?

Answer 14

Record locks: when a transaction on a record starts, there’s a temporary exclusive lock on that record, and other users cannot edit the data until the lock is removed

Timestamp ordering: Timestamps are generated for each transaction, database records timestamp of the last time the record was written to, and aborts the transaction if it will result in loss of data integrity

Question 15

Creating a table

Answer 15

CREATE TABLE TableName
{
AttribName1 datatype (length),
AttribName2 datatype (length),
PRIMARY KEY (AttribName1)
};

Question 16

Inserting values into a table

Answer 16

INSERT INTO TableName (AttribName1, AttribName2)

VALUES (“69”, “demoman”);

Question 17

Updating a table

Answer 17

UPDATE TableName
SET AttribName2 = “shrek”
WHERE Attrib1 = “69”;

Question 18

Deleting values from a table

Answer 18

DELETE FROM TableName
WHERE AttribName1 = “69”;

or

DELETE * FROM TableName;

Question 19

Querying data from a table

Answer 19

SELECT AttribName1, AttribName2
FROM TableName
WHERE AttribName2 = “shrek”
ORDER BY AttribName1 DESC

• is the wildcard operator
  AND and OR can also be used

Question 20

Common errors in SQL code

Answer 20

Missing quotation marks, no linking condition between the two tables