Spreadsheets and Databases

Question 1

What is a database? What is a database management system?

Answer 1

A database is a collection of organised information that can be easily accessed, managed and updated. A database management system is a software that allows for the creation and editing of databases.

Question 2

Define the following terms:
1. Table
2. Field
3. Record

Answer 2

1. Table - Stores data in rows (records) and columns (fields)
2. Field - A column that holds a specific category of data (E.g., Name, Product number)
3. Record - A row containing all the details/information about one item (E.g., a student’s details)

Question 3

What is a primary key?

Answer 3

A primary key is a field which uniquely identifies each record in a table. It doesn’t allow for duplicate data to be entered and cannot be left empty.

Question 4

What are the datatypes for databases?

Answer 4

1. Text - For words and letters
2. Number/Numerical/Integer - For numerical values
3. Date/Time - For dates ad times
4. Yes/No or Boolean - For true/false or yes/no answers
5. Curreny - For monetary values

Question 5

What is data validation?

Answer 5

Data validation ensures the data entered into a database is meaningful, consistent and accurate. It prevents incorrect data from being stored in your database.

Question 6

What is the purpose of -
1. Range Check
2. Presence Check
3. Length Check

Answer 6

1. Range Check - Ensures a number or data falls within a specific range
2. Presence Check - Ensures that a field is not left blank
3. Length Check - Ensures that the data entered is of a specific length or within a length range

Question 7

What is the purpose of -
1. Type Check
2. Format Check
3. Uniqueness Check
4. Consistency Check

Answer 7

1. Type Check - Ensues that a data is of a correct type (e.g., date, numerical)
2. Format Check - Ensures that te data matches a specific format or pattern
3. Uniqueness Check - Ensures that the entered data is unique and doesn’t duplicate existing data
4. Consistency Check - Ensures that the related data is logically consistent

Question 8

How to write a length check?

Answer 8

Len([Field_Name])<=LENGTH

Question 9

Describe the following functions -
SUM, AVERAGE, PRODUCT, ROUND

Answer 9

Sum - Adds all the numbers in a given range
Average - Calculates the average/mean of all the numbers in a given range
Product - Multiplies all the numbers in a given range
Round - Rounds a number to a specified number of decimal places

Question 10

Describe the following functions -
MAX, MIN, COUNT, COUNTA, COUNTIF, SUMIF

Answer 10

Max - Returns the highest value of all the numbers in a given range
Min - Returns the lowest value of all the numbers in a given range
Count - Counts the number of numerical values in a given range
CountA - Counts all the non-empty cells in the range
Countif - Counts the number of cells which meet a specific condition
Sumif - Adds the values of cells that meet a specific condition.

Question 11

How do you write these functions?
IF, NESTED IF, COUNTIF, SUMIF

(Write via examples)

Answer 11

=IF(condition, “value_if_true”, “value_if_false”)

=IF(condition, “value_if_true”, IF(condition2, “value_if_true”, “value_if_false”))

=COUNTIF(range, criteria in “”)

=SUMIF(range, “criteria”)