Rental Store Application - PowerPoint

Document Sample
Rental Store Application - PowerPoint Powered By Docstoc
					                 CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

          Final Project
Video Rental Store Database for PDA

           TackSoo Im

     CS 8628, Summer 2003
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Windows 2000 Professional Edition
  – Includes ODBC support

• Sybase SQL Anywhere 8
   – Mobilink (for synchronization)
   – Ultralite (remote database, schema file)
   – Interactive SQL (access to consolidated database)

• AppForge MobileVB
   – Database application development (FrontEnd)
•   I used the trial version of Sybase and AppForge. It was not a pleasant experience using commercial tool that are
    available as a trial. MobileVB has a restriction that is very annoying. (2-day limit)
                                                                       Project Description
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• A simplistic implementation of a video rental store

• Stores the customer, tapes, and rental tables on the
  PDA so that the video store manager can update the
  database of his store without booting up the
  consolidated database.

• Needs more features to be useful.

•   My video rental store is a “toy app”. More features are needed to make it useful.
                                                                    Sequence of Tasks 1
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

    • The Database Design – Due to time and resource
      constraints, I had to come up with a simple database.
      There are only three tables in my database. Foreign
      keys were implemented as integrity constraints.

    • The Consolidated Database – Originally tried to used
      Oracle or MS SQL. Using Sybase’s database format
      turned out to be an easier solution.

•     Making the consolidated database is one of the more easier aspects of the project. Knowledge of SQL is
      required to make a database but it is simple compared to database application development using various tools.
                                                                      Sequence of Tasks 2
                                                            CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• ODBC Setup – One of the easier parts of the process.
  Windows ODBC tool makes it easy to set an ODBC data
  source. Unlike Access, Sybase’s database requires login
  and password.

• The Remote Database – A schema file is needed to
  create a remote database. The Ultralite Schema Painter
  or the “dbinit” tool can be used to create the schema
  file. The “dbinit” tool only applies to the Sybase
  database format.

•   Sybase’s database has a built-in security feature. It restricts database access by login and password. It may be
    useful in an enterprise but it was an annoying functionality in my project.
•   “dbinit” is a handy tool to create the consolidated database itself and the schema file. There are a lot of other
    command line tools provided by Sybase SQL Anywhere 8.
                                                                   Sequence of Tasks 3
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Adding Publication – Publications refer to tables that will
  be synchronized. This can be set using the the Schema

• Synchronization scripts and subscription – The default
  synchronization scripts and subscription are good
  enough for my project. This is one of the more complex
  part of setting up the synchronization process. The SQL
  Studio Help file contains detailed description of this

•   Adding publication, subscription and synchronization scripts is very complex and time consuming. The default
    was good enough so I used it. Sybase help files describes all the necessary details to do those tasks.
                                                                    Sequence of Tasks 4
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Application Development – AppForge MobileVB was
  used to cut development time.

• Testing – Application is tested on the Toshiba PDA.
  Mobilink needs to be started for synchronization to work.
  The “dbmlsrv8” command is used to start the
  synchronization server.

•   Testing the application on Visual Basic and on the PDA are two different tasks. My application runs smoothly on
    Visual Basic but runs quite slowly on the PDA. Another reason to have a PDA when you need to do development
    on it.
                                                                            E-R Diagram
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

             tape_id                                                              customer_id

                                  0:N                              0:N
           tapes                                   rents                            customers

             title                               rental_id
                                                                         customer_name           phone

•   A simple E-R diagram. Customer rents zero or more tapes. Tape is rented by zero or more customers. (there are
    more than one tape for each title.)
                                                             Logical Schema
                                               CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

      Customers(customer_id, customer_name, phone, birth_date, balance)

       Tapes(tape_id, title)

       Rental(rental_id, tape_id, rental_date, customer_id)

•   A simple logical schema
                                                                  Physical Schema (DDL)
                                                            CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes
•   CREATE TABLE "customers" (
•          "customer_id" integer primary key,
•          "customer_name" character varying(50) not null,
•          "phone" character(8) null,
•          "birth_date" date null,
•          "balance" decimal(7,2)
•   );

•   CREATE TABLE "tapes" (
•          "tape_id" character(8) primary key,
•          "title" character varying(80) not null
•   );

