Databases and SQL

Question 1

What is a data model?

Answer 1

An abstract description of the essential data within a given context and how data is related.

Question 2

What is an entity?

Answer 2

A distinct thing about which data must be stored.

Question 3

What is an attribute?

Answer 3

A single property or piece of information of an entity.

Question 4

What is data modelling?

Answer 4

The process of producing an abstract model that represents the organisation and structure of the data

Question 5

What problems exist in a flat-file database?

Answer 5

1) Data redundancy
2) Data inconsistency

Question 6

What is an entity identifier?

Answer 6

An attribute or combination of attributes that uniquely identify each instance of an entity.

Question 7

How is information from a flat-file database represented?

Answer 7

A single table with columns for each attribute and each row representing a new instance of each entity.

Question 8

What is a composite key?

Answer 8

Multiple attributes are combined to form the table’s entity identifier

Question 9

What information is stored in a entity description?

Answer 9

Entity name, primary key and attributes
(entity is always singular)

EntityName( PrimaryKey , Attribute1)
- primary key must be underlined

Question 10

How to create a table in SQL?

Answer 10

CREATE TABLE table_name (
primaryAttribute DATATYPE PRIMARY KEY,
attribute2 DATATYPE NOT NULL
);
- remember that you can write NOT NULL, DEFAULT constraints

Question 11

What is an entity-relationship diagram used for?

Answer 11

E-R diagrams are used to graphically represent the relationships between tables in a database

Question 12

How can we tell from an entity description what the E-R diagram looks like?

Answer 12

The table which contains a foreign key of the other table holds the “many” side of the relationships.

Question 13

Understanding E-R relationships

Answer 13

1. One-to-many: one tutor, many students (and each student has one tutor)
2. Many-to-many: one teacher, many pupils - one student, many teachers
3. One-to-one: one school, one headteacher

Question 14

What are flat-file databases?

Answer 14

• simple ways of storing data in a text file such as CSV
• each line is a record with fields separated by commas

Question 15

What are advantages and disadvantages of flat-file databases?

Answer 15

+ easy to create and use for datasets
- inefficient to analyse data and gain insight and dataset grows

Question 16

What is a relational database?

Answer 16

A collection of tables with relationships created through common attributes, namely primary and foreign keys.

Question 17

What data types are supported in SQL?

Answer 17

1) INTEGER
2) FLOAT
3) TEXT
4) VARCHAR(x) where x is the length
5) DATETIME
6) BOOLEAN

Question 18

How to insert a new record into a table in SQL?

Answer 18

INSERT INTO table_name
VALUES (field1val, field2val…);

Question 19

How to select a record from a table in SQL?

Answer 19

SELECT FieldName
FROM TableName
INNER JOIN OtherTableName
ON TableName.ForeignKey = OtherTableName.PrimaryKey
WHERE Field1 = condition
ORDERBY ASC/DESC

Question 20

What is the wildcard symbol and what does it do?

Answer 20

*
returns all fields

Question 21

How to use LIKE in SQL?

Answer 21

For conditions:
WHERE FieldName LIKE ‘word%’
- make sure to use single quotation around word
- if % after, word starts with that
- if % before, word ends with that
- if % before and after, words contained in the text

Question 22

Date range shortcut

Answer 22

for a given age range starting 1/1/25 ending 31/1/25 :
WHERE StartDate < #2025/01/31# AND EndDate > #2025/01/31#

say not equal to default date if needs be

Question 23

What properties do fully normalised databases have?

Answer 23

All non-key attributes depend upon the key, the whole key and nothing but the key (and have no repeating groups).

Question 24

What are the advantages to having a fully normalised database?

Answer 24

1) Eliminate data redundancy
2) Eliminate data inconsistency
3) Limit unnecessary data duplication

Question 25

What are the problems of data redundancy?

Answer 25

- increase storage costs (larger storage capacity needed) - reduced performance (slower queries)

Question 26

What issues arise with data inconsistency?

Answer 26

- data is no longer **reliable** - can occur from update, insertion or deletion anomalies

Question 27

What is database normalisation?

Answer 27

The process of organising data into **related tables** so each piece of data is **only stored once**.

Question 28

What are many-to-many relationship indicative of?

Answer 28

- Lack of database normalisation - repeating groups

Question 29

What is a client server database?

Answer 29

A database that allows **simultaneous access** to a single, central database from **multiple clients**.

Question 30

How do clients access client-server databases?

Answer 30

**Client applications** that are installed locally on users' computers.

Question 31

What are benefits of client-server databases?

Answer 31

- all users have access to **same, up-to-date data** - data accessed from anywhere **without duplicating and distributing data** - **less data redundancy** and hence lower risk of **data integrity issues** - centrally managed data that can be mined for analysis

Question 32

What are issues with client-server databases?

Answer 32

- data inaccessible if central server is unavailable - simultaneous access can cause congestion and poor performance - multiple access for same resources need to be handled

Question 33

What issues occur with concurrent access?

Answer 33

**Data integrity issues** due to **insertion anomalies** and **lost updates**.

Question 34

What is an insertion anomaly?

Answer 34

Two clients insert records simultaneously that are logically inconsistent.

Question 35

What are lost updates?

Answer 35

Two clients or transactions edit the **same record** to update at the **same time**, which can lead to the earlier update being overridden by the later update.

Question 36

What is a transaction?

Answer 36

A single unit of work in a databases (which changes the databases when **committed** to the database).

Question 37

How to manage the lost update problems and issues with concurrent access generally?

Answer 37

- Record Locks - Serialisation: 1) Timestamp ordering 2) Transaction queueing 3) Commitment ordering

Question 38

What happens in record locks?

Answer 38

Maintains information about which records are being **accessed** at any times and **prevents any transactions to the record** that can compromise data integrity in this time. *Read lock = no writing transactions Write lock = no reading or writing transactions*

Question 39

What issues occur with record locks?

Answer 39

1) Deadlock: two transactions have placed locks on records that their updates are dependent on 2) Performance issues: other users are forced to wait

Question 40

How does timestamp ordering work?

Answer 40

- Timestamp is associated by transaction when it begins - Records timestamp of last successful read or write - Applies rules before committing to database to check if data integrity will be lost - If it will, abandon transaction

Question 41

How does serialisation work?

Answer 41

- Groups updates into transaction - Adds transaction to a processing **queue** - uses FIFO for committing transactions This can lead to reduced performance and longer waiting times

Question 42

How does commitment ordering work?

Answer 42

- Software tracks conflicts between transactions - Applies transactions in an order that reduces conflicts and reduces data inconsistency - Can lead to transactions being delayed and abandoned if leads to issues

Question 43

How do you delete a record from a table in SQL?

Answer 43

DELETE FROM table_name WHERE condition;

Question 44

Issues of concurrent access MS

Answer 44

Question 45

Record locks MS

Answer 45

Question 46

Describe how two users trying to edit the same record could cause a problem if the database system did not implement a method to manage concurrent access. (2 marks)

Answer 46

The update made by the user who saved (the record) first will be lost // only the update made by the user who saved (the record) second will be kept // the update made by the user who saved second will overwrite the update of the user who saved first

Question 47

How to do greater than or equal to in SQL? How to query a string?

Answer 47

- Greater than or equal to is >= - Query a string with quotation marks around the string