DEPARTMENT OF MANAGEMENT AND MARKETING MKT 675: MARKETING APPLICATIONS IN eBUSINESS                           Syllabus    Schedule   Library

eBusiness Database Design Team Assignment
(50 points)

RATIONALE

Database technologies are moving to the forefront of organizational marketing functions.  Traditional approaches such as segmentation and positioning require information about customer characteristics and preferences.  More recent approaches such as database marketing, relationship marketing, data mining and micro marketing depend on the collection of detailed information about customers, their preferences and purchase behavior.  Personalization of content and product presentation are hallmarks of the most effective Ecommerce applications and are achieved through database system design.  

To understand and exploit the potential of these approaches, the modern marketing manager must have a solid grasp of the basic functions of database applications.  This assignment contains a series of exercises designed; 1) to develop students' competence in basic and intermediate skills with Microsoft Access, and 2) to develop students' competence in basic database design in support of eBusiness activities.  Specifically, you will design a system to record and analyze the operations of your eBusiness team.

Preparation for Database Design Assignment

The Database Applications Assignment for this class assumes that you have basic Microsoft Access skills and a general understanding of the approach to database design.  If you need to review these areas, you may consult your class materials for the CSC 199 and ACC 355 courses, both required courses in the College's business curricula.  If you did not take these classes or need to review these topics, you may do so using the following materials.

• For review of Microsoft Access skills, complete the Introductory Access modules in the InfoSource Training Modules provided by Murray State's Academic Computing and Technology Services Center. The USERID for this resource is student/learn and the PASSWORD is learn.
• For a general approach to database design, read John Paul Ashenfelter's Designing Good Databases,   Web Review, 26 March 1999.

NOTICE: NO CLASS TIME WILL BE DEDICATED TO INTRODUCTORY ACCESS INSTRUCTION, NOR WILL I ANSWER EMAIL QUESTIONS THAT ARE COVERED IN THE INTRODUCTORY TUTORIALS.  You will NOT have time to finish the tutorials during the week in which the first exercise is due.  You MUST complete them to be ready for that exercise.

Designing a Basic eBusiness Database System (50 points)

Part I: Creating Basic Database Objects

In this and the two following exercises, you will build a simplified order entry database for a your eBusiness with Microsoft Access.  While the final product will not be a complete, comprehensive system, it will provide a good orientation to the design of such a system.  In Part 1, you will build the basic database objects (tables, queries, forms, reports) for the system.  If necessary, you should complete the Introductory Access tutorial to help you with this exercise.

Using the appropriate object functions in Microsoft Access, build a database, 575FML.mdb (where FML are the initials of your first, middle and last names), with following objects. Populate each with the designated number of fictitious records. Primary key fields are designated with an asterisk and should be formatted as AutoNumber fields.  

All fields should be described in the design screen and should be assigned the appropriate data type.  Each table should have an appropriate field identified as the primary key.  

Part II: Designing a Multi-Table Query and Report 

In this exercise, you will build a multi-table query in Microsoft Access to accumulate order-related information from several of the tables you have created.  You will then design a report which presents the same information in a formal document.  To simplify the task, each order will consist of a single product, though the quantity may vary. In preparation for this exercise, you should complete the "Multi-Table Queries" lesson in the Intermediate Access tutorials provided by ACTS.

Using the Query Object function of Microsoft Access, design a multi-table query qryWebOrders in which you;

1. include tblWebOrders, tblCustomers, tblProducts and  tblTeamMembers in the query,
2. from the tblWebOrders table, include the fields for dbl Order#, datOrderDate, dblCustID#, dblMemID#, dblProdID#, curPrice, dblQuantity and curShippingCost.
3. from the tblCustomers table, include the fields for txtCustomerName (see above for expression) and txtEmai
4. from the tblProducts table, include the field for txtProd Name, and txtProdCost 
5. from the tblTeamMembers table, include the field txtMemberName (see above for expression) 
6. create a calculated field in which you calculate curExtendedPrice (curProdPrice * dbl Quantity)
7. create a calculated field in which you calculate curOrderTotal  ((curProdPrice * dblQuantity) + curShipping Cost)
8. create a calculated field in which you calculate curOrderCost  ((curProdCost * dbl Quantity) + curShipping Cost)
9. create a calculated field in which you calculate curOrderMargin (curOrderTotal - curOrderCost)
10. sort the records in the query Design view in descending order by curOrderTotal

