810:114 Data Base Management Systems

081:114G Fall 2009 Data Base Management Systems(3 hours)
Last updated: August 12, 2009

Requirements
Assignments: 35%
Participation & Quizes: 10%
Tests (2 @ 15% each) 30%
Final: 15%
Project: 10%
- Participation means attending class and participating in discussions.
- Quizes means surprize quizes given at my whim. Minimum grade of 50% if present to take a quiz.
- 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 be sure your printer has ink, your dog is fed, you have an adequate supply of paper and that your car has sufficient gasoline to make it to campus.
- 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 request me to provide class summaries.
- Assignment submissions:
  - On-time and the beginning of class. Late assignments not accepted witout verified excuse.
  - 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.
  - Assignments are to be written in the language assigned. No Klingon, please.
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.
If any work is submitted which has been copied from an Internet source without attribution, a final grade of F will be entered, you will not be permitted to attend classes, and a disciplinary letter requesting further punishment will be filed with the Department, Dean and Provost.
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 to 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 normally 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 Wed Oct 7, 2009

Test 2 Fri Nov 20, 2009

Supplemental Questions Due Wed Oct 14 at 10 am

810:114-01 Fall 2009

Each answer must be explained in full as to how your
SQL query satisfies the request. Your explanation must
be detailed and show how your query solves the problem.
Incomplete explanations will be scored as errors.

All submissions must be typed and stapled.

1. Given the database tables:

      MovieStar(name, address, gender, birthdate)

            Ben Jones,    Hollywood,     male,   1970-10-28
            Mary Smith,   Beverly Hills, female, 1980-5-10
            John Affleck, New York,      male,   1950-2-11
            Bill Hanks,   Seattle,       male,   1960-5-5
            Liz Judd,     San Diego,     female  1990-4-6           
      
      MovieExec(name, address, company, salary)

            Ben Jones,   Hollywood,   MGM,  $500k
            John Huston, Forest Lawn, WB,   $250K
            Mike Zanuck, Hollywood,   MGM,  $350K
 
      Movies(title, year, length, genre, studio)

            Star Wars,    1977, 2hrs,   SciFi,   MGM
            Moulin Rouge, 1938, 2hrs,   mystery, RKO
            Moulin Rouge, 1998, 1.5hrs, mystery, MGM
            Conan,        1980, 2 hrs,  fantasy, FOX

   Where some movie stars are also movie executives. 

   a. Write the SQL query to list those people who are only 
      movie stars or only exceutives (i.e., not dual-role). 

   b. Some movie titles are reused (remakes). Write the SQL
      query to list each movie title and year made but, in 
      the case of remakes (same title), list only the earliest 
      production.

2. Given the database tables:

      Printer(model, color, type, price)

            HP123,     beige, inkjet, 125
            Epson 100, gray,  inkjet, 100
            Lexmark 5, black, inkjet, 75

      PC(model, speed, ram, hd, price)

            Dell 111, 2, 2, 500, 300
            Acer 555, 3, 4, 250, 350
            HP   355, 4, 3, 160, 325

      Laptop(model, speed, ram, hd, screen, price)

            Dell A12,     1.6, 1, 100, 15, 400
            Emachines 22, 1.8, 2, 150, 15, 450
            HP 1234,      2.5, 4, 300, 16, 800
            Acer 44,      2.0, 3, 200, 15, 400

    Write and explain the SQL queries to:

    a. Find the makers of PCs with a speed of at least 3.0
    b. Find the printer with the highest price
    c. Find the laptops whose speed is slower than that of
       any PC.

Test 2 Fri Nov 20, 2009

Objective
To understand data base systems
Book:
Database Systems: A Practical Approach to Design, Implementation and Management (5th Edition) (Paperback) by Thomas M. Connolly (Author), Carolyn E. Begg (Author)
Publisher: Addison Wesley; 5 edition (March 6, 2009) ISBN-10: 0321523067 ISBN-13: 978-0321523068
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 Systems: MySql, PostgreSQL
Project - Click Here
Assignments
1. Using the examples in the screen caps of item six from LAMP-notes.html, modify the code so that it will retrieve and display the 'financial' table information for patient 1003. What to turn in:
  1. HTML code for initial screen
  2. Screen shots of the initial browser screen and the final result screen
  3. PHP code
  Due: Fri Sept 11.
2. Do Exercise 5.8 on page 130 of the book. Due: Fri Oct 2.
3. Exercise 12.10 on page 347. Due Fri Oct 30.
4. Do Exercise 14.15 on page 391. Due: Mon Nov 9.
5. PHP assignment - distributed by email. Due Mon Nov 29 Sample code:
  isr1.php;
  simdoc.php;
  SQL commands for a document database (SQL.rar);
  Notes on document database
Software Needed
If you want to use your own PC-based machine, get Ubuntu and install PostgreSQL. Ubuntu can be installed as either (1) separate partition dual boot mode; (2) NTFS resident dual boot (WUBI); or (3) using Sun's free Virtual Box under Windows (easiest option). If you have an Apple, install PostgreSQL. If you have no machine you can use, accounts on one of my servers will be available. A demonstration of installing the Sun Virtual Box, Linux and LAMP (Linux, Apache, MySQL and PHP) will be given. You may bring your laptop to these sessions.
Tutorial and Example Pages
Links
Running PostgreSQL, Apache and Mumps
1. Installing and running PostgreSQL on Ubuntu and Cygwin: Click Here
2. Building a test data base in PostgreSQL: Click Here
3. Installing and running Apache on Cygwin: Click Here
4. Installing and running Apache on Ubuntu: Click Here
5. A hierarchical scripting language: Introduction to Mumps
6. Mumps scripts under Apache and PostgreSQL Click Here
7. Consolidated Mumps/Sql notes: http://math-cs.cns.uni.edu/~okane/114/MumpsSql.html
8. Running a C program that talks to the PostgreSQL server Click Here
Brief overview of HTML FORM tag Click Here
File operations in C

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