60-470 Project Report (Group V)

Final Exam Time: April 26^th 10:00am

Maintenance management system

(Produced for Ms. Ritu Chaturvedi)

Group members:

Xudong Xie 100887020

Yuan Wang 100

Yan wang 100588439

April 25, 2004

Table of contents

I. Abstraction ------------------------------------------------- 3

II. Proposal ----------------------------------------------------- 4

III. The Oracle Tools ------------------------------------------ 5

IV. Development ----------------------------------------------- 7

V. Interfaces and user documentation ---------------------- 12

Abstraction

This report describes the proposal of developing a maintenance management system in a manufactory environment. Then, it describes the characteristics of the Oracle Tools (Forms, Reports or PL/SQL) we used in this project. After that, it describes the development of this project including an entity relationship diagram, the database design and the interface we used in our application. Finally, it describes how to use this management system, i.e. the manual of this system.

Proposal

In this project, we are going to use Oracle Tools (Forms, PL/SQL) to develop a maintenance management system in a manufacturing environment.

As a maintenance management system, it includes three subsystems as following:

(1) Maintenance job management subsystem

(2) Spare parts management subsystem

(3) Manager’s monitoring & system administration subsystem

Maintenance job management subsystem has the functions as following:

(1) Maintenance job generation and job assignment, job finishing and finishing confirmation.

(2) Equipment information.

(3) Job detail information.

Spare parts management subsystem has functions as following:

(1) Spare parts ordering.

(2) Spare parts checking and distribution.

(3) Spare parts receiving.

Manager’s monitoring & system administration subsystem has functions as following:

(1) Spare parts inventory monitoring.

(2) Maintenance job monitoring.

(3) New data addition and obsolete data deletion, data modification.

(4) Access authority.

The Oracle Tools

In this project, we use oracle tools (Forms, Reports and PL/SQL). These tools help us to create forms and reports based on the data tables that we have created.

Our project has a 3-tier structure. The Oracle tools are very powerful to develop 3-tier structured systems.

(1) The front tier is the front end: clients use this tier to interact with the maintenance management system to complete all the functions we described in the proposal. Normally, we use html and JSP to finish this part. In this project, we have used Object Navigator, Layout Editor and Menu Editor to design and customize our forms and reports, add a lot of kinds of functionality, like buttons, radio buttons, list of values to make our forms and reports more user friendly and easy to develop.

(2) The middle tier is the control part: we use this part to control the system and make bridge between the front end and database. Normally, we use Java Serverlet and JDBC to finish this part. In this project, we have used PL/SQL Editor to write triggers on our objects to add functionality to them and capture errors. The Oracle tools have shortened our programming and testing time dramatically.

(3) The back tier is the database: The oracle tools we have used provide use a convenient way to manipulate the data, handle the relationship of data tables, avoid errors while manipulate data. For example, it provides user friendly interface to connect two tables to create a new view. The build in function have reduced our programming time dramatically.

When we use these Oracle tools, we take a standard development sequence which makes the oracle tools so powerful. The sequence is:

(1) Use Object Navigator to name the form.

(2) Use Developer menu command to connect to the database.

(3) Use New Block window to create blocks.

(4) Use Layout Editor to fine-tune the layout.

(5) Use Properties window to set properties.

(6) Use PL/SQL Editor to add code.

(7) Use Runform to test the form.

As I know, besides these Oracle tools, IBM provides similar tools named Lotus Notes Domino Designer. Compare to Lotus Notes Domino Designer, besides both of them have very similar forms and reports development functions, the Oracle tools are more powerful in data block development. But Lotus Notes Domino Designer can connect to other kinds of database, like DB2, Access, and it provides more beautiful and finer appearance of forms, reports.

Development

According to our proposal, we drew an Entity Relationship Diagram of the data we would use in this project as following:

	(0, n)
			(1, n)
(1, n)

Then, we began to design the tables.

First of all, we need a table for data access control.

(1) The staff table is used for login and rank. When login, we can check the password. After login, we can check out the user’s position. Then give a rank to the user, and the rank will control the user’s authority to access the database. The userID is the index of this table.

Staff

UserID

Password

Position

Information

create table staff(

userid CHAR(8) not null,

pd CHAR(8) not null,

position CHAR(12),

info VARCHAR2(200),

PRIMARY KEY (userid))

Then, we need tables for spare parts management. In this subsystem, we need all information about parts. After normalization, we get parts, order, supplier and consume tables.

(2) The parts table is used for parts’ information. PartsNo is the index of this table. We will generate a list of parts which number of on stock is smaller than the minimal number.

Parts

Parts No.

Order No.

Min. No.

On Stock

Description

Unit Price

Location

ordNo CHAR(4) not null,

minNo NUMBER(3),

onStock NUMBER(3),

description VARCHAR2(100),

price NUMBER(6,2),

location CHAR(20),

PRIMARY KEY (partNo))

(3)After we generate the above list of the parts, we need to order parts according to the list. So we need this Order table. OrderNo is the index of this table. After the parts arrive, the quantity of the arrival parts will add to the numbers of on stock. Parts (which actually is the parts number) is the foreign key from parts table and supplier is the foreign key from supplier table. OrderPerson is the foreign key from staff table.

Order

Order No.

Ordering Person

Parts

Supplier

Arrival Date

Quantity

create table ordering(

ordNo CHAR(4) not null,

ordPerson CHAR(8) not null,

part CHAR(4) not null,

supplier CHAR(16) not null,

orderDate DATE,

arrDate DATE,

quantity NUMBER(3),

PRIMARY KEY (ordNo),

FOREIGN KEY (part) REFERENCES parts,

FOREIGN KEY (supplier) REFERENCES supplier,

FOREIGN KEY (ordPerson) REFERENCES staff)

