Overview of Key Relational and SQL Concepts (Part 2 of 3)

Overview of Key Relational and SQL Concepts (Part 2 of 3)

Why Relational Databases?

Relational model first proposed in 1970 by Dr E F (Ted) Codd in the paper ‘A relational model of data for large shared data banks’.

Purpose

• Achieve program/data independence

• Treat data in a disciplined way

- Apply rigour of mathematics

- Use set theory

• Improve programmer productivity

Implementation

At first considered impractical, but in the late 1970s prototype System R developed by IBM and during 1980s commercial products began to appear, in particular Oracle from Oracle Corporation.

What is a Relational Database?

A relational database is made up of relations (tables) in which data are stored.

A relation (table) is a 2-dimensional structure made up of attributes (columns) and tuples (rows).

Relation

A relation is a table that obeys the following rules:

• There are no duplicate rows in the table.

• The order of the rows is immaterial.

• The order of the columns is immaterial.

• Each attribute value is atomic, ie each cell can contain one and only one data value.

Example of a Table (Relation)

ANIMAL

ANAME / AFAMILY / WEIGHT
Candice / Camel / 1800
Zona / Zebra / 900
Sam / Snake / 5
Elmer / Elephant / 5000
Leonard / Lion / 1200

Relational Database Terminology

Relationa table with rows and columns

Tuplea row of a relation

Attributea named column of a relation

Primary keya unique identifier for each row in a relation

Domainthe set of allowable values for a column

Degreethe number of columns in a relation

Cardinalitythe number of rows in a relation

ANIMAL

ANAME / AFAMILY / WEIGHT
Candice / Camel / 1800
Zona / Zebra / 900
Sam / Snake / 5
Elmer / Elephant / 5000
Leonard / Lion / 1200

Primary and Foreign Keys

A primary key is a unique identifier for each row in a table.

It may consist of one or more columns.

ANIMAL

ANO / ANAME / AFAMILY / WEIGHT
CA1 / Candice / Camel / 1800
ZE4 / Zona / Zebra / 900
SN1 / Sam / Snake / 5
EL3 / Elmer / Elephant / 5000
LI2 / Leonard / Lion / 1200

No part of the primary key may have a null value. This is known as the entity integrity rule.

Each table contains data about one entity.

ANIMAL-FOOD

ANO / FOOD
CA1 / Hay
CA1 / Buns
ZE4 / Brush
SN1 / Mice
SN1 / People
EL3 / Leaves
LI2 / People
LI2 / Meat

You may need to combine 2 or more tables to find out a particular piece of information, eg you may want a report on the animal name, family and food.

You relate the data in one table to the data in another through foreign keys.

A foreign key is a column or columns in one table which reference(s) a primary key column or columns in another table.

Values in a foreign key must match an existing value in the primary key or be NULL. This is known as the referential integrity rule.

ANIMAL ANIMAL-FOOD

ANO / ANAME / AFAMILY / WEIGHT / ANO / FOOD
CA1 / Candice / Camel / 1800 / CA1 / Hay
ZE4 / Zona / Zebra / 900 / CA1 / Buns
SN1 / Sam / Snake / 5 / ZE4 / Brush
EL3 / Elmer / Elephant / 5000 / SN1 / Mice
LI2 / Leonard / Lion / 1200 / SN1 / People
EL3 / Leaves
LI2 / People
LI2 / Meat

ANO in the ANIMAL-FOOD table is part of the primary key and also a foreign key.

Database Languages

SQL, often known as a query language, is a combined DDL, DML and DCL used with relational databases.

• A Data Definition Language (DDL) is used to specify the data in a database.
• A Data Manipulation Language (DML) is used to access the data in a database.
• A Data Control Language (DCL) is used to control access to the data in a database.

Characteristics of SQL

• The DDL statements define database objects, eg databases, tables, views, indexes, users, constraints, user-defined data types:

CREATE, DROP, ALTER

• The DML statements manipulate data:

SELECT, INSERT, DELETE, UPDATE

• The DCL statements control access to data:

GRANT, DENY, REVOKE

Transact-SQL is the SQL Server implementation of SQL.

SQL Server Object Names

Standard Identifiers

