SQL databases

Question 1

What does Second Normal Form mean?

Answer 1

Non-key attributes are dependent on the primary key

Question 2

What does First Normal Form mean?

Answer 2

All data values in the data are atomic

There are no repeating columns

Question 3

What does Third Normal Form mean?

Answer 3

Non-key attributes are non-transitively dependent on primary key

Question 4

Define a table in a relational database

Answer 4

a set of data elements organised by columns and rows

Question 5

Define a record in a relational database

Answer 5

a row of data in a table consisting of a value from each column in the table

Question 6

Arrange the following SQL keywords in the correct syntax order

ON
SELECT
WHERE
JOIN
ORDER BY
GROUP BY
FROM

Answer 6

SELECT
FROM
JOIN
ON
WHERE
GROUP BY
ORDER BY

Question 7

What are the five supported data types in SQLite

Answer 7

NULL
INTEGER
REAL
TEXT
BLOB

Question 8

How are booleans represented in SQLite?

Answer 8

SQLite represents booleans as integers (0 = False, 1 = True)

Question 9

When is a junction table required?

Answer 9

Junction tables are needed to represent many-to-many relationships in a relational database

Question 10

Write SQL code to create a junction table to join the following two tables:

Student (studentID, class, …)
CCA(ccaID, teacher_in_charge, …)
studentID and ccaID are the PKs of the tables.

Assume the PK of the junction table is a composite key and PKs are INTEGERs

Answer 10

CREATE TABLE StudentCCA (
studentID INTEGER,
ccaID INTEGER,
PRIMARY KEY (studentID, ccaID),
FOREIGN KEY (studentID) REFERENCES Student(studentID),
FOREIGN KEY (ccaID) REFERENCES CCA(ccaID)
)

Question 11

How should quotes be used in SQL?

Answer 11

Double-quotes (“) for identifiers (table and column names)

Single-quotes (‘) for literals (strings, values to match in a WHERE clause, etc)

Question 12

What is the syntax to create an SQL table?

Answer 12

CREATE TABLE (
  Col1 TYPE,
  Col2 TYPE,
  ...,
  PRIMARY KEY (Col1)
)

Question 13

The following SQL code raises an error. Why?

CREATE TABLE Student (
  ID INTEGER,
  Name TEXT,
  Class TEXT,
  PRIMARY KEY (ID),
);

Answer 13

Last line (PRIMARY KEY) should not have comma

Question 14

The following SQL code raises an error. Why?

CREATE TABLE 'Student' (
  'ID' INTEGER,
  'Name' TEXT,
  'Class' TEXT,
  PRIMARY KEY ('ID')
);

Answer 14

Table and column names are identifiers and should be double-quoted (“), not single-quoted (‘)

Question 15

What is a primary key? State its properties.

Answer 15

A primary key is a candidate key that is most appropriate to become the main key for a table.

Uniquely identifies each record in a table
Should not change over time

Question 16

What is a composite key? State its properties.

Answer 16

A composite key is a combination of two or more fields in a table that can be used to uniquely identify each record in a table

Uniqueness is only guaranteed when the fields are combined
When taken individually, the fields do not guarantee uniqueness

Question 17

What is a foreign key?

Answer 17

A foreign key is a field in one table that refers to the primary key in another table.

Question 18

When is a variable Y functionally dependent on a variable X?

Answer 18

Y is functionally dependent on X if for every valid instance of X, the value of X uniquely determines the value of Y.

i.e one-one relationship.

Question 19

What is data redundancy?

Answer 19

Data redundancy refers to the same data being stored more than once