1.3.2 Databases

Question 1

Database

Answer 1

An organised collection of data

Question 2

Database advantages

Answer 2

Make data available to users, the data is correct and the data can be held securely

Question 3

Table

Answer 3

A collection of associated data stored within a database

Question 4

Fields

Answer 4

The characteristics of each table

Question 5

Record

Answer 5

An individual entry that belongs in a table

Question 6

Entity

Answer 6

Something in the real world that we store data about

Becomes a table in a database

Question 7

Attribute

Answer 7

A characteristic of an entity

Becomes a field in a database

Question 8

Entity description format

Answer 8

TableName(Entity1, Entity2…)

Question 9

Char(n) vs VarChar(n)

Answer 9

A char string has fixed length n, a varchar string has variable length up to n

Question 10

How to access a database

Answer 10

Using a DBMS (Database Management System)

Question 11

Flat file database

Answer 11

A database comprised of a single table, based around a single entity and its attributes

Question 12

Flat file advantages and disadvantages

Answer 12

+ simple and quick to set up

- very inefficient, used for storing small amounts of data

Question 13

Relational databases

Answer 13

Databases split into multiple tables linked by relationships

Question 14

Primary key

Answer 14

A field in a record which is unique, allowing each record to be uniquely identified. Must not be duplicated or reused.

Question 15

Secondary key

Answer 15

A field can be defined as a secondary key to be indexed for faster lookups.

Question 16

Foreign key

Answer 16

The key in a table that correlates to a primary key in another table

Question 17

Entity Relationship Diagrams (ERD)

Answer 17

One to one is linked by a straight line
One to many diverges into 3 near the many side]
Many to many diverges into 3 at both ends

Question 18

SQL

Answer 18

Structured Query Language

A declarative language used for querying and updating tables in a relational database

Question 19

SQL Queries

Answer 19

Retrieves data based on a given criteria

Question 20

SQL Clauses

Answer 20

Filters data and returns data that fulfils a specific condition

Question 21

SQL Predicates

Answer 21

Specify conditions which return the filter

Question 22

SQL Expression

Answer 22

A combination of one or more values, functions and operators

Question 23

SQL Select format

Answer 23

SELECT lists the fields to be displayed
FROM states which table they come from
WHERE specifies a condition
ORDER BY lists how they are sorted (ASC,DESC)
e.g. SELECT name FROM sales WHERE cores=4 ORDER BY name DESC

Question 24

SQL UPDATE

Answer 24

Modifies existing values in a table

Question 25

SQL DELETE

Answer 25

Deletes existing records from a table

Question 26

SQL DROP

Answer 26

Deletes a database or table

DROP table tableName

Question 27

SQL INSERT INTO

Answer 27

Inserts new records into a table

INSERT INTO Table(Fields), VALUES (Data)

Question 28

Join

Answer 28

Combines rows from 2 or more tables based on a related column between them

Question 29

INNERJOIN

Answer 29

The SQL for joining tables

INNERJOIN newTableName on table.field = table.field

Question 30

SQL UPDATE Syntax

Answer 30

UPDATE tableName SET field = newValue WHERE …

Question 31

SQL Additional Data Types

Answer 31

TIME and CURRENCY

Question 32

SQL Select All

Answer 32

SELECT *

Question 33

SQL Like

Answer 33

Like “%xx%”

Where the string xx is included anywhere in the data it is returned