McCord Leakless Sealing Company by DOd6Xt70


									McCord Leakless Sealing Company
       NCMR Database

   Preliminary Design & Proposal

        Prepared for Dr. Rich

      CIS 440 Database Design
       Athens State University


          Brandon McBay
         November 15, 2006
                          Table of Contents

Database Initial Study…………………………………….3

Scope and Boundaries…………………………………… 5

Entity Relationship Diagram…………………………….. 6

Relationship Matrix……………………………………… 7

Data Dictionary………………………………………….. 8

McCord Leakless Sealing Company               Page 2
CIS 440 Database Systems Project
Company Situation:

McCord Leakless Sealing Company (MLS) is a manufacturing company located in
Athens, Alabama. MLS is actually a joint-venture company owned equally by two
companies: Federal-Mogul Corporation, based in the United States and Nippon Leakless,
located in Japan. McCord Leakless Sealing Company’s mission is to manufacture, and
assemble quality multilayered gaskets to automobile companies such as Honda and
Chrysler for use in engine assembly.

MLS continually strives to meet the customer’s needs on a daily basis, and continually
subjects all business and operational functions to analysis to find ways to improve and
streamline the manufacturing and shipment of quality finished goods. Included is a
specialty automobile certification: TS16949. The TS16949 certification sets guidelines
and standards for documenting all job functions, including methods for retaining and
storing any data pertinent to MLS operations.

Description of Operations

Within the manufacturing of multilayer steel gaskets, the product produced must meet
very specific physical specifications. Therefore, there are procedures and methods in
place within MLS to contain and control any products that do not meet specific
requirements. One overall method that is used to control products is called a Non-
Conforming Material Report (NCMR). The NCMR is a form along with a documented
process that starts when a non-conforming product is identified. A non-conforming
product can be the raw material used to create the single layers, or the finished multilayer
product, after the multiple layers have been put together and assembled. The NCMR
process ends when the non-conforming material has been analyzed by appropriate
personnel and a decision is made as to the disposition of the non-conforming product.
Some examples of disposition include: Use as Is, Return to Vendor, Rework, and Scrap.


MLS recently received a TS16949 Re-Certification Audit. Audits cover processes and
documentation and confirm that the company is upholding the standards the certification
implies. A Non-Conformance is issued to the company when an issue is discovered
during a TS16949 audit. With the recent audit, MLS received one Non-Conformance
during the recertification audit. The current idea is to replace the current method of
Microsoft Excel to contain data, and Microsoft Word for forms to be filled out with a
Microsoft Access database which would combine both the ability to store data, and allow
an ‘electronic form’ to be filled out, lowering the need to print and store forms.

 McCord Leakless Sealing Company                                              Page 3
 CIS 440 Database Systems Project
Problems and Constraints

The problem lies within the NCMR Process. Currently, the NCMR process revolves
around a Microsoft Excel spreadsheet, and a Microsoft Word form that is required to be
filled out and filed. The current process and system has too many flaws which allow
material and forms to be lost. Also, the current system has some difficulty getting the
form passed around to everyone responsible for their specific portion. To help solve the
documentation problem, a Microsoft Access Database has been conceived to be used,
instead of an Excel Spreadsheet. By converting the process to Access, forms can be built
within the Access database such that different personnel can filter out everything required
by them and allow them to perform their operation. Enhancements would include the
ability for segregated sections along the NCMR process, so that one department would
only have to see their portion, and what is in their queue. There should also be an ability
to see what is in other queues if any of the departments desired. The queues should be set
up to match the flow of the NCMR process, and move the NCMR Record through the
queues, depending on what actions are required. Simplicity has a value-added effect on
this system as well. Several employees will be initiating the NCMR process, so a user-
friendly interface should prove beneficial.

 McCord Leakless Sealing Company                                             Page 4
 CIS 440 Database Systems Project
Scope and Boundaries

