3.7 Databases real Flashcards
What is a flat file database
A flat file database stores a single table of data inside a single text file
How are flat file databases recorded (3)
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
Ease of flat file database?
This format is very easy to set up, however, it is hard to manage for anything but the simplest of data sets
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
.
Primary key define
A primary key is a field that stores unique data for each record in a table
Label parts of flat file database
.
What can be used as a primary key? And its data type
An ID number can be introduced that is unique for each record
The data type will be an int or autonumber
.
Flat file database issues (2)
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
Table-
Record-
Field-
Primary key-
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
Integer-
Real-
Date,time,datetime-
Char-
Varchar-
Text-
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)
What is a relational database (3)
A relational database contains multiple tables
These tables will have links known as relationships
Each table is also known as a relation
Why are relational databases good
Relational databases allow us to design tables that reduce inconsistencies and eliminate data redundancy
Primary key?
A primary key is a field, where every value stored in it will be unique
Foreign key?
A foreign key is a field in a table that references the primary key of another table
3 different relationships between tables
.
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
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
SQL?
SQL stands for Structured Query Language
It is a language which allows you to create, query, update and delete data to and from databases
The SQL syntax for querying a database is:
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)
How to select all in sql
* — wildcards
SELECT *
FROM members
WHERE Surname LIKE ‘H*’
SQL search between and starting letters
BETWEEN between an inclusive range
LIKE search for a pattern
SELECT *
FROM members
WHERE Surname LIKE ‘H*’
Write SQL queries to find:
All the names and breeds of female dogs
All fields for dogs older than four (including those aged four)
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
How to sort data in ascending or descending order?
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
How could you change all 3 year old pets to be 4?
UPDATE Dogs SET Age = 4 WHERE Age = 3
To change the age of Milly from 5 to 6 use:
UPDATE Dogs SET Age = 6 WHERE DogID = 2
The WHERE criteria allows?
The WHERE criteria allow more than one record to be deleted/added at a time
How could you delete all Brown Labradors from the table ‘Dogs’?
DELETE FROM Dogs WHERE Breed = ‘Labrador’ AND Colour = ‘Brown’
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’
SELECT Owners.Firstname, Owners.Lastname, Dogs.Breed
FROM Owners, Dogs
WHERE Owners.DogsID = Dogs.DogsID AND Dogs.Age = 4
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
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’
