project overview n.
Skip this Video
Loading SlideShow in 5 Seconds..
PROJECT OVERVIEW PowerPoint Presentation
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 23

PROJECT OVERVIEW - PowerPoint PPT Presentation

  • Uploaded on

PROJECT OVERVIEW. Hyun J. Moon and Carlo Zaniolo University of California, Los Angeles CS143, Fall 2004. Projects. Two Projects Project1 Defining and Querying Databases in SQL Using IBM DB2 Techniques: DB2, JDBC (Java Database Connectivity) Project2

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
project overview


Hyun J. Moon and Carlo Zaniolo

University of California, Los Angeles

CS143, Fall 2004

  • Two Projects
  • Project1
    • Defining and Querying Databases in SQLUsing IBM DB2
    • Techniques: DB2, JDBC (Java Database Connectivity)
  • Project2
    • Web-based Online Access of Databases: online access/update of a movie database
    • Techniques: DB2, JDBC, HTML, Java Servlet
  • Hot topics! Good for job hunting!
  • Start early, really early!
project seasnet account
Project SEASNET Account
  • Each student is assigned a seas account for the project (not your personal seas account)
    • Pick it up in the break of discussion or office hours
    • The login/passwd will be explicitly used in your project
      • Change your passwd to anything except your private passwd
      • Change your home directory permissions:chmod go-rxw dir
project seasnet account cont d
Project SEASNET Account (cont’d)
  • 3 machines to login
  • To decide which machine you need to login: LAST_2_DIGITS_OF_YOUR_STUDENT_ID mode 3
project dbms ibm db2
Project DBMS: IBM DB2
  • IBM DB2 (also Universal Database Server)
  • Why DB2
    • Garner 2002 on RDBMS market share (new licenses)
      • IBM 34.6% ORCL 32.0% MSFT: 16.3% SYBASE: 2.6%
    • DB2 is cheap: at least 4 times cheaper than Oracle
    • DB2 is the standard
    • DB2 is free for education/research use
    • DB2 is small and simple
setup db2 environments
Setup DB2 Environments
  • DB2 environments like path for db2 libraries, executable files, DB2 Java CLASSPATH
  • csh/tcsh:
    • source /u/cs/class/cs143v/cs143vta/sqllib/db2cshrc
    • or put it into .cshrc to run automatically after login
  • bash:
    • source /u/cs/class/cs143v/cs143vta/sqllib/db2profile
    • or put it into .bashrc to run automatically after login
test db2
Test DB2
  • Set your db2 environments first
  • connect to a database:
    • $ db2 connect to sample1 user yourlogin
    • type your passwd
    • $ db2 “select * from cs143vta.staff”
    • Other sample tables:
      • album, cl_sched, department, emp_act, emp_photo, emp_resume, employee, in_tray, org, project, sales, staff
  • Get help on DB2:
use db2
Use DB2
  • Many ways:
    • command line
    • From applications: embedded SQL, JDBC, Stored Procedures, etc.
  • Command line:
    • DB2 shell: $db2; then input SQL commands dynamically
    • Through OS command line: $db2 SQL command
    • SQL script files: put SQL scripts in a script file, and run as:
      • $db2 –tf <filename>
use db2 db2 command line
Use DB2: DB2 Command Line
  • DB2 command line: enter $db2<enter>
    • You can enter SQL commands + DB2 system commands
    • enter ? for help, or ? cmdname for a specific command
    • commonly used: (no semicolon at the end please)
      • connect to dbname (connect to database dbname)
      • list tables (list tables of your schema)
      • list database directory (list all database directory)
      • connect reset (drop your connection to the database)
      • terminate (terminate current session and exit db2 interactive mode. Always leave your session this way!)
      • echo abc (make comments to your codes)
use db2 cont d
Use DB2 (cont’d)
  • Similar as db2 command line except beginning with “db2”
      • $db2 connect to dbname
      • $db2 “select * from cs143vta.staff” (SQL needs to be quoted on UNIX)
  • SQL script file
    • put SQL scripts to a file query.sql seperated by semicolons:
      • connect to studbn user xxx using yyy;
      • echo test sql script;
      • create table emp(empno int, name varchar(32) );
      • insert into emp values(10, ‘Joe Doe’);
      • select * from emp;
      • connect reset
      • terminate;
    • Run as: $db2 –tf query.sql
