Section 11 - SQL & Client-Server Databases

Question 1

What is SQL?

Answer 1

Structured Query Language is a declarative language used for querying and updating tables in a relational database.

Question 2

What is a declarative language?

Answer 2

A programming paradigm that expresses the logic of computation without describing its control flow

Question 3

What is the SELECT statement?

Answer 3

A SQL statement that is used to extract a collection of fields from a given table

Question 4

What is the syntax of a SELECT statement?

Answer 4

SELECT [the list of fields to be displayed]
FROM [the table/tables where the data will come from]
WHERE [the list of search criteria]
ORDER BY [list the fields that the results are to be sorted on]

Question 5

What is the difference between CHAR(n) and VARCHAR(n)?

Answer 5

CHAR is a character strong of fixed length n, whilst VARCHAR is a character string of variable length, with the max length being n.

Question 6

What is the INSERT statement?

Answer 6

A SQL statement that is used to insert a new record into a database table

Question 7

What is the syntax for an INSERT statement?

Answer 7

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

Question 8

What is the UPDATE statement?

Answer 8

A SQL statement that is used to update a record in a database table

Question 9

What is the syntax for an UPDATE statement?

Answer 9

UPDATE tableName
SET column1 = value1, column2 = value2, …
WHERE columnX = value

Question 10

What is the DELETE statement?

Answer 10

A SQL statement that is used to delete a record from a database taleb

Question 11

What is the syntax for a DELETE statement?

Answer 11

DELETE from tableName
WHERE columnX = value

Question 12

What is a wildcard?

Answer 12

Wildcards can be used in SQL commands to specify any possible value. For example, rather than selecting a specific attribute in a SELECT command, a wildcard could be used to return all attributes.

Question 13

What is the syntax for a wildcard?

Answer 13

In SQL, wildcards are usually notated with an asterix

Question 14

What is the CREATE command?

Answer 14

A command that creates a new database table. You must specify the name of the new table, its attributes and their data types. Additionally, entity identifiers are specified.

Question 15

What are the different data types for SQL?

Answer 15

• CHAR(size)
• VARXHAR(size)
• INT(size)
• FLOAT(size, precision)
• DATE
• DATETIME
• TIME
• YEAR

Question 16

What is a client server database system?

Answer 16

A system that provides simultaneous access to a database for multiple clients

Question 17

What is concurrent access?

Answer 17

Where different users attempt to access the same field at the same time. It can result in database updates being lost if 2 users edit a record at the same time.

Question 18

How can you prevent concurrent access?

Answer 18

You can use:
- Record locks
- Serialisation
- Timestamp ordering
- Commitment ordering

Question 19

What are record locks?

Answer 19

When a record is accessed by one user, it is locked to other users until the first user has finished using it.

Question 20

What is the issue with record locks?

Answer 20

The chance of deadlocking

Question 21

What is deadlocking?

Answer 21

Where 2 users attempt to update 2 records, however neither can proceed

Question 22

What is serialisation?

Answer 22

Instead of locking a field, requests from other users are placed in a queue. Once the first user has finished using the field, the next command is executed.

Question 23

What is timestamp ordering?

Answer 23

When multiple commands are sent to the same field in a databases they are each assigned a timestamp. Commands are then carried out on the field in the order of their timestamps

Question 24

What is commitment ordering?

Answer 24

An algorithm is used to work out the optimum order in which to execute commands for the same field. The algorithm considers the impact of commands on other parts of the database in an attempt to minimise issues.

Question 25

What are the advantages of client-server databases?

Answer 25

• The consistency of the data is maintained as there is only one copy
• An expensive resource (the powerful computer) can be made available to a large number of users
• Access rights and security can be managed and controlled centrally
• Backup and recovery can be managed centrally

Question 26

What is a Database Management System?

Answer 26

The system used for client-server databases. DBMS server software runs on the network server, whilst DBMS client server runs on individual workstations

Question 27

How does DBMS servers work?

Answer 27

• The server software processes requests from individual workstations running DBMS client software
• The DBSM server searches for the information and returns it back to the client workstation