Bookstore Operations Management and Maintenance Application

1
Bookstore Operations Management and Maintenance
Application
KyungSoo Im
CS 8628, Summer 2003
2
Project Goals

• Write an application to manage the operations of
  a bookstore and facilitate the maintenance of
  data
• Application to be deployed to a PDA
• Use concepts learned in class
• Client / Server Database
• Application shall manipulate data (view, insert,
  delete) in its remote database and any changes in
  data will successfully synchronize with the data
  in the consolidated database

• These are the goals for this project.

3
Sequence of Tasks

• Create E-R Diagrams
• Create Logical Schema
• Create Consolidated DB (Adaptive Server
  Anywheres Reference DB)
• Add ODBC Data Source for Consolidated DB
• Create Tables and populate Consolidated DB with
  data
• Create Ultralite Schema File
• Add Publications
• Use default synchronization scripts
• Start Mobilink synchronization server
• Start Mobilink synchronization client from
  program
• After synchronizations, all rows from
  consolidated DB will be downloaded into the
  remote Ultralite DB, which is specified within
  the program.

4
Project Description

• Manages the operations of a bookstore and
  maintain its data
• Application Functions
• Book Inventory (View, Insert, Delete, Search)
• Customer Information (View, Insert, Delete,
  Search)
• Process Transaction (Customer buys a book)
• Sales History (View)
• Synchronize button to synchronize data between
  consolidated and remote database

• The application has four major functions Book
  Inventory, Customer Information, Process
  Transaction, and Sales History.
• Book Inventory will let the user navigate through
  the book listings, insert and delete an entry,
  and search by its ISBN, which is the primary key.
• Customer Information has the same functionality
  of Book Inventory but for data from the customer
  table.
• Process Transaction allows a customer to buy a
  book. Customer ID and ISBN of the book are
  required to purchase a book.
• Sales History lists the books bought by customers.

5
E-R Diagram
6
Logical Schema
7
Physical Schema (DDL)

