Assumed background

1
Introduction

• Assumed background
• What is a DBMS?
• Why use a DBMS?
• What is a data model?
• Database schema
• relational data model
• Three levels of abstraction
• SQL the relational query language
• Advanced issues covered
• Object-oriented database concepts
• Data warehousing
• Data mining
• Transaction management
• Web databases
• Domain specific databases

2
Keeping all scripts
3
SQL-DDL

• Creating tables
• CREATE TABLE table_name
• (column_name datatype default default_value
  not null
• Example CREATE TABLE slsrep
• (slsrnumb integer NOT NULL,
• totcomm number (7,2)
  DEFAULT 0,
• commrate number (3,2)
  DEFAULT 0)
• Valid Oracle object names
• must be unique
• lt 30 characters
• not a reserved word
• must start with a letter
• can contain letters, digits, some special
  characters (_,,)
• Copying tables
• CREATE TABLE super_slsrep AS
• SELECT FROM slsrep
• WHERE totcomm gt2000

4
SQL DDL

• Renaming tables

• Removing tables
• DROP TABLE slsrep
• Truncating tables
• TRUNCATE TABLE slsrep
• Adding columns
• ALTER TABLE table_name ADD
• (column_name datatype DEFAULT default_value
  NOT NULL)
• Example ALTER TABLE slsrep add (address
  varchar2 (40))
• Removing columns??
• Modifying columns
• ALTER TABLE table_name MODIFY (column_name
  datatype DEFAULT default_value NOT NULL
• Cannot change datatype or reduce size unless
  all column values are
• null
• Cannot change a column to mandatory if at least
  one of the values is null

5
SQL DDL contd

• Sequences a database object that generates
  unique numbers from an arithmetic
• series. Used to
  populated primary keys.
• Syntax CREATE SEQUENCE sequence_name
• INCREMENT BY interval
• START WITH value

• Using sequences
• generate new sequence values
  sequence_name.nextval
• access the current sequence value
  sequence_name.currval
• undefined until the first sequence value is
  generated

6
Review of SQL

• Syntax SELECT column_name,...,column_name
• FROM table_name,...table_name
• Can include a list of columns separated by
  commas or a single but not both
• Columns are displayed in the order specified
• Table names are separated by commas
• SELECT FROM slsrep
• SELECT slsrnumb from slsrep
• Column prefix
• required when two tables have an identical
  column name
• Example SELECT slsrep.slsrnumb
• FROM slsrep, customer
• Alias
• Column alias
• SELECT column_name AS column_alias,...
• FROM table_name table_alias...
• Example
• SELECT slsrnumb AS sales_rep, totcomm AS
  total_commission
• FROM slsrep

7
Review of SQL contd.

• DISTINCT only one instance of duplicate
  values is returned
• Example

• WHERE clause restricts rows retrieved from the
  tables
• Example

• Special comparison operators
• IN--equal to any member of a set
• SELECT FROM part WHERE itemclss in
  (HW,SG)
• BETWEEN x and y
• SELECT FROM part WHERE unitprce BETWEEN 10
  and 100
• LIKE SELECT FROM customer WHERE address
  LIKE GRANT
• IS NULL
• Logical operators AND, OR, NOT

8
Exercise 1

• NOTE save all scripts in your disk for future
  use
• Create the SLSREP, CUSTOMER, ORDERS, ORDLNE,
  PART tables
• Enter the following constraints
• ALTER TABLE slsrep add (constraint slsrep_pk
  primary key (slsrnumb))
• Using the above syntax, create primay key
  constraints for tables
• CUSTOMER primary key custnumb
• ORDERS primary key ordnumb
• ORDLNE primary key (ordnumb, partnumb)
• PART primary key partnumb
• ALTER TABLE customer add (constraint cust_fk
  foreign key (slsrnumb)
• references slsrep(slsrnumb))
• Using the above syntax, create referential
  integrity constraints for tables
• ORDERS custnumb references customer(custnumb)
• ORDLNE ordnumb references orders(ordnumb)
• ORDLNE partnumb references part(partnumb)
• Insert all rows for SLSREP, CUSTOMER, ORDERS,
  ORDLNE, PART