Is311- Information Systems Fundamentals Tutorial-6

Name: ----------------------------------------------------------------- ID: -------------------------------------------

Section: --------------------------------------------------------------- Homework No.: --------------------------

Homework submission guidelines:

1. Write your name and ID number clearly at the top of the first page, along with the homework number, and the section number,

2. Homework is due in-class on the assigned due date.3. Late assignments will NOT be accepted.4. Assignment must be printed on A4 papers, NOT handwritten.5. Electronic submissions will not be accepted.6. Please staple together your sheets.7. No credit will be given to solutions obtained

Question 1:

What are the benefits and limitations of the relational database model for business applications today?

Benefits:

Most popular of the three main database structures. The data structures are logical and easy to understand. Provides significant flexibility over time.

Limitations:

Cannot process large amounts of business transactions as quickly and efficiently as the hierarchical and network models.

Question 2:

Why is the object-oriented database model gaining acceptance for developing applications and managing the hypermedia databases on business Web sites?

Object-oriented databases are able to handle complex types of data (graphics, pictures, voice, and text) better than other structures. They are also relatively easy for programmers to use.

Is311- Information Systems Fundamentals Tutorial-6

Question 3 : Use MS Access to solve the case study below

You have the responsibility for managing technical training classes within your organization. These classes fall into two general types: highly technical training and end user training. Software engineers sign up for the former and administrative staff sign up for the latter. Your supervisor measures your effectiveness in part according to the average cost per training hour and type of training. In short, your supervisor expects the best training for the least cost.

1. Training Cost Management Part 1

a. Using the data below, design and populate a table that includes basic training rate information. Designate the "Technical" field type as "Yes/No" (Boolean).

Provide a print screen for the design view of the Pricing Table. Provide a print screen for the datasheet view of the Pricing Table after filling in the data

Pricing Table

Technical Price per Day CapacityYes $2,680.00 15No $2,144.00 30

b. Using the data below, design and populate a course table. Designate the CourseID field as a "Primary Key" and allow your database to automatically generate a value for this field. Designate the "Technical" field type as "Yes/No" (Boolean).

Provide a print screen for the design view of the Course Table. Provide a print screen for the datasheet view of the Course Table after filling in the data

Course Table

Is311- Information Systems Fundamentals Tutorial-6

ID Course Name Duration Technical1 ASP Programming 5 Yes2 XML Programming 5 Yes3 PHP Programming 4 Yes4 Microsoft word-Advanced .5 No5 Microsoft Excel-Advanced .5 No

c. Prepare a query that lists each course name and its cost per day of training.

Provide a print screen for the design view of the query1

Provide a print screen for the result after pressing RUN !

Is311- Information Systems Fundamentals Tutorial-6

Problem 1 cCourseName PricePerDay

PHP Programming 2680XML Programming 2680ASP Programming 2680Microsoft Excel - Advanced 2144Microsoft Word - Advanced 2144

Training Cost Management Part 2

d. Using the information provided in the sample below, add a course schedule table to your training database. Designate the ScheduleID field as a "Primary Key" and allow your database program to automatically generate a value for this field. Make the CourseID field a number field and the StartDate field a date field.

Provide a print screen for the design view of the Class Schedule Table. Provide a print screen for the datasheet view of the Class Schedule Table after filling in the data

Class ScheduleSchedule ID Course ID Location Start Date Price per Day

1 1 101-A 12/07/2008 $2,680.002 1 101-A 19/07/2008 $2,680.003 1 101-B 19/07/2008 $2,680.004 4 101-A&B 26/07/2008 $2,144.005 5 101-A … B 2/08/2008 $2,144.00

e. Using the information provided in the sample below, add a class roster table to your training database. Make ScheduleID field a number field. Make the Reminder and Confirmed fields both "yes/no" (Boolean) fields.

Is311- Information Systems Fundamentals Tutorial-6

Provide a print screen for the design view of the Class Roster Table. Provide a print screen for the datasheet view of the Class Roster Table after filling in the data

class rosterID Participant e-mail Reminder Confirmed1 Linda adams adams.l@hotmail.com Yes Yes1 Fatima Ahmed Ahmed.f@hotmail.com Yes No1 Adam Alba Alba.A@hotmail.com Yes Yes4 Denys Alyea Alea.d@hotmail.com No No4 Kathy Bara bara.k@hotmail.com Yes No

f. Write a query that shows how many people have registered for each scheduled class. Include the class name, capacity, and date.

Provide a print screen for the design view of the query2

Provide a print screen for the result after pressing RUN !

Is311- Information Systems Fundamentals Tutorial-6

,

SELECT CourseTable.CourseName, PricingTable.Capacity, ClassSchedule.DateScheduled, Count(ClassRoster.ScheduleID) AS Attendees

FROM PricingTable INNER JOIN ((ClassSchedule INNER JOIN CourseTable ON ClassSchedule.CourseID = CourseTable.CourseID) INNER JOIN ClassRoster ON ClassSchedule.ScheduleID = ClassRoster.ScheduleID) ON PricingTable.Technical = CourseTable.Technical

GROUP BY CourseTable.CourseName, PricingTable.Capacity, ClassSchedule.DateScheduled;

The result :

Problem 2 eCourseName Capacity DateScheduled Attendees

ASP Programming 15 7/12/2004 3Microsoft Word - Advanced 30 7/26/2004 2