Employee Information Database Application with Project Assignment Manager

1
Employee Information Database Application with
Project Assignment Manager
Changsoo Im
CS 8628, Summer 2003
2
Sequence of Tasks

• Create Consolidated Database
• Create ODBC Data Source for Consolidated Database
• Create Tables and Insert Data in Consolidated
  Database
• Add publications for tables that need to be
  synchronized
• Use default synchronization scripts
• Start Mobilink Synchronization Server
• Start Mobilink Synchronization Client from within
  program
• Synchronize data All data from consolidated
  database will be downloaded into the remote
  database.
• The application knows how to download the data by
  looking at the UltraLite Schema file of the
  database

3
Project Description

• A database application to store data about a
  companys
• Department
• Employee
• Project
• Assigns employees to projects
• Data can be manipulated through add and delete
  buttons.
• Will synchronize data between consolidated
  database and remote database.

• My project is an application intended for use by
  a small company where companys data about
  department, employee, and projects are stored.
  Then employees can be assigned to projects.

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

• create table Department (
• dept_id integer not null primary key,
• dept_name varchar(50),
• dept_location varchar(50)
• )
• create table Employee (
• emp_ID integer not null primary key,
• emp_name varchar(50),
• emp_salary decimal(8,2),
• dept_ID integer not null,
• foreign key (dept_ID) references Department
  (dept_ID),
• )

7
Physical Schema (DDL) 2

• create table Project (
• proj_id integer not null primary key,
• proj_name varchar(50)
• )
• create table WorksOn (
• work_id integer not null primary key,
• emp_id integer not null,
• proj_id integer not null,
• foreign key (emp_id) references Employee
  (emp_id),
• foreign key (proj_id) references Project
  (proj_id)
• )

8
Publication/Synchronization Script

• Publication Script
• Added publication script for all tables in
  database
• Used UltraLite Schema Painter
• Syncrhonization Script
• Added synchronization script to all tables in
  database
• Used default synchronization scripts
• These scripts can be accessed (and add new
  scripts) through Sybase Central -gt Mobilink
  Synchronization -gt Synchronized Tables

• To add publications, I used UltraLite Schema
  Painter and selected all of the tables in the
  database to indicate all the tables should be
  updated with changes when synchronized.
• For the synchronization scripts, I used the
  default scripts.

9
Screen Snapshot

• Screen shots of application running from desktop.
  (Application can be deployed into pocket PC for
  use on PDA and has been tested to work
  successfully on the Toshiba e330 PDA)
• User can browse through the data in each table by
  the lt gt buttons. When user presses lt or gt
  button located on Assignments, all rows in the
  screen will be filled with data that corresponds
  with the work id.
• User can add and delete any rows.

10
Code Sample 1

• Private Sub DisplayCurrentWorksOnRow()
• Dim empid As String
• Dim projid As String
• Dim deptid As String
• If TWorksOn.RowCount 0 Then
• workIDBox.Text ""
• Else
• workIDBox.Text TWorksOn.Column("work_id"
  ).StringValue
• empid TWorksOn.Column("emp_id").StringVa
  lue
• projid TWorksOn.Column("proj_id").String
  Value
• End If
• TEmp.FindBegin
• TEmp.Column("emp_ID").StringValue empid
• TEmp.FindFirst
• DisplayCurrentEmpRow
• TProj.FindBegin
• TProj.Column("proj_id").StringValue projid

11
Code Sample 2

• Private Sub btnWorkDel_Click()
• Dim checkResponse As Integer
• TWorksOn.FindBegin
• TWorksOn.Column("work_id").StringValue
  workIDBox.Text
• checkResponse MsgBox("Delete - Are you
  sure?", vbYesNo)
• If checkResponse vbYes Then
• If TWorksOn.FindFirst Then
• TWorksOn.Delete
• Connection.Commit
• End If
• End If
• DisplayCurrentWorksOnRow
• End Sub

• This piece of code will delete the currently
  viewing data from the WorksOn table.
• From the currently viewing data, it gets the
  work_ID, which is the primary key of WorksOn
  table.
• Since primary keys are unique, find the first row
  that matches the current primary key.
• Then delete that row from the database.

12
Conclusion

• AppForges MobileVB and Sybases UltraLite
  database are efficient tools in developing
  applications for PDAs
• Sybase Mobilink provides good and fast
  synchronization between consolidated database and
  remote database.
• Gives flexibility to user in terms of which
  tables you want to synchronize and how you want
  it to synchronize
• Cost-Effective way to build quick database
  applications for PDAs
• Less development time using VB
• Multi Platform development possible (Pocket PC,
  Palm, Symbian)
• Overall, a good learning experience in making
  database application for PDA.