(4) Supplier table includes the information about the parts suppliers. Name is the index of this table.

Supplier

Name

Contact Person

Delivery Duration

Information

create table supplier (

name CHAR(16) not null,

contactor CHAR(8) not null,

delivDuration NUMBER(2),

meno VARCHAR2(100),

PRIMARY KEY (name))

(5)Consume table includes the information about the parts consuming. PartsNo is the index and foreign key from parts table. Consumer is the foreign key from staff table. When the record generates, the quantity of the consuming parts will be deducted from the number of on stock in parts table.

Consume

Parts No.

Consumer

Quantity

Date

Notes

create table consume (

partNo CHAR(4) not null,

consumer CHAR(8) not null,

quantity NUMBER(2),

comsumDate DATE,

notes VARCHAR(100),

PRIMARY KEY (partNo, consumer),

FOREIGN KEY (partNo) REFERENCES parts,

FOREIGN KEY (consumer) REFERENCES staff)

After that, we develop table for equipment management. In this subsystem we need all information about the equipment maintenance information. After normalization, we get equipment, work and job tables.

(6) Equipment table includes information about all the equipment. EqNo is the index.

Equipment

Eq. No.

Loaction

Information

create table equipments (

eqNo CHAR(4) not null,

loaction CHAR(16) not null,

information VARCHAR2(100),

PRIMARY KEY (eqNo))

(7) Work table includes information about maintenance routes. WorkNo is the index. Supervisor is the foreign key from the staff table.

Work

Work No.

Information

Interval

Supervisor

create table work (

workNo CHAR(4) not null,

interval NUMBER(3),

supervisor CHAR(8),

info VARCHAR(100),

PRIMARY KEY (workNo))

(8) Job table includes information about the tasks of maintenance. Job no. is the index. WorkNo is the foreign key from work table. EqNo is the foreign from the equipment table. Technician is the foreign key from staff table.

Job

Job No.

Work No.

Eq. No.

Technician

Due Date

Assign Date

Finishing Date

create table job (

jobNo CHAR(4) not null,

workNo CHAR(4) not null,

eqNo CHAR(4),

technician CHAR(8),

dueDate DATE,

assignDate DATE,

finishingDate DATE,

PRIMARY KEY (jobNo),

FOREIGN KEY (workNo) REFERENCES work,

FOREIGN KEY (eqNo) REFERENCES equipments,

FOREIGN KEY (technician) REFERENCES staff)

After that, we need to develop interfaces (forms and reports) used in our application. Interfaces will be introduced with user documentation.

Interfaces and user documentation

First, install Oracle Form 6i on the PC, create all the above data tables in the Oracle system and insert the records. Save the Menu.mmb files and MMS.fmb files in certain fold.

Open Form Builder. In Form Builder environment, go File->Open, and open Menu and MMS module files. Then, go File->Connect to connect to the Oracle system with the help of your DBA, go File->administration->compile files to compile both files. After that, press Ctr+R.

Then you enter the MMS system. In this system, if you click the item or button you are not permitted to go or use, it will give you error message.

(1) User Menu: you can find this menu on every page except the login page. So you can easily go to the page you want by clicking the menu. If you click the item you are not permitted to go, it will give you error message.

Figure 1 User Menu

(2) Login: After you enter this system, it appears this login page. Input your user ID and password. It will give error information if login fails. If login successful, it jump to help page.

Figure 2 Login page

(3) Help page: this page includes explanation plus index of different pages. Click the button to the page you want to go.

Figure 3 Help Page

(4) If you click Equipment information(query), it appears this page. You can query equipment information in this page.

Figure 4 Equipments information (query)

(5) If you click Parts information (query), it appears this page. You can query parts information in this page.

Figure 5 Parts information (query)

(5) If you click Job information (query), it appears this page. You can query job information in this page.

Figure 6 Job information (query)

(7) If you click Job Confirm, it appears this page. You can confirm job after you finish the job in this page.

Figure 7 Job confirm

(8) If you click Job Generation, it appears this page. You can generate job in this page.

Figure 8 Job generation

(9) If you click Part Stock Check, it appears this page. You can generate a list of parts which need to be ordered in this page.

Figure 9 Parts stock check

(10)If click Make New Order, it appears this page. You can query order and supplier. If you want to make a new order, click new button, input information, then click save button, and a new order is created. If you want to modify an order, click modify button, input modified information, then click save button, and a order information is modified.

Figure 10 Make new orders

(11) If click Parts Consume, it appears this page. You can query parts and consume information in this page. If you want to make a new consuming record, click new consume button, input information, then click save button, and a new consuming record is created.

Figure 11 Parts consume

(12) If you are system administrator and click Staff Info/Management, it appears this page. You can query, insert, delete and edit staff record in this page. Don’t forget to press save button after you insert, delete and edit staff record.

Figure 12 Staff info (management)

(13) If you are system administrator and click Supplier & Parts Info/Management, it appears this page. You can query, insert, delete and edit parts or supplier record in this page. Don’t forget to press save button after you insert, delete and edit parts or supplier record.

Figure 13 Supplier & Parts info (management)

(14) If you are system administrator and click Work & Equipment Info/Management, it appears this page. You can query, insert, delete and edit work or equipment record in this page. Don’t forget to press save button after you insert, delete and edit work or equipment record.

Figure 14 Work & Equipment info (management,)