Chapter 6: Databases Flashcards Preview

Computer Science GCSE > Chapter 6: Databases > Flashcards

Flashcards in Chapter 6: Databases Deck (49):
1

Database

A structured collection of related data

2

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

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

3

Popular ways of sorting data include... (3)

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

4

What are the two main types of databases?

1. Flat-file
2. Relational

5

Flat-file database

Stores all of its data in a single table

6

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

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

7

Relational database

Organises its data into related tables of records

8

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

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

9

Data duplication OR Data redundancy

Where the same data is stored more than once, unnecessarily

10

Data duplication leads to...

Data inconsistency

11

Data inconsistency

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

12

Entity

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

13

Table

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

14

Record

Data stored about a particular entity within a table

15

Field

One specific data item being stored about a particular entity

16

Fill in the blanks:
Records form the _____ of a table. Fields form the table's _____.

1. Rows
2. Columns

17

Primary key

The field in a table that uniquely identifies a record

18

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

Once

19

Foreign key

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

20

Relationship

A connection between two tables

21

How do you form a relationship?

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

22

What are the three different kinds of relationships between tables?

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

23

Index

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

24

How does an Index work?

It sorts the data by the most commonly searched columns

25

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

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

26

Primary index

The primary key field

27

SQL

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

28

Query

A request for information from a database based on specified criteria

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

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

30

SELECT [field(s)]

Lists the Fields you want to display

31

FROM [Table(s)]

Lists the Table or Tables where the data will come from

32

WHERE [condition(s)]

Lists the search criteria

33

Logical operator

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

34

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

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

35

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

1. ASC: ascending
2. DESC: descending

36

What is the default order using ORDER BY?

Ascending, although the ASC may still be included for clarity

37

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

INSERT INTO Table
VALUES ([values])

38

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

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

39

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

No. Any unfilled Fields will have the NULL value

40

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

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

41

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

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

42

Format of a Query that deletes a Record

DELETE *
FROM Table
WHERE Primary_key = value

43

Format of a Query to delete a Table

DROP TABLE Table

44

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

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

45

Format of a Query to create a Table

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

46

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

Dot notation

47

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

Books.title

48

What does the * wildcard character mean?

All

49

What does the following Query do?

SELECT id, title
FROM Movies
WHERE duration >= 86 AND genre = 'Horror'
ORDER BY duration ASC;

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