Databases

Question 1

What is an entity in entity-relationship modelling?

Answer 1

An entity is a category of object, person, event or thing of interest about which data is to be recorded.
Examples: Employee, Film, Actor, Product, Recipe, Ingredient
Each entity in a database system has attributes.

Question 2

What entities might there be for a database used to store data about dentist appointments at a particular dentist surgery?

Answer 2

E.g. Dentist, Patient, Appointment.

Question 3

What attributes might the entity “Dentist” have in a database used to store data about dentist appointments at a particular dentist surgery?

Answer 3

E.g. DentistID, Title, FirstName, Surname, Qualification

Question 4

What attributes might the entity “Patient” have in a database used to store data about dentist appointments at a particular dentist surgery?

Answer 4

E.g. PatientID, Title, FirstName, Surname, EmailAddress, TelephoneNumber

Question 5

What attributes might the entity “Appointment” have in a database used to store data about dentist appointments at a particular dentist surgery?

Answer 5

E.g. StartTime, EndTime, PatientID, DentistID, Cost, Procedure

Question 6

How are entity descriptions normally written?

Answer 6

An entity description is typically written using the format
Entity1 (Attribute1, Attribute2)
E.g.
Dentist(Title, Firstname, Surname, Qualification)
Patient(Title, Firstname, Surname, Address, Telephone)

Question 7

Write an entity description for the entity Pet with the attributes Name, Species, Age, and Owner.

Answer 7

Pet(Name, Species, Age, Owner)

Question 8

Write an entity description for the entity Student with the attributes FirstName, Surname, Age, YearGroup, and EmailAddress.

Answer 8

Student(FirstName, Surname, Age, YearGroup, EmailAddress)

Question 9

What is an entity identifier?

Answer 9

Each entity needs to have an entity identifier which uniquely identifies the entity.
In a relational database, the entity identifier is known as the primary key.
An ID number can be used as a primary key for Dentist and Patient:
Dentist(DentistID, Title, Firstname, Surname, Qualification)

Question 10

What are the three types of relationships that can exist between entities?

Answer 10

One-to-one
One-to-many
Many-to-many

Question 11

Give an example of a one-to-one relationship.

Answer 11

Husband and Wife (in the UK)
Country and Prime Minister

Question 12

Give an example of a one-to-many relationship.

Answer 12

Customer and order (a customer can have multiple orders)
Borrower and library book (a borrower can borrow multiple books)
School and pupil (a school can have multiple pupils)

Question 13

Give an example of a many-to-many relationship.

Answer 13

Student and course
Student and teacher
Stock item and supplier
Film and actor

Question 14

What is an entity-relationship diagram?

Answer 14

A diagrammatic way of representing the relationships between the entities in a database.
To show the relationship between two entities, both the degree and the name of the relationship need to be specified.

Question 15

How do relational databases work?

Answer 15

In a relational database, a separate table is created for each entity identified in the system.
Where a relationship exists between two entities, an extra field called a foreign key links the tables.

Question 16

What’s a foreign key?

Answer 16

A foreign key is an attribute that creates a joint between two tables. It is the attribute that is common to both tables.
The primary key in one table is the foreign key in the table to which it is linked.

Question 17

How do you link tables that have a many-to-many relationship with each other?

Answer 17

When there is a many-to-many relationship between two entities, tables cannot be directly linked.
E.g. the relationship between Student and Course. A student takes many courses, and the same course is taken by many students.
In this case, an extra table is needed to link the two entities.
For this example, this table could be called Enrolment.

Question 18

What is a composite primary key?

Answer 18

Sometimes 2 or even more attributes are needed to uniquely define a record.
For example, in a customer order consisting of many different order lines, each order line may be uniquely identified by the two attributes orderNumber and orderLine.

Question 19

How are primary keys shown in an entity-relationship diagram?

Answer 19

The name of the attribute which is the primary key is underlined.

Question 20

How are foreign keys shown in an entity relationship diagram?

Answer 20

The name of the attribute which is the foreign key is in italics.

Question 21

What is normalisation?

Answer 21

Normalisation is a process used to come up with the best possible design for a database.
Tables should be organised so that data is not duplicated in the same table or in different tables.
The structure should allow complex queries to be made.
There are 3 stages in normalisation, called First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

Question 22

What is First Normal Form (1NF)?

Answer 22

A table is in First Normal Form if it contains no repeating attributes or groups of attributes.
All attributes must be atomic – a single attribute cannot consist of 2 data items (e.g. addresses, each part must be on a separate line to be able to search by street name, postcode etc.)

