HW #1

Download and install Oracle SQL Developer (available from Oracle).  We will begin using it after your Oracle accounts are set up.

Read these sections of chapter 1 of Oracle Database Concepts and be prepared to answer questions:

  • Oracle Database Architecture
    • Overview of Application Architecture
    • Overview of Physical Database Structures
    • Overview of Logical Database Structures
    • Overview of Schemas and Common Schema Objects
    • Overview of Accessing the Database
  • Oracle Database Features
    • Overview of Scalability and Performance Features
      • Concurrency
      • Read Consistency
      • Locking Mechanisms
    • Overview of Security Features
    • Overview of Data Integrity and Triggers
  • Oracle Database Application Development

HW #2

Exercise 2.3. This tool requires drawing ER diagrams.  Either use a tool like Visio or be very legible.

HW #3

Create an ER diagram for a calendar scheduling application.  The application must keep track of multiple people and resources, and must handle repeating events.

HW #4

Translate this ER diagram into a relational schema, and show the SQL statements needed to create the relations.  Include constraints.

Solution

HW #5

Translate your ER diagram from HW #3 into a relational schema, and show the SQL statements needed to create the relations.  Include constraints.

One Solution

HW #6

Exercise 4.3.  Just do the odd questions.  You only need to write the queries in relational algebra.

Solution

HW #7

Exercise 5.2

Exercise 5.3, #1, 2, 5, 7, 10

Directions for installing sample data on the csdb oracle database:

  1. Login to csdb using your csc unix username and password.
  2. Enter these commands at the unix prompt:
    wget zilla.villanova.edu/dbexercise.zip
    unzip dbexercise.zip
    cd excercise_data
    sh dbbook.sh
  3. You may now access the data from SQL Developer.

Solution

HW #8

Install eclipse (or your favorite Java delelopment environment), create a new project, and use this source file to create a working program that queries your Oracle database.  You will also need this jar file.   This will only work if you installed the sample data from HW #7.

HW #9

Write a java program that can:

  • add and delete a student
  • enroll a student in a course
  • list all students
  • list courses for a particular student

HW #10

Exercises 16.3 and 16.7.

HW #11

From Oracle Database Concepts, read:

  • pages 4-1 to 4-6 (stop at "Transaction Naming")
  • pages 13-1 to 13-24 (skip "Quiesce Database on pages 13-11 and 13-12; stop at "Flashback Query")

HW #12

Exercises 19.6 and 19.7.

Solution