CS3431 Description: Envision a database Database Systems I - PDF document

One continuous course project CS3431 – � Description: Envision a database Database Systems I application, and implement it fully. Project Overview � Teaming : Teams of 2 students each � Grading : Collect points over the phases Murali Mani CS3431 1 CS3431 2 Phases What DBMS to use? � Phase 0: (due Jan 25, 11:59 am) � Decide on your team and project. Send an email to � Oracle cs3431-ta@cs.wpi.edu with (a) the people in the team (b) project title and a short one para description of what you � Accounts already created, Version 10.1.0.2.0 will work on for this project � Documentation: http://otn.oracle.com � Phase 1: (due Feb 8, 11:59 am via turnin) � Represent the application requirements as an ER schema, translate the ER to relational, analyze the relational design � mySQL: Version 4.1.14 using normalization theory, come up with DDL statements for the relational schema, test the DDL statements. � To create an account, visit � Phase 2: (due Feb 27, 11:59 am via turnin) http://www.wpi.edu/Academics/CCC � Analyze the operations needed for your application, � Documentation: http://www.mysql.com represent them in SQL DML, build an interface for the end- user. CS3431 3 CS3431 4 How to set up Oracle How to set up Oracle � Add following to your .cshrc – if � Check the type of shell that you are using. your path is not empty For this from your unix prompt, type: echo $SHELL � Most of you will get for above “ /bin/tcsh “ setenv ORACLE_BASE /usr/local/oracle/ – this means you are using turbo c-shell setenv ORACLE_HOME /usr/local/oracle/product/10.1.0/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID cs � From your shell prompt, type : setenv TWO_TASK ${ORACLE_SID} echo $PATH � Set environment variables based on if path is empty or not ( next slide ) CS3431 5 CS3431 6 1

How to set up Oracle (contd…) How to set up Oracle (contd…) � Add to .cshrc – if your path is empty � After editing file .cshrc � Please run : setenv PATH . setenv ORACLE_BASE /usr/local/oracle source ~/.cshrc setenv ORACLE_HOME /usr/local/oracle/product/10.1.0/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID cs setenv TWO_TASK ${ORACLE_SID} CS3431 7 CS3431 8 Problems while setting up Oracle Oracle introduction � Connecting � Important – Set up Oracle immediately and see that it is working � sqlplus <userName>/<passwd> � For example, � Many of you will run into problems, typically sqlplus myname/myname � due to simple typos � Change passwd using password command � If you have identified a project partner, start � You will end up submitting your passwd; therefore working with him/her on this ! don’t use password that you use for other purposes. CS3431 9 CS3431 10 Oracle useful commands Using Oracle from Windows These commands can be executed from the SQL shell � Multiple ways: � Use aquastudio software from aquafold.com. connect SELECT * FROM cat; -- lists tables you have created to -- SELECT table_name FROM user_tables; -- as above. server: oracle.wpi.edu DESCRIBE <tableName>; -- describes the schema for port: 1521 (this is the default) the table with name tableName SID: cs help index; -- shows list of help topics; � Download oracle client for windows. Connect using help start; -- illustrates how to use command start sqlplus client or other tools: sqlplus exit; -- exit from the SQL shell rundenst/rundenst@//oracle.wpi.edu:1521/cs.wpi.edu CS3431 11 CS3431 12 2

MySQL introduction � Connecting � mysql -h<host> -u<user> -p<passwd> Working with the Data Server <dbname> � Useful commands � show tables; � describe <tableName>; � exit; � Look at manual for other commands. CS3431 13 CS3431 14 Testing that you are set Running scripts in SQLPlus CREATE TABLE student(sNum INTEGER,sName VARCHAR (30)); To enter OS environment, use sqlplus command: -- creates table student with two columns Host INSERT INTO student VALUES (1, ‘Joe’); Now you can execute OS commands, like : -- insert one row into the student table cd.. , exit , etc. SELECT * FROM student; -- select all rows from student table � Create a file in your file system in the current directory called createTable.sql DELETE FROM student; � @createTable -- executes the script -- delete all rows in the student table � start createTable -- also executes the script DROP TABLE student; � If you want to save your output to a file (similar to script in Unix) -- drop student table spool <fileName> � � <executeCmds...> Purge recyclebin; -- purge recyclebin tables that get created. spool off; � CS3431 15 CS3431 16 Loading data from a text file Loading from text file (Contd) CREATE TABLE myTable1 (a int, b int); � � Create control file, say load.ctl LOAD DATA INFILE sample.dat � Create data file, say: sample.dat INTO TABLE myTable1 � Put data into the file : FIELDS TERMINATED BY ‘,’ 1,11 (a,b) 2,22 3,33 � Invoke SQL Loader (from your UNIX shell): 4,44 � $ sqlldr <user/password> control=load.ctl CS3431 17 CS3431 18 3

Building Interfaces Get Started Now … � Call Level Interface � Pick project partner (feel free to use mywpi to recruit partner) � Perl – to build web interfaces � JDBC – Java, servlets etc � Jointly toss around ideas about cool project � Embedded SQL � C API (Pro*C) � C++API (Pro*C++) � Try out basics – to assure you have access � Java API (SQLJ) [ Oracle ] to oracle (or mysql) CS3431 19 CS3431 20 4