Homework 4 (revised on 4/19/2018)
Due Date: 5/2/2018
NO LATE HOMEWORK WILL BE ACCEPTED. NOT EVEN AT THE END OF CLASS.
Points: 50 (Fifty!)
1) Create a table MONEY with one column DOLLAR and insert all the following dollar amounts into it. Note that some of these are valid dollar amounts and some are invalid.
I have marked all the invalid ones in red. This initial test data.
However, there might be other invalid amounts that you have to “figure out yourself.” This IS NOT A COMPLETE LIST OF TEST DATA.
You may assume that no dollar value is higher than $999,999,999.99
Dollar amounts may have a minus sign in front of them, but not a plus sign.
There may be blanks between the minus sign and the dollar sign.
[2]
$1
$1.00
$5.
$2345.66
$2,345.90
$1,456,722.50
$1456722
- $80
-$22,345,876.
$01
$1.000
123.00
$23.1
$8,2345,666.00
$8.123.456,90
$8 345 667
+$40
$500-
2) Write a select statement for the table MONEY that will show only the valid dollar amounts on the screen. This might consist of several subselects, but the main assignment is to use REGULAR EXPRESSIONS in this problem.
Get your select statement working with the above data. However,
I will email you the “real test data” about 24 hours before the due date.
[8]
3) Write a select statement (hint: using set operations) that shows only the invalid dollar amounts (hint: reuse the answer from question 2).
I will email you the “real test data” about 24 hours before the due date.
[5]
4) Create the MongoDB Collection cafes, as described below.
{
"_id" : ObjectId("5ab293ec280bea0f5ca96904"),
"Name" : "Best Coffee Cafe"
}
{
"_id" : ObjectId("5ab295c5280bea0f5ca96905"),
"Name" : "Dream Cafe",
"Style" : "Bistro",
"Address" : {
"Number" : "255",
"Street" : "Fifth Ave.",
"City" : "New York",
"State" : "New York"
}
}
{
"_id" : ObjectId("5ab295c5280bea0f5ca96906"),
"Name" : "My Favorite Cafe"
}
{
"_id" : ObjectId("5ab295c5280bea0f5ca96908"),
"Name" : "British Flavor"
}
{
"_id" : ObjectId("5ab29f91280bea0f5ca96909"),
"Name" : "Morning Glory",
"Drinks" : [
"Mocha",
"Flat White"
]
}
Note that the ObjectIds are automatically created. DO NOT TYPE THEM IN.
I URGE YOU TO TRY OUT ALL CRUD OPERATIONS AND ALL ARRAY OPERATIONS ON THE SYSTEM. THIS IS YOUR MAIN OPPORTUNITY TO LEARN THEM. AND YOU WILL NEED TO KNOW THEM.
[10]
5) Go to yelp.com. Type in a request for cafes in Edison, NJ.
Skip the “Ad” results. Insert the first 10 cafes you find after the “Ad” results into your MongoDB cafes database.
More specifically, insert the name of the café, the address, and the number of stars written as a number, not as a string. (Note that there can be half stars.)
[5]
6) Find the Mongo documentation on the Web. We did NOT study this in class. Learn how to do a query that will find all cafes with 4 or more stars.
Write and test that query against your cafes database.
Display the name and the number of stars of all cafes with 4 or more stars, but do NOT display the address. Sort the output by the number of stars.
Show the query. Show the result.
[5]
7) Go the website http://json.org/example.html
Scroll to the second JSON example (“menu”).
Using the format I showed in class, draw this JSON example as diagram.
Use any editing tool you like. But NO HAND DRAWING.
[5]
8) In class we created an Oracle table JSON_DOCUMENTS that contained two people. Recreate this table in your Oracle account. Then insert your own complete information (name, address, etc.) into the table, so you will have three people in there.
Show the insert operations. Show the complete database.
[5]
9) Write a PL/SQL program using an implicit cursor that will show the name and phone number of the three people on the screen.
As always, show the program and show the result.
[5]
================================================================
THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:
- Create a nice looking cover page with
your name, assignment number, class number and a descriptive
title of the assignment. You may be creative to make it nice looking.
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.
If you use a folder, write your name ALSO on the folder. Or use
a folder with a transparent cover page.
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
(that means all SQL statements and their results).
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 print the file.
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 Google documents, 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.
PROGRAMS MUST BE INDENTED.
- 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 8 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).