Relational Models – SS2 Book Keeping Lesson Note (Second Term)

A relational database is a collection of data items organised as a set of formally described tables from which data can be accessed or reassembled in many different ways without having to reorganise the database tables. The relational database was invented by E. F. Codd at IBM in 1970.

Behavioural objectives

By the end of the lesson, students should be able to:

• define a relational database and state who invented it;
• describe how data is arranged in tables, rows and columns;
• explain what a user view of a database is;
• list common SQL commands and state what each one does;
• write a simple SELECT statement.

How is data arranged in a relational database?

The standard user and application program interface to a relational database is the Structured Query Language (SQL).

A relational database is a set of tables containing data fitted into predefined categories. Each table (which is sometimes called a relation) contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.

For example, a typical business order entry database would include a table that describes a customer with columns for name, address, phone number, and so forth. Another table would describe an order: product, customer, date, sales price, and so forth.

What is a view of a database?

A user of the database could obtain a view of the database that fits the user’s needs. For example, a branch office manager might like a view or report on all customers that had bought products after a certain date. A financial services manager in the same company could, from the same tables, obtain a report on accounts that needed to be paid.

Creating and modifying relations using SQL

What are SQL statements?

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the “Customers” table:

Example

SELECT * FROM Customers;

What are the most important SQL commands?

1. SELECT – extracts data from a database
2. UPDATE – updates data in a database
3. DELETE – deletes data from a database
4. INSERT INTO – inserts new data into a database
5. CREATE DATABASE – creates a new database
6. ALTER DATABASE – modifies a database
7. CREATE TABLE – creates a new table
8. ALTER TABLE – modifies a table
9. DROP TABLE – deletes a table
10. CREATE INDEX – creates an index (search key)
11. DROP INDEX – deletes an index

What is the SQL SELECT statement?

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result set.

SQL SELECT syntax

SELECT column_name,column_name
FROM table_name;

and

SELECT * FROM table_name;

Evaluation

1. Define a relational database.
2. Who invented the relational database, and in what year?
3. What does each row of a table contain, and what do the columns define?
4. State the purpose of the SELECT command.
5. Write an SQL statement that selects all the records in the “Customers” table.

Assignment

1. List four SQL commands and state what each one does.
2. Explain, with one example, what a view of a database is.
3. Write the SQL syntax for selecting all records from a table called table_name.