Database Management Systems

1
Database Management Systems

• DB Application Development Project Statement
  Introduction to Oracle

2
One project done in multiple steps

• Description Envision a database application, and
  implement it fully.
• To be done individually or in teams of 2

3
Phases

• Project Intent (due Sep 18, 600 pm)
• Decide on your project. Send an email to
  mmani_at_cs.wpi.edu with (a) project title and a
  short one para description of your project, and
  (b) if working in a team, the names of the team
  members
• Phase I (due Oct 16, 600 pm)
• Represent the application requirements as an ER
  schema, translate the ER to relational, analyze
  the relational design using normalization theory,
  come up with SQL DDL statements and test them.
• Phase II (due Nov 6, 600 pm)
• Analyze the operations needed for your
  application, represent them in SQL DML.
• Phase III (due Dec 11, 600 pm)
• Build an interface in a suitable PL for your DB
  application. Demo your project in class.

4
What DBMS to use?

• Oracle (available on ccc)
• Accounts already created, Version 10.2.0.3.0
• Documentation http//otn.oracle.com
• Oracle client (sqlplus), Version 11.1.0.6.0
• mySQL Version 5.0.27 (available on ccc)
• To create an account, visit http//www.wpi.edu/Aca
  demics/CCC
• Documentation http//www.mysql.com

5
How to set up Oracle

• From a CCC machine, check the type of shell that
  you are using. For this from your unix prompt,
  type echo SHELL
• Most of you will get the result of the above as
  /bin/tcsh this means you are using turbo
  c-shell
• From your shell prompt, type echo PATH
• If the path is not empty, then set environment
  variables as in the next slide
• If the path is empty, then set environment
  variables as in the slide after the next

6
How to set up Oracle

• Add the following to your .cshrc if your path
  is not empty
• setenv ORACLE_BASE /usr/local/oracle
• setenv ORACLE_HOME ORACLE_BASE/product/11.1.0/d
  b_1
• setenv PATH PATHORACLE_HOME/bin
• setenv ORACLE_SID WPIDBR2
• setenv TWO_TASK ORACLE_SID

7
How to set up Oracle (contd)

• Add the following to your .cshrc if your path
  is empty
• setenv PATH .
• setenv ORACLE_BASE /usr/local/oracle
• setenv ORACLE_HOME ORACLE_BASE/product/11.1.0/d
  b_1
• setenv PATH PATHORACLE_HOME/bin
• setenv ORACLE_SID WPIDBR2
• setenv TWO_TASK ORACLE_SID

8
Problem while setting up Oracle

• Important Set up Oracle immediately and see
  that it is working
• Most of you will run into problems, also most of
  these problems will be simple typos
• However, feel free to approach the instructor if
  such issues.

9
Oracle introduction

• Connecting
• sqlplus ltuserNamegt/ltpasswdgt
• For example, sqlplus mmani/mmani
• Change passwd using password command
• Remember that you might finally end up submitting
  your passwd therefore do not use a password that
  you use for other purposes.

10
SQLPLUS useful commands

• SELECT FROM cat -- lists tables you have
  created
• SELECT table_name FROM user_tables -- same as
  above.
• DESCRIBE lttableNamegt -- describes the schema for
  the table with name tableName
• help index -- shows list of help topics
• help start -- illustrates how to use command
  start
• exit -- exit from the SQL shell
• More info http//www.ss64.com/orasyntax/plus.html

11
Using Oracle from Windows

• Multiple ways
• Use aquastudio software from aquafold.com.
  connect to --
• server oracle.wpi.edu
• port 1521 (this is the default)
• SID WPIDBR2
• Download sqlplus client for windows. Connect
  using
• sqlplus mmani/mmani_at_//oracle.wpi.edu1521/WPIDBR2
  .wpi.edu

12
MySQL introduction

• Connecting
• mysql -hlthostgt -ultusergt -pltpasswdgt ltdbnamegt
• Useful commands
• show tables
• describe lttableNamegt
• exit
• Look at manual for changing passwords and other
  commands.

13
Testing that you are set

• CREATE TABLE student (sNumber INTEGER, sName
  VARCHAR (30)) -- creates table student with two
  columns
• INSERT INTO student VALUES (1, Joe) -- insert
  one row into the student table
• SELECT FROM student -- select all rows from
  student table
• DELETE FROM student -- delete all rows in the
  student table
• DROP TABLE student -- drop student table
• Purge recyclebin -- purge recyclebin tables that
  get created.

14
Running scripts in SQLPlus

• To enter the OS environment, use the following
  sqlplus command
• host
• Now you can execute OS commands, like cd.. type
  exit to exit
• Create a file in your file system in the current
  directory called createTable.sql
• _at_createTable -- executes the script
• start createTable -- also execute the script
• If you want to save your output to a file
  (similar to script in Unix)
• spool ltfileNamegt
• ltexecuteCmds...gt
• spool off

15
Loading data from a text file

• CREATE TABLE myTable1 (a int, b int)
• Create data file, say sample.dat
• 1,11
• 2,22
• 3,33
• 4,44

16
Loading from text file (Contd)

• Create control file, say load.ctl
• LOAD DATA
• INFILE sample.dat
• INTO TABLE myTable1
• FIELDS TERMINATED BY ,
• (a,b)
• Invoke the SQL Loader (from your UNIX shell)
• sqlldr controlload.ctl

17
Datatypes in SQL

• INT (or) INTEGER
• FLOAT (or) REAL
• DECIMAL (n, m)
• CHAR (n)
• VARCHAR (n)
• DATE, TIME