Databases & Database Driven Websites Notes

Information Systems Design & Development:Structures and links

ContentsDatabases Recap

Questions

Fields & Their Properties Questions

Data Dictionaries Questions

Entity Relationships Questions

Database User Interfaces Questions

SQL & Queries Questions

Database Driven Websites Questions

You have already learned much about databases at National 5 level.

Recall:A database is an organised collection of information

Databases are a very powerful way of storing information and are in widespread use. Computer databases give the user a great deal of power in terms of storing, accessing, manipulating and viewing information

Databases Recap

Advantages of Computer DatabasesComputer databases are common because they hold a number of advantages over paper databases:

Computer databases are physically smaller.

Easier to insert, amend, delete, search and sort the information

Information can be easily visualised in different ways

Database StructureRecall that all databases will be made up of the following elements

A field is a single element of information about one thing – date of birth is an example of a field

Fields have a data type that describes what kind of information they hold: text, numbers, date/time, graphics, calculated, hyperlinks and boolean

A record is a collection of fields about one person or object – all the information stored about you (first name, last name, date of birth, home phone number etc.) is an example of a record

A file is the collection of all the records in a database – your record, plus the record of your classmates and all the other pupils in the school, as well as information about the classes you take, make up the file of the school database

All the records containing the same fields in a database are stored in a table.

Flat file databases only contain one table of records

Flat file databases result in data duplication – the same information is repeated more than once within the database.

Data duplication is inefficient as memory is wasted in storing any repetitions of information.

There are also problems in updating the database – every instance of the duplicated data must be updated separately, increasing the risk of errors occurring

A flat table with data duplication

A relational database uses multiple tables. Tables in a relational database are linked to each other using foreign keys

Relational databases solve the problem of database duplication. Flat file tables are split into multiple tables so that information is only included once

Two tables linked by publisher code

Questions – Databases RecapAnswer questions in full sentences!1. What is a database? Give an example of where a

database might be used.2. Describe in detail why computer databases are better

than paper databases3. Describe the meaning of the following database terms:

a) Fieldb) Recordc) Filed) Table

4. What is meant by a flat file database5. Explain what data duplication is and why it causes

problems for a database6. What is a relational database?7. How do relational databases prevent data duplication?

Fields & Their PropertiesRecall, a field is a single element of information about one thing

When designing a database, fields have a number of properties to describe how the information will be stored.

The data type of a field describes what sort of information it will store. This affects how the information is encoded in binary on the computer

Database data types include: Text Numbers Date & time Calculated fields Graphics Hyperlinks Boolean

Different database will describe these type differently

Primary KeysThe primary key field in a table is a unique identifier for records in that table.

The value stored in a primary key field in a record must be different from the values stored in the other records

When choosing a primary key field, you must take care to ensure that duplicated values can’t occur. Name is not a good primary key field as sometimes people have the same name. Instead automatically generated number IDs are often used

Every table needs a primary key

Compound Primary KeysSometimes it is impossible to identify a field in a database table that will contain a unique value. This is often the case in linking tables used to create many to many relationships. Instead, multiple fields are used

A compound primary key is made up of more than one field. Taken together, the values in the field that make up the compound primary key are unique within their table

Foreign KeysA foreign key is a field in a table that uniquely identifies a record in a different table

Not every table requires a foreign key. Some tables will have multiple foreign keys linking to different tables. A foreign key is usually required at the many end of a one to many relationship

Foreign keys are usually the primary key of another table.

Stars In.Star ID is a foreign key to the Stars table

ValidationValidation is when a database forces the user to enter data that meets a certain set of rules. If the data does not meet the rules –is invalid – the database rejects it

There are a number of different types of validation check:

Presence check – ensures something has been entered

Unique check – ensures the value entered is different from every other instance of the field in the table.

Length check – restricts the number of characters that can be entered

Range check – ensures the value entered falls within a numerical range

Restricted choice – give the users a limited set of choices to pick from to prevent errors. Commonly used with look up tables for foreign keys

Questions – Fields & Their PropertiesAnswer questions in full sentences!

1. What is a primary key and why are they needed?

2. Which two types of validation have to be applied to a primary key field and why?

3. What is meant by a compound primary key?

4. Why might a compound primary key be needed?