After reviewing the process and examining the current Excel columns and Microsoft
Word forms, it has been determined that the new database system:

      Must provide a method for a New NCMR Initiation.
      Must restrict the user from inputting incorrect data, where possible.
      Must set up Visual Queues within the Forms for different departments to
       determine which NCMR entries require that department’s attention.
      Queries must include:
           o On Hold Listing: for Shipping Department to put on ‘Hold’ within the
              MRP system.
           o Disposition Review: for the Review Board to determine which NCMR
              entries require reviewing to determine the actions to be taken.
           o Release Materials Status: for the Shipping Department to change the status
              of the ‘Hold’ items to the status determined by the Review Board.
           o Queried list of NCMR entries that require Action be taken.
           o Queried list of NCMR entries that are complete.
                   Possibly with Different Sort options including by Date and by
                       NCMR Number.
      Should be able to print an NCMR Form with appropriate data filled out,
       regardless of what the Status is. Have the current Status on the printed report

 McCord Leakless Sealing Company                                         Page 5
 CIS 440 Database Systems Project
                   McCord Leakless Sealing Company
                     NCMR Database Entity Relationship Diagram

          tbl_NCMR                                               tbl_NCMR_Items
   PK   NCMR_Number                                    PK        NCMR_Item_List_Number

        Date                   (1,1)              (0,M) FK2,I2 NCMR_Number
        Internal_Part_Numer            Can Have                  Traceability
        Part_Description                                         Quantity
        Issuer                                         FK1,I1    Defect_Code
        Rework_Instruction                                       On_Hold
        Rework_Total                                             Disposition_Closeout
        UAI_Total                                                (1,M)
   I1   Disposition_Code
        MRB_Review                                                  Have


                                                         PK,I1    Defect_Code


McCord Leakless Sealing Company                                                   Page 6
CIS 440 Database Systems Project
                CIS 440: Database Systems – Fall 2006
                 Term Project – Relationship Matrix
            Instructor: Dr. Lisa Rich     Student: Brandon McBay

                          tbl_NCMR      tbl_NCMR_Items   tbl_Defect_Code
        tbl_NCMR_Items    (1,M)                          Describes (1,M)

McCord Leakless Sealing Company                                        Page 7
CIS 440 Database Systems Project
Table            Attribute               Contents                       Type         Format
tbl_NCMR         NCMR_Number             NCMR ID Number                 Autonumber   ####
                 Date                    NCMR Date                      Date         MM/DD/YYYY
                 Internal_Part_Number    MLS Part Number                Char(12)     XXXXXXXX
                 Part_Description        Description of Part            Char(125)    XXXXXXXX
                 Issuer                  NCMR Initiator                 Char(30)     XXXXXXXX
                 Rework_Instructions     Instructions for Reworks       Char(200)    XXXXXXXX
                 Rework_Total            Rework Part Quantity           Int          ####
                 Scrap_Total             Quantity of Scrap              Int          ####
                 UAI_Total               Use As Is Total                Int          ####
                 RTV_Total               Return to Vendor Total         Int          ####
                 Cust_Deviation_Total    Total Customer Deviations      Int          ####
                 Comments                MRB Review Comments            Char(255)    XXXXXXXX
                 Disposition             Disposition Comments           Char(255)    XXXXXXXX
                 Dispositioned_By        Person that Dispositioned      Char(30)     XXXXXXXX
                 MRB_Review              Status of MRB Review           Yes/No       Check/NotChecked
                 Closeout_Comments       Final Comments                 Char(255)    XXXXXXXX
                 Approver                Closeout Approver              Char(30)     XXXXXXXX
                 Closeout_Date           Date of NCMR Closeout          Date         MM/DD/YYYY
                 Complete                NCMR Complete                  Yes/No       Check/NotChecked

tbl_NCMR_Items   NCMR_Item_List_Number   NCMR Item List ID Number       Autonumber   ####
                 NCMR_Number             NCMR Reference Number          Int          ####
                 Traceability            Traceability Number            Char(30)     XXXXXXXX
                 Quantity                Item Listing Quantity          Int          ####
                 Defect_Code             Defect Code for Item Listing   Char(2)      XX
                 On_Hold                 Hold Status for Item Listing   Yes/No       Check/NotChecked
                                         Disposition Status for Item
                 Disposition_Closeout    Listing                        Yes/No       Check/NotChecked

Defect_Code      Defect_Code             Defect Code ID                 Char(2)      XX
                 Defect_Description      Defect Description             Char(200)    XXXXXXXX

McCord Leakless Sealing Company                                         Page 8
CIS 440 Database Systems Project

To top