CS541: Project #2

Overview

In this project, you'll create a simple Oracle database & perform some common database operations. The schema and sample data of the database are provided. The project should be done and submitted in podn-n workstations.

Information about getting your Oracle account and general initial configuration is available in:

http://www.cs.purdue.edu/oracle

Due date: Sept 22nd at 11:59pm.

Step 1: Create the tables

The schema for the database is as follows:

1. STUDENT(*snum: integer, sname: string, major: string, slevel: string, age: integer)
2. CLASS(*name: string, meets_at: date, room: string, fid: integer)
3. ENROLLED(*snum:integer, *cname: string )
4. FACULTY(*fid: integer, fname: string, deptid: integer)
5. DEPARTMENT (*deptid: integer,dname: string, Location:string)

The fields with '*' are primary key.

The meaning of these relations is straightforward:
STUDENT contains one record per student identified by snum;
CLASS contains one record per class uniquely defined by its name; the fid field of class gives the instructor of the class;
ENROLLED contains a record for each student enrolled in each course; and
FACULTY contains one record per faculty member uniquely identified by the fid.
DEPARTMENT contains one record per department.
 

Create all the necessary key and integrity constraints necessary to model the application. Make sure that your field & table names correspond to those listed above.

Your task: create a file called tables.sql which will contain five create ... statements corresponding to the tables listed above.

You should test your file by telling SQL*Plus to load it from disk: 

 
$ sqlplus
...
SQL> @ tables.sql

Top

Step 2: Read data files

You are given a file (data.sql) with all the data you will need to insert in the tables you created in previous step.

Your task: test that all the data insertion runs without any problem.

Your script will be tested in the following way: 

 
$ sqlplus
...
SQL> @ data.sql

Top

Step 3: Query your database

Now that you have a functional database, you'll design some queries.

Queries

Write SQL queries that answer the questions below (one query per question) and run them on the Oracle system. The query answers should be duplicate free, but you should use distinct only when necessary. If you are making any assumptions, state them clearly, and document your queries.

1. Print the slevel, the average age, the maximum age and the minimum age of students for that level, for each level.

2. Find the names, major and level of students who are enrolled in the maximum number of classes (Maximum=4)

3. Find the name, major and age of the youngest student who is either a Math major or is enrolled in a course taught by a professor that belongs to Computer Sciences.

4. Find the names and the department of the faculty members who teach in every room in which some class is taught.

5. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than 6 and more than 2. Print also the combined enrollment.

6. Find the names of all students who are enrolled in two or more classes that meet at the same time (Each student selected must be enrolled in two or more classes that meet at the same time).

7. Print the department name and the number of classes taught by faculty members that belong to that department, for all the departments except Management. If the number of classes is 0 print “0”

8. Find the names of departments that have faculty members that are not teaching any class.

9. Find the names of all classes, rooms and the name of the professor who is teaching that course, for all classes that either meet in room R128 or have three or more students enrolled.

10. Who is the professor who teaches the class with the biggest number of students? What is this number?

Your task: Create a file called queries.sql, which contains the queries listed above, in the order they are listed. Before query i, please put the following comment: rem Query i. So, your file should look something like this: 

 
rem Query 1
select ...
 
rem Query 2
select ....
...

Top

Step 4: Views

Here, you'll create some simple views.

Create two views (Please name them VIEWA and VIEWB) and show the contents of them

1. A view that shows the class name and the number of students who are enrolled in that course, ordered by the name of course. Print “0” if the number of students is 0.

2. A view that shows the names of people (students and faculty) that are expected to be present in each room at each time that a class is taught. This should be one view with both faculty and student names. A student enrolled in a class is "expected" to be present for each session of the class, and a faculty member teaching a class is "expected" to be present for each session of the class. The name field in the view should simply be "name".

Your task: Create a file called views.sql, which contains SQL commands (create view FOO as ...) to create the views listed above AND the select statements that list all the data of both views.

Top

Step 5: Submitting your work

Please create a README file that contains identifying information.

So let's recap. You should have the following files:

To turn in your project, create a temporary directory. Copy the files listed above into that directory. Cd to that directory. Then execute the following command: 

 
    $ turnin -c cs541 -p proj2 .

Top

Back