5. What is a foreign key?

6. What fields are likely to be used as foreign keys?

7. What is range check validation? Give an example of how a range check might be used as part of your answer.

8. List and describe the different database data types.

Database Design - TablesOne part of designing a database is deciding what each individual table will be like.

For each table, the design has to set out the following:

Field Names The data types of each field The validation applied to each field

Other limitations such as the size of the field and similar Whether the field is a primary key field, a foreign key field,

or even both

This information must be set out before the table can be created.

Data DictionariesA data dictionary is a design document that sets out all the information required to create a table in a database

Data dictionaries don’t set out the relationships between each table. Although they do state which fields are foreign keys, this isn’t enough to define relationships.

You need a relationship diagram as well as data dictionaries in order to fully design a database

Here is an example data dictionary for a table called customers:

Every field must have a name and a data type.

Not all fields need validation.

Other limitations such as field size should also be listed here.

Every table needs a primary key.

Not every table needs a foreign key.

Table name

Questions – Data DictionariesAnswer questions in full sentences!1. What things need to be included in the design for a database table?2. What is a data dictionary?3. Why is a data dictionary not enough to design a table by itself?4. When designing a database, which document is likely to be created

first – the data dictionaries or the relationship diagram?5. Create data dictionaries for the following database tables based on

the shown record. Remember to identify appropriate validation and limitations on field based on the nature of the database

Use the table below to help you complete each data dictionary.

Field Name

Data Type Validation Primary / Foreign Key

a) School pupils database table

b) Caravan Parks Table

c) Flights Table

RelationshipsIn a relational database, the connections between the tables are relationships.

Relationships work by matching fields in one table to fields in another. These fields are usually key fields. The most common relationships link a foreign key in one table to the primary key of another.

Relationships all have a type.

These relationships show parts of a compound primary key being related to other primary keys

One to One RelationshipIn a one to one relationship, the rows in one table can have only one matching row in the other table

One to one relationships are uncommon. If two tables have a one to one relationship then they could be incorporated into a single table.

There are some reasons for using a one to one relationship

Dividing a table so different access rights can be applied to each part

Dividing a large table into more readable chunks

In our relationship diagrams, one to one relationships will be denoted by a single line

Husband Wife

One to Many RelationshipIn a one to many relationship, a row in the one of the tables can have multiple matching rows in the other table. However, each row in the other table can only match to one row of the first table

One to many relationships are the most common type or relationship. They are usually implemented by including the primary key field of the table at the ‘one’ end of the relationship as a foreign key in the table at the ‘many’ end of the relationship.

In our relationship diagrams, a one to many relationship will be denoted with a line with a crow’s foot at the many end of the relationship

Mother Children

Many to Many RelationshipsIn a many to many to relationship, the rows in both tables in the relationship can link to multiple rows in the other table.

In our relationship diagrams, many to many relationships are denoted by lines with crow’s feet at both ends of the line

However, many to many relationships are awkward to implement. Instead they are split up into more tables that make use of one to many relationships:

The intermediate table usually uses a compound primary key

Movies Stars

Movies Roles Stars

Relationship DiagramsA relationship diagram is a drawn representation of all the tables in a database, showing the type of relationships between them:

Each block in the diagram corresponds to a table in the database. The blocks are sometimes also referred to as entities

The lines between blocks represent the relationships between each table. Whether or not the lines use single lines or crows’ feet shows the type of relationships used. The type of the relationship is sometimes also referred to as the cardinality of the relationship

Although a relationship diagram shows which tables are related, and where foreign keys will be needed, they do not show the contents of each table. For that you need to make use of a data dictionary

Questions – Relationship DiagramsAnswer questions in full sentences!1. What is meant by a relationship in a database?2. What is a one to one relationship? Why are they not

common?3. Give one reason why one to one relationship might be

used?4. Explain what a one to many relationship is.5. Explain what a many to many relationship is. Why are

they not used in databases?6. What is usually done in a database instead of

implementing a many to many relationships?7. Correct the following many to many relationship diagram

for a holiday home rentals database. Draw the corrected diagram in your jotterHoliday

HomesCustomers

8. The tables below show the fields for a database of football teams. Using the tables, draw a relationship diagram for the databaseFans

Fan ID *

