1 / 19

Final Project

Final Project. TackSoo Im. CS 8628, Summer 2003. Video Rental Store Database for PDA. 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)

burton
Download Presentation

Final Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Final Project TackSoo Im CS 8628, Summer 2003 Video Rental Store Database for PDA

  2. 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) Tools

  3. A simplistic implementation of a video rental store database. 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. Project Description

  4. 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 1

  5. 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 2

  6. Adding Publication – Publications refer to tables that will be synchronized. This can be set using the the Schema Painter. 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 topic. 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 3

  7. 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. Sequence of Tasks 4

  8. 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.) E-R Diagram tape_id customer_id birth_date 0:N 0:N tapes rents customers balance rental_id title customer_name phone

  9. A simple logical schema Logical Schema Customers(customer_id, customer_name, phone, birth_date, balance) Tapes(tape_id, title) Rental(rental_id, tape_id, rental_date, customer_id)

  10. 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) Physical Schema (DDL)

  11. All tables have the CRD properties. (Create, Read, and Delete) The Update property can also be implemented. (Easy to do) 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 necessary. Matrix: Forms vs. Tables

  12. 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. Publication Script

  13. 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. Synchronization Script

  14. 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 1

  15. 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. Difficulties Encountered 2

  16. The “customers”, “tapes”, and “rentals” buttons on the top makes the user change the table that is being viewed. Screen Snapshot

  17. Public Sub Form_Load() Dim conn_parms As String Dim open_parms As String Dim schema_parms As String 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 …. …. 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. Code Sample \videorental\movies.udb \videorental\movies.usm

  18. 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. Conclusion

  19. 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. References

More Related