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).