Fan Name

Date of birth

Address

Team ID

Teams

Team ID*

Team Name

Badge

Stadium

Trophies

Trophy ID*

Trophy Name

Winners

Team ID *

Trophy ID *

Year

Players

Player ID *

Player Name

Date of birth

Position

Played For

Player ID *

Team ID *

Start Date

End Date

Appearances

Database User InterfacesRecall:The user interface is the part of the computer which the user interacts with in order to control the computer.

Databases provide very simple basic user interfaces that let the user view and edit the contents of tables. These are of use to expert users who understand in detail how the database works.

Some databases will allow expert users to modify and create user interfaces. This can be done to make the database friendlier for novice users

FormsA database form allows data to be entered into a database in a more user friendly way.

Forms usually present information for a single record in a database, with each field clearly labelled. The layout of the form can be altered and instructions can even be added to make it simpler for novice users

Data entry via table

The same data entry via form

Restricted Entry & LookupsAnother way to improve the user friendliness of data entry in a database to restrict what the user can enter. Typically the user is offered choices from a menu or radio buttons.

A Lookup Table is a list of valid data that can be entered into a database field. Lookup tables are set up to generate automatically using the contents of another table in the database

Lookup tables are usually used for entering foreign key fields. The user will only be able to choose from the primary key values of the corresponding table

In this lookup table, the user sees the names of the different customers in the database

The database will store the information as the unique number representing each customer

ReportsA database report is a visualisation of a set of information from a database

Reports are different from forms in a number of ways

Reports are primarily used for output rather than input Reports are generated from queries as well as from

tables. Reports don’t show all of the information in a table – they just show some of it

The same information as a query and as a report

Like forms, the layout of a report can be altered in order to make it more readable. Ways to make a report more readable include:

Removing unneeded information Adding explanatory labels

Additionally, reports can make use of grouping and calculations to provide summaries of the information they contain

Questions – Database User InterfacesAnswer questions in full sentences!1. What is a database form?2. Explain why novice users benefit from the inclusion of

forms in a database3. Describe two ways in which using a form can reduce

the likelihood of data entry error in a database?4. How do look up tables restrict data entry in a

database? Where do the values in a look up table come from

5. Explain how numeric value primary keys fields can appear as text in a lookup table?

6. What is a database report?7. Describe two differences between a report and a form.8. List two ways in which a report can be made more

readable.

SQL & QueriesTwo operations to query the data in a database can be carried out – searching and sorting

A simple search finds all the records in a database that match a criterion set on one field

Comparison operators such as =, > and < are used to match the value in a given field for each record. For example:

Cost < 0.10 Matches all records where the value in the field Cost is less than 10

A complex search matches multiple criteria. The logical operators AND (for both) and OR (for either or) are used to connect criteria together.

A database sort puts the records, or results of a search, into order.

A field can be specified as the basis for sorting. The sort can either be ascending (lowest value first) or descending (highest value first). For example:

Sort by Name in ascending order

The records will be displayed ordered by the Name field, starting at A and ending at Z.

A secondary sort field can be specified. When the values in the primary sort field are the same, the secondary field will be used to differentiate between the records

Searches and Sorts can be created in SQL. Structured Query Language is a scripting language that can be used to manipulate the information in databases

Example SQL Query

SELECT Name, DATEDIFF("yyyy",[Date of Birth],NOW()) AS AgeFROM PeopleWHERE Age > 65ORDER BY Age DESC, Name ASC;

ORDER BY sorts the query, first by age descending and then by name ascending when ages are the same

WHERE searches the records. In this case only people older than 65 will be found

FROM identifies which tables in the database are to be used

SQL queries are always ended with the ; symbol.

SELECT says which fields are going to be used in the query

AS allows calculated fields to be created. In this case the query works out someone’s age from their date of birth and calls it Age

SQL FunctionsSQL contains a number of functions that can perform simple calculations across all the records matched in a query:

AVG() Finds the average value of a numeric fieldeg: SELECT AVG(Height) FROM People;

SUM() Adds together all the values from a numeric fieldeg: SELECT SUM(Quantity) FROM Products;

COUNT() Returns the number of records with a non-null value in the specified fieldeg: SELECT COUNT(Feedback) FROM Orders