•   CREATE TABLE "rentals" (
•          "rental_id" integer primary key,
•          "tape_id" character(8) references tapes,
•          "rental_date" date not null,
•          "customer_id" integer references customers
•   );

•   A foreign key is used in the “rentals” table so that only a customer may rent a video. My application complains if
    a non-customer tries to rent a video. The primary key is required for each table. (Ultralite requirement)
                                                               Matrix: Forms vs. Tables
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• All tables have the CRD properties. (Create, Read, and

• The Update property can also be implemented. (Easy to

• Matrix is not very useful when there are only three
  tables. (the UI makes things intuitive)

•   The matrix is useful when many tables and forms are used. But my application is too simple to make it
                                                                        Publication Script
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Added Publication Script using Ultralite Schema Painter.
  It provides a easier and faster way to make the
  Publication Script.

• In Interactive SQL, it would be
• CREATE PUBLICATION publish_all (
     TABLE customers
     TABLE tapes
     TABLE rentals

•   Adding the publication is made simple by the Ultralite Schema Painter. I made the usm file with the “dbinit”
    command and used the Schema Painter to add the publication.
                                                                   Synchronization Script
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Used the default synchronization script available.

• Good enough for test application.

• Default behavior is to upload from the consolidated
  database and download from remote database.

• Small problem with default synchronization script.

•   You can use Sybase’s SQL syntax to add synchronization scripts. The process is described in the help files.
                                                                Difficulties Encountered 1
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Visual Basic is more lax compared to AppForge
  MobileVB’s “compile and validate”.

• Error messages from MobileVB and Sybase are cryptic
  and unhelpful.

• Two day trial limit on MobileVB compiled application.
  (using trial version)

•   Visual Basic is too lax compared to AppForge’s “compile and validate”. Visual Basic lets you run the code even if
    the code will not work in the PDA! So what happens is that the programmer may think that the program is
    correct until the “compile and validate” step is done.
•   The error messages are nearly useless.
                                                              Difficulties Encountered 2
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• MobileVB is too slow. (performance doubtful when
  hundreds or thousands of records are involved.)

• Annoying Path Problem – The paths (for the usm and
  udb files) must be different for testing on the desktop
  and testing on the PDA.

• Dependencies Problem – Need to specify the usm file
  before deploying it.

•   Speed is a serious issues when it comes to PDA applications. The speed of my program is partially my fault but
    after seeing some other people’s MobileVB application, I come to a conclusion that MobileVB is slow.
                                                                        Screen Snapshot
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

•   The “customers”, “tapes”, and “rentals” buttons on the top makes the user change the table that is being
                                                                             Code Sample
                                                           CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

•   Public Sub Form_Load()
•     Dim conn_parms As String
•     Dim open_parms As String
•     Dim schema_parms As String                                                          \videorental\movies.udb
•       conn_parms = "uid=DBA;pwd=SQL"
•       open_parms = conn_parms & ";" & "FILE_NAME=movies.udb"
•       schema_parms = open_parms & ";" & _
•         "SCHEMA_FILE=movies.usm"
•       On Error Resume Next
•       ….
•       ….                                                  \videorental\movies.usm


•   The “weird” path problem is illustrated here. Different paths are required for compilation on the desktop and
    PDA. The PDA requires a folder that contains the usm and udb file. The desktop does not require the folder.
                                                          CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

• Implementing a database application for the PDA is
  challenging using AppForge’s MobileVB and Sybase
  Ultralite, Mobilink and Interactive SQL.

• The process is very complex and prone to errors.

• An integrated solution for building these kinds of
  application is needed.

•   The cost is also an issue. MobileVB alone costs hundreds of dollars. Sybase SQL Anywhere is also quite
    expensive. Developers who are not wealthy might want to look for other tools.
                                CS 8628 – n-tier Client-ServerArchitectures, Dr. Guimaraes

•   1.   AppForge Tutorials
•   2.   Sybase Help Files and Tutorials
•   3.   PostgreSQL by Douglas Chapter 2
•   4.   Sybase sample program “custdb”
•   5.   Dr. Guimaraes’s Lecture Notes

•   Reference that were used.

Shared By:
Description: Rental Store Application document sample