Participation means attending class and participating in
discussions.
All tests are cumulative.
Assignments will be graded on functionality (i.e., do they work),
structure and style. Structure includes clear and efficiently
written code. Style includes documentation, indentation
and overall readability. Documentation includes comments.
Programs without comments are unclear and unreadable.
Assignments are due at the beginning of class
on the date assigned. No late assignments accepted.
Please note that assignments count for 40% of the final
grade.
While much of the material is from the book, a significant
portion will be from the lectures. You are responsible for all
material presented in class. If you do not attend, find someone
who can give you notes. Do not send email asking for
class summaries.
Assignment submissions:
Neatly printed and stapled. Clever edge and corner folding to
attach multiple pages will not be accepted nor will paper
clips.
Your name, the course, the date, the subject of the
assignment and the assignment number will appear in a comment
block at the beginning of each assignment. Non-complying
assignments will be not be accepted.
Assignments will not be accepted by email.
No screen-caps unless graphic output is involved.
Makeup Tests
Makeup tests will be given only in
cases of documented and demonstrated need for causes such as serious illness,
family emergency or University sanctioned schedule conflict.
Note: all makeup tests will be essay format.
Final Grades
Final grades will not be available via email. If you want your
grade mailed to you, bring a stamped, self-addressed envelope to the
final.
Originality
If your work duplicates in whole or in part the work of someone
else, both works will receive a grade of 0.
If this occurs twice, a final grade of F will be assigned.
Classes
Classes are lecture format. Cell phones, pagers and PDA's
may not be used during class. You are encouraged to bring your book to
class and take notes.
You are responsible for all material presented in class.
If you elect not to attend, you are responsible for obtaining the
material you missed. Email requests to me for summaries of material
presented in class will not be answered.
Usage of laptops, PDAs, pagers and cell phones in class is not permitted.
This is due to problems in the past with people using these devices
in class to chat, IM, browse, play games, work on assignments for other classes,
display images/videos and send/rcv email.
These activities are incompatible with a college classroom and create a
distraction to neighbors and people seated in rows further back.
As a result, the faculty Senate has established a policy that these devices may not be
used without consent of instructor.
Tests
Test 1
TBA
Test 2
Fri Nov 14.
Objective
To understand data base systems
Book:
Database Systems: Design, Implementation, and Management, Eighth Edition
by Peter Rob (Author), Carlos Coronel (Author)
ISBN-10: 1423902017
ISBN-13: 978-1423902010
or the older very similar version:
Database Systems: Design, Implementation, and Management, Seventh Edition
by Peter Rob (Author), Carlos Coronel (Author)
ISBN-10: 1418835935
ISBN-13: 978-1418835934
Topics
File Systems and Databases
The Relational Database Model
An Introduction to Structured Query Language (SQL)
Entity Relationship (ER) Modeling
Normalization of Database Tables
Database Design
The University Lab: Conceptual Design
The University Lab: Conceptual Design Verification, Logical Design, and Implementation
Transaction Management and Concurrency Control
Distributed Database Management Systems
Object-Oriented Databases
Client/Server Systems
The Data Warehouse
Database Administration
Databases and the Internet
Database System
PostgreSQL - see below.
Assignments
Run the test database creation routine shown below with the test creation command file
(right click and download here)
on your installation of Postgresql. Then, using psql, as shown below, execute the
following SQL commands. Turn in the typescript (cleansed of escape sequences and non-printing characters)
of the session (hint, you can make a file of the commands and run them in the
same manner as the creation run and redirect the output to a file. This file should
not have any non-printing or escape sequences). Do not turn in the output from the
creation run.
Annotate the file you turn-in with a comment as to what you think
each command is retrieving.
commands:
select ptid, dob from demographic where gender='Male';
select ptid, namefirst, namelast from ptname;
select ptname.ptid, namefirst, namelast, demographic.ptid from ptname, demographic where gender='Male' and ptname.ptid=demographic.ptid;
select ptname.ptid, namefirst, namelast, problem from ptname, demographic, problems where gender='Male' and ptname.ptid=demographic.ptid and problems.ptid=demographic.ptid;
Due: Friday Sept 12, 2008
Do exercise 23 on page 59. Due Fri Sept 26. No crayons!
Using the relational tables (click here) ,
write the relational algebra to find the following:
for each part supplied, get the part number and the names of all cities supplying that part.
get the supplier names for suppliers who do not supply part P2.
get the supplier numbers and cities for suppliers who supply part P2.
get the supplier names for suppliers who supply at least one red part.
Due: Fri Oct 10.
Run test queries against the database from assignment 1 from a web server.
Go to the appropriate link below and make it work on your machine. Turn in
screen caps of query page and answer page. Use test query a from assignment
1 above. Due Mon Oct 20.
You can select columns from multiple tables with a SELECT command such as:
select ptname.ptid, demographic.dob, address.zip from ptname,demographic,
address where ptname.ptid=demographic.ptid and address.ptid=ptname.ptid;
ptid | dob | zip
------+-------------+-------
1001 | 1952-Mar-28 | 50613
1002 | 1962-Jan-18 | 50613
1003 | 1932-Dec-08 | 50613
1004 | 1922-Nov-02 | 50613
1005 | 1942-Feb-02 | 50613
(5 rows)
The above gets results for all ptid values. The following gets the
values for only the person with ptid='1001'
select ptname.ptid, demographic.dob from ptname,demographic,address where
ptname.ptid='1001' and demographic.ptid='1001' and address.ptid='1001';
ptid | dob
------+-------------
1001 | 1952-Mar-28
Write a web page which permits the user to enter a patient id (ptid) and select
with check boxes columns to display from tables ptname, demographic and address.
From the items checked, build a select statement such as the above, send it to
the server then format and display the results back to the browser.
You can probably get away with a single prototype SELECT statement even if no items are
extracted from one or more tables but it would be nicer if the SELECT only
mentioned tables from which actual columns are pulled.
Evil example:
select ptname.ptid, demographic.dob from ptname,demographic,address
where ptname.ptid=demographic.ptid and address.ptid=ptname.ptid;
ptid | dob
------+-------------
1001 | 1952-Mar-28
1002 | 1962-Jan-18
1003 | 1932-Dec-08
1004 | 1922-Nov-02
1005 | 1942-Feb-02
(5 rows)
select ptname.ptid, demographic.dob from ptname,demographic,address where
ptname.ptid='1001' and demographic.ptid='1001' and address.ptid='1001';
ptid | dob
------+-------------
1001 | 1952-Mar-28
(1 row)
The address table is mentioned twice but no data is extracted from it.
Obviously, it would be better to omit it if it isn't needed.
Notes:
The initial web page checkboxes (part of a FORM tag block) can look something
like:
<input type="checkbox" name="x1" value="ptname.ptid" >Display Patient ID <br>
<input type="checkbox" name="x2" value="demographic.dob" >Display DOB<br>
<input type="checkbox" name="x3" value="ptname.name" >Display Patient Name<br>
.
.
.
Display Patient ID
Display DOB
Display Patient Name
In your Mumps program (if you use Mumps), if some one checks each of the above
boxes, you will have variables x1, x2, and x3 and they will have the values
ptname.ptid, demographic.dob and ptname.name, respectively. If a box
was not checked, its variable will not exist.
You need to build a string containing a SELECT statement.
The first item, after the SELECT will be a list of what to
display. This can come directly from the values of x1, x2, x3 ...
The only tricky thing is to get the commas correct. Then, you
need to add the table names. These can be determined from the
x1, x2,... values too also with a hack for the commas.
Then you need the expressions after the WHERE which
repeats the tables but with: tablename.ptid=value AND ... where value is
the actual value.
You can build up a string to insert in you select like this:
set s=""
if $data(x1) set s=x1
if $data(x2) set s=s_", "_x2
if $data(x3) set s=s_", "_x3
.
.
.
The result in s will look like:
ptname.ptid, ptname.name ...
or:
, demographic.doc, ptname.name ...
That is, it will either begin with ptname.ptid or a comma followed
by a blank (it might be empty if no boxes were checked - this is an
error condition). If it begins with a comma-blank, chop them off:
if $extract(s,1)="," set s=$extract(s,2,255)
Then:
set s="select "_s_"from "
Then add table names:
set t=""
if $find(s,"demographic") set t="demographic"
if $find(s,"ptname") set t=t_", ptname"
if $find(s,"address") set t=t_", address"
if $extract(t,1)="," set t=$extract(t,2,255)
set s=s_t_" where "_XXX_"; "
Where XXX is a string with the "tablename.ptid=value AND" sequence.
Be sure ptid exists [$data(ptid)]. the string should s should be ready to
send to the sever:
sql/o="/tmp/"_$job_".file" &~s~
If there are no errors, you can use the program rsltprint.mps (from
http://math-cs.cns.uni.edu/~okane/114/pgsql-mps.html)
to print the resulting table to the browser (it builds nice
html tables). Be sure you've made the connection and done
teh Content-type .. thing. Note: you may be able to
use a connection to my server as shown in the link.
People say it works from off-campus. Otherwise,
connect to your own server - you only need to
specify the dbname=medical part.
The languages of instruction will be SQL and C. The course
will cover the SQL language. You may write your assignments in other
languages, if you wish, if they support access to PostgreSQL API's.
Software Needed
A host system.
The CNS Labs will have both Linux and Cygwin installed this fall.
You may need a flash drive to store your files.
Ubuntu (try installing it in VirtualBox).
Be sure LAMP, PostgreSQL and either C or Java are installed.
Mac OSX: You will need to install a web server, PostgreSQL and a development language, either C/C++ or Java.
Write a small hospital information system using PostgreSQL (or MySQL),
Apache and server-side scripts. Design the system to
construct appropriate queries to implement information
access by minimally computer trained staff.
Design web pages to enter queries and display results
Functions:
Admissions and Discharge
Lab reporting and data entry
Lab result entry / update
Lab results by patient
Medications reporting and data entry - similar to labs
Diagnosis reporting and data entry - similar to labs
Vital signs reporting and data entry
temperatures
blood pressures
Reports:
For a given patient id, a display of all their data.
For each diagnosis, a list of medications and lab results.
Average systolic BP, diastolic BP, glucose, HCT and HGB for each patient and for all patients.
Running PostgreSQL, Apache and Mumps
Installing and running PostgreSQL on Ubuntu and Cygwin:
Click Here
Building a test data base in PostgreSQL: Click Here
Installing and running Apache on Cygwin: Click Here
Installing and running Apache on Ubuntu: Click Here
"The Americans with Disabilities Act of 1990 (ADA)
provides protection from illegal discrimination for
qualified individuals with disabilities. Students
requesting instructional accommodations due to
disabilities must arrange for such accommodation
through the Office of Disability Services. The ODS
is located at: 103 Student Health Center, and the
phone number is: 273-2676."
Because the Office of Disability Services has procedures in place
to determine the validity of disability claims as well as the need
for instructional accommodations, faculty are reminded that they are
to direct all students with accommodation requests to the above listed
office.
UNDER NO CIRCUMSTANCE SHOULD A FACULTY MEMBER MAKE AN ACCOMMODATION
INDEPENDENT OF THE OFFICE OF DISABILITY SERVICES.
Questions may be directed to: Disability Services
Coordinator, at 273-2676 or to this office at 273-2846.