MAX() Returns the largest value from a fieldeg: SELECT MAX(Height) FROM People;

GroupingNormally these SQL functions are applied to a table, or matches from a table as a whole

The SQL GROUP BY keyword splits the table into sections based on the values in the field that the query is being grouped by

For example:

SELECT Type, COUNT(*) AS [Products Available]FROM ProductsGROUP BY Type;

This query shows the number of each type of product that is available:

Joins and Multiple Table QueriesQueries can include more than one table. When this happens, the query must join the two tables together.

Tables are usually joined on primary and foreign keys. Some databases do this automatically if a relationship has been established

When joining tables together, the database will look for records where the values stored in the foreign key field from one table matches the values stored in the corresponding primary key field in the other table

Another SQL Query

SELECT [Order ID], [Orders Contents].[Product Code],Quantity, Cost, Quantity*Cost AS SubtotalFROM Products INNER JOIN [Orders Contents] ON Products.[Product Code] = [Orders Contents].[Product Code];

INNER JOIN connects two tables together on the specified fields.

Some databases can generate this line of SQL for you automatically

When using SELECT with two tables, you may need to specify both the table and the field to avoid ambiguity

Questions – SQL and Queries

Answer questions in full sentences!1. What is meant by a search? Explain the

difference between a simple and complex search

2. Explain the purpose of a secondary sort field3. List 6 SQL functions and give a short

description of what they do4. Which SQL keyword is used to pick what fields

appear in a query?5. Which SQL keyword is used to search records?6. Which SQL keyword is used to sort records?7. Explain how the GROUP BY keyword works

8. Use the database below to create the following SQL Queries. You may leave the FROM line blank instead of writing a join

Teams

Team ID*

Team Name

Badge

Stadium

Players

Player ID *

Player Name

Date of birth

Position

Appearances

International Caps

Plays for

a) Write an SQL query to show all the names of all the players whose position is “defender”

b) Write an SQL query to list all the team names in alphabetical order

c) Write an SQL query to work out the total number of international caps won

d) Write an SQL query to work out average number of appearances for players whose position is “striker”

e) Write an SQL query to list the names of all the players who play for the team “Motherwell”. You may leave the FROM line blank instead of writing the join

Database Driven WebsitesThe information displayed on a database driven website is generated dynamically by using information stored in a database.

A common use of database driven websites is for online shopping, banking and other similar e-commerce sites. Information like the products being sold, the details of customers and what they have ordered is all stored in the database that drives the website.

The user of a database driven website can update and add information to the database directly without any additional human intervention

Information about the products sold on Amazon is stored in a database that the user can search and place orders with

How Database Driven Websites workSQL queries are used to access the database. The queries are embedded in PHP or a similar server side scripting language.

The database executes the SQL queries and returns the results to the PHP script. The PHP script dynamically generates HTML that includes the information received from the database.

SQL Query

PHP Script

Database executes SQL andreturns results to PHP

PHP generates HTML that includes query results and sends it to browser

Updating Databases Via WebsiteOnline shopping websites allow users to place orders and get goods sent to them. In order to do this the information stored on the database has to be updated.

HTML uses <FORM> tags to allow for user input. PHP is used to process the input from the form and turn it into an SQL query which is sent to the database

It follows that SQL can modify the contents of a database as well as query it

SQL Query

PHP Script

User completes and submitsform using browser

PHP script uses inputs to createSQL. SQL executed by database

Inserting & AmendingThe INSERT keyword adds a new record to a database:

INSERT INTO Pet ( [Pet Name], Animal )VALUES ("Rover", "Dog");

The UPDATE keyword allows existing values to be changed. By including a WHERE clause, the query can be set to only affect specific records

UPDATE Pet SET Cost = Cost *1.1WHERE Cost>= 50;

Questions – Database Driven WebsitesAnswer questions in full sentences!1. What is a database driven website? Give an example

of a database driven website that you are familiar with and describe at least two tables that its database will include.

2. Explain how a scripting language such as PHP is used to include information from a database in a website

3. What SQL keyword allows new records to be added to a database?

4. What SQL keyword allows existing records in a database to be updated? Why is it important that such queries include a WHERE clause?

5. Explain how PHP is used to allow a website user to update information in a database.