Fundamentals of Databases

Question 1

What is a field?

Answer 1

A column in a database

Question 2

What is a record?

Answer 2

A row in a database

Question 3

What is a primary key?

Answer 3

A unique field in a database table which indexes a record

Identified by an underline

Question 4

What are the three entity-relationships?

Answer 4

One-to-one

Many-to-many

One-to-many

Question 5

What is a foreign key?

Answer 5

A foreign key is a column in a database table that links to the primary key of another table, establishing a connection between the two tables.

Question 6

Why are databases normalised?

Answer 6

Database normalization is done to reduce data redundancy and improve data integrity, ensuring consistency and efficient use of storage.

Question 7

What is First Normal Form?

Answer 7

Contains only atomic values; each column has only one value.

A column should only contain values of the same type.

Each row holds a unique set of data; doesn’t have repeating field names.

It doesn’t matter what order the data is in; we can fetch it from anywhere using a query.

Question 8

What is Second Normal Form?

Answer 8

To be in second normal form, it must be in first normal form and not contain any partial dependency.

Question 9

What is Partial Dependency?

Answer 9

When a primary key is made up of two columns, and an attribute depends on only one of these columns.

Question 9

How do you remove Partial Dependency?

Answer 9

Move it to another table where it can be dependent on one key.

Question 9

What is Third Normal Form?

Answer 9

Where there’s no transitive dependency and it’s in 2nd and 1st normal form.

Question 10

What is Transitive Dependency?

Answer 10

A condition in which an attribute is dependent on another attribute that isn’t a part of the primary key.

Question 11

How do you remove Transitive Dependency?

Answer 11

Split the fields which had the transitive dependency into new tables.

Question 12

SELECT Command

Answer 12

Used to retrieve data from a database table

SELECT <column> FROM <table> WHERE <condition> ORDER BY <column><ASC/DESC></column></condition></column>

The ORDER BY is optional
- Defaults to ASC

Question 13

UPDATE command

Answer 13

Used to modify the attributes of an existing entity

UPDATE <table> SET <column> = <value>
WHERE <column> = <value></value></column></value></column>

Question 14

DELETE command

Answer 14

Used for removing entities from a database

DELETE FROM <table>
WHERE <condition></condition>

Question 15

INSERT command

Answer 15

Used to add new records to an existing table

INSERT INTO <table> VALUES (<value1>, <value2> …)</value2></value1>

INSERT INTO <table> (<column1>, <column2> ...) VALUES (<value1>, <value2> ...)</value2></value1></column2></column1>

Question 16

What is a Wildcard?

Answer 16

Used in SQL to specify ALL.

Signified by a * symbol

Question 17

How to create a table?

Answer 17

CREATE TABLE <table_name>
(<column-name> <column-type>,</column-type></column-name></table_name>

<column-name> <column-type>
…
PRIMARY KEY (<column-name>)
)

data types include..... VARCHAR, INT, SMALL MONEY etc
</column-name></column-type></column-name>

Question 18

What is a client-server database?

Answer 18

A client-server database is a system where one server machine hosts a database and multiple client machines access it over a network. The server manages, stores, and retrieves the data as requested by the clients.

Question 19

What is Concurrent Access?

Answer 19

This issue arises when multiple users try to access the same field at the same time, leading to potential loss of database updates. It can be managed using record locks, serialisation, timestamp ordering, and commitment ordering.

Question 20

What are Record Locks?

Answer 20

When a field is accessed by a user, it gets locked for other users until the first user is done. This prevents others from accessing or modifying the field until it’s unlocked.

Question 21

What is Serialisation?

Answer 21

Instead of locking a field, requests from other users are queued. After the first user is done, the next command in the queue is executed.

Question 22

What is Timestamp Ordering?

Answer 22

Each command sent to the same field in a database is assigned a timestamp. Commands are executed based on their timestamps.

Question 23

What is Commitment Ordering?

Answer 23

An algorithm is used to determine the optimal order to execute commands for the same field. This algorithm considers the impact of commands on other parts of the database to minimize potential issues.

Question 24

What is an INNER JOIN in SQL?

Answer 24

An INNER JOIN combines rows from two tables based on a related column between them. It’s like matching and merging information from two different lists based on a common key.

SELECT Orders.OrderID, Customers.CustomerName, Orders.Product
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;