Database Management Systems

Database Management Systems DB Application Development Project Statement + Introduction to Oracle Murali Mani -- CS542

One project done in multiple steps • Description: Envision a database application, and implement it fully. • To be done individually or in teams of 2 Murali Mani -- CS542

Phases • Project Intent: (due Sep 22, 6:00 pm) • Decide on your project. Send an email to mmani@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 20, 6:00 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 10, 6:00 pm) • Analyze the operations needed for your application, represent them in SQL DML. • Phase III (due Dec 8, 6:00 pm) • Build an interface in a suitable PL for your DB application. Demo your project in class. Murali Mani -- CS542

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/Academics/CCC • Documentation: http://www.mysql.com Murali Mani -- CS542

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 Murali Mani -- CS542

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/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID WPIDBR2 setenv TWO_TASK ${ORACLE_SID} Murali Mani -- CS542

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/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID WPIDBR2 setenv TWO_TASK ${ORACLE_SID} Murali Mani -- CS542

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. Murali Mani -- CS542

Oracle introduction • Connecting • sqlplus <userName>/<passwd> • 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. Murali Mani -- CS542

SQLPLUS useful commands SELECT * FROM cat; -- lists tables you have created SELECT table_name FROM user_tables -- same as above. DESCRIBE <tableName>; -- 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 Murali Mani -- CS542

Using Oracle from Windows • Multiple ways • Use aquastudio software from aquafold.com (NOT FREE) • Use DreamCoder for Oracle 4.2 (FREE !!). Connect as: server: oracle.wpi.edu port: 1521 (this is the default) SID: WPIDBR2 • Download sqlplus client for windows. Connect using: sqlplus mmani/mmani@//oracle.wpi.edu:1521/WPIDBR2.wpi.edu Murali Mani -- CS542

MySQL introduction • Connecting • mysql -h<host> -u<user> -p<passwd> <dbname> • Useful commands • show tables; • describe <tableName>; • exit; • Look at manual for changing passwords and other commands. Murali Mani -- CS542

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. Murali Mani -- CS542

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 calledcreateTable.sql • @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 <fileName> • <executeCmds...> • spool off; Murali Mani -- CS542

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 Murali Mani -- CS542

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 control=load.ctl Murali Mani -- CS542

Datatypes in SQL • INT (or) INTEGER • FLOAT (or) REAL • DECIMAL (n, m) • CHAR (n) • VARCHAR (n) • DATE, TIME Murali Mani -- CS542

Database Management Systems