• create table Customer (
• cust_id integer not null primary key,
• cust_name varchar(30),
• cust_address varchar(50)
• )
• create table Books (
• ISBN integer not null primary key,
• title varchar(50),
• author varchar(30),
• price decimal(7,2),
• booktype varchar(10)
• )
• create table Purchases (
• order_id integer not null primary key,
• cust_id integer not null,
• ISBN integer not null,
• foreign key (cust_id) references Customer
  (cust_id),

8
Publication Script

• Publications can be added with the following SQL
  statement
• CREATE PUBLICATION publish_all (
• TABLE books
• TABLE customer
• TABLE purchases
• )
• Publications can also be added by using the
  UltraLite Schema Painter, which is the method
  used for this project.

• Publication is a database object on the remote
  database that identifies data to be synchronized.
  A publication describes which tables and/or
  columns to be synchronized.
• A publication can be added by the SQL statement
  shown here or can also be added through UltraLite
  Schema Painter.

9
Synchronization Script

• Used Default Synchronization Scripts
• Download_cursor
• SELECT "cust_id", "cust_name", "cust_address"
  FROM "Customer
• Upload_cursor
• SELECT "cust_id", "cust_name", "cust_address"
  FROM "Customer" WHERE "cust_id" ?
• Upload_insert
• INSERT INTO "Customer" ( "cust_id", "cust_name",
  "cust_address" ) VALUES ( ?, ?, ? )
• Upload_delete
• DELETE FROM "Customer" WHERE "cust_id" ?
• Upload_update
• UPDATE "Customer" SET "cust_name" ?,
  "cust_address" ? WHERE "cust_id" ?

• The synchronization script here is for the
  Customer table.
• Synchronization scripts for the Books table and
  Purchases table follows this same script,
  except with their respective attributes and
  primary keys.

10
Screen Snapshot 1

• These screen snapshots were captured while
  running the application on desktop.
• The application can successfully run on a PDA but
  since I have no way to capture a PDA screen, I
  chose to capture the screens on desktop.
• The first one is the main screen of the
  application.
• The second one shows how user can view data
  through Next and Prev buttons.
• The third one inserts a new book into the remote
  database.

11
Screen Snapshot 2

• The first one is a snapshot of how user can view
  data through Next and Prev buttons.
• The search function only works if you search by
  the primary key (customer ID).
• The second one shows the screen when a user wants
  to insert a new customer into the remote database.

12
Screen Snapshot 3

• The first snapshot shows the screen when user
  presses Process Transaction button.
• This screen allows a user to input the orderID,
  customerID, and ISBN, so that the customer can
  check out the specified book. User may press the
  Confirm button to make sure the customer name and
  book description are really correct.
• The second snapshot simply shows the sales
  history. (By Querying the Purchases Table)

13
Screen Snapshot 4

• This two snapshots show the updated consolidated
  database tables after synchronization has
  completed.
• Notice in table Customer, Terry Parish (example
  customer inserted in application) was inserted.
• In table Purchases, order ID 103 (example order
  purchased in application) was inserted.

14
Screen Snapshot 5

• Shows the newly updated consolidated database
  after synchronization.
• Notice that An Insert Example book that was
  inserted using the application has been updated
  in the consolidated database.

15
Code Sample 1

• Set Connection
• conn_parms "uidDBApwdSQL
• open_parms conn_parms ""
  "FILE_NAME\BookStore\bookstore.udb
• schema_parms open_parms ""
  "SCHEMA_FILE\BookStore\bookstore.usm
• Set Connection DatabaseMgr.OpenConnection(open_p
  arms)
• Set Connection DatabaseMgr.CreateDatabase(schema
  _parms)
• Open Tables
• Set TCustomer Connection.GetTable("Customer
  ")
• TCustomer.Open
• TCustomer.MoveBeforeFirst
• If Err.Number ltgt ULSQLCode.ulSQLE_NOERROR
  Then
• MsgBox Err.Description
• End If
• Set TBooks Connection.GetTable("Books")
• TBooks.Open
• TBooks.MoveBeforeFirst

16
Code Sample 2

• Insert values into table.
• Private Sub btnInsert_Click()
• Do not insert if there is any null values
• If Len(ISBN.Text) 0 Or Len(Title.Text) 0
  _
• Or Len(Author.Text) 0 Or _
• Len(Price.Text) 0 Or Len(BookType.Text)
  0 Then
• MsgBox "One or More Field is Missing"
• Exit Sub
• End If
• On Error GoTo InsertError
• TBooks.InsertBegin
• TBooks.Column("ISBN").StringValue field1
• TBooks.Column("title").StringValue field2
• TBooks.Column("author").StringValue field3
• TBooks.Column("price").StringValue field4
• TBooks.Column("booktype").StringValue
  field5
• TBooks.Insert

17
Code Sample 3

• Delete current entry
• Private Sub Delete_Click()
• TBooks.FindBegin
• TBooks.Column("ISBN").StringValue ISBN.Text
• iMsgBoxResult MsgBox("Delete This Entry?",
  vbYesNo)
• If iMsgBoxResult vbYes Then
• If TBooks.FindFirst Then
• TBooks.Delete
• Connection.Commit
• End If
• Else
• Unload Me
• Books.initializeForm
• Books.Show
• End If
• DisplayCurrentRow

18
Code Sample 4

• Search Function
• Private Sub Go_Click()
• 'Search Button
• TBooks.FindBegin
• TBooks.Column("ISBN").StringValue
  BookSearch.Text
• TBooks.FindFirst
• ISBN.Text _
• TBooks.Column("ISBN").StringValue
• Title.Text _
• TBooks.Column("Title").StringValue
• Author.Text _
• TBooks.Column("Author").StringValue
• Price.Text _
• "" TBooks.Column("Price").StringValue
• BookType.Text _
• TBooks.Column("BookType").StringValue

19
Code Sample 5

• Synchronization Button
• Private Sub btnSync_Click()
• With Connection.SyncParms
• .UserName "afsample"
• .Stream ULStreamType.ulTCPIP
• .Version "ul_default"
• .SendColumnNames True
• End With
• Connection.Synchronize
• End Sub

20
Conclusion

• Successfully deployed application to PDA and all
  functions perform as intended.
• All goals of project met.
• Further development may include
• Implement the disjoint tables as shown in ER
  Diagram. (These three disjoint tables were not
  part of this project)
• Have a more detailed sales report, where you can
  select on each row to view all relevant
  information such as customer name, address, book
  title, author, price, and sale date.
• Have an automated / pre-assigned order_ID so user
  does not need to type in order ID every time a
  book is purchased.
• Write a more detailed synchronization script that
  will download deleted rows from the consolidated
  database.