Link to Syllabus

BUS 116: Advanced Database Management Systems
Fall 2008

Class Announcements

Instructor: A. Shirani

Please verify your scores posted in Blackboard/WebCT and report any discrepancies to the instructor not later than one week after your graded work is returned in class. Scores may not be modified after that.

Final Examination
Thursday, December 18 @ 5:15 PM

PROJECT
Due Date & Time: Beginning of class on Dec. 4, 2008
One or two students may complete and submit a project

PROJECT OPTION 1: Write PL/SQL Code

1.     Please read Case 6-1 (Updating shopping basket data upon order completion) on page 228 in the Casteel textbook. Write necessary PL/SQL code for this case, spool program output to a file, and test/execute each program unit so an output is generated and saved in a file, which you will need to print out and submit.

Please be sure to internally document your program by including your name/s at the very beginning and a brief description before each program unit and before executing any procedures or functions. Fully debug your program before spooling for submission

Deliverables: (1) A stapled printout of the spooled code and its output – in class. (b) The script (text file) containing the program – please e-mail this to me. 

PROJECT OPTION 2: Write a Research Paper

RESEARCH TOPICS:

Please select either a or b: (a) Assignments/Projects #1 & #2 on page 78 (b) Assignment/Project #1 on page 216

RESEARCH REQUIREMENTS:

Please do necessary research to find and include in your paper relevant information in support of your answers, arguments, and assertions. Such research should include but not be limited to the sources cited in each project assignment. You should do additional search for relevant information available from legitimate and reliable published sources (see below). Your project grade will depend on how thoroughly you research and report on the topic, and whether the paper is formatted properly and contains little or no spelling or grammatical errors.

SOURCES OF INFORMATION:

Please use only reliable sources of information. Your references should primarily be peer-reviewed professional journals or other legitimate sources (e.g., government documents, agency manuals, reliable and relevant company and industry group websites). An excellent list of journals and online sources of information appears at the end of each chapter in Turban et al. textbook.

REFERENCES:

You must include a list of references at the end of your paper. Any quotes, statistics, tables, charts, etc. in your paper that are drawn from other sources must be cross-referenced in the text to the list of references. Any material that you interpret, summarize, or significantly alter in extracting it from the source material must also be cross-referenced. Failure to attribute such material to original sources constitutes plagiarism.

FORMATTING YOUR PAPER:

Please type – nothing on your paper should be hand-written. Use 12 point, Times New Roman font and one inch margin on all sides of your paper. Use the APA guidelines to format references and to cite them in the text of your paper. A good source for these guidelines is available at: http://owl.english.purdue.edu/workshops/hypertext/apa/index.html

SUBMISSION:

Add a cover sheet in front of your paper containing the following information: Topic (or topics) of the paper; your full name (or names of two students); student ID; your class (bus 116), and submission date. Staple and submit – do not submit in any kind of binders.

Posted: November 2, 2008
Homework Assignment #5
Due Date & Time: beginning of class on Thursday, Nov. 13

Introduction to MicroStrategy Business Intelligence
Creating Scorecards with the MicroStrategy Sales Force Analysis Module

The objective of this homework is to gain a hands-on introduction to business intelligence (BI) and learn to generate scorecards and reports using the MicroStrategy BI software.

• Please download the following two documents: Homework #5, and MicroStrategy Scripted Demo.

• Read instructions and do the hands-on work as described in the homework #5 document. You'll need to login to Teradata Student Network to access the hosted MicroStrategy software and data warehouse to do this work.

• Print out the scorecards and reports that you generate in the four tasks described in the homework document. Add a cover sheet with your name, date, and title of this assignment, staple, and submit.

Posted on: October 31, 2008
Business Intelligence PowerPoint Slides

Brewbean Coffee E-R Diagram
In-Class Exercises: Oct. 16
In-Class Exercises: Oct. 9
In-Class Exercises: Oct. 2
In-Class Exercises: September 25
In-Class Exercises: September 18
In-Class Exercises: September 4

Review Questions End of Chapters 1-6

