Transcript Lecture 1

IS6126
Databases for Management
Information Systems
Lecture 1: Introduction to IS6126
Rob Gleasure
[email protected]
robgleasure.com
IS6126




Lecture times
 15.00-17.00, Wednesday (WGB G18)
Lab times
 13.00-14.00, Tuesday (ORB 1.113)
 16.00-17.00, Thursday (ORB 1.113)
Contact me at
 Ext 2503
 Room 2.112
 [email protected]
Website for this course
 robgleasure.com
IS6126

Module content and learning outcomes
 Using databases (i.e. SQL)
 Semistructured data (i.e. XML)
 Unstructured data
IS6126

Course Assessment
 Continuous assessment: 50 marks
 In-class exam* – 20 marks
 Group report* – 30 marks
 Exam: 50 marks
* To be confirmed
Database Management Systems

A Database Management System (DBMS) is a software package
designed to store and manage databases.

This is different from a database file for several reasons
 DBMS makes tables visible and generates reports
 DBMS helps less technical users to use data efficiently
 DBMS protects data from inconsistency and duplication due to
multiple concurrent users
 DBMS handles crash recovery
 DBMS handles security and access control
Database Management Systems

DBMS operates at the physical level
Management
and business
users
Conceptual model
Logical model
Database
administrators

System
analysts
and
designers
Physical model
This is where data handling becomes increasingly technical
Database Management Systems

DBMS are typically managed by a set Database Administrator,
whose responsibilities include
 Defining or refining logical schema in response to requirements
 Granting appropriate access to users
 Monitoring DBMS performance
 Updating/changing DBMS software
Database Management Systems

Other users will include
 Application developers (write programs that need to
access/modify data)
 Sophisticated/specialised users (use DBMS directly to generate
reports and assist business analysis)
 Naïve users (use applications that access/modify data)
Examples of DBMS
Oracle
 Large multi-user DBMS that excels at handling many clients
requesting, accessing, and modifying data
 Popular in large organisations and cloud services due to scalability
 Very effective handling of concurrency, read consistency, parallel
execution, and locking mechanisms
MS Access
 Popular DBMS for small scale projects due to its relatively low cost
and usability
 Imports and exports data to many formats commonly used in
organisations, e.g. Excel, Outlook, SQL Server, Oracle
 Integrates easily with MS tools like VB, C# and .NET
Examples of DBMS
MySQL
 Open source SQL database with free and paid versions
 Flexible and scalable
 Open source means lots of support and the potential for adaptability
MS SQL Server
 Good performance
 XML integration
 Inclusion of try/catch queries
Comparison of DBMS from lab
manuals (out of 5 stars)
Oracle
MS Access
MySQL
MS SQL
Server
2.5
4
5
3
Web friendly
3
3
3
3.5
Reliable
4
2
3
4.5
Complex
3
2
4
4.5
Security
4
3
3
3
Capacity
3.5
2
3
5
Flexibility
3
2
4
4
Technical fit
3
2
4
5
Ease of use
3
4.5
3
4.5
Cost
Accessing a DBMS with SQL


So once we have our data models in place and our DBMS set up,
how do we get started using our data?
 The SQL (pronounced like sequel) query language
SQL (Structured Query Language) was introduced in the 70’s and
has evolved since to add features such as
 Compatibility with object-oriented programming
 Compatibility with XML
 Compatibility with XQuery
Accessing a DBMS with SQL
The Structure of SQL

SQL can be divided into two parts:
 The Data Manipulation Language (DML)
 Used to create, read, update and delete tuples (CRUD
operations)
 Mostly used by application programmers and
sophisticated/specialised users
 The Data Definition Language (DDL)
 Used to define database structure (relation schemas) and data
access control, as well as integrity constraints and views
 Mostly used by database administrator
Executing SQL in a DBMS
Accessing DBMS with SQL

The basic syntax of SQL queries are as follows:
COMMAND
column_name1, column_name2, …
FROM/SET/VALUES
table_name1, table_name2, …
WHERE
column_name comparison_operator value;

All statements in a semi-colon
The SQL Select statement


The SELECT statement is used to retrieve data from a database into
a result table, or result-set
You can use an asterisk (*) instead of a column name if you wish to
select all columns satisfying the criteria
e.g.
SELECT * FROM my_table;
The SQL Select statement

Let’s open up an online example from W3Schools

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
The SQL Select statement

Sometimes non-primary key columns may contain duplicate values you can also use SELECT DISTINCT when you want to avoid
duplicates in your result-set
e.g.
SELECT DISTINCT * FROM my_table;
The SQL Where clause


A number of comparison operators
are possible with the WHERE
clause
Examples


http://www.w3schools.com/sql/trysql.as
p?filename=trysql_select_between_in
http://www.w3schools.com/sql/trysql.as
p?filename=trysql_select_in
Operator
Description
=
Equal
<>, !=
Not equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
Between an range of
numbers
LIKE
Searches for patterns
IN
When a specific value
is sought
The ‘LIKE’ condition and Wildcards

Sometimes we want to identify records based on slices of the data
within a cell - we do this using wildcards and the LIKE condition
Wildcard
Description
%
A substitute for zero or more characters
_
A substitute for a single character
[charlist]
Sets and ranges of characters to match
[^charlist]
Matches only a character NOT specified within the
or [!charlist] brackets

Examples



http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_percent
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_underscore
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_charlist&ss=-1
The SQL Where clause (continued)



Numerous clauses can be combined with the keywords AND & OR
e.g.
SELECT * FROM my_table
WHERE val1=‘true’ AND val2=‘false’;
Complex clause can be creates by nesting clauses in parentheses
e.g.
SELECT * FROM my_table
WHERE val1=‘true’ AND (val2=‘false’ OR val2=‘null’);
Example

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_where_and_or
The SQL Order By keyword

The ORDER BY keyword is used to sort the result-set
e.g.
SELECT * FROM my_table ORDER BY NAME;

You may also specify whether you want the returned result-set to
be sorted in ascending or descending order by using the keywords
ASC or DESC
e.g.
SELECT * FROM my_table ORDER BY NAME DESC;

Example

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby_desc
Readings


http://www.w3schools.com/sql
http://www.tizag.com/sqlTutorial/