7 - Databases

Question 1

What is a database? What is the difference between a (flat file) database and a relational database?

Answer 1

-permanent structured collections of data

-a flat file database has only one table, while a relational database has 2 or more tables that are related by foreign keys

Question 2

What is a table in a database?

Answer 2

all of the data about a defined group of things structured with fields (columns) and records (rows)

a database can contain more than one table (that is a relational database)

Question 3

Define a record:

Answer 3

all of the data about one particular object (as a row) in a table

both begin with r

Question 4

Define a field:

Answer 4

a category within a table (as a column)

Question 5

What are primary keys? Give some examples:

Answer 5

a unique identifier for each record in a table (e.g. a car number plate, website name, emails)

Question 6

Define a foreign key:

Answer 6

the primary key from another table used to create a relationship between them

Question 7

Name 4 ways in which tables in a relational database can be linked:

Answer 7

1:1
many:many
1:many
many:1

The example below is a 1:many

Question 8

Why might you want to use a relational database?

Answer 8

reduces data inconsistency (data is different in different places) and redundancy (repeated data)

Question 9

What does SQL stand for?

Answer 9

Structured Query Language

Question 10

What is the select...from... command used for?

Answer 10

specifies the field(s) from the specified table(s) to be included in the displayed output table

The "FROM" part doesn't have to be on a separate line

Question 11

What is the where command used for?

Answer 11

specifies certain conditions that need to be met to filter the displayed output data

Question 12

How do you put 2 where conditions after one another?

Answer 12

where [condition 1]
and [condition 2]
...
and [condition n]

Question 13

How can you make a link between 2 tables in a relational database?

Answer 13

where table1.primaryKey = table2.foreignKey

primaryKey and foreignKey should be the same word/phrase in the two tables

Question 14

How do you order data in SQL?

Answer 14

STOP FORGETTING THE FIELD NAME

order by [field name] asc/desc

Question 15

What does the insert command do? Give an example:

Answer 15

-inserts a new record into a table

insert into [table name] (field1, field2...)
values (value1, value2...)

You don’t have to give a specific field

In field 1, the new record will contain value 1

Question 16

How could you change a certain record’s data in a specific field? Give an example:

Answer 16

-update command

update [table name]
set field1 = value1, field2 = value2...
where [condition]

Question 17

How can you delete records from a table that meet a certain condition?

Answer 17

delete from [table name] where [condition]