Databases
What is a database?
A database is an organized collection of information or data, allowing for flexibility in organization, displaying and printing of information.
Why do we use databases?
It doesn’t take up a lot of room, can be easily edited, easier to search and easier to sort.
Database Entities:
An entity is a person, place, thing or concept about which data can be collected about them.
Some examples of database tables might be:
a customer table
an employee
a movies table
a smartphone table
a car table
Attributes:
An attribute describes the facts, details or characteristics of an entity.
ENTITY = CUSTOMER
First name
Surname
Address
Phone number
Date of birth
ENTITY = MOVIES
Movie title
Release date
Director
How much made at box office
Movie length
Attribute Types:
Text - Stores characters or words and can be a combination of text and numbers. A telephone number is technically a text as it starts with a 0
Number - Can store whole numbers (integers) or numbers with decimal places. For example, 1.3 or 131008 are both numbers.
Date - Stores dates (and can be in different formats). Examples include: 13/10/2008.
Time - Stores time of day. Normally in 24-hour format such as 15:07.
Boolean - Stores true or false/yes or no answers.
Attribute Size:
Limits should be applied to limit amount of storage space. For example, a person’s first name should be limited to 20 characters as it is unlikely you will get a bigger name.
Entity Relationship Diagrams:
GREEN = Entities
ORANGE = Attributes
YELLOW = Relationship (One to Many)
Database Relationships (One to Many):
The most common type of relationship between database entities. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, a mother and her children tables have a one-to-many relationship: a mother can have many children. a child can have only one mother.
Example One to Many relationship:
Walker - Walk:
There is only one walker to many walks. A dog walker can do many different walks.
Dog - Walk:
There is one dog to many walks. One dog can go on many different walks.
Owner - Dog:
There is one owner to many dogs. One owner may have different dogs.
Flat File databases:
Useful when storing only a small amount of records but data can be duplicated and increases the chance of human error when typing in the same data a number of times.
Relational Databases:
Within a database, tables are linked to prevent duplication of data. The advantages of using a relational database is that more complex queries can be carried out on multiple tables, it is easier to maintain security and it is easier to change/update data.
Validation:
Done to ensure data entered is allowable and sensible. Range checks ensure the data entered is between a upper and lower limit. Restricted choice gives the user a list of options to choose from and prevents user typing errors. Length check ensures that the data entered has a restricted number of characters. Presence check ensures the field is not left blank.
Data Dictionary Example:
Key Fields:
Primary Key - The key that uniquely identifies a single record. E.g. ‘student ID’
Foreign Key - Used to link tables together and create a relationship. Essentially the field in one table that is linked to the primary key in another.
Database Queries:
A request for information from a data base. To perform this in SQL, you follow the entityName.attributeName and criteria = “searchCriteria”.
Dog.dog name and criteria = “Olly”
Dog.dog type
Dog.gender
Walker.walker name
Walker.walker phone number
SQL code:
Always end with ;
Select Operations:
Queries and retrieves data from one or more tables in a database
SELECT attributes (* means all)
FROM table_name
WHERE attribute = criteria
ORDER BY attribute ASC/DESC
Examples:
SQL Code | Plain English |
SELECT * FROM Pupils; | This will print out all the information in the database called Pupils. * is a will select everything in your table. |
SELECT * FROM Pupils WHERE House = 'Nevis'; | This will print out only the pupils who are in the guidance house Nevis from the Pupils table. Note there is speech marks around a string value. |
SELECT * FROM Pupils WHERE age = 17; | This will print out only the pupils who are aged 17 years old from the Pupils table. Note there is no speech marks around an integer. |
SELECT name, age FROM Pupils WHERE age >= 12; | This will print out only the attributes name and age from the Pupils table where the age is greater than or equal to 12. |
SELECT * FROM Pupils WHERE age >=16 AND house = ‘Lochiel’; | This will print out only the pupils who are aged greater than or equal to 16 and in guidance house Lochiel from the Pupils table. |
SELECT * FROM Pupils WHERE age >= 12 AND age <= 16; | This will print out all the pupils who are aged between 12 and 16 from the Pupils table. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age ASC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. The results will be sorted by the age attribute in ascending order. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age DESC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. The results will be sorted by the age attribute in descending order. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age DESC, house ASC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. Results are sorted by the age attribute in descending order and house in ascending order. |
Insert Into SQL:
Inserts new record into database.
INSERT INTO table_name
Values (value1, value2, value3, …); [IN ORDER OF ATTRIBUTE]
Example:
INSERT INTO Pupils
VALUES (6, "Jack Lawrie", 18, "Shiel");
UPDATE RECORD:
Updates Recodes in a database
UPDATE table_name
SET attribute1 = value1 etc.
WHERE attribute = criteria
Example:
UPDATE Pupils
SET house = “Ben Nevis”
WHERE house = “Nevis”;
DELETE RECORD:
Deletes a record from database
DELETE_FROM table_name
WHERE attribute = criteria
Example:
DELETE_FROM Pupils
WHERE name = “Bob Jordan”;
What is a database?
A database is an organized collection of information or data, allowing for flexibility in organization, displaying and printing of information.
Why do we use databases?
It doesn’t take up a lot of room, can be easily edited, easier to search and easier to sort.
Database Entities:
An entity is a person, place, thing or concept about which data can be collected about them.
Some examples of database tables might be:
a customer table
an employee
a movies table
a smartphone table
a car table
Attributes:
An attribute describes the facts, details or characteristics of an entity.
ENTITY = CUSTOMER
First name
Surname
Address
Phone number
Date of birth
ENTITY = MOVIES
Movie title
Release date
Director
How much made at box office
Movie length
Attribute Types:
Text - Stores characters or words and can be a combination of text and numbers. A telephone number is technically a text as it starts with a 0
Number - Can store whole numbers (integers) or numbers with decimal places. For example, 1.3 or 131008 are both numbers.
Date - Stores dates (and can be in different formats). Examples include: 13/10/2008.
Time - Stores time of day. Normally in 24-hour format such as 15:07.
Boolean - Stores true or false/yes or no answers.
Attribute Size:
Limits should be applied to limit amount of storage space. For example, a person’s first name should be limited to 20 characters as it is unlikely you will get a bigger name.
Entity Relationship Diagrams:
GREEN = Entities
ORANGE = Attributes
YELLOW = Relationship (One to Many)
Database Relationships (One to Many):
The most common type of relationship between database entities. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, a mother and her children tables have a one-to-many relationship: a mother can have many children. a child can have only one mother.
Example One to Many relationship:
Walker - Walk:
There is only one walker to many walks. A dog walker can do many different walks.
Dog - Walk:
There is one dog to many walks. One dog can go on many different walks.
Owner - Dog:
There is one owner to many dogs. One owner may have different dogs.
Flat File databases:
Useful when storing only a small amount of records but data can be duplicated and increases the chance of human error when typing in the same data a number of times.
Relational Databases:
Within a database, tables are linked to prevent duplication of data. The advantages of using a relational database is that more complex queries can be carried out on multiple tables, it is easier to maintain security and it is easier to change/update data.
Validation:
Done to ensure data entered is allowable and sensible. Range checks ensure the data entered is between a upper and lower limit. Restricted choice gives the user a list of options to choose from and prevents user typing errors. Length check ensures that the data entered has a restricted number of characters. Presence check ensures the field is not left blank.
Data Dictionary Example:
Key Fields:
Primary Key - The key that uniquely identifies a single record. E.g. ‘student ID’
Foreign Key - Used to link tables together and create a relationship. Essentially the field in one table that is linked to the primary key in another.
Database Queries:
A request for information from a data base. To perform this in SQL, you follow the entityName.attributeName and criteria = “searchCriteria”.
Dog.dog name and criteria = “Olly”
Dog.dog type
Dog.gender
Walker.walker name
Walker.walker phone number
SQL code:
Always end with ;
Select Operations:
Queries and retrieves data from one or more tables in a database
SELECT attributes (* means all)
FROM table_name
WHERE attribute = criteria
ORDER BY attribute ASC/DESC
Examples:
SQL Code | Plain English |
SELECT * FROM Pupils; | This will print out all the information in the database called Pupils. * is a will select everything in your table. |
SELECT * FROM Pupils WHERE House = 'Nevis'; | This will print out only the pupils who are in the guidance house Nevis from the Pupils table. Note there is speech marks around a string value. |
SELECT * FROM Pupils WHERE age = 17; | This will print out only the pupils who are aged 17 years old from the Pupils table. Note there is no speech marks around an integer. |
SELECT name, age FROM Pupils WHERE age >= 12; | This will print out only the attributes name and age from the Pupils table where the age is greater than or equal to 12. |
SELECT * FROM Pupils WHERE age >=16 AND house = ‘Lochiel’; | This will print out only the pupils who are aged greater than or equal to 16 and in guidance house Lochiel from the Pupils table. |
SELECT * FROM Pupils WHERE age >= 12 AND age <= 16; | This will print out all the pupils who are aged between 12 and 16 from the Pupils table. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age ASC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. The results will be sorted by the age attribute in ascending order. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age DESC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. The results will be sorted by the age attribute in descending order. |
SELECT name, age, house FROM Pupils WHERE age < 15 ORDER BY age DESC, house ASC; | This will print out the attributes name, age and house from the pupils table that are less than 15 years old. Results are sorted by the age attribute in descending order and house in ascending order. |
Insert Into SQL:
Inserts new record into database.
INSERT INTO table_name
Values (value1, value2, value3, …); [IN ORDER OF ATTRIBUTE]
Example:
INSERT INTO Pupils
VALUES (6, "Jack Lawrie", 18, "Shiel");
UPDATE RECORD:
Updates Recodes in a database
UPDATE table_name
SET attribute1 = value1 etc.
WHERE attribute = criteria
Example:
UPDATE Pupils
SET house = “Ben Nevis”
WHERE house = “Nevis”;
DELETE RECORD:
Deletes a record from database
DELETE_FROM table_name
WHERE attribute = criteria
Example:
DELETE_FROM Pupils
WHERE name = “Bob Jordan”;
