IMS1907 Database Systems

1
IMS1907 Database Systems

• Summer Semester 2004/2005
• Lecture 9
• Structured Query Language SQL
• Data Definition Language - DDL

2
Structured Query Language - SQL

• Has become de facto language for creating and
  querying relational databases
• mainframe and personal database environments
• Accepted as a standard
• ANSI, FIPS, ISO
• First published in 1986
• updated in 1989, 1992 (SQL-92), 1999 (SQL-99) and
  2003 (SQL2003)

3
Structured Query Language - SQL

• SQL-99 was a significant extension on SQL-92
• added regular expression matching, recursive
  queries, triggers, non-scalar types and some
  object-oriented features (the last two are
  somewhat controversial and not yet widely
  supported)
• SQL2003
• introduced XML-related features, standardized
  sequences and columns with auto-generated values
  (including identity-columns)

4
Structured Query Language - SQL

• Benefits of a standard relational language
• reduced training costs
• increased productivity
• increased application portability
• extended application longevity
• reduced dependence on single vendor
• enhanced cross-system communication

5
Structured Query Language - SQL

• Disadvantages of a standard relational language
• stifle creativity and innovation
• standardisation the natural enemy of variety
• may not meet all user needs
• not ideal as a result of being a set of
  compromises between many industry parties
• difficult to change
• vendor features can limit portability and offset
  other advantages

6
The SQL Environment

• Concepts
• catalog
• schema
• data definition language (DDL)
• data manipulation language (DML)
• data control language (DCL)

7
The SQL Environment

• Catalog
• a set of schemas which, when put together,
  constitute a description of a database
• describes any object that is part of a database
• a DBMS may manage many catalogs
• Schema
• a structure containing descriptions of objects
  created by users
• base tables, views, domains, constraints,
  character sets, triggers, roles,

8
The SQL Environment

• Data definition language (DDL)
• commands used to define a database
• used to create, change and remove objects from a
  database and establish constraints
• generally restricted to one or more DBAs to
  protect database from unexpected changes
• DDL statements include
• CREATE, ALTER, DROP
• In MySQL we also need the USE statement

9
The SQL Environment

• Data manipulation language (DML)
• considered by many as the core SQL commands
• commands used to maintain and query a database
• used to update, insert, change and query data
• can be interactive or embedded in programs
• embedded SQL gives programmer more control over
  report timing, interface appearance, error
  handling and database security
• some common DML statements include
• INSERT, DELETE, SELECT, SHOW, DESCRIBE

10
The SQL Environment

• DML allows us to perform the following relational
  operations
• select
• selects a subset of rows in a table satisfying a
  selection criteria
• project
• selects only certain columns from a table
• join
• combines data from two or more tables based on
  one or more common values

11
The SQL Environment

• Data control language (DCL)
• commands used to control a database
• used to administer privileges and the committing
  of data
• controls granting and revoking of access, and
  retrieving and storing transactions that could
  affect database
• generally restricted to DBAs to protect database
  from unexpected changes and unauthorised access
• DCL statements include
• GRANT, REVOKE, LOAD, BACKUP, ROLLBACK

12
The SQL Environment Data Types

• Each DBMS has a defined list of data types it can
  handle
• All have equivalents for structured data types
• numeric, string and date/time variables
• Many allow for unstructured data types
• graphic, image, spatial
• When creating a table, the data type for each
  attribute must be specified
• Choice of data type depends on
• data values that need to be stored
• expected uses of data

13
MySQL Datatypes
14
MySQL Datatypes
15
MySQL Datatypes
16
Data Definition Language (DDL)

• Three DDL CREATE commands are included in SQL-92
  and later versions of standard
• CREATE DATABASE (or CREATE SCHEMA in Oracle)
• used to define portion of database owned by user
• contains tables, views, domains, constraints,
• CREATE TABLE
• defines table and its columns
• dependent on a schema
• CREATE VIEW
• defines logical table from one or more tables or
  views

17
Data Definition Language (DDL)

• Other DDL CREATE commands are also included in
  SQL-92 and later versions of standard
• CREATE CHARACTER SET
• CREATE COLLATION
• CREATE TRANSLATION
• CREATE ASSERTION
• CREATE DOMAIN
• Other statements such as CREATE INDEX are
  performance-related and not part of standard

