very short lecture notes

WYSIWYG
what you see is what you get

1) go thru wizard
2) customize for criteria in design view

lecture

Relational databases
Internet vs. World Wide Web

Cartesian product
Select *
From Orders, Customer

then, filter by keys
Select *
From Orders, Customer
Where Orders.CUSID = Customer.CUSID

This is called an Inner Join
alternatively:
SELECT Orders.OrderDate, Orders.Description, Orders.Cost, Customer.FirstName, Customer.LastName
FROM Customer INNER JOIN Orders ON Customer.CUSID = Orders.CUSID;


Internet is connection of computers, or of networks
as opposed to an Intranet

can communicate across the Internet using a protocol
protocol - agreed upon method of communicating

http://en.wikipedia.org/wiki/Hypertext_Transfer_Protocol

HTTP is only ONE protocol that exists on the internet

domain names

database:
tables
queries - asking questions, performing actions to your data
reports
forms

SQL - a language for queries

within access, there are three ways to design a query
1) Wizard
2) Design View for queries
3) SQL


within wizard, three things can choose
1) what table?
2) what fields?
3) what will we name this query?


SELECT LastName, SSN, Age
FROM Customer;



SELECT Customer.LastName, Customer.SSN, Customer.Age, Customer.FirstName
FROM Customer
WHERE Customer.FirstName="Joshua";

where clause

SELECT LastName, SSN, Age, FirstName
FROM Customer
WHERE Age>50 AND FirstName="Joshua"

SELECT Customer.LastName, Customer.SSN, Customer.Age, Customer.FirstName
FROM Customer
WHERE (((Customer.LastName)="Waxman")) OR (((Customer.Age)<25));


UPDATE Customer
SET FirstName="Josh"
WHERE FirstName="Joshua"

UPDATE Customer
SET FirstName="James"
WHERE FirstName="Josh" And LastName = "Waxman"

UPDATE Customer SET Customer.FirstName = "Jimmy"
WHERE (((Customer.LastName)="Waxman"));

DELETE *
From Customer
Where LastName = "Waxman"

INSERT into Customer
VALUES
("Joshua", "Waxman", "1234346", 54)

lecture

non-relational database
problems:
1) gets huge, repetitive data
2) inconsistent data

relational database
primary key
foreign key is a field which is a primary key of a different table

why should i use an index?
instead of (n^2)/2 guesses (is it 1? is it 2? is it 3? etc. not very productive)
log(n) is much more productive

if n is 100, instead of an average of 50 guesses, it would be an an average of 6 or 7.

DataSheet View -- see the actual data
Design View -- see the metadata


saving is weird in MSACCESS
also, no undos.

Solver, in Excel
goal seek has limitations:
1) seek for a specific value
2) can only fiddle with one value
3) no way of imposing constraints

Solver is an Add-In
lets us do all these things
lets us optimize

lecture

interesting UNIX commands you can use to log on to the cs12 computer and "do stuff"

unix commands
cd -- change directory
elm -- electronic mail
pine -- pine is not elm
pico -- pine composer

http://eniac.cs.qc.edu/~svitak/cs12/webpageassign.html

plus, we are going to do:
forms
css
php
javascript

imperative programming languages (like Visual Basic, Java, JavaScript, PHP, C++)

http://www.w3schools.com/php/default.asp

This is the beginning of my program!

{?php
for($i = 0; $i < 10; $i = $i + 1) echo "

Hello $i

";

?}

amazon.com
how do we remember info about the user?
server side scripting
client side scripting


early websites that implemeted shopping carts
used client side scripting
javascript
cookie

cookies dont move from computer to computer
they leave a trace
3rd party cookies -- used to track your movement across the web

some javascript that writes a cookie, when displaying an ad. if mult sites use same 3rd party for ads, they can track where you have been. privacy concerns.

use server side scripting
PHP, ASP, ASP.NET

next quiz up to here, no further

Database:
collection of objects used for storing and accessing information

Tables
Table is collection of records
Record is collection of fields

Ways of accessing the data in the table
Form
Report
Query

in MSACCESS

walkthru 5th excel exam

walkthru 4th excel exam

walkthru 3rd excel exam

walkthru 2nd excel exam

lecture notes

connect to cs12 machine

if you have a mac,
to download fugu
http://download.cnet.com/Fugu/3000-2155_4-26526.html
instructions for setting it up with your text editor
http://cs12.cs.qc.cuny.edu/~xiuyi/fugu.html

if you can't log in using a wireless connection from your laptop
on campus, it is because you signed on as Guest. you need to
use real credentials

to convert from APR to annual rate
(1 + RATE/freq)^freq

Walkthru, first practice Excel exam

more to come...

we can nest
HOWEVER, only 7 deep
restriction of Excel


this is limitation
the other is that the formula becomes incredibly complicated

we can reduce the height of the tree by BALANCING the tree
then we can ask a lot more questions
and get a lot more results
specifically, 2^height results

AI = artificial intelligence
expert system
20Q

2^20?

Turing Test. by Alan Turing
http://en.wikipedia.org/wiki/Alan_Turing

http://www.sosmath.com/CBB/viewtopic.php?t=7238&sid=e7a5ec278f2f205c20b1b11074468e35

chatbots
eliza
http://www.cleverbot.com/

try interacting with one of these
=RATE(5,-4500,-25000,70000,0)
=NPER(0.6%, -1000, 12000, 1000000, 0)
=A2/12
=PV(0.7%, 10*12, -1000, 1000000, 0)
=PV(8%, 25, 25000, 0, 0)
forever?
=PV(8%, 3000, 25000, 0, 0)

=PV(8%, 1000, 0, 1000000)

lecture notes

Excel Financial functions

using John Walkenbach, Excel 2003 Formulas
Chapter 11
Not present in the 2007 book

PMT, FV, PV, RATE, NPER

Time value of money
FV = PV*(1 + RATE) ^ NPER

for one year:
FV = PV + PV * RATE
FV = PV * (1 + RATE)

if FV is PV for next year,
for subsequent year, mult by (1 + RATE)

FV = PV*(1 + RATE) ^ NPER
we can solve for PV

flow of money represented by a sign
money flowing towards me is positive
money flowing away from me is negative
answers:

=FV(7%, 3, 0, -1000)
=RATE(8, 0, -1000, 2000)
=NPER(14%, 0, -100000, 1000000)
=PV(1%, 12, 0, 10573.45)
=FV(1%, 24, -300, 0, 1)
=FV(7%, 3, 0, 1000)
=FV(0.75%, 36, -500, -5500, 0)

we finished problem #8

back to the Ifs
=(Subtotal+TaxAmt)* IF(Subtotal>B14, IF(Subtotal>B15, C15, C14), 0)

This is called Nesting
especially when we put one If inside of another If