Using the Report Wizard within the Report Object function of Microsoft Access, design a report rptWebOrderSummary based on the qryWebOrders query, in which you;

1. include fields for dblOrderID#, datOrderDate, dblCustID#, txtCustomerName, txtProdName, dblQuantity, curOrder Total and curOrderMargin
2. group records in the report by customer and calculate total and average Order Total for each customer.

Stylistically, 

• The report should have a prominent title which contains the company name and conveys the purpose of the report.
• Headings should be clearly presented, properly aligned and communicate the organization of the report. DO NOT USE VARIABLE NAMES FOR REPORT TITLES OR COLUMN HEADERS
• The report layout should use page space efficiently.

Part III: Designing an Order Entry Form 

In this exercise, you will design a form frmWebOrderEntry using the qryWebOrders query you developed in the previous exercise.  This form will allow a user such as a customer service representative to enter orders in real time.  This form would also be the prototype for a Web-based system which captures customer orders directly.   Again, for the sake of simplicity, orders are confined to a single product. In preparation for this exercise, you should complete the "Creating Forms" and "Customizing Forms" modules in the Intermediate Access interactive tutorials provided by ACTS.  The USERID for this resource is student/learn and the PASSWORD is learn.

Using the Form Object function in Microsoft Access, design a form frmWebOrderEntry based on the qryWebOrders you created in the last assignment,

1. add the dblOrder#, datOrderDate (set to a default value of the current date), dblCustID#, dblMemID#, dblProdID#, cur Price, dblQuantity and curShippingCost fields to the form as data entry fields with appropriate tab stops,
2. add txtCustomerName, txtMember Name, txtProdName, curExtendedPrice and curOrderTotal fields to the form, setting these fields without tab stops, (These fields will display the data corresponding to the their numbers or calculations, thus there is no need for data entry.)
3. Place an "Enter New Order" command button on the form which presents a blank order entry form when clicked.
4. Place  "Add Customer" and "Close Form" command buttons on the form. The "Add Customer" button should open the frmCustomers form you designed in Part I.  The "Close Form" button should close the form when clicked.

Stylistically,

• The form should occupy only one screen with each field clearly labeled and all labels and fields neatly and logically aligned.  
• The width of data fields should match their content.  
• Labels should convey the content of each field clearly and not be limited variable names. 
• The form should have a prominent title which contains the company name and conveys the purpose of the form.

Part IV: Designing a User Interface with Switchboards

You have created all the elements for an order entry system.  In this exercise, you will design a user interface which organizes this system using the Switchboard tools in MS Access.  To prepare for this exercise, you should review the Defining Switchboards lesson in the Advanced Access 97 tutorial system provided by ACTS.  You should review only sections 1-4 and 6.  You may also use the Switchboard Manager in Access 2000 to complete this exercise.  No tutorial is currently available for this tool, but you may use the Help function in Access 2000 to learn how to use it.

Using the Switchboard Manager or the Form Object function in MS Access, you should create a frmMainSwitchboard form which;

1. contains a graphic logo for the firm,
2. uses a background color consistent with the logo,
3. displays the firm's name and appropriate title in bold, large text,
4. contains separate command buttons to open the frmCustomers, frmAuctions, and frmOrderEntry forms,
5. contains separate command buttons to preview the rptCustomers,  rptAuctionOrders and rptWebOrderSummary reports, 
6. includes an "Exit Database" command button which closes the database system when clicked, and
7. is displayed as the primary database interface when a user opens the database (The Database Window should NOT be visible to the user.).

Stylistically,

• the graphic design of the form should be consistent and pleasing, with consistent colors, fonts and text styles,
• command buttons should be placed in a logical, organized pattern
• the function of each button should be very clear, either in the button title or a text label, and
• all buttons should function properly and guide users smoothly through the database system.

Submit your complete database system file either on diskette or through the Student Drop Box utility by 4:30 PM of the due date in the syllabus.  Web Students:  You may submit your database files through the Student Drop Box utility, which you will find in the Student Tools area of the course. 

Last updated 07 March, 2001 . İFred Miller, 2001
Send comments to Fred Miller