SQL Databases

Question 1

What does First Normal Form mean?

Answer 1

All data values in the data are atomic
There are no repeating columns

Question 2

What does Second Normal Form mean?

Answer 2

Non-key attributes are fully dependent on the primary key.
(If a non-key attribute is dependent on some but not all of the columns in a composite primary key, the table violates 2NF.)

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 organized into columns and rows

Question 5

Define a record in a relational database

Answer 5

a row of data in a table consisting of single value in 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, …)

Assume the first column is the PK 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 data integrity?

Answer 15

the accuracy: data recorded should accurately reflect real world

consistency: data in one part of the system should not contradict another part

Question 16

differences between SQL and NoSQL

Answer 16

structure:
-records in SQL but have all the same columns and all the columns must have the same data type with NoSQL does not

strict:
- constraints are difficult to change as SQL follows a strict data schema (less flexible)
while NoSQL does not have a schema, hence it will require,
- 1. more validation to be implemented to ensure..
- 2. there will be more duplicated or hard to normalise data since collections cannot be joined in a query (more careful and individual updates to prevent breaking of data integrity)

scale:
-as a SQL database grows in size it requires a more powerful machine to handle the database (scales horizontally) while NoSQL can scale vertically better as more machines can be added

Question 18

why should data be normalised

Answer 18

1. reduced data redundancy
2. protects data integrity