1 / 29

PropMan

PropMan. A Property Management Database Interface. Mark Johnson. CS 8628, Summer 2003. The following are the steps that I took in designing and creating this project. Formulate and design a 3 table database. Create the ERD, logical schema, and DDL.

tress
Download Presentation

PropMan

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. PropMan A Property Management Database Interface Mark Johnson CS 8628, Summer 2003

  2. The following are the steps that I took in designing and creating this project. Formulate and design a 3 table database. Create the ERD, logical schema, and DDL. Create the schema using Ultralite Schema Painter. Visualize the needs and layout of the application. Create the four forms of the application. Add the controls and code to the Main, Properties, Tenants, and Payments forms. Create the server database using Sybase Central. Create the publication using Interactive SQL. Create the synchronization user using Interactive SQL. Sequence of Tasks

  3. Create the synchronization subscription using Interactive SQL. Proceed to test and debug the application from within VB. Deploy the application to the Pocket PC. Proceed with testing and debugging. Create the synchronization scripts using Sybase Central. More testing and debugging. Project completion. Sequence of Tasks Continued

  4. PropMan is an application for entering, viewing, and modifying property management information on the Pocket PC, allowing multiple agents, with multiple Pocket PC’s, to all synchronize their remote databases with a central database containing all properties, tenants, and payments. The remote database, pm.udb, is synchronized with the server database, PropManDB.db, using Mobilink. Three forms contain information on rental property, tenants, and payments. The Properties form contains the property number, rental amount, the street address, city, state, and zip code. Properties can be added and deleted using this form, and the rental amount for a property can be modified. Project Description

  5. The Tenants form contains the tenant number, property number, tenant name, and phone number. Tenants can be added and deleted using this form, and the tenant’s name and phone number can be modified. The Payments form contains the payment number, tenant number, payment amount, and payment date. Payments can be added and deleted, but not modified. The application’s fourth form, the Main form, is the access point to the other three forms and also the form from which synchronization takes place. Project Description Continued

  6. E-R Diagram Rent PropNum Name PropNum City 1:1 0:N Tenant Property Zip 1:1 Phone TenantNum State Address 0:N Payment PaymentNum PayAmt PayDate TenantNum

  7. The Property table contains PropNum (Primary Key), Address, City, State, Zip, and Rent. The Tenant Table contains TenantNum (Primary Key), PropNum (Foreign Key to the Property table), Name, and Phone. The Payment table contains PaymentNum (Primary Key), TenantNum (Foreign Key to the Tenant table), PayDate, and PayAmt. Logical Schema Property PropNum State Zip City Address Rent Tenant PropNum Name Phone TenantNum Payment PaymentNum PayAmt PayDate TenantNum

  8. Describe Property PropNum char(3) Not Null Primary Key Address char(25) Not Null City char(15) Not Null State char(2) Not Null Zip char(7) Not Null Rent double Not Null The DDL are represented on this page and the next as SQL describe statements. All of each table’s columns are represented with their respective data type and precision, and whether they accept null values. The primary keys for each table are also shown. Physical Schema (DDL)

  9. Physical Schema (DDL) Continued Describe Tenant TenantNum char(3) Not Null Primary Key PropNum char(3) Not Null Name char(15) Not Null Phone char(12) Describe Payment PaymentNum char(3) Not Null Primary Key TenantNum char(3) Not Null PayAmt double Not Null PayDate char(8) Not Null

  10. The Main form does not access any tables. The Properties form can create, read, update, and delete from the Property table. The Tenants form can create, read, update, and delete from the Tenant table. The Payments form can create, read, and delete from the Payment table. Matrix: Forms vs. Tables

  11. The publication script was created as PM_Pub for the Property, Tenant, and Payment tables. The synchronization user PM_User was created. The synchronization subscription was created for PN_User to the PM_Pub publication. The connection protocol is TCP/IP and the address is the local host address. Publication Script, etc. CREATE PUBLICATION PM_Pub { TABLE Property, TABLE Tenant, TABLE Payment }; CREATE SYNCHRONIZATION USER PM_User; CREATE SYNCHRONIZATION SUBSCRIPTION TO PM_Pub FOR PM_User TYPE ‘tcpip’ ADDRESS ‘host-localhost’

  12. Upload_cursor: SELECT PropNum, Address, City, State, Zip, Rent FROM Property Upload_insert: INSERT INTO Property(PropNum, Address, City, State, Zip, Rent) VALUES(?, ?, ?, ?, ?,?) Upload_delete: DELETE FROM Property WHERE PropNum = ? Upload_update: UPDATE Property SET Rent = ? WHERE PropNum = ? The upload scripts for the Property table do the following during synchronization: For Property inserts, PropNum, Address, City, Stat, Zip, and Rent are uploaded to the server database. For Property deletes, the table entry associated with the primary key is deleted. For Property updates, the Rent associated with the primary key is updated. Synchronization Scripts Property Table Upload Scripts

  13. Download_cursor: SELECT PropNum, Address, City, State, Zip, Rent FROM Property Download_insert: INSERT INTO Property(PropNum, Address, City, State, Zip, Rent) VALUES(?, ?, ?, ?, ?,?) Download_delete: DELETE FROM Property WHERE PropNum = ? Download_update: UPDATE Property SET Rent = ? WHERE PropNum = ? The download scripts for the Property table do the following during synchronization: For Property inserts, PropNum, Address, City, Stat, Zip, and Rent are downloaded to the remote database. For Property deletes, the table entry associated with the primary key is deleted. For Property updates, the Rent associated with the primary key is updated. Synchronization Scripts Property Table Download Scripts

  14. Upload_cursor: SELECT TenantNum, Name, Phone, PropNum FROM Tenant Upload_insert: INSERT INTO Tenant(TenantNum, Name, Phone, PropNum) VALUES(?, ?, ?, ?) Upload_delete: DELETE FROM Tenant WHERE TenantNum = ? Upload_update: UPDATE Property SET Name = ?, Phone = ? WHERE TenantNum = ? The upload scripts for the Tenant table do the following during synchronization: For Tenant inserts, TenantNum, PropNum, Name, and Phone are uploaded to the server database. For Tenant deletes, the table entry associated with the primary key is deleted. For Tenant updates, the Name and Phone associated with the primary key is updated. Synchronization Scripts Tenant Table Upload Scripts

  15. Download_cursor: SELECT TenantNum, Name, Phone, PropNum FROM Tenant Download_insert: INSERT INTO Tenant(TenantNum, Name, Phone, PropNum) VALUES(?, ?, ?, ?) Download_delete: DELETE FROM Tenant WHERE TenantNum = ? Download_update: UPDATE Property SET Name = ?, Phone = ? WHERE TenantNum = ? The download scripts for the tenant table do the following during synchronization: For Tenant inserts, TenantNum, PropNum, Name, and Phone are downloaded to the remote database. For Tenant deletes, the table entry associated with the primary key is deleted. For Tenant updates, the Name and Phone associated with the primary key is updated. Synchronization Scripts Tenant Table Download Scripts

  16. Upload_cursor: SELECT PaymentNum, PayAmt, PayDate, TenantNum FROM Payment Upload_insert: INSERT INTO Payment(PaymentNum, PayAmt, PayDate, TenantNum) VALUES(?, ?, ?, ?) Upload_delete: DELETE FROM Payment WHERE PaymentNum = ? The upload scripts for the Payment table do the following during synchronization: For Payment inserts, PaymentNum, TenantNum, PayAmt, and PayDate are uploaded to the server database. For Payment deletes, the table entry associated with the primary key is deleted. Synchronization Scripts Payment Table Upload

  17. Download_cursor: SELECT PaymentNum, PayAmt, PayDate, TenantNum FROM Payment Download_insert: INSERT INTO Payment(PaymentNum, PayAmt, PayDate, TenantNum) VALUES(?, ?, ?, ?) Download_delete: DELETE FROM Payment WHERE PaymentNum = ? The download scripts for the Payment table do the following during synchronization: For Payment inserts, PaymentNum, TenantNum, PayAmt, and PayDate are downloaded to the remote database. For Payment deletes, the table entry associated with the primary key is deleted. Synchronization Scripts Payment Table Download

  18. The majority of the difficulties that I encountered were because of my unfamiliarity with Visual Basic. Trying to format a double as currency, I added a component that is not supported by MobileVB. This led to MobileVB validation errors later because I had not removed the component even though I didn’t use it. I also had a complication error from reading a double in from a text box. Realizing that the string should be cast as a double, it took me a while to figure out the Visual Basic syntax for casting. I had one major difficulty that I spent a couple of days on. After my application was complete and working when run within Visual Basic, I deployed it to the Pocket PC. The application started on the Pocket PC, but quit when any of the buttons were clicked. By a process of elimination, commenting out sections of code and creating test applications, I was able to determine that the application failed when trying to connect to any of the tables in the remote database. I sought support from AppForge. They did not see anything wrong with the code and suggested that I put bebugging code into the application. Difficulties Encountered

  19. Meanwhile, I created more test applications and a new, totally different schema. These applications worked as expected on the Pocket PC. At that point, I recreated my original schema and tried it on the Pocket PC with the original application. The application did not work. I then created a new schema and tried it on the Pocket PC with the original application and it worked. Then I renamed the new schema to the name of the original schema and the application failed. That is when I realized that the name of the schema was causing the application to fail. I changed the name of the schema, originally PropMan.usm to pm.usm and the application worked as expected. On following up with Appforge, they could not explain the behavior, but thought that maybe PropMan was a reserved word for Mobilink. I am not certain that I agree with that explanation. Difficulties Encountered Continued

  20. The Main form contains buttons for accessing the other forms. It also contains buttons for exiting the application and synchronizing the database. The Properties form is for viewing, inserting, updating, and deleting property information. There is also a button for returning to the Main form. The Tenants form is for viewing, inserting, updating, and deleting tenant information. There is also a button for returning to the Main form. The Payments form is for viewing, inserting, and deleting payment information. There is also a button for returning to the Main form. Screen Snapshot # 1

  21. Screen Snapshot # 2 • Here are screen captures of Adaptive Server Anywhere and Mobilink running on the server. • The bottom picture is Mobilink during synchronization.

  22. Screen Snapshot # 3 • Here are the different forms viewed on the PDA.

  23. Code - Main Form • btnDone_Click() – Closes the tables and then the connection to the database and ends the application. • btnPayments_Click() – Hides the Main form and shows the Payments form. • btnProp_Click() – Hides the Main form and shows the Properties form. • btnSync_Click() – Specifies the synchronization parameters and synchronizes the databases. • btnTenants_Click() – Hides the Main form and shows the Tenants form. • Form_Load() – Calls InitDB from the mdlPropMan module.

  24. Code - mdlPropMan Module • InitDB() – Sets the database connection parameters, uid, password, database name and schema name and connects to the database. If the database is not present, it creates it using the schema. It then opens the Payment, Tenant, and Property tables. • GetError() – Gets the error number in case of connection error.

  25. Code Property, Tenant, and Payment Forms • The code for the Property, Tenant, and Payment forms are almost the same, except for working with different tables and different columns. • btnInsert_Click() – inserts values input from the form into the appropriate columns of the table.

  26. Code - ContinuedProperty, Tenant, and Payment Forms • btnReturn_Click() – Hides the current form and shows the Main form. • Form_Load() – Moves to the first row in the table or moves before the first row if there are zero rows. • Form_Activiate() – Displays the current row. • btnNext_Click() – Moves to the next row. • btnPrevious_Click() – Moves to the previous row. • BtnDelete_Click() – Deletes the current row.

  27. Code - ContinuedProperty, Tenant, and Payment Forms • btnUpdate_Click() - updates values input from the form in the appropriate columns of the table. • The Payment form does not have the ability to update.

  28. Code - ContinuedProperty, Tenant, and Payment Forms • DisplayCurrentRow() – Displays columns from the database in the appropriate text boxes on the form. If there are no rows in the table, the text boxes are displayed as blank.

  29. Mobilink is a flexible and fairly straight forward means of synchronizing databases between a remote PDA and a server. Although at times confusing, the tools integrated into Visual Basic by Appforge vastly simplify development for the Pocket PC. With these tools, and the ease of development, we are likely to see a great number of applications being developed for PDA’s and the number of PDA’s produced and sold should vastly rise. My experience with these programs has shown that the development time for mobile applications can be greatly reduced by the simplicity of use that they provide. I had no experience with VB or DBMS and was able to develop the PropMan application in a reasonable timeframe with limited instruction. Conclusion

More Related