Question 23

What is Second Normal Form (2NF)?

Answer 23

A table is in Second Normal Form if it is in first normal form and contains no partial dependencies.
Partial dependencies can only occur if the primary key is a composite key.

Question 24

What is Third Normal Form (3NF)?

Answer 24

A table is in Third Normal Form if it is in Second Normal Form and contains no non-key dependencies. This can be defined by saying
“All attributes are dependent on the key, the whole key, and nothing but the key”

Question 25

What are the advantages of normalisation?

Answer 25

It is easier to maintain and change a normalised database.
There is no unnecessary duplication of data.
Data integrity is maintained – if a person changes address, for example, the update needs to be made only once to a single table.
Having smaller tables with fewer fields means faster searches and savings in storage.

Question 26

What is SQL?

Answer 26

SQL stans for Structured Query Language.
It is a declarative language used for querying and updating tables in a relational database.
It can also be used to create tables.
You can create SQL statements in a programming language such as Python to access and manipulate a database.

Question 27

How do you use a SELECT…FROM…WHERE statement in SQL?

Answer 27

The select statement is used to extract fields from one or more tables, using the following syntax:
SELECT – list of fields to be displayed
FROM – list the table or tables the data will come from
WHERE – list of search criteria
ORDER BY – list the fields that the data is to be sorted based on (ASC or DESC, default is ASC)

Question 28

How do you use a wildcards (*) and LIKE statements in SQL?

Answer 28

The wildcard () can be used to select all fields – SELECT *
LIKE can be used to search for a pattern:
E.g.
SELECT *
FROM tblProduct
WHERE subject LIKE “Comp”

Question 29

What are some operators you can use in the WHERE clause when selecting records from a database with SQL?

Answer 29

BETWEEN – between an inclusive range
IN – specify multiple possible values for a column
For example:
SELECT *
FROM tblProduct
WHERE price BETWEEN 5.00 and 10.00
The following can also be used in a WHERE clause:
= > < >= <= AND OR NOT

Question 30

How are semicolons used in SQL?

Answer 30

Some database systems require a semicolon at the end of each SQL statement.
A semicolon is the standard way to separate each SQL statement.
DO NOT put a semicolon at the end of each line.

Question 31

How do you extract data from multiple tables in one SELECT statement?

Answer 31

Using SQL, you can combine data from two or more tables by specifying the links between the tables.
This is specified in the WHERE clause.
E.g.
SELECT *
FROM Pets, Owners
WHERE Pets.PetID == Owners.PetID

Question 32

What if you are selecting data from multiple tables, but an attribute name occurs in more than one of the tables?

Answer 32

When you are selecting attributes from linked tables, if the attribute name occurs in more than one table, you should specify the table name.
If the attribute only occurs in only one table, specifying the table name is optional.

Question 33

How is the JOIN keyword used in SQL?

Answer 33

Data from two linked tables can be extracted using the join keyword (an alternative to WHERE clause).

Question 34

What is Data Definition Language (DDL) in SQL?

Answer 34

DDL is used to create the database, design tables, set access privileges for users, etc.

Question 35

What is Data Manipulation Language (DML) in SQL?

Answer 35

DML can be used to retrieve (select), update, insert, and delete data in a database.

Question 36

How do you insert data into a table using SQL?

Answer 36

The INSERT INTO statement is used to insert a new record into a database.
E.g.
INSERT INTO Product(ProductID, Description, Price)
VALUES(“A345”, “Pink rabbit”, 7.50)

Question 37

What is the data type CHAR(n) in SQL?

Answer 37

Character string of fixed length n.
E.g. ProductCode CHAR(6)
would mean that ProductCodes have to be 6 characters long.

Question 38

What is the data type VARCHAR(n) in SQL?

Answer 38

Character string of variable length, the maximum being n.
E.g. Surname VARCHAR(25)
would mean that Surnames can’t be longer than 25 characters.

Question 39

What is the data type BOOLEAN in SQL?

Answer 39

BOOLEAN means that the data can either be TRUE or FALSE.
e.g. ReviewComplete BOOLEAN
would mean that ReviewComplete can either be TRUE or FALSE.

Question 40

What is the data type INTEGER or INT in SQL?

Answer 40

INTEGER or INT means that the data can be any whole number (positive, zero, or negative).
e.g. Quantity INTEGER
would mean that Quantity can be any whole number.

Question 41

