Advanced Database management

(In the ppt file, slides 16-32 and 66-73 are hidden; they will not be

covered)

Spreadsheet vs. Database

Spreadsheet: dealing with numbers and calculations

Database: dealing with information (mostly text) Library search Internet e-business websites Records in schools, hospitals, Revenue Canada, …

Database Design

Databases are stored into Database Management Systems (DBMS)

A database is used to store data that exists in real life

The database design must reflect the organization that exists within the data

To capture the organization of the data we need to generate a model of the world

Models

We need to translate real-world concepts and data into concepts and data that can be stored in a database system

The process of analyzing and structuring the real world information into abstract information that can be implemented is called modeling

The result is a model

Models and reality

The real world information is complete and extremely detailed: we can not include it all

Selecting the details to be included in our model depends on our goals. Example: the SIN may be irrelevant in a database

system used to store and retrieve the marks of students but it certainly is not in a database system used for fiscal information

We will be trying to organize real world information using entities and relations

The Entity Relationship Model

A model of reality will consist of

Entities Attributes of the entities Relationships that exist between the entities Attributes of the relationships

The modeling technique that we will be using is the

Entity – Relationship model (E–R model)

Entities

An entity is a person, place, thing or event Within the university environment, we could

identify the following entities Student Course Faculty Member Program

The entities are the types of objects that we will be dealing with

Entities are implemented as tables

Attributes

Entities have attributes which describe properties of the entity

For example, the “Student” entity could have the following attributes Student number Name Address Phone number Date of birth

The attributes of an entity are the columns of its corresponding table

Instances

An instance is the database representation of a real world object

An instance is directly related to an entity (object – object type)

The database will contain instances of the entities The attributes of an entity describe the details we

will know about the entity Instances are rows in the table corresponding to its

entity

COURSE_ID NAME NUMBER TERM LOCATION

6701901 COMP SCI 031 A MC301

6701402 COMP SCI 031 B NS1

6701203 COMP SCI 032 A SCC20

6701604 COMP SCI 032 B MS12

ST_NUM NAME ADDRESS

250078563 Rhonda Odanski 65-927 Richmond St.

250004423 Peter Chen 1848 Downes Crt.

250016788 Susanne Ferber 39 Danielle Cr.

250012745 Rick Mattatall 87 Dillabough St.

The COURSE table

The Student table

Examples

Key Attributes

It is necessary to uniquely identify the instances of entities

For example, people are given SIN, student numbers, employee numbers, etc. so they can be uniquely identified.

An attribute (or attributes) that uniquely identifies an instance of an entity or relationship is called a key attribute.

Relationships

A relationship is an association between two (or more) entities

For example, Person works for Company Student takes Course Faculty Member is chair of Department

Relationships are implemented as tables connecting the tables corresponding to the entities involved

Connecting tables

A relationship table connects entity tables by placing key information of instances of objects that are in the said relationship in the same row.

Example: Student is uniquely identified by their student number. Let's assume that courses are uniquely identified by a key named COURSE_ID

The relationship is Student takes Course.

Connecting tables (continued)

The relationship is implemented as a table with two columns: ST_NUM and COURSE_ID.

A student takes a course if and only if a record with his/hers student number and the ID of the course is present in the relationship table.

COURSE_ID NAME NUMBER TERM LOCATION

6701901 COMP SCI 031 A MC301

6701402 COMP SCI 031 B NS1

6701203 COMP SCI 032 A SCC20

6701604 COMP SCI 032 B MS12

ST_NUM NAME ADDRESS

250078563 Rhonda Odanski 65-927 Richmond St.

250004423 Peter Chen 1848 Downes Crt.

250016788 Susanne Ferber 39 Danielle Cr.

250012745 Rick Mattatall 87 Dillabough St.

ST_NUM COURSE_ID

250016788 6701402

250004423 6701901

250004423 6701604

250012745 6701901

250078563 6701402

250016788 6701203

The COURSE table

The Student table

The TAKES table

Example

Queries

A query is a request for data that matches a set of conditions

Real-life querying examples: Library book search Movie search and filtering (www.imdb.com) Search engines

We need to specify where the data is located and what conditions it must satisfy

