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 ways without reorganising the tables. It was invented by E. F. Codd at IBM in 1970.

Behavioural objectives

1. Define a relational database and state who invented it.
2. Describe how data is arranged in tables, rows and columns.
3. Explain what a user view of a database is.
4. List common SQL commands and state what each one does.
5. Write a simple SELECT statement.

How is data arranged in a relational database?

Data in a relational database is arranged in tables, and each table is made up of columns and rows.

• Table (relation): a set of data on one subject, such as customers or orders.
• Column (field): one predefined category of data, such as name, address or phone number.
• Row (record): one unique instance of data for the categories set by the columns.

The table below is an example of a Customers table.

• Columns: CustomerID, Name, City and Phone.
• Rows: each customer is one row.

A business order entry database would have a table for customers (name, address, phone number). Another table would describe orders (product, customer, date, sales price).

How are tables linked in a relational database?

Tables are linked by a common column called a key, so data in one table can be matched with data in another.

• Primary key: a column whose value is unique for each row, such as CustomerID.
• Foreign key: a column in one table that holds the primary key of another table, such as CustomerID in the Orders table.
• Relationship: the link between two tables, for example one customer can have many orders.

What is a view of a database?

A view is a report or selection of data from the tables that fits the needs of one user.

• Branch office manager: may want a view of all customers who bought products after a certain date.
• Financial services manager: may want, from the same tables, a report on accounts that need to be paid.
• Key point: different users get different views from the same tables.

What is SQL?

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

• Most actions on a database are done with SQL statements.
• Each statement begins with a command word such as SELECT.
• A statement ends with a semicolon (;).

What are the most important SQL commands?

The most important SQL commands extract, change and set up data and tables in a database.

Commands for working with data

Commands for working with the structure

What is the SQL SELECT statement?

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

SELECT syntax

• Chosen columns: SELECT column_name, column_name FROM table_name;
• All columns: SELECT * FROM table_name;

Examples of SELECT statements

• All records in Customers: SELECT * FROM Customers;
• Two columns only: SELECT Name, Phone FROM Customers;
• Customers in one city: SELECT Name, Phone FROM Customers WHERE City = ‘Lagos’;

How do you write a SELECT statement?

1. Write the word SELECT.
2. List the columns you want, separated by commas, or write * for all columns.
3. Write FROM and then the name of the table.
4. Add WHERE and a condition if you want only some rows.
5. End the statement with a semicolon.

What are the advantages of a relational database?

• Flexible access: data can be reassembled in many ways without changing the tables.
• Less repetition: customer details are stored once and linked to many orders.
• Different views: each user can get a report that suits the job.
• Standard language: SQL is used to work with the data.

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. Then explain, with one example, what a view of a database is, and write the SQL syntax for selecting all records from a table called table_name.