IT 3300 - Database Management Mid-Term Project - VTech, UVA and George Mason Universities

Anatomical Pathology Laboratory receives tissue specimens for Cancer Diagnosis from Clinicians who work for Physician Practices, known as Practices in Laboratory Terminology.

Each Practice is identified by the Practice Name and Practice Location.

A Practice Location defines the Address, Phone and Fax of the Location.

A Practice may have multiple Locations.

A Clinician may work at one or more Locations of the Practice.

The specimens that are received by the Anatomical Pathology Laboratory and sent by Clinicians are accompanied by a Paper Requisitions.

The Paper Requisition is identified by a unique Requisition Number that is printed on the Paper Requisition. The Requisition identifies the Patient and Tests or Procedures (Diagnosis Procedures) that need to be performed on the specimen. The Tests or Procedures on the Requisition are known as Ordered Procedures in Laboratory Terminology.

Each Requisition may contain one or more Ordered Procedures. A Patient is identified by their Name (Last Name, First Name, and Middle Initial) and their Home Address.

For each Ordered Procedure on the Requisition, a Clinician may submit one or more specimens of the same type. Each specimen comes in its own container, known as Vial in Lab Terminology. For Example, a Clinician may submit 12 Vials each containing a tissue specimen for Prostate Histology Procedure. Each Vial is labeled with sequential alphabet character starting from A. Thus the 12 Vials will be labeled as A, B, C, D, E, F, G, H, I, J, K, and L. If the number of Vials exceeds 26 then 27th Vial will be labeled as AA, the 28th Vial will be labeled as AB and so on.

When the Laboratory receives the Requisition, for each Requisition they create a Case within the Laboratory. The Case contains the Practice Information, Clinician Information, Patient Information and All Ordered Procedures. The Ordered Procedure is the Procedure that will be performed on the specimen in all Vials accompanying the Ordered Procedure. A Case is identified by an Accession Number that is generated by the Laboratory to uniquely identify the Case. The Accession Number is a 15 Character Alpha-Numeric data. The Case also contains the Requisition Number listed on the Paper Requisition.

Within the Anatomical Pathology Laboratory, the specimen is taken out of the Vial and put in small containers called Cassettes. If the specimen in the Vial is too large, it maybe to cut into smaller pieces and put into multiple Cassettes. Each Cassette is labeled with the Label of the Vial followed by a number. Thus if Vial A specimen is cutup into 4 pieces and put into 4 Cassettes, the 4 Cassettes will be labeled as A1, A2, A3 and A4 on the other hand if the specimen does not need to be cutup then it is put into 1 Cassette labeled as A1

Each Cassette is processed in the Laboratory and Slides are prepared for each Cassette. Usually there are only 2 Slides that are prepared for each Cassette, but some specimens like Skin Tissue specimen sent by Dermatologists require 4 slides to be prepared per Cassette.

Once the Slides are prepared in the Laboratory, they are sent to the Pathologist for Diagnosis. All the Paperwork along with the original Requisition is also sent to the Pathologist along with the Slides.

The Pathologist looks at the slides under the Microscope and writes one Diagnosis per Cassette regardless of the number of Slides that are prepared for the Cassette.

The diagnosis that the Pathologist writes contains the Diagnosis such as Benign, Suspicious or Malignant. Along with the Diagnosis, the Pathologists also write the Diagnosis Description, a small narrated paragraph that describes his/her Diagnosis findings in detail.

The Pathologist Diagnosis along with his narrated description of the Diagnosis is put on a Paper Report along with Patient Information, Practice Information and Clinician Information and sent back to the Ordering Clinician as Final Diagnosis Report for the Requisition.

Attached below is the Conceptual ERD of the above requirements as prepared by the Business Analyst.

Normalize the Requirements and Prepare a Logical ERD Diagram.

Show your work for First, Second and Third Normal Form as you normalize the requirements for Logical Data Structure.

Clinician Patient OrderedProcedure PracticeLocation ClinicianName LastName FirstName Middlelnitial HomeAddress OrderedProcedureName Address Phone Fax Practice Case Vial Specimen PracticeName AccessionNumber RequisitionNumber PathologistName VialLabel SpecimenName Cassette CassetteLabel NumberOfSlides Diagnosis Diagnosis Description Clinician Patient OrderedProcedure PracticeLocation ClinicianName LastName FirstName Middlelnitial HomeAddress OrderedProcedureName Address Phone Fax Practice Case Vial Specimen PracticeName AccessionNumber RequisitionNumber PathologistName VialLabel SpecimenName Cassette CassetteLabel NumberOfSlides Diagnosis Diagnosis Description