Query Languages

Used to communicate with the DBMS People and applications software need a way to

communicate with the DBMS

Examples SQL (Structured Query Language) QBE (Query By Example) SQUARE QUEL

Common query languages

SQL and QBE are the most common ways to communicate with DBMS’s

SQL is the internationally agreed upon standard language

QBE is implemented as a GUI and therefore it differs from one implementation to another

Structured Query Language

In 1985 the preliminary Structured Query Language (SQL) standard was published

SQL is an English-like language Uses words like SELECT, INSERT, DELETE,

UPDATE and GRANT to pass instructions to the DBMS

Querying in SQL

A request for data We need to specify

where the data is located: the tables that contain the information.

what conditions our data must satisfy: a set of conditions on the rows of the table involved

In other words, we must specify what entities we are interested in and what criteria their attributes must fulfill

SQL Query Format

The general format of an SQL query:

SELECT

<LIST OF COLUMNS>

FROM

<TABLE_SPECIFICATION>

WHERE

<CONDITIONS>

Notice the use of SQL keywords: SELECT, FROM, and WHERE.

Query return

A query returns a table with the columns specified in the <LIST OF COLUMNS>.

Sometimes, we are looking for a single value – a special case of table with a single row and a single column.

SQL Format Details

The <LIST OF COLUMNS> can be replaced by * meaning that we want to select all available columns from the <TABLE_SPECIFICATION>

The clause WHERE <CONDITIONS> need not be present

The clauses SELECT <LIST OF COLUMNS> and FROM <TABLE_SPECIFICATION> must be present

Single table queries

Assume that we have a student table named STUDENT with the following structure:

The SQL query used to return Susanne Ferber’s student number is:

SELECT ST_NUM FROM STUDENT WHERE NAME = "Susanne Ferber"

ST_NUM NAME ADDRESS

Same example in Access Design View

SELECT ST_NUM FROM STUDENT WHERE NAME = "Susanne Ferber"

Single table example

What is Rick Mattatall’s address?

SELECT ADDRESS FROM STUDENT WHERE NAME = "Rick Mattatall"

Another single table example

Assume that the information about courses in stored in the table COURSE

Find all of the “B” term courses (return the name, the number and the term of the course)

SELECT NAME, NUMBER, TERM FROM COURSE WHERE TERM = "B"

The columns in the list are separated by commas

COURSE_ID NAME NUMBER TERM LOCATION

Same example in Access Design View

SELECT NAME, NUMBER, TERM FROM COURSE WHERE TERM = "B"

Multiple table queries

It is possible that the information we want to retrieve spans multiple tables

Our goal is to create a temporary table, for the purposes of our query, which contains all the information we need

The procedure is to join tables together making use of the relationship table.

Multiple table queries (continued)

Assume that we want to select the id of the courses taken by Peter Chen and we have the tables: STUDENT TAKES

The table TAKES is a relationship table and contains a row with a student number and a course id if the student takes the course.

ST_NUM NAME ADDRESS

ST_NUM COURSE_ID

Multiple table queries (continued)

The specification of our temporary table will be:

STUDENT INNER JOIN TAKES ON

STUDENT.ST_NUM = TAKES.ST_NUM

A row in this table will be a row from the table STUDENT joined with a row from the table TAKES such that the ST_NUM is the same in both rows

Since the column ST_NUM appears in both tables, we need to specify the table that it belongs to (STUDENT.ST_NUM, TAKES.ST_NUM)

Multiple table queries (continued)

Our final query would be:SELECT COURSE_ID FROMSTUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUMWHERE NAME = "Peter Chen"

Joins in Access In order to create a join, drag one of the columns involved

in the join over the other.

This is the join

ST_NUM NAME

250078563 Rhonda Odanski

250004423 Peter Chen

250016788 Susanne Ferber

ST_NUM COURSE_ID

250016788 6701402

250004423 6701901

250004423 6701604

250078563 6701402

250016788 6701203

The STUDENT table

The TAKES tableJoin

Example

STUDENT.ST_NUM NAME TAKES.ST_NUM COURSE_ID

250078563 Rhonda Odanski 250078563 6701402

