10 Fundamentals of Databases

Question 1

Define attribute

Answer 1

A property of an entity equivalent to a field in a table

Question 2

Define entity

Answer 2

Any item in the system about which data is stored

Question 3

Define primary key

Answer 3

An attribute that uniquely identifies each record in a table

Question 4

Define composite key

Answer 4

A primary key that involves more than one attribute

Question 5

Define foreign key

Answer 5

A primary key in another table to create a relationship

Question 6

What are the properties of a first normal form?

Answer 6

• No repeating attributes or groups of attributes
• all attributes must be atomic (cannot consist of two data item e.g. first name and surname
• this would make it difficult to order by surname

Question 7

What are the properties of a second normal form?

Answer 7

• In first normal form
• With no partial dependencies
• (this only occurs if primary key is a composite key)
• the value of the non-key attribute can be determined by a subset of the primary key but not the entire key

Question 8

What are the properties of a third normal form?

Answer 8

• Second normal form
• No non-key dependencies

Question 9

SQL syntax for retrieving data

Answer 9

SELECT attribute FROM table

Question 10

SQL syntax for relational database

Answer 10

SELECT attributes FROM tables
JOIN table2
ON table1.primarykey = table2.foreignkey

Question 11

SQL syntax to order results

Answer 11

SELECT attribute FROM table
WHERE attribute2 = 10
ORDER BY attribute ASC

Question 12

SQL syntax to create a new table

Answer 12

CREATE TABLE Customers
(
CustomerID CHAR(4) NOT NULL PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
Surname VARCHAR (50) NOT NULL,
PricePaid CURRENCY
Height FLOAT (5, 2) #float with 5 digits and max of two after decimal
)

Question 13

SQL syntax to add attribute

Answer 13

ALTER TABLE Customers
ADD Age INT

Question 14

SQL syntax to delete attribute

Answer 14

ALTER TABLE Customers
DROP Height

Question 15

SQL syntax to alter data type of attribute

Answer 15

ALTER TABLE Customers
MODIFY COLUMN Surname VARCHAR(40) NOT NULL

Question 16

SQL syntax to insert a new record

Answer 16

INSERT INTO Customers(CustomerID, Firstname, Surname)
VALUES (“8472”, “Alice”, “Wonderland”)

Question 17

SQL syntax to delete a record

Answer 17

DELETE FROM Customers
WHERE CustomerID = “2342”

Question 18

SQL syntax to update a record

Answer 18

UPDATE Customers
SET Surname = “Borderland”
WHERE CustomerID = “8462”

Question 19

What are the benefits of normalisation?

Answer 19

• Easier to maintain and update a normalised database
• No unnecessary duplication of data
• Data integrity is maintained e.g. if a person changes their address, the update only needs to be made to one table
• Smaller tables and fewer fields means faster searches and saves storage

Question 20

Define client-server database.

Answer 20

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

Question 21

Define normalisation.

Answer 21

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

Question 22

What are the strengths of client-server databases?

Answer 22

• Consistency of database is maintained as only one copy is held
• Backup and recovery - as the database is centralised, a server admin can create backups so that if there’s a failure or loss of data the database can be recovered with little or no consequences
• Centralised security - limiting and controlling access levels can preserve the integrity of the data in the database

Question 23

What are the techniques used to handle simultaneous database transactions?

Answer 23

• Record locking
• Timestamp ordering
• Commitment ordering

Question 24

Describe record locking.

Answer 24

Prevents simultaneous access to objects in a database to prevent updates being lost or inconsistencies in the data arising

• Record is locked when a user retrieves it for editing or updating
• Anyone else attempting to retrieve it is denied access until the transaction is completed

Question 25

Outline serialisation and timestamp ordering

Answer 25

• Serialisation ensures that transactions do not overlap in time
• Every object in the database has a read and a write timestamp which are updated when an object is read or written
• When a user tries saving an update, if the read timestamp is not the same as the start of the transaction, we know that another user has accessed it and will cancel the update

Question 26

Explain commitment ordering.

Answer 26

• Transactions are ordered in terms of their dependencies on one another as well as the time they were initiated

Question 27

Outline the system life cycle approach.

Answer 27

Each stage has to be completed before the next one can begin

Question 28

Outline the agile approach.

Answer 28

• More modern and flexible approach
• Gets feedback from user throughout development of a system
• Adjustments can be made at each stage

Question 29

Benefits of agile approach.

Answer 29

• User is not sure what is possible so they cannot clearly define requirements
• Analyst may misunderstand the wishes of the user/client
• Usually it is easier to identify drawbacks with the prototype rather than expressing what you expect in advance
• Expensive and time-consuming to make changes to a final product