SQL Server Database Triggers & Procedures

IN PROGRESS
Bids
17
Avg Bid (USD)
$401
Project Budget (USD)
$250 - $750

Project Description:
I need a SQL Server database designer to review a new SQL database to get it ready for development. I need someone who is great at SQL server to go through my new database, review it, improve it, and get it ready for development. This should take a few hours work. Specifically:

a) Create 3 procedures (and assign to calculated columns in the table) to calculate totals for three tables using their sub table totals: Tour, Bookings, and Passengers
b) Create triggers on 9 tables with a [Status] column, so that when the [Status] is updated, the corresponding [Status Date] column is set to current timestamp
c) Create views for core tables linking the foreign tables (Tours, Bookings, Passengers, Sales, Stock, Agents, Ticket,Stock)

I also need a general review and suggestions on changes to improve the database, including but not limited to

d) Review the indexes on each table and ensure best practice and good designs
e) Review the data types, relationships and general setup

You should record the SQL you propose to change the database so I can execute it on my copy and review your suggest changes. I also want an email explaining what changes you would recommend and why.

I will provide a zip file of a SQL Server 2012 backup. It's a new database without any real data and is around 1mb in size. You can just send it back to me updated.

Database structure overview

Tour > has Bookings > has Passengers > has Sales
Booking > has Payments

The logic for the procedures to calculate totals is very simple, something like follows (excuse poor SQL, this is why I need your help, but the general gist is here):

Passenger.[Calculated Total] = [Passenger].[Ticket Price] + SELECT SUM([Sale Total]) FROM Sale WHERE Passenger.[Passenger ID] = Sale.[Passenger ID]
Booking.[Calculated Total] = SELECT SUM(Passenger.[Calculated Total]) FROM Passenger WHERE Passenger.[Booking ID] = Booking.[Booking ID]
Booking.[Calculated Payment] = SELECT SUM(Payment.[Payment ID] = Booking.[Booking ID]
Tour.[Calculated Total] = SELECT SUM(Booking.[Calculated Total]) FROM Booking WHERE Booking.[Tour ID] = Tour.[Tour ID]

Skills required:
Database Administration, SQL
About the employer:
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


Hire SovDyn
$ 333
in 3 days
$ 526
in 3 days
$ 526
in 7 days
Hire feninsoftech
$ 388
in 7 days
$ 277
in 7 days
$ 333
in 3 days
Hire sswijaya
$ 333
in 3 days
$ 421
in 3 days
$ 370
in 8 days
Hire shreyasoftwares
$ 277
in 3 days