250004423 Peter Chen 250004423 6701901

250004423 Peter Chen 250004423 6701604

250016788 Susanne Ferber 250016788 6701402

250016788 Susanne Ferber 250016788 6701203

The tableSTUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM

A three table join

Furthermore, assume that we want the name, number and term of the courses taken by Peter Chen.

We already have a table specification with the student name the course id, we only need to join it with the table COURSES:

(STUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM) INNER JOIN COURSES ON TAKES.COURSE_ID = COURSE.COURSE_ID

A three table join (2)

The final query is:SELECT COURSE.NAME, NUMBER, TERM

FROM(STUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM) INNER JOIN COURSE ON TAKES.COURSE_ID = COURSE.COURSE_ID

WHERE STUDENT.NAME = "Peter Chen"

Notice that the both the tables COURSE and STUDENT have a column NAME so must distinguish between them by adding the table name

Three table join in AccessSELECT COURSE.NAME, NUMBER, TERM FROM

(STUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM) INNER JOIN COURSE ON TAKES.COURSE_ID = COURSE.COURSE_ID WHERE STUDENT.NAME = "Peter Chen"

Aggregate functions

An aggregate function can be applied to the values returned by an SQL query.

Some built-in aggregate functions AVG – the average of the values in an attribute SUM – total of the values MIN – the smallest value MAX – the largest value COUNT – the number of values

Aggregate functions example

The statementSELECT COUNT(COURSE_ID)

FROM STUDENT INNER JOIN TAKES ON

STUDENT.ST_NUM = TAKES.ST_NUM

WHERE NAME = "Peter Chen"

would return the number of courses taken by Peter

Chen.

Aggregate functions in Access

SELECT COUNT(COURSE_ID)

FROMSTUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM

WHERE NAME = "Peter Chen"

Aggregate functions examples (2)

More uses for the aggregate functions: AVG(MARK) MIN(AGE) MAX(SALARY) SUM(SALES) COUNT(EMPLOYEE)

can be used in select queries.

Renaming columns

Sometimes, it is useful to rename the columns in the return table of the query – especially when multiple tables have columns with the same name.SELECT STUDENT.NAME AS ST_NAME, COURSE.NAME AS C_NAMEFROM (STUDENT INNER JOIN TAKES ON STUDENT.ST_NUM = TAKES.ST_NUM) INNER JOIN COURSES ON TAKES.COURSE_ID = COURSE.COURSE_ID

The above query returns a table with 2 columns named ST_NAME and C_NAME.

Conditions

The <CONDITIONS> describe the list of conditions that must be true for the DBMS to retrieve data

The list of conditions is a list of Boolean expressions connected using logical operators: AND, OR, NOT

The Boolean expressions are built using the standard comparison operators (<, >, <=, >=, =, <>) or one of keywords IN, LIKE or BETWEEN.

Example Conditions

NAME = "Rhonda" INITIAL < "M" – letters “A” to “L” INITIAL >= "M" – letters “M” to “Z” VALUE = 100 VALUE <= 200 VALUE > 0

Where NAME, INITIAL, and VALUE are columnnames from the tables from which we are selecting

Example Conditions

START_DATE >= #1/1/2001# - date is on or after Jan. 1/2001

START_DATE BETWEEN #1/1/2001# and #12/31/2001# - date is in 2001

START_DATE = #2/*/2001# - date is in Feb. 2001

START_DATE IS Null – there is no value for the attribute

Example Conditions

NOT(VALUE = 20) – all instances where the attribute does not equal 20

COUNTRY="France" OR COUNTRY="Spain" – value is “France” or “Spain”

TEXT LIKE "Market*" - any value that has Market as its first six letters (* is a wildcard that in combination with the keyword LIKE matches any number of letters)

PROVINCE IN ("Ontario", "Quebec") – only instances with the value Ontario or Quebec

Ordering the return tables

The return table of a query can be ordered using the keyword ORDER BY followed by a column name and one of the keywords ASC or DESC

Example:SELECT NAME FROM STUDENT

ORDER BY NAME ASC

returns the list of students in alphabetical order

Ordering in Access

SELECT NAME FROM STUDENT

ORDER BY NAME ASC