CIS431     Dr. J. M. Scher    HME#12   Oracle TEAM Project   DUE: December 10, 6:00PM

Building and Querying an Oracle Database, Using SQL*PLUS

 

READ:  Oracle module (from Powerpoint Slides)

 

TEAMS: For this Home Mini Exam (and only for this HME), you may (if you wish) pick ONE 'buddy' in the class, and collaborate on this assignment, where each buddy should contribute equally. (Picking a 'buddy' is optional, and you may choose to work independently.) If you pick a buddy and work as a team, you will be required to also submit a document entitled 'Oracle Software Project Management' where it is clearly described in detail how the total effort was divided, and who did what. If you choose to work as a team, you need only submit one copy, and the identical grade will be awarded to both buddies on the team (provided there was equivalent or near-equivalent contributions from both buddies). A team can consist of no more than two "buddies."

 

 

 

 

  Building and Querying an Oracle Database for TIJN Software Consulting Services, Inc.

 

 

     TIJN Software Consulting Services, Inc., is a small start-up company which provides consulting services to various users, and itself plans to employ various consultants (including you!) who have expertise in numerous areas in computing.  They maintain three relations in their database - a CONSULTANT table, which has specific details on the consultants who work with them, an EXPERTISE table which describes the various software areas where TIJN has expertise, and the hourly rate they will charge their clients for work in the particular software area, and a SKILLS table, which describes which consultants are associated with each of the EXPERTISE areas.

 

 

     From a discussion with the company, you determine the schema as follows:

 

 

CONSULTANTS ( ConsultantID, Fullname, Email, State)

EXPERTISE (ExpertiseID, Description, HourlyRate)

SKILLS (SkillID, ConsultantID, ExpertiseID, MonthsExperience)

 

 

    Use SQL "Create" to create these tables in your database, and "Describe" each to be sure you created them satisfactorily. Use "Varchar2" datatypes for each attribute, except for 'HourlyRate' and 'MonthsExperience' which must be numeric, and for ConsultantID, ExpertiseID and SkillID use CHAR(4). Fullname, Email and Description will have no more than 20 characters each, and State will be the usual 2-character abbreviation.

 

 

    Because TIJN Software Consulting Services, Inc. needs to have this database established quickly (before the end of the semester!), they have already created some of the SQL Data Definition Language to establish the database (and make things a little easier for you, that is, feel free to "copy" into a script file and execute).

 

 

   So, the following SQL describes the Expertise that they will market, and the hourly rates they intend to charge clients, based upon the current market conditions:

 

 

insert into expertise values('1','Microsoft Access',275);
insert into expertise values('2','Oracle',225);
insert into expertise values('3','Table Designer',290);
insert into expertise values('4','Java',215);
insert into expertise values('5','COBOL',195);
insert into expertise values('6','C++',185);
insert into expertise values('7','SQL Server 2000',227);
insert into expertise values('8','GPSS',360);
insert into expertise values('9','Cold Fusion',265);
insert into expertise values('10','Crystal Analysis',318);

 

 

   Next, the following SQL will create the SKILLS table, and maps the consultants into their particular expertise areas:

 

 

insert into skills values('1','1','1',27);
insert into skills values('2','1','2',14);
insert into skills values('3','1','3',38);
insert into skills values('4','2','2',45);
insert into skills values('5','3','4',34);
insert into skills values('6','4','5',19);
insert into skills values('7','4','1',25);
insert into skills values('8','5','6',55);
insert into skills values('9','6','7',11);
insert into skills values('10','7','3',21);
insert into skills values('11','7','8',42);
insert into skills values('12','8','9',14);
insert into skills values('13','8','7',31);
insert into skills values('14','9','1',59);
insert into skills values('15','10','2',14);
insert into skills values('16','10','9',44);
insert into skills values('17','11','7',14);
insert into skills values('18','11','9',34);
insert into skills values('19','12','2',32);

insert into skills values('20','12','3',16);
insert into skills values('21','13','8',18);
insert into skills values('22','13','9',72);

 

 

    For the CONSULTANTS relation, you are to populate it with your own data, BUT, for ConsultantID number 1, use your own name and email address (from the SKILLS instances, you will see that this implies that you have expertise in Microsoft Access, Oracle and Table Designer). Fill in the names for 13 records, with ConsultantID's going from 1 to 13, creating whatever names and emails you wish, but for states, use either NJ or NY or PA, with each state appearing several times.

 

 

Then, after you have created the tables and populated them, create and run SQL queries for the following tasks:

 

 

1) For each of the three tables, list all the attributes (via the SQL*PLUS Describe)

2) For the CONSULTANTS table, list all the records.

3) Design and run an SQL query to give the names and email addresses of all consultants who live in NY.

4) Design and run an SQL query to determine the count  of how many particular "skills' (i.e., consultant-expertise combinations) in the database have more than 18 months experience.

5) Design and run a single SQL query to list the names of the different consultants who have expertise in either Microsoft Access or ColdFusion. (Hint: you might wish to try a UNION operator.)

6)Design and run a single SQL query to list the names of the different consultants who have expertise in both Oracle and TableDesigner.

    For the following queries, you will need to use the SQL aggregation functions for sum and average as applied to groups.

7) Design and run a single SQL query which for Consultant #1 (YOU!), will list the total number of months experience, and the average hourly rate for all expertise items for which you are listed in the EXPERTISE table.

8) Design and run a single SQL query which will compute the average hourly rate earned by consultants in each of the three states (NJ, NY and PA).

 

 

     For each of these 7 queries, you will need to document both the SQL query you designed  AND, the answers to your queries. You might wish to SPOOL your results to a file (the "SPOOL filename" command will put everything you typed and the results of the query into filename.lst, which you may view in an editor). (The "Spool Off" will stop the spooling.) You will then have various choices to print the results - you may use a Screen Capture Utility (such as ScreenSeize) to capture the screen as in previous DBMS projects, or you can also copy and paste the text into some other application (Word, Notepad) or, if you are familiar with UNIX, use UNIX commands to print, etc.

 

 

    Of course, the records in this database will change over time, and thus, the SQL queries you design for the 7 tasks above should work, no matter what the current contents of the database are.

 

    Note that no submissions of this Home Mini Exam will be accepted after the due date. There will be no exceptions to this rule, and no extensions.

 

********************************************************************************************

 

SPECIAL OPTION/ESCAPE CLAUSE: If, for some reason relating to the inability of both members of a team to access the Limpid Server and use Oracle (and you have exhausted all avenues for rectifying this situation), and if you can document this, then, since there can be no extensions on this last Home Mini Exam, each member of the 2-person team can INDIVIDUALLY do the following Substitute Home Mini Exam relating to SQL:

 

On pages 220-222 of the text, do problems 6.28 through 6.41 inclusive, clearly showing the SQL code that you write.

 

To exercise this option, you must adhere to the following rules: