Introduction

In this assignment, you will work on ORACLE to perform SQL queries. The assignment has two parts. In the first part you are to set up a database (this should be a new database of your choosing, but try to do something other than a student/classes database). In the second part you will be accessing a large database and designing queries to retrieve data from that database.

You will be working on ORACLE in this assignment. Oracle is one of the most widely used DBMS programs in the world. It runs on virtually every kind of computer, from PCs and Macintoshes, to minicomputers and giant mainframes. Oracle uses a relational data model. Oracle provides a form of SQL as its query language. Oracle provides an interactive interface for generating queries, SQL*PLUS that will perform many useful functions for you.

Getting Started

Oracle can be accessed from either Unix or DOS (PC). Click here to find documentation on how to access ORACLE and how to work in SQLPLUS. Note that for this assignment (and this assignment only) you will need to log in to Bulldog or one of the other mainframes in order to work. For all later assignments you should plan on using the workstations in HH314 (either directly or remotely).

Building Your Own Database

In the first part you are to setup a small database with the following requirements:

• Set up three relations, each with a primary key and at least one with a foreign key. Do not repeat any relations from the book, make up something new based on your own interests.
• Insert at least three tuples into each relation.
• Add an index for a non-primary key attribute of one of the relations.
• Display each relation.
• Perfrom some sample queries on your db:
  • Perform two or more queries producing a subset of the attributes of one or more relations.
  • Perform queries selecting specific tuples from tuples from two of the relations.
  • Perform a query that involves joining two relations (perhaps on the foreign key reference).
  • Perform two different queries involving aggregation operators.
  • Perform a query that is equivalent to the relational algebra idea of division.
• Drop the index you added.
• Update a tuple.
• Update a relation (Add a column).
• Delete several tuples.
• Drop all of your relations.

Querying An Existing Database

In the second part you are to query an existing database. The database that you will query is for a book publishing company. You are to write queries for the database and then spool your query along with the results to a file. You should submit this file containing your results.

Design Of The Book Database

The bookbiz database keeps track of the activities of three subsidiary publishing companies. Since the fiscal arrangements of the companies are not independent, the parent publisher has chosen to maintain a single database:

• The publishers table contains information about the three subsidiary publishing lines - their identification numbers, names and addresses.
• For each author under contract with any of the publishers, the authors table contains an identification number, first and last name, and address information.
• The editors table contains similar information about each editor, with the addition of a position column that describes the type of work the editor does.
• For each book that has been or is about to be published the titles table contains an identification number, name, type, publisher identification number, price, advance, year-to-date, sales, contract status, comments, and publication date.
• The titles and authors can be linked together with a third table, the titleauthors table. For book, titleauthors contains a row for every author involved with information on the title ID, the author ID, the author cover credit order and the royalty split among the authors of the book.
• The titleditors table similarly links the titles and their editors, instead of cover credit order, it lists editing order, so that it is possible to find who was the first or last editor.
• The roysched table lists the unit sales ranges and royalty connected with each range. The royalty is some percentage of the net receipts from sales. The percentage is used to calculate the amount due each author on sales of his or her book.
• The sales table has top level information about each purchase order received from bookstores: sales, order number (assigned by the publisher), store identification, purchase order number (assigned by the store), and date.
• The salesdetails table contains information about each line in the purchase order : title, quantity ordered, quantity shipped, date shipped.

The relations have the following schemas:

• Publishers(pub_id,pub_name,address,city,state)
• Authors(au_id,au_lname,au_fname,phone,address,city,state,zip)
• Editors(ed_id,ed_lname,ed_fname,ed_pos,phone,address,city,state,zip)
• Titles(title_id,title,type,pub_id,price,advance,ytd_sales,contract,notes,pubdate)
• Titleauthors(au_id,title_id,au_ord,royaltyper)
  Titleditors(ed_id,title_id,ed_ord)
  Roysched(title_id,lorange,hirange,royalty)
• Sales(sonum,stor_id,ponum,date)
• Salesdetails(sonum,qty_ordered,qty_shipped,title_id,date_shipped)

The tables were created using the following SQL statements:

create table authors 
        (au_id char(11) not null, 
        au_lname varchar(40) not null, 
        au_fname varchar(20) not null, 
        phone char(12) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null, 
        zip char(5) null); 

create table publishers 
        (pub_id char(4) not null, 
        pub_name varchar(40) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null); 

create table roysched 
        (title_id char(6) not null, 
        lorange int null, 
        hirange int null, 
        royalty float null); 
        
create table titleauthors 
        (au_id char(11) not null, 
        title_id char(6) not null, 
        au_ord int null, 
        royaltyshare float null);

create table titles 
        (title_id char(6) not null, 
        title varchar(80) not null, 
        type char(12) null, 
        pub_id char(4) null,
        price float null, 
        advance float null, 
        ytd_sales int null, 
        contract int not null, 
        notes varchar(200) null, 
        pubdate date null); 

create table editors 
        (ed_id char(11) not null, 
        ed_lname varchar(40) not null, 
        ed_fname varchar(20) not null, 
        ed_pos varchar(12) null, 
        phone char(12) null, 
        address varchar(40) null, 
        city varchar(20) null, 
        state char(2) null, 
        zip char(5) null); 
        
create table titleditors 
        (ed_id char(11) not null, 
        title_id char(6) not null, 
        ed_ord int null); 

create table sales 
        (sonum int not null, 
        stor_id char(4) not null, 
        ponum varchar(20) not null, 
        sdate date null); 
        
create table salesdetails 
        (sonum int not null, 
        qty_ordered int not null, 
        qty_shipped int null, 
        title_id char(6) not null, 
        date_shipped date null);

These tables have already been created. You can access any of these tables by specifying anagaraj.tablename. Here "tablename" will be the name of the table you want to work on.

To see all of the tables in your tablespace run the following query:

        SQL>Select * from tab;

To select all of the rows in a table such as "editors" you can use the query:

        SQL> Select * from anagaraj.editors;

You can copy the data from each of these tables to tables you create in your own tablespace. For example, to copy the data from the authors table to a table named authors1 in your own tablespace execute the following query:

        SQL>create table authors1 as select * from anagaraj.authors;

To view all the information about a table you use the query:

        SQL> desc tablename;

The Queries

You should write queries for the following situations:

1. Write a query to retrieve information about authors who live in Oakland or Wisconsin.
2. Write a query to find titles of all books with a price between and including $10 and $20.
3. Write a query to find which book title has sold more than 50 copies but less than 75 copies from any store.
4. Find the full names of the authors whose first names are John.
5. List all the publishers associated with the authors in the authors table.
6. List the editors for all the titles in the titles table.
7. Find all science and business books that have advances over 5000$.
8. List the names of all the titles whose authors live in California, Indiana or Maryland.
9. Find the names of the books with the lowest and the highest prices.
10. Write a query to find the names of authors who live in the same city as "Livia Karsen".
11. Find the names (last and first) of the authors who do not make less than 50% of the royalties on at least one book.
12. Find the names of all books with prices ranging from the minimum price to minimum price + $50.
13. Find the types of books in which the maximum advance is at least $50 more than the average advance for that type.

What To Turn In

Turn in a script of a session on ORACLE with your queries and the answers. To do this, spool your queries along with the output to a file and then hand in a copy of the file. See the ORACLE guide for more information.