Introduction, Exercises

Exercise 1.1 in the SKS database book

Exercise 1.2 in the SKS database book

Exercise 1.3 in the SKS database book

Construct an ER-diagram for a university registrar's office. The office maintains data about each class, including the instructor, the enrollment, and the time and place or the class meetings. For each student-class pair a grade is recorded. Document all assumptions you make about the mapping.

Downloaded the jar files for the Apache Derby DBMS to your local pc. The jar files are available from http://incubator.apache.org/derby/. Setup the DBMS this includes a separate download of IBM's JDBC driver for Derby. The basic steps in the installation are the following

1. Download and uncompress the three Derby jar files and save this jar files for example in c:\derby
2. Download the IBM JDBC driver for Derby. Uncompress the driver and copy the two JDBC jar files to the directory c:\derby\lib.
3. Create or update the CLASSPATH environment variable to contain the jar files derby.jar, derbytools.jar, db2jcc.jar, and db2jcc_license_c.jar
4. Test that you can run the Derby sysinfo utility. From a command prompt write java org.apache.derby.tools.sysinfo.
5. Run the ij command line DBMS tool from a command prompt write java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij.
6. Create a new database and a single table using the following commands

   -- set CLASSPATH to both derby JDBC driver jar files (in the directory derby/lib)
   java org.apache.derby.tools.ij
   
   
   -- from command prompt
   java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
   
   -- from ij prompt create a new database called newDB
   connect ’newDB;create=true’;
   
   -- from ij prompt connect to the newly created database
   connect 'newDB' as new
   
   -- from ij prompt create a new table called testtab with two columns
   create table testtab (i int, c varchar(30));
   
   -- from ij prompt create two new row in testtab
   insert into testtab values (1, 'hello world');	
   insert into testtab values (2, 'hello again');	
   
   -- from ij prompt look at the rows in the testtab table
   select * from testtab;
   
   -- from ij prompt disconnect from the newDB database
   disconnect;
   
   -- exit from ij
   exit;
       	

Create a JDBC connection to your newly created database and select all rows from the table you have created.