• Can contain from one to 128 characters, including letters, symbols (_ @ or #) and numbers.

• No embedded spaces are allowed.

• The first character must be alphabetic.

• A name beginning with @ denotes a local variable or parameter.

• A name beginning with # denotes a temporary table or procedure.

• A name beginning with ## denotes a global temporary object.

NB:Names for temporary objects shouldn’t exceed 116 characters including # or ## as SQL Server gives them an internal numeric suffix.

SQL Server Object Names (contd)

Delimited Identifiers

Do not comply with the rules for standard identifiers and must, therefore, always be delimited.

You can use delimited identifiers when:

• Names contain embedded spaces.

• Reserved words are used for object names or portions of object names.

You must enclose delimited identifiers in square brackets or quotation marks when you use them in Transact-SQL statements, eg:

SELECT * FROM [Blanks In Table Name]

SELECT * FROM “Blanks In Table Name”

NB:You can always use bracketed delimiters but can only use quotation marks if the SET QUOTED_IDENTIFIER option is on.

SQL – DML Statements

The SELECT Statement

A query, ie a request for information to be retrieved from the database.

The SELECT statement is made up of a number of clauses, with a minimum of two:

SELECT the columns to retrieve

FROM the table(s) which contain those columns

• The clauses may be entered on the same line or on separate lines.

• You can enter statements in UPPER CASE, lower case or a MiXtUrE of the two. However, reserved words are, by convention, entered in UPPER CASE.

Selecting All Columns and All Rows

To display all the information from a table called Student:

SELECT * FROM Student;

Selecting Specific Columns and All Rows

To select some of the columns and all the rows:

SELECT Studentno, Surname FROM Student;

The order of column names in a SELECT statement determines the order in which the columns are displayed.

SELECT Surname, Studentno FROM Student;

Selecting Specific Rows

Requires the WHERE clause following the FROM clause:

SELECT the columns to retrieve

FROM the table(s) which contain those columns

WHEREcertain conditions are met

SELECT * FROM Course

WHERE Courseno = 30;

SELECT Courseno, Coursename

FROM Course

WHERE Coursename = 'Computing';

Comparison Operators

OperatorMeaning

=equal to

!= or >not equal to

greater than

>=greater than or equal to

less than

<=less than or equal to

BETWEEN … AND …between two values

IN (list)any of a list of values

LIKEmatch a character pattern

IS NULLis a null value

To negate the last four operators, use the operator NOT, ie NOT BETWEEN, NOT IN, NOT LIKE and IS NOT NULL.

Examples

SELECT * FROM Course

WHERE Coursetype > 'BSc';

SELECT Studentno, Surname FROM Student

WHERE Dob < '01/26/77';

SELECT * FROM Student

WHERE Surname > 'M';

SELECT Tutorno, Job FROM Tutor

WHERE Salary >= 20000 AND Salary <=25000;

SELECT Tutorno, Job FROM Tutor

WHERE Salary BETWEEN 20000 AND 25000;

SELECT Tutorno, Job FROM Tutor

WHERE Salary NOT BETWEEN 20000 AND 25000;

SELECT Courseno, Coursename FROM Course

WHERE Courseno IN (30, 40, 50, 90);

SELECT Courseno, Coursename FROM Course

WHERE Coursename

NOT IN (‘Electronics’, ‘Computing’);

SELECT * FROM Course

WHERE Coursename LIKE 'Comp%';

SELECT Courseno, Studentno

FROM CourseStudent

WHERE Studymode LIKE '_T';

NB:Each _ represents one character. The % represents any number of characters.

SELECT Tutorno, Studentno

FROM TutorStudent

WHERE Studentno NOT LIKE '_ _D%';

Using AND

SELECT Tutorno, Job FROM Tutor

WHERE Salary >= 20000 AND Salary <=25000;

SELECT * FROM CourseStudent

WHERE Courseno = 50 AND Studymode > 'FT';

A SELECT statement with AND only retrieves a row or rows if both parts of the WHERE clause are true.

Using OR

A SELECT statement with OR retrieves a row or rows if either part of the WHERE clause is true.

SELECT * FROM CourseStudent

WHERE Courseno = 50 OR Studymode > 'FT';

SELECT Courseno, Coursename FROM Course

WHERE Courseno = 30 OR Courseno = 40;

Operator Precedence

When AND and OR appear in the same WHERE clause, AND has a higher precedence than OR, ie all the ANDs are performed first, then all the ORs.

SELECT * FROM Tutor

WHERE Job = ‘Lecturer' AND Salary > 26788

OR Job = 'Senior Lecturer' AND Salary < 30058

OR Job = 'Principal Lecturer' AND Salary < 30058;

All the comparison operators have equal precedence, then the order of precedence is:

NOT

AND

OR

Changing Precedence

To override the rules of precedence, place the part of the expression you want evaluated first in parentheses ().

Example

SELECT * FROM Tutor

WHERE Salary > 26788 AND Salary < 30058

AND (Job = 'Lecturer'

OR Job = 'Senior Lecturer'

OR Job = 'Principal Lecturer');

If in doubt, add parentheses to clarify your SELECT statements and ensure the correct results.

Eliminating Duplicate Rows

The default display of a result from a query is all rows, including duplicate rows.

To eliminate duplicate rows, use the DISTINCT keyword in the SELECT clause, eg:

SELECT DISTINCT Coursename

FROM Course;

SELECT DISTINCT Job

FROM Tutor;

SELECT DISTINCT Courseno, Coursename

FROM Course;

Controlling the Order of Displayed Rows

You can control the order in which rows are displayed by adding the ORDER BY clause as the last clause of a SELECT statement.

ORDER BY lets you order rows in ascending order, in descending order and by multiple columns.

SELECT * FROM Course

ORDER BY Coursename;

SELECT Tutorno, Job, Salary FROM Tutor

ORDER BY Salary DESC;

SELECT * FROM Student

ORDER BY Surname, Fname;

SELECT Tutorno, Job, Salary FROM Tutor

WHERE Salary BETWEEN 20000 AND 25000

ORDER BY Job, Salary DESC;

1

Overview of Key Relational and SQL Concepts (Part 2 of 3)