3.7 Databases real

Question 1

What is a flat file database

Answer 1

A flat file database stores a single table of data inside a single text file

Question 2

How are flat file databases recorded (3)

Answer 2

Flat file databases are often stored using a CSV (comma separated values) format
Each record appears on a separate line
Each field is separated by a comma

Question 3

Ease of flat file database?

Answer 3

This format is very easy to set up, however, it is hard to manage for anything but the simplest of data sets

Question 4

Create a design for a flat file database that will store a contact book with names and mobile telephone numbers

For each field name, include the data type required
Create two lines of a CSV file with sample data

Answer 4

.

Question 5

Primary key define

Answer 5

A primary key is a field that stores unique data for each record in a table

Question 6

Label parts of flat file database

Answer 6

.

Question 7

What can be used as a primary key? And its data type

Answer 7

An ID number can be introduced that is unique for each record

The data type will be an int or autonumber

.

Question 8

Flat file database issues (2)

Answer 8

Flat files mean that we often have to repeat data in each record.

This leads to inconsistencies in the data – these make it hard to search or sort the data
This also causes redundant data – so the database uses more memory or storage than it needs to, it may also take longer to search

Question 9

Table-
Record-
Field-
Primary key-

Answer 9

Table – stores records and fields in an organised manner
Record – an individual collection of data for one person/object, a row in a table
Field – one item of data, a column in a table
Primary key – field containing unique values for all records

Question 10

Integer-
Real-
Date,time,datetime-
Char-
Varchar-
Text-

Answer 10

Integer (whole number)
Real, Float, Decimal (number with a decimal component)
Date, Time, Datetime (to store a dates and times)
Char (fixed length string up to 8,000 characters)
Varchar (variable length string up to 8,000 characters)
Text (variable length string up to 2 GB of data)

Question 11

What is a relational database (3)

Answer 11

A relational database contains multiple tables
These tables will have links known as relationships
Each table is also known as a relation

Question 12

Why are relational databases good

Answer 12

Relational databases allow us to design tables that reduce inconsistencies and eliminate data redundancy

Question 13

Primary key?

Answer 13

A primary key is a field, where every value stored in it will be unique

Question 14

Foreign key?

Answer 14

A foreign key is a field in a table that references the primary key of another table

Question 15

3 different relationships between tables

Answer 15

.

Question 16

A ______________ makes use of ______________, also known as relations. These are linked by ______________.
Each table has a ______________ which is used to uniquely identify each record. A second table can refer to information in the first table by referring to its primary key. In this case it is referred to as a ______________.
Using relational databases and multiple tables allows the elimination of ______________ and reduces data inconsistency.
Data redundancy Relational database Relationships Foreign key Multiple tables Primary key

Answer 16

A relational database makes use of multiple tables, also known as relations. These are linked by relationships.
Each table has a primary key which is used to uniquely identify each record. A second table can refer to information in the first table by referring to its primary key. In this case it is referred to as a foreign key.
Using relational databases and multiple tables allows the elimination of data redundancy and reduces data inconsistency

Question 17

SQL?

Answer 17

SQL stands for Structured Query Language
It is a language which allows you to create, query, update and delete data to and from databases

Question 18

The SQL syntax for querying a database is:

Answer 18

The SQL syntax for querying a database is:
SELECT … (list the fields to be displayed)
FROM … (specify the table name)
WHERE … (list the search criteria)

Question 19

How to select all in sql

Answer 19

* — wildcards

SELECT *
FROM members
WHERE Surname LIKE ‘H*’

Question 20

SQL search between and starting letters

Answer 20

BETWEEN between an inclusive range
LIKE search for a pattern

SELECT *
FROM members
WHERE Surname LIKE ‘H*’

Question 21

Write SQL queries to find:
All the names and breeds of female dogs
All fields for dogs older than four (including those aged four)

Answer 21

All the names and breeds of female dogs
SELECT Name, Breed FROM Dogs WHERE Gender = ‘F’
All fields for dogs older than four (including those aged four)
SELECT * FROM Dogs WHERE Age >= 4

Question 22

How to sort data in ascending or descending order?

Answer 22

ORDER BY allows a query to sort data by ascending or descending order

For ascending order
SELECT * FROM members
ORDER BY Surname ASC

For descending order
SELECT * FROM members
ORDER BY Surname DESC

Question 23

How could you change all 3 year old pets to be 4?

Answer 23

UPDATE Dogs SET Age = 4 WHERE Age = 3

Question 24

To change the age of Milly from 5 to 6 use:

Answer 24

UPDATE Dogs SET Age = 6 WHERE DogID = 2

Question 25

The WHERE criteria allows?

Answer 25

The WHERE criteria allow more than one record to be deleted/added at a time

Question 26

How could you delete all Brown Labradors from the table ‘Dogs’?

Answer 26

DELETE FROM Dogs WHERE Breed = ‘Labrador’ AND Colour = ‘Brown’

Question 27

A query can be made which selects information from two tables
How do you think you select the owner and dog names for all four year old dogs using the keywords SELECT, FROM, WHERE and AND?
Tables ‘Owners’ + ‘Dogs’

Answer 27

SELECT Owners.Firstname, Owners.Lastname, Dogs.Breed

FROM Owners, Dogs

WHERE Owners.DogsID = Dogs.DogsID AND Dogs.Age = 4

Question 28

In pairs create SQL statements for the following:
Find all animal names in alphabetical order
Find all animal names and weights that are over 1000 kg
Find all animals, including all fields that are over 2 m
Change the Giraffe height from 5.5 to 5.6
Remove the record for the Sealion

Answer 28

All animal names in alphabetical order
SELECT Animal FROM AnimalsORDER BY Animal ASC

All animal names and weights that are over 1000 kg
SELECT Animal, Weight_kg FROM Animals WHERE Weight_kg > 1000

All animals, including all fields that are over 2 m
SELECT * FROM Animal WHERE Height_m > 2

Change the Giraffe height from 5.5 to 5.
6UPDATE Animals SET Height_m = 5.6 WHERE Animal = ‘Giraffe’

Remove the record for the Sealion
DELETE FROM Animals WHERE Animal = ‘Sealion’