connect to db2
Connect to DB2
  • DB2 has 1 or more instances: cs143vta
  • Each instance can have databases
      • 3 project databases: studbl, studbn, studbw
  • To access a database, the user must have the privilege to connect to that database
      • For DB2, a user to a database must also be a user to the OS
  • Connect to a database named sample1:
      • connect to sample1 [user userid] [using passwd]
      • If locally: you can ignore userid/passed
      • In our project, you will connect remotely
  • Always run “terminate” after you finish
  • Schema: a named collection of objects such as tables. Such objects have to be unique inside the schema
  • When you create an object (e.g., a table), a default schema name is automatically created
      • e.g., create table test(a int); a schema named your login, e.g., cs143vxy is automatically created.
      • You can refer to the table as cs143vxy.test, or test (schema cs143vxy, your loginname is implied)
a sample table
A Sample Table
  • A sample employee relation:
    • employee(id, firstname, lastname, gender, hiredate, deptno, title, salary)

connect to studbn user xxx passwd yyy;

drop table employee;

create table employee (

id char(8),

firstname varchar(24),

lastname varchar(24),

sex char(1),

hiredate date,

deptno char(8),

title varchar(24) not null,

salary integer);

insert into employee values(‘10001’, ‘Joe’, ‘Doe’, ‘F’, ’10/02/2003’, ‘d01’, ‘Engineer’, ‘50000’);

Run as: $db2 –tf query.sql

db2 built in data types
DB2 Built-in Data Types
  • Date/Time:
      • Date(10/02/2003), Time(17:12:25), Timestamp(2003-10-02-
  • String:
      • char (fixed length), varchar(variable length), clob( character LOB), blob(binary LOB). e.g., char(4): n <254. varchar(32): n<4000 clob(2M)
  • Numeric:
      • REAL, DOUBLE
      • SMALLINT(16bits), INTEGER(32bits), BIGINT(64bits), DECIMAL. e.g., DECIMAL(5,2): 5: scale, 2 is the # of digits after the decimal point
  • Maximum length in a row: 4005 for 4KBytes pages
load data
Load Data
  • Import example:

Normally data files can be:

      • ASC (non-delimited ASCII format)
      • DEL (delimited ASCII format, default delimiter: ,)
      • IXF (integrated exchange format)
  • Refresh data:
    • I. drop tables + create tables + load data
    • II. delete from emp + load data



MESSAGES import.msg insert INTO employee;

db2 setup
DB2 Setup
  • You can work on your project on your own DB2 and copy it over to seas account, but make sure it works!
  • DB2 download:
      •, download
  • For windows, when you setup, you need to create an administration account, e.g., db2admin
  • To start up db2, run with an Administrator account:
      • c:>db2cmd (setup db2 environment)
      • c:>db2start (start up db2 instance)
      • c:>db2jstrt (start up java connection)
      • c:>db2admin start (startup admin server so you can run DB2 control center)
      • c:>db2cc (Control Center, a GUI for administration)
      • c:>db2stop (stop db2)
      • c:>db2admin stop (stop db2 admin server)
project 1
Project 1

Defining and Querying Databases in SQLUsing IBM DB2Due: Noon Tuesday, Nov 2

  • Tasks
    • Define and create tables
    • Load data into the tables
    • Write queries as SQL scripts
    • Write queries with JDBC
  • Start early!
  • JDBC (Java Database Connectivity) API: a standard SQL database access interface
    • same API for all databases
  • Environment (already setup if you set db2 env)
    • Java and javac
    • (jdbc lib)
  • Sample files:
    • /w/class.01/cs/cs143v/cs143vta/sample-code
establishing a connection
Establishing a Connection
  • Load the vendor specific driver:
      • Class.forName("") (on local)
      • Class.forName("") (remote)
  • Make the connection
    • connection url: e.g., jdbc:db2://
    • username, passwd
    • Connection con = DriverManager.getConnection(url, userid, passwd);
creating jdbc statements and send queries
Creating JDBC Statements and Send Queries
  • A JDBC Statement object is used to send your SQL statements to the DBMS
    • Statement stmt = con.createStatement();
  • Execute the query:
    • For select queries:
      • ResultSet rs = stmt.executeQuery("SELECT * from employee");
    • For update/insert/delete/or DDL queries:
      • ResultSet rs = stmt.executeUpdate(“delete from employee where empno=‘1’ ");
accessing resultset
Accessing ResultSet
  • returns false when there are no more rows
      • while ( {
      • String a = rs.getString(1);
      • String str = rs.getString(2);
      • }
  • Help:
academic honesty policy
Academic Honesty Policy
  • You are expected to submit your own original work
  • It is encouraged to discuss the project with peer students, provided that you indicate their names in your submission.
  • All code must be written up independently.
  • Any suspected academic dishonesty will be reported to the Dean of Students for disciplinary action, which may result in suspension or dismissal from the University.