As Discussed in Class
The Midterm Exam is Now Scheduled for October 23rd

Posted: October 13, 2008
Homework Assignment #4
Due Date & Time: beginning of class on Thursday, Oct. 23

Create and test an Oracle PL/SQL function that accepts a shopper ID and returns the total dollars the shopper has spent with the company. The function needs a formal parameter for the shopper ID and a variable to sum (and return) the total column of the BB_BASKET table in the Brewbean Coffee database. (Please make sure to refresh the database by executing the c6dbcreate.sql script in Oracle SQL*Plus or Oracle SQL Developer.)

Test the procedure by using it in an SQL statement to display every shopper's ID and her/his total purchases from the bb_shopper table in the database.

Posted: October 4, 2008
Homework Assignment #3
Due Date & Time: beginning of class on Thursday, Oct. 9 in class (or Oct. 13 in my office)

Create and test an Oracle PL/SQL procedure that returns the most recent order status for a particular shopping basket. It should determine the most recent stage of the order from the BB_BASKETSTATUS table in the Brewbean Coffee database. (Please make sure to refresh the database by executing the c5dbcreate.sql script in Oracle SQL*Plus or Oracle SQL Developer.) Use an IF statement to return description of the order stage. The IDSTAGE column of the BB_BASKETSTATUS table describes each stage as follows:

1 = Submitted and received
2 = Confirmed, processed, sent to shipping
3 = Shipped
4 = Cancelled
5 = Backordered

The procedure should accept a basket ID number and return the most recent status description. If no status is available for the basket queried, return a message stating that no status is available.

Test the procedure twice using basket ID 4 and then 6.

Posted: September 24, 2008
Homework Assignment #2
Due Date & Time: beginning of class on Thursday, October 2
 

1. Please do Assignment #3-8 on p. 109 in the textbook.

2. Start Oracle SQL*Plus, login, and refresh the Brewbean Coffee database by running the script named c3Dbcreate.sql (located in chapter 3 folder of the data files that you downloaded).

3. Execute the following command: set serveroutput on. Also set appropriate linesize and pagesize as necessary.

4. Write code for the given problem and spool the output so you can print it out and submit. Please be sure to include the following information as comments at the beginning: Your full name; Problem # (i.e., Assignment 3-8).

5. You should write the code in Notepad first and copy and paste in SQL*Plus to test it and correct any errors. Finally, clear the screen in SQL*Plus and spool the output using the final (correct) version of your code. Printout out the code and its output from the spooled text file and submit.

Posted: September 6, 2008
Homework #1
Due Date & Time: beginning of class on Thursday, Sep. 18

1. Use either SQL Developer or SQL*Plus to do this homework. If you use SQL*Plus, you'll need to spool the output to a file so you can later open and print it out. The example described in the next section shows you how to spool a program output.

2. Write and execute PL/SQL code for the following problems:  Assignments 2-3, 2-5, and 2-8 at the end of chapter 2 on page 71 of the textbook.

3. Include the following information as comments at the beginning of each problem: Your full name; Problem # (e.g., Assignment 2-3, Assignment 2-5, or Assignment 2-8).

4. You should write the code in Notepad first and copy and paste in SQL*Plus (or SQL Developer) to test it and correct any errors. Finally, clear the screen and execute the final (correct) version of your code. (Please do not submit any printouts that contain error messages.)

5. Use Notepad to open the spooled output file and print, staple, and submit it.

Posted: September 6, 2008
An Example of Spooling the Program Output

1. Use Notepad to write the program script (PL/SQL code). Save the script in a convenient location (e.g. in C:\). For this example, I have written a short PL/SQL script and saved it in C:\ drive as Example-Input.txt. I want to generate output from this program and save the output in a file called Example-Output.txt.
    

2. Login to Oracle SQL*Plus and enter the following commands.
   
       clear screen
       spool C:\Example-Output.txt
       set echo on
       set serveroutput on
       START C:\Example-Input.txt
       spool off
    

3. You should see the program output on screen. Also, this output should have been saved in the Example-Output file. Open the output file in Notepad and see what it contains.