Link to Syllabus

BUS 112: 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
Section 1: Tuesday, December 16 @ 9:45 AM
Section 2: Tuesday, December 16 @ 5:15 PM

In-Class SQL Practice on Dec. 9

Rob & Coronel Chapters since the Midterm

Chapter 6 (please read all)
Chapter 7 (skip subsections 7.2.2; 7.2.3; 7.3.1; 7.5.1 through 7.5.4; 7.5.7; 7.8.2
Chapter 8 (skip from page 305 to end of chapter)
Chapter 10 (please read all)

Adamski (Access) Tutorials since the Midterm

Tutorials 5-8

(Although we discussed Chapter 13: Data Warehousing, and I demonstrated Access switchboards, these topics won't be on the final exam.)

Learn to Create a Switchboard
YouTube Switchboard Video Tutorial

Chapter 10 PowerPoint Slides

DATABASE  PROJECT
Due Date & Time: Beginning of class on Dec. 9
You may work on this project alone or in a team of up to 3 students

Valley University (VU) needs a database to for its student internships program and has requested you to design and build one. Currently, VU maintains all its internship-related data in paper-based files, as described below.

·     An organization may offer one or more internships in different categories, such as finance, marketing, information systems, healthcare, law, social services, etc. Organizations typically provide the following information about each internship: internship title (e.g., programmer, legal aid, financial analyst, business analyst, etc.); description (brief description such as “write programs in Java”, “assist the CFO”, etc.); hourly rate; preferred major (e.g., marketing, finance, MIS, law, etc.); minimum academic standing (junior, senior etc.).

·     VU maintains at least the following information about each organization that offers an internship: organization name and address; contact person’s name and phone.

·     Once a student is accepted for an internship, VU maintains the following data about student and her/his internship: student name, major, phone, address, social security number, internship start date, internship end date, and performance (e.g., good, fair, poor).

In addition to maintaining necessary data in tables, the internship database should have the following features:

·         Form with a Subform: Students should be able to browse for available internships by organization. Create a form with a subform to display the internships by organization information. The main form should contain the following:
(1) In addition to the necessary organization information, the main form should contain two calculated fields: (a) to display total number of internships offered by the organization, and (b) to display average hourly rate for internships offered by the organization. (Please see an example of similar calculated fields on pages ACC 306-310.)
(2) The main form should also contain a combo box so students can select an organization from the drop-down list and display all internships for that organization in the subform (see an example on pages AC299-301).
(3) Display current date in the top left corner of main form.
(4) Please show name/s of your team members in the main form’s header section.

·         Report: VU likes to print out available internships by category. Create a report such as the following (this is only an example: it shows sample data for part of the report – your data, and report may be different). Note that the report contains current date and names of the report’s authors.

Please do the following:

·        **  Use MS Access 2007 to create the internships database. Please follow all of the requirements for selecting primary and foreign keys and appropriate data types for the fields. Names for the database, database objects, and fields should adhere to the naming conventions recommended in the Access book. Be sure that the tables are normalized to 3NF.

**  **  Create necessary relationships and enforce referential integrity. Generate a relationships report using the Database Tools from the menu. Include your name/s in the top left corner of the report and print out the relationship report (see page AC 267 for instructions).

        **  Enter fictitious data in all of the tables. In addition to other data, please enter your actual full name/s in the student table (data other than your name/s should be fictitious). Also include your name/s as contact persons for some of the internships. The database should contain at least 5 organizations and for each organization, there should be three or more internships. Also, there should be at least 5 categories of internship and one or more internships in each category. Print out datasheets for all of the tables.

**  Create and print out (in form view) the internships by organization form. (This form is described above.)

**  Create and print out (in report view) the internships by category report. (This report is described and a sample shown above.)

**  Use MS Word to prepare and print a cover sheet. Include your full name/s, Student IDs, BUS 112 Section (1 or 2), and submission date. Submit the printouts arranged and stapled in the following order: the cover sheet; relationship report; table datasheets; internships by organization form; internships by category report.

In Class SQL Practice - Part 1
In Class SQL Practice - Part 2

Homework Assignment #5
Due Date & Time: beginning of class, Tuesday, November 18

1. Read and do all of the steps described in the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 5 (pages AC 251-253) in the Access book. Print out the following:
   (a) After completing step 6 (before closing the query, qryTeacherNames), please print out the query datasheet.
   (b) After completing step 7 (before closing the query, qryLessonTypeParameter), please print out the query datasheet.
    

2. For the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 6 (pages AC 324-325), please do the following:
   (a) After completing step 5, switch to Form view and print out the frmLessonsByTeacherMultipleItems form.
   (b) After completing step 7(c), switch to Form view and print out the frmContract form.

3. Use MS Word to prepare and print a cover sheet. Include your full name, BUS 112 Section 1 or 2, and assignment #5 on the cover sheet. Submit the FOUR printouts along with coversheet arranged and stapled in the order as listed/highlighted above.

Please download this SaleCo.txt file to work with SQL practice/examples in the lab
(SaleCo.txt is a script to create SaleCo database in Oracle)

Here is the SaleCo database in Access format

Posted on: October 31, 2008
In Class SQL Practice - Part 1

Midterm Exam - Tuesday, October 21
Rob & Coronel: Chapters 1-5
MS Access (Adamski) book: Tutorials 1-4

PowerPoint Slides
Chapters 1-4
Chapters 5-8
Chapter 3 (modified)
Chapter 5 (additional)

Posted on: October 4, 2008
Homework Assignment #4
Due Date & Time: beginning of class, Tuesday, October 14

For each of the following two tables, indicate its current normal form and convert it into a set of 3NF tables. If any of the the 3NF tables that you create does not have a primary key column in the given list of columns, please include one yourself - thus every table must have a primary key. Underline the primary key in each table. Use MS Word to type your full name and answers to these questions.

1. The following table is for the database of a large bookstore. Use your knowledge of books and bookstores to answer the question.
   BOOK (ISBN, Book-Title, Author-First-Name, Author-Last-Name, Year-of-Publication, Book-Price, Author's-Book-Payment)
    

2. The following table is for a college. Some classes meet in more than one room (for example, a class may meet in the classroom on Mondays and in the lab on Wednesdays).
   CLASS (Class-Code, Instructor-ID, Class-Title, Room#, Instructor-FirstName, Instructor-LastName, Room-Capacity, Class-Meeting-Day, Class-Meeting-Time)

Posted on: September 27, 2008
Homework Assignment #3
Due Date & Time: beginning of class, Tuesday, October 14

This homework is a continuation of homework #1 in which you completed Case 1 at the end of Tutorials 1 & 2. For this homework, please do the following:

1. Read and do all of the steps described in the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 3 (pages AC 146-147) in the Access book. Print the query datasheet for Current Guitar Lessons query after step 12.

2. For the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 4 (pages AC 191-192), please do the following after step 1:
   Modify the first name and last name in the Teacher table for the teacher whose ID  is 34-4506 to your own first name and last name. Print the following:
   (a) After step 9, display the form/subform for Teacher whose ID is 34-4506 and print that one page only.
   (b) Print only the first page of the report as described in step 15.

3. You may print on both sides of a sheet, if you can - otherwise print on one side only.

4. Use MS Word to prepare and print a cover sheet. Include your full name, BUS 112 Section 1 or 2, and assignment #3 on the cover sheet. Submit the THREE printouts along with coversheet arranged and stapled in the order as listed/highlighted above.

Posted on: September 23, 2008
Homework Assignment #2
Due Date & Time: beginning of class, Tuesday, October 7

• Read and practice with the Visio tutorial on how to draw entity-relationship diagrams (ERD). This tutorial was demonstrated in class and you can download it using this link.

• Use the crow's foot methodology in Visio to draw an ERD for Problem #3 as described on p. 137 of the Rob & Coronel textbook.
  Please be sure to:

• Convert many-many relationships into 1-many relationships before drawing the diagram.

• Show entities, attributes, relationships, and minimum and maximum connectivities/cardinalities.

• Every entity must have a primary key. Primary key attributes are typically named as ID, #, or code. (Customer_ID,  Class_Code, Course#, VIN, ISBN, etc. are some examples of primary key attributes.)

• Include foreign key columns in entities as necessary.

• Show only the primary and foreign key attributes and other essential attributes including the following: names of employees, departments, and divisions; e-mail and phone number of employees; dates and hours an employee worked on a project).

• Let Visio write name (verb phrase) of the relationship above each relationship's line.

• Include your full name and 'Assignment #2', using the text tool from the Visio menu bar.

• Please submit one single-page printout.

  Steps Suggested for Drawing an ER Diagram 1. Identify entities. Also identify attributes of interest for each entity. 2. Identify relationships and relationship cardinalities between entities. 3. Convert many-many relationships into one-many relationships by adding composite entities. 4. Draw entities and their relationships. For each entity, show its attributes and indicate which attribute (or attributes) will serve as its primary key.

Posted: September 12, 2008
Homework Assignment #1
Due Date & Time: Tuesday, September 23, beginning of class
 

This is not a homework, but a reminder: Please do all of the hands-on work in the Tutorials. So far, I have demonstrated Tutorials 1 and 2 in class and students have also practiced some of it in the lab. Reading the tutorials and doing hands-on work described there is essentials for you to learn Access, be able to do your Access homework and project, and do well in Access exams.

Here is the homework:

1. Read and do all of the steps described in the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 1 (pages AC 38-39) in the Access book. This is one of the running cases - i.e., this case appears at the end of all tutorials. It is therefore important that you understand the case and follow its progress. Also be sure to keep a digital copy of all the work you do since each future homework will be a continuation of the previous homework. Print the report as instructed in step 10.

2. Read and do all of the steps described in the Pine Hill Music School case (Case Problem 1) at the end of Tutorial 2 (pages AC 89-91) in the Access book. Print out the following:
   (a) In step 15, before closing the Student table, print first page of the table in portrait layout.
   (b) In step 20, before closing the Contract table, print first page of the table in landscape layout.
   (c) After step 21, print the relationship report (see page AC 267 for printing instructions).

3. You may print on both sides of a sheet, if you can - otherwise print on one side only.

4. Use MS Word to prepare and print a cover sheet. Include your full name and assignment #1 on the cover sheet. Submit the printouts arranged and stapled in the following order: First the cover sheet and then printouts from steps 1, 2(a), 2(b), and 2(c) above.

Posted: September 12, 2008
Practice Homework from Rob & Coronel Book
(This homework is for practice only - nothing to turn in)

Please do the following problems in the Rob/Coronel textbook:

1. # 24 and #25 on pages 54-55.

2. #1 - #7 on pages 94-95. (For problems requiring E-R diagrams, draw free-hand diagrams. You'll soon learn to draw E-R diagrams using Visio. See the link to Visio tutorial below).

Tutorial on How to Use Visio to Draw E-R Diagrams

Posted: September 7, 2008
Microsoft Access Hands-on Practice in the Computer Lab, BBC 305
Section 1: Thursday, September 11, 12:00-1:15 pm
Section 2: Tuesday, September 9, 7:30-8:45 pm

As announced in class, we'll meet in the computer lab, BBC 305, according the above noted schedule to get students started with MS Access 2007. Please bring the following to the lab: (1) MS Access book (2) Level 1 Tutorial folder on a USB drive. Before coming to the lab, download student data files using this link: Level 1: Tutorials 1-4 and End of Chapter Cases. Unzip/extract the files and bring the Tutorial folder on a USB drive so you can work with Tutorial 1 in the lab. You may download the data files using your own computer or one of the computers in the open lab, BBC 302.

Posted: August 29, 2008
Microsoft Access Book & Modification in the Class Schedule

SJSU (Spartans) bookstore is expected to have the Microsoft Access (Adamski - Introductory) book available by September 2nd or 3rd. The Roberts bookstore on 10th Street had the book until recently but they're out of stock today (Aug. 29) and expect to have more copies available by September 3rd or 4th.

I have modified the class schedule so we'll work with Access starting the third week of the semester, instead of the second. Please take a look at the revised schedule on the Syllabus page of this website (see the link on the left or above). Modifications are in red font.