Sounds like you want a tac. Sci has sort of that role, but only in a team setting, setting up kills for the team to finish, rather than blasting them through on your own. (Tacs, by comparison, wait for the team to set up a kill, then make with the shooty.)
The two passives you will need are Accurate and Evasive. The other two don't particularly matter, so you can pick whatever species you want. Alien, with it's potential 4 space passives, will have a tiny edge over most species, which have a maximum of 3, but the difference is minimal. Alien also has the most dress-up options, and is therefore super OP in at Fashion v Fashion.
There are plenty of guides to setting up a tactical officer for pvp, and they all follow the "super ultra mega spike damage" path, so I won't go into that. Instead, I will just leave you with two things:
1: The Aquarius Destroyer is too fragile for PvP. Get a Fleet Patrol Escort, a Fleet Heavy Escort Carrier, a Fleet Advanced Escort, or if you can afford it, a Jem Hadar Bug.
2: Join a PvP fleet. STO is not a solo game, and you can't learn very much from guides and forum posts.
All player types are equally useful in PVP, you just have to know what each is capable of. Cruisers don't "destroy" thing per-say, but they do make sure it's allies escorts can do their job, for example.
If you're going down the escort path, don't try an Aquarius. It's just awful. While it's turn-rate is great that's pretty much it. Not to mention it'll cost you 4 or 5 fleet ship modules just to get one...
Have a look at all the escorts available, their all good at something. Personally I go for either the Defiant or the Jemmy for aggressiveness.
As guriphu said below, consider a PVP fleet to join it too, or at least hang out with. You're welcome to hang with us and get a few tips.
For PvP you may try a sci or tac in tactical Vesta with MES III and VM III and Aux cannons (with high shield/aux power setup). It will require quite specific gameplay, with your ship being sort of F-22 (or BoP) which strikes and vanishes, no dogfights etc. but I think it could work.
But this will be either a lone wolf ship for Ker'rat or for a team that you know really well and played with for long time, othervise it would be rather squishy ship, as you will need to dedicate the two highest sci slots for stealth and offensive powers and the universal lt.cmdr for tactical to get CRF II and HYT II.
And, of course, some real money or long grind to get Vesta in three pack (3x Aux cannons).
I played with simmilar idea - just for MVAE. Worked pretty well, but the ship was extremely squishy, I think worse then BOP as you needed full Aux to MES and full Weapon power for cannons and could not swith the power as the ship relied on Alphas and hit-run tactics.
But for killing it worked pretty well in Ker'rat and C&H - quite a BoP hunter. Loved to kill them when they were decloaking for Alpha strike not realizing that my black MVAE was nearby
For PUGer as myself it was pretty useless in Arenas.
It should work with Vesta a little better cause of Aux cannons and more capable shielding, but then Vesta is less manoeuvrable then MVAE so who knows.
The Vesta isn't slow, it doesn't even come close to being slow. Now Carriers, or at least the Fat Vo'quv and Kitty Carrier, those are slow.
As for a Defiant being squishy, built wrong, they are squishy (actually they are crunchy, not squishy, like salad croutons), but we all have seen or flown Defiants that moved so fast, it seemed like they were taking a severe beating when really, everything coming at them just missed completely. So built right, they aren't so easily destroyed.
As for what ship you would want to fulfill how you want to play, Escorts are what you need to look at. Which one you pick depends on which one you like the best.
For more durability, go for the Patrol Escort or the Fleet Patrol Escort, or the Escort Retrofit or Fleet variant (they are the Saber class ships you get at Tier 2, just beefed up to Tier 5 standards). These ships have more Engineering console slots and more wiggle room with Engineering Bridge Officers.
For more Science tricks, the Advanced Escort, Multi-Vector Advanced Escort, or the Fleet Advanced Escort is the ship for you. They are less durable than the previous ships I mentioned, but have more Science Consoles and Science Bridge Officer slots, and you can slot some Science heals to make up for lost Engineering power.
For sheer destructive power, the Tactical Escort Retrofit or the Fleet Version is your ship. The Fleet Version even possesses 5 Tactical Consoles. That is a lot of damage to be unleashing on your enemies, and they can be slotted with the Cloaking Console for surprise Alpha's.
You should not get the Aquarius, it is severely gimped. It is squishy as hell, possesses fewer weapons than other Escorts, and although it has more Engineering consoles and universal slots, it's weak hull and shields, and no battle cloak (which is the only thing that keeps the Bird of Prey's on the Klingon side from being terrible ships), make this possibly the worst Escort in the game.
And I doubt you will have the money to purchase the Jem'hadar Attack Ship, hence why I didn't mention it in the Escorts to choose list.