What is the data type FLOAT in SQL?

Answer 41

FLOAT means that the data can be any number which can be represented with a floating decimal point.
e.g. Length FLOAT(10,2) means the value of float can have a maximum of 10 digits with a maximum of 2 after the decimal point.

Question 42

What is the data type DATE in SQL?

Answer 42

DATE stores Day, Month, and Year values.
e.g. HireDate DATE
can be used to store the Day, Month, and Year that an employee was hired.

Question 43

What is the data type TIME in SQL?

Answer 43

TIME stores Hour, Minute, and Second values.
e.g. RaceTime TIME
can be used to store how long it took each person to complete a race (in hours, minutes and seconds).

Question 44

What is the data type CURRENCY in SQL?

Answer 44

CURRENCY formats numbers in the currency used in your region.
e.g. EntryFee CURRENCY
can be used to store the entry fee in the local currency.

Question 45

How do you update data using SQL?

Answer 45

The UPDATE statement is used to update a record in a table.
E.g.
UPDATE Product
SET Description = “Blue Rabbit”, Price = 8.25
WHERE ProductID = “A345”

Question 46

How do you delete a record in SQL?

Answer 46

The DELETE statement is used to delete a record in a table.
E.g.
DELETE FROM Product
WHERE ProductID = “A345”

Question 47

How do you create a new table in SQL?

Answer 47

Using the CREATE TABLE statement.
E.g.
CREATE TABLE tblProduct
(
ProductID CHAR(4) NOT NULL PRIMARY KEY,
Description VARCHAR(20) NOT NULL,
Price CURRENCY,
)

Question 48

How do you alter a table structure in SQL?

Answer 48

The ALTER TABLE statement is used to add, delete, or modify columns (fields) in an existing table.

Question 49

How do you add a new column to a table structure using the ALTER TABLE statement?

Answer 49

Using the ADD statement.
E.g.
ALTER TABLE tblProduct
ADD QtyInStock INTEGER

Question 50

How do you delete a column from a table structure using the ALTER TABLE statement?

Answer 50

Using the DROP statement.
E.g.
ALTER TABLE tblProduct
DROP QtyInStock

Question 51

How do you change the data type of a column using the ALTER TABLE statement?

Answer 51

Using the MODIFY COLUMN statement.
E.g.
ALTER TABLE tblProduct
MODIFY COLUMN Description VARCHAR(30) NOT NULL

Question 52

How do you define linked tables in SQL?

Answer 52

Using the FOREIGN KEY and REFERENCES statements.
E.g.
CREATE TABLE ProductComponent
(ProductID CHAR(4) NOT NULL
CompID CHAR(6) NOT NULL
Quantity INTEGER,
FOREIGN KEY ProductID REFERENCES Product(ProductID)
FOREIGN KEY CompID REFERENCES Component(CompID)
PRIMARY KEY (ProductID, CompID) )

Question 53

What is a client-server database?

Answer 53

A client-server database provides simultaneous access to a database for several clients.
Database Management System (DBMS) software on the server processes requests for clients, which are individual workstations.
E.g. When you make an online transfer from your bank account to a company you have bought goods from, a client program in your computer forwards the request to a server program at the bank. When your account has been retrieved from the database, the amount is deducted and the new balance displayed to you by the client program in your computer.

Question 54

What are the advantages of a client-server system?

Answer 54

Consistency of the database is maintained because only one copy is held.
The resources of a powerful computer are made available to a large number of users.
Access rights and security are managed centrally.
Backup and recovery are managed centrally.

Question 55

What is record locking used for in a client-server system?

Answer 55

Record locking prevents simultaneous access to objects in a database in order to prevent updates being lost or inconsistencies in the data arising.
With record locking, a 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 or cancelled.

Question 56

What is serialisation and timestamp ordering in a client-server system?

Answer 56

Serialisation is an alternative technique to ensure that transactions do not overlap in time. It can be implemented using timestamp ordering. Every object in the database has a read timestamp and a write timestamp. These are updated every time an object is read or written.
When the user tries to save an update, if the read timestamp is not the same as it was when they started the transaction, the DBMS knows that another user has accessed the same object. The transaction will be cancelled and a message will be sent to the user.

Question 57

What is commitment ordering in a client-server system?

Answer 57

This is another serialisation technique to ensure that no transactions are lost if two clients are simultaneously trying to update a record. Transactions are ordered in terms of their dependencies on one another as well as the time they were initiated. It can be used to prevent deadlock by blocking one request until another is completed.