Correction

If you did not do Question (2) yet then the result should also display the Person ID. This was not previously said.

Homework 1

Due Date: 2/15/08
Points: 50

IMPORTANT NOTE:  

If you are having problems with the Web client, you can always go back to the
old command-line client.

Just get into any afs machine (with ssh).

You can use afs1 to afs25.  Don't all use afs1 !!  :)

So you type 

ssh afs20    [for example]

and your user name and password and click yes.


Then type in 

sqlplus

and choose "1" as option.
Then you log in with your usual UCID and password.


(1) (a)  Create a table PERSON with the columns
FirstName, MiddleInitial, LastName, BirthDate, PersonID.

(b) Create a table PERSONINTERESTS with the columns
PersonID, Interest.

Note the following rules:
- Every person in the database has at least one interest.
- Many people have several interests.
- There is no complete list of interests obtainable in any way.
  New interests can appear at any time.
- Thus, the interests in the data below are just limited examples.
- In other words, you CANNOT make a list of all interests "by hand"
  and then use that list in your programs.  You can find all currently
  existing interests with select statements, of course.

(c) There is always a problem getting good data.
Go to the following Web site:
http://www.knoxcotn.org/cemeteries/donahue/concordmasonic.htm

Select 20 people whose last names start with the same letter
like YOUR last name. (If there are fewer than 20, go on to the next
letter in the alphabet).  If they were born before 1900, then 
add the number 100 to the birth year.  That is, if somebody was born
in 1855, then change the year to 1955.  If birth dates are 
incomplete, skip the person.  

Enter those people into your table PERSON.

Use the numbers 1001 to 1020 as the values of PersonID.

(d) Enter the following data into the Interest Table:

PersonID   Interest
------------------------
1001       Swimming
1002       Dancing
1002       Music
1003       Movies
1004       Skiing
1004       Politics
1005       Swimming
1005       Running
1005       Music
1006       Politics
1007       Dancing
1008       Running
1008       Swimming
1009       Weapons
1009       Toy_Soldiers
1009       Kites
1010       Stamps
1011       Coins
1012       Politics
1012       Skiing
1013       Swimming
1014       Dancing
1015       Weapons
1015       Skiing
1015       Politics
1016       Stamps
1017       Coins
1018       Stamps
1018       Coins
1018       Music
1019       Running
1020       Cycling

Show all the SQL statements that you use to do this.
Show the two resulting tables.
This rule applies ALWAYS.  Anything you don't show will
be assumed as not correct.

[10 points]


(2) Write an SQL Query which will select the complete names and birth dates
of all people which have exactly the two interests Politics and Skiing.
In other words, it should list the names (and bdates) of people that have both
interests Politics AND Skiing, but that DO NOT HAVE any other
interests.

Hint: I solved this by using several nested select statements,
using the operators: intersect, minus, in.  However, you do not have
to do it this way.  Do it however you like, as long as the results
are correct.  I developed the solution in several steps, making the
select statements more and more complicated.  Do not try to solve 
the problem "at once" unless you are REALLY good in SQL. Do it step
by step.  After every step make sure the result you get is correct.

[10 points]


(3) Write a PL/SQL program using a cursor which displays in the output
area all the currently existing interests exactly once.

[10 points]

(4) Write a PL/SQL program using two cursors which displays in the output
area all pairs of existing interests exactly once.

For example, if you display

Swimming  Dancing
Swimming  Weapons
Swimming  Skiing
Coins     Dancing
Coins     Weapons
etc.
etc.


then you should NOT display

Dancing Swimming
Weapons Swimming
Skiing  Swimming
Dancing Coins
Weapons Coins

[10 points]


(5) (a) Create a new table PAIRSINTERESTS.
This table should have two columns called Interest1, Interest2.

(b) Extend and redo question 4 so that all pairs of interests displayed
there are also stored in your new table PAIRSINTERESTS.

Keep this and all other tables.  We will reuse them in the next homework.

[10 points]




THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:


- Create a nice cover page with 
  your name, assignment number, class number and a descriptive
  title of the assignment.
  
  All parts of your assignment need to be stapled 
  together, or held together by a folder.  Points will be taken off
  for not complying with these rules.

  No handwriting anywhere.
  No handwriting anywhere.
  No handwriting anywhere.

  Make things look nice and professional.  

- Documentation:
  Do a reasonable amount of internal documentation.
  Internal documentation typically means that you explain
  your code or SQL statements.
  No external documentation unless I specifically ask for it.

- Test runs:
  Show your table after you build it.  Show testruns.
  Show as much as you can.  The burden of proof is up to you.
  NO TESTRUNS, NO POINTS. 
  (Or very, very few.)  You may record the results in a file.
  Thus, you don't need to do screen dumps.
  You will not be punished for too many test runs.

- Hardcopy:
  All code, documentation, and testruns will be accepted as Hardcopy
  (Printout) ONLY.  No email, No fax etc.

- Grading:
  Grading takes into account correctness of the results, but also
  how well they are documented, and what their visual appearance is.
  Things should look nice and PROFESSIONAL.  Not sloppy.  Use a spelling
  corrector for your comments or (later on) write-ups.  Some people do this
  very nicely.  Don't cut corners here, or you will look bad in comparison.

- Cheating:
  The usual policies apply.  0 points for "suspiciously similar" results.
  Report to the Dean in case of repetition.

- Files:
  You must keep all your files of every assignment up to the end of
  the semester.  If there is any doubt about your assignment I will
  ask you to demonstrate it online.

- Late policy:
  I will take off 10 points for being late up to 1 week.  Programs
  that are handed in more than a week late will receive no credit
  at all, unless you have a good reason with WRITTEN documentation
  (usually medical).