There are some important questions here. The most salient is, how long have you had her for? You describe her as "new", so I will assume (forgive me) that it hasn't been very long.
It sometimes happens that, if you've had Shelties in the past, you remember what a wonderful dog you had... gentle and friendly with everyone, loyal and obedient, an easy companion to live with. It's easy to place that expectation on a new dog, and then to become frustrated when he or she does not measure up.
You need to be patient (and then more patient). Shelties, especially Shelties who come into new homes as adults, need time to adjust. I know, you're saying, "I knew that!" But when I say "time", I mean "months, maybe a year, maybe more, who knows?"
Let me tell you two stories, to illustrate the point (look, you can exercise your patience muscles while you read my stories!).
The first was Rosie. She was a bitch we bred, and then placed with a newlywed couple when she was about four years old. The wife had grown up with Shelties, and was so excited to finally have one of her own. The husband, on the other hand, had never lived with a dog before - but liked the idea of the wagging tail. Rosie was shy at first; she bonded strongly to the wife, who was patient and quiet and willing to work with her. On the other hand, Rosie was leery of the husband. He wanted a dog who would adore him right away, and he eventually showed his disappointment by "playfully" intimidating her (from Rosie's perspective, this was not "play", it was terrifying, and it made the situation worse). Eventually, the wife called us and, in tears, asked us to take Rosie back and find her a more suitable home. The husband went out and bought the Golden Retriever puppy he always wanted. (Rosie went on to live with a quiet, gentle retired couple who adored her to the end of her days. The young wife and husband divorced.)
The second was Belle. Belle was also a puppy we bred; she went to her new home, with an older couple, as a puppy. The years went by, the husband passed away, and the wife began to have catastrophic health problems. She asked if we could take Belle - now ten years old - and find her a new home, which we did. Belle's new family were very concerned at first: she would not eat, except through the middle of the night, she would only urinate in a specific spot in a park two blocks away, and she was flighty and nervous. They had had Shelties before, and were very concerned that Belle didn't seem to be bonding to them. Luckily, I live in the same city as her new owners (they're my aunt and uncle!), and I see them often. I recommended patience, and heaps of it! I encouraged them to stick it out, work with the dog, and not get frustrated (because, as above, frustration would make things worse). They did. Belle has lived with them for six months now, and every time I visit I am amazed at how much more relaxed and comfortable she is. It's been a slow, incremental change, but it's happening. And as her owners continue to work on her schedule, I know that she will continue to blossom.
I also want to highlight some things from your post that stood out to me:
"is a basket case": Right off the bat, you have given her a label. By thinking of her as "a basket case", I can guarantee that you will make her so.
"scared of my sons... scared of him... avoids him": And so? She just met them. Give her time to watch them. She might never adore them. They have the power in this relationship: the onus is on them to win her trust, not on her to come to trust them.
"my mom who is here for his graduation": Again, she's in a new home, with a new family, and now a new person is visiting. This is stressful for her; she is not ready to be a friendly-happy dog with newcomers.
"I cannot have a scaredy cat": Well, can you? I will be upfront and say that we place dogs who don't meet the criteria for show or breeding. Are you willing to place this dog if she doesn't meet your criteria for participating in sports? You may never be able to make her into not-a-scaredy-cat. Either be 100% willing to responsibly place her in a home that is a better fit, or be 100% willing to live and work with the dog you have. (And again, watch out for those labels: once you apply them, they tend to stick.)
You need to meet the dog where she is, in this moment. If she is new (six months or less), then I can tell you that she WILL improve, but only with patience, followed by more patience. Do not get caught up in "what's wrong" or "what if" (or "she would do so great in [sport]"); stay in the moment with her. Does she want to retire to the bedroom when company calls? That's OK. Does she not want to make eye contact with your son? That's OK too (he can start trying to win her: lay down on the floor, look away, and gently toss her treats. It will take time.). Frustration and flooding (that is, giving the dog more to cope with than she is able to) will not help - they will set her back, making you more frustrated and making the situation worse.
There are great trainers and resources out there to help you build confidence in a nervous dog; this post is long enough already! My goal here is to lay the foundation for you to let go of your expectations (and your frustration) and to give the dog room.
