It's never actually explain how he's related to the Bouvier family, like whether he was the uncle of Marge and her sisters, thus making him Bart, Lisa and Maggie's great-uncle, or if he was the brother of the Bouvier sisters. He was only mentioned in the fifth season episode, The Boy Who Knew Too Much, by Marge, who told Bart that he used to have a saying, "Shoot 'em all and let God sort 'em out," until one day he put his theory into practice, and "it took 75 federal marshals to bring him down."
Arthur seems to be a source of shame and embarrassment to the family. After Marge finished telling Arthur's story to Bart, she said, "Now let's never speak of him again."
Shortly afterward, she briefly alluded to him to Bart when cautioning him about listening to voices in his head, as he did on one December morning (implied to be the same day he decided to put his saying into practice). The references to voices inside his head imply that he was mentally ill, possibly schizophrenic.
Arthur was shot to death in 1986 at age 60.