Chapter 6: Databases

Question 1

Database

Answer 1

A structured collection of related data

Question 2

A database is structured in a way that allows you to… (3)

Answer 2

1. Search (find data)
2. Sort (order the data)
3. Add, update and remove (change the data)

Question 3

Popular ways of sorting data include… (3)

Answer 3

1. Alphabetical OR reverse alphabetical
2. Numerical OR reverse numerical
3. Chronological OR reverse chronological

Question 4

What are the two main types of databases?

Answer 4

1. Flat-file

2. Relational

Question 5

Flat-file database

Answer 5

Stores all of its data in a single table

Question 6

Pros and Cons of a Flat-file database (3 | 3)

Answer 6

Pros

1. Simple and easy to implement
2. Easy to access using many different applications
3. Easy to understand - all information stored in one place

Cons

1. Easy to extract information - less secure
2. Data duplication makes data inconsistency more likely
3. Searching process is time-consuming with larger databases

Question 7

Relational database

Answer 7

Organises its data into related tables of records

Question 8

Pros and Cons of a Relational database (4 | 2)

Answer 8

Pros

1. Information only has to be written or updated in one area - less data duplication
2. More efficient storage
3. Flexible and well-established
4. Standard data access language in SQL

Cons

1. Harder to design and maintain
2. Has a lot of overhead and complexity

Question 9

Data duplication OR Data redundancy

Answer 9

Where the same data is stored more than once, unnecessarily

Question 10

Data duplication leads to…

Answer 10

Data inconsistency

Question 11

Data inconsistency

Answer 11

Where different and conflicting versions of the same data appear in different places, thus compromising data integrity

Question 12

Entity

Answer 12

A single person, place or thing about which data can be stored

Question 13

Table

Answer 13

A collection of data about a certain topic organised into records and fields

Question 14

Record

Answer 14

Data stored about a particular entity within a table

Question 15

Field

Answer 15

One specific data item being stored about a particular entity

Question 16

Fill in the blanks:

Records form the _____ of a table. Fields form the table’s _____.

Answer 16

1. Rows

2. Columns

Question 17

Primary key

Answer 17

The field in a table that uniquely identifies a record

Question 18

How many times can a primary key occur in a table?

Answer 18

Once

Question 19

Foreign key

Answer 19

A field in one table that is the primary key in another, used to create a relationship between the two

Question 20

Relationship

Answer 20

A connection between two tables

Question 21

How do you form a relationship?

Answer 21

By including the primary key of one table as a foreign key in another

Question 22

What are the three different kinds of relationships between tables?

Answer 22

1. One-to-one
2. One-to-many OR many-to-one
3. Many-to-many

Question 23

Index

Answer 23

A data structure defined along the columns of a database table, used to significantly speed up queries

Question 24

How does an Index work?

Answer 24

It sorts the data by the most commonly searched columns

Question 25

Pros and Cons of using an Index (4 | 2)

Answer 25

Pros 1. Usually leads to much better performance 2. Makes it possible to quickly retrieve data 3. Can be used for sorting 4. Unique indexes guarantee uniquely identifiable records in the database Cons 1. They decrease performance on inserts, updates and deletes 2. They take up disk space

Question 26

Primary index

Answer 26

The primary key field

Question 27

SQL

Answer 27

Structured Query Language: a high level command language, used to search through and manipulate databases

Question 28

Query

Answer 28

A request for information from a database based on specified criteria

Question 29

Format of a Query that selects data from one or more Tables based on a certain condition in a specified order of one or more fields

Answer 29

SELECT [field(s)] FROM [Table(s)] WHERE [condition(s)] ORDER BY [field(s)] [optional order keyword]

Question 30

SELECT [field(s)]

Answer 30

Lists the Fields you want to display

Question 31

FROM [Table(s)]

Answer 31

Lists the Table or Tables where the data will come from

Question 32

WHERE [condition(s)]

Answer 32

Lists the search criteria

Question 33

Logical operator

Answer 33

NOT, AND or OR. Used in complex criteria in queries

Question 34

ORDER BY [field(s)] [optional order keyword]

Answer 34

Lists the Field or Fields by which the data is sorted in a certain order

Question 35

What two keywords can be used with the ORDER BY clause?

Answer 35

1. ASC: ascending | 2. DESC: descending

Question 36

What is the default order using ORDER BY?

Answer 36

Ascending, although the ASC may still be included for clarity

Question 37

Format of a Query that inserts a Record into a Table (column names not specified)

Answer 37

INSERT INTO Table | VALUES ([values])

Question 38

Format of a Query that inserts a Record into a Table (column names specified)

Answer 38

INSERT INTO Table ([columns]) | VALUES ([respective values])

Question 39

Do all of the Fields within a Record have to be filled?

Answer 39

No. Any unfilled Fields will have the NULL value

Question 40

Format of a Query that changes the value of a Field when certain criteria is met

Answer 40

UPDATE Table SET field = value WHERE [condition(s)]

Question 41

Format of a Query that deletes one or several Fields from a Record

Answer 41

DELETE [field(s)] FROM Table WHERE Primary_key = value

Question 42

Format of a Query that deletes a Record

Answer 42

DELETE * FROM Table WHERE Primary_key = value

Question 43

Format of a Query to delete a Table

Answer 43

DROP TABLE Table

Question 44

Why should you be careful when using DELETE and DROP TABLE statements?

Answer 44

SQL doesn't warn you before executing commands, so you could accidentally delete important information

Question 45

Format of a Query to create a Table

Answer 45

``` CREATE TABLE Table ( column data_type [...] ) ```

Question 46

How do you show which Table a particular Field comes from when the Query involves more than one Table's contents?

Answer 46

Dot notation

Question 47

How would you refer to the title attribute of the Books Table?

Answer 47

Books.title

Question 48

What does the * wildcard character mean?

Answer 48

All

Question 49

What does the following Query do? SELECT id, title FROM Movies WHERE duration >= 86 AND genre = 'Horror' ORDER BY duration ASC;

Answer 49

Lists the ID and title of any horror movies with a duration of 86 minutes or longer from the Movies table, in ascending order of duration