18
Data Definition Language (DDL)

• The DROP command is used to reverse CREATE
  commands
• DROP DATABASE (or DROP SCHEMA in Oracle)
• removes entire database and related schemas
• DROP TABLE
• removes table and related columns and constraints
  from database
• DROP VIEW
• removes logical table from database schema
• Other DROP commands can remove keys, indexes,
  users, ..

19
Data Definition Language (DDL)

• The ALTER command is used to change objects
  created using the CREATE command
• ALTER DATABASE (or ALTER SCHEMA in Oracle)
• changes DB characteristics character sets,
  collations
• ALTER TABLE, ALTER COLUMN
• changes table definitions by altering column
  specifications
• ALTER VIEW
• changes specifications of logical table
• Can include ALTER, ADD, DROP keywords

20
Creating a Database

• At the mysqlgt prompt enter the command and the
  name of the database you want to create
• mysqlgt CREATE DATABASE menagerie
• Creating a database in MySQL does not
  automatically select it for use
• Select a database for use with the use command
  and the name of the database you want to use
• mysqlgt USE menagerie

21
Creating Tables

• Once a database has been selected you can create
  tables
• Creating tables requires that you define
• column names
• data types and lengths
• column and table keys and constraints
• Although MySQL does not necessarily require you
  to specify keys and constraints, they should be
  defined if known

22
Creating Tables

• Use the CREATE TABLE statement to define the
  layout of the table
• CREATE TABLE pet
• (name VARCHAR(20), owner VARCHAR(20), species
  VARCHAR(20), sex CHAR(1), birth DATE, death
  DATE)
• You can specify more detail about your table by
  defining keys and constraints

23
Creating Tables

• CREATE TABLE person
• (person_id SMALLINT UNSIGNED NOT NULL
  AUTO_INCREMENT, name VARCHAR(60) NOT NULL,
  PRIMARY KEY (person_id)) 
• CREATE TABLE shirt
• (shirt_id SMALLINT UNSIGNED NOT NULL
  AUTO_INCREMENT, style ENUM('t-shirt', 'polo',
  'dress') NOT NULL, color ENUM('red', 'blue',
  'orange', 'white', 'black') NOT NULL, owner
  SMALLINT UNSIGNED NOT NULL REFERENCES
  person(person_id), PRIMARY KEY (shirt_id))

24
Altering Tables

• Use the ALTER TABLE statement to re-define the
  layout of the table
• add/remove/rename columns, constraints, keys,
  types,
• ALTER TABLE person
• ADD (address VARCHAR(60) NOT NULL) 
• ALTER TABLE person
• MODIFY (name VARCHAR(40) NOT NULL) 

25
Deleting a Database

• Use the DROP DATABASE command to remove a
  database and all related tables and schemas
• Enter the command and the name of the database
• DROP DATABASE menagerie 
• You can add the optional keywords IF EXISTS to
  prevent errors occurring for non-existent
  databases  
• DROP DATABASE IF EXISTS menagerie 
• Use this command with caution!

26
Deleting a Table

• Use the DROP TABLE command to remove a table and
  all its related definitions
• Enter the command and the name of the table
• DROP TABLE pet 
• You can add the optional keywords IF EXISTS to
  prevent errors occurring for non-existent tables
   
• DROP TABLE IF EXISTS pet 
• You can use this command to remove one or more
  tables

27
References

• Bordoloi, B. and Bock, D., (2004), Oracle SQL,
  Pearson Education Inc., Upper Saddle River, NJ,
  USA.
• Hillyer, M., (2004), Visual Basic / MySQL
  Datatypes, last accessed 10th September 2004 at
  http//dev.mysql.com/tech-resources/articles/visua
  l-basic-datatypes.html
• Hoffer, J.A., Prescott, M.B. and McFadden, F.R.,
  (2005), Modern Database Management, (7th edn.),
  Pearson Education Inc., Upper Saddle River, NJ,
  USA.

28
References

• Kroenke, D.M., (2004), Database Processing
  Fundamentals, Design and Implementation, (9th
  edn.), Pearson Education Inc., Upper Saddle
  River, NJ, USA.
• MySQL Reference Manual, (2004), last accessed
  10th September 2004 at http//dev.mysql.com/doc/my
  sql/