SQL Server Database Triggers & Procedures

In Progress

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: Database Administration, SQL

See more: review triggers database sql server, triggers sql, why data structure, what is record in data structure, sql price, sql get date, sql create table price, sales administration, need sql help, my sql create database, designer booking, core logic, c.i.e. tours, tour booking, ticket id, sql views, SQl server DEVELOPMENT, sql database improve, Sql database administration, server 2012 administration, procedures, passenger, get sql help, email administration, backup database

Project ID: #4518780

Awarded to:

SovDyn

Hello, I've been a Microsoft Certified DBA since year 2002 and a trainer at official MSFT courses on SQL Server in my region until 2011 so I would be happy to help you. Regarding optimization it depends on your data a More

$333 USD in 3 days
(7 Reviews)
6.1

15 freelancers are bidding on average $382 for this job

eperfections

Experienced Database programmer. I can review your database and create the triggers etc as specified. Thanks

$526 USD in 3 days
(74 Reviews)
6.0
ielkfarna

I can do this job perfectly.

$526 USD in 7 days
(2 Reviews)
4.2
feninsoftech

Hi, I have 8 years experience in SQL Server designing, indexing, writing Stored Procedure, triggers and views. Will provide new procedure with proper joins and index selection.

$388 USD in 7 days
(5 Reviews)
3.9
rakib062

Please check private message.

$277 USD in 7 days
(6 Reviews)
3.5
giorgitbs

Hello, Can support you with your task. See PM for more details. Regards, Giorgi

$333 USD in 3 days
(2 Reviews)
2.3
sswijaya

Hi let me help you with this project.

$333 USD in 3 days
(2 Reviews)
2.1
shabirkssol

hi immediately ready to start work please check pmb for details

$421 USD in 3 days
(1 Review)
1.4
qualitylover

I have more than 7 years of experience as SQL / Application developer.Lets start the work to finish it professionally and within timeline.Sample of work provided upon request.

$370 USD in 8 days
(1 Review)
1.4
shreyasoftwares

I have more than 5+ year experience in C# programing and 2+ year experience in wpf application with DevExpress ,Telerik controls and using sql server. I want to work with you, if you give me a chance then I will work More

$277 USD in 3 days
(0 Reviews)
0.0
sandeep136

Hi I am a SQL Server Developer having 6 years of experience in development, design and optimization. we are good to start with the project. waiting for the positive response from your side. Thanks

$277 USD in 5 days
(0 Reviews)
0.0
suniljainmca2008

Hi, I have done a lot of work in database designing in SQL Server. I have designed total of 10 databases in which 3 were of very complex in nature.

$250 USD in 1 day
(0 Reviews)
1.6
YainaVilla

Hi, i can do it. perfectly.

$555 USD in 3 days
(0 Reviews)
0.0
userdatacode

It would be my pleasure, to have the chance to work with you. Your satisfaction is 100% Guaranteed. I have read the project description. I am ready to work on your project I have a team of experienced mobile and web de More

$666 USD in 20 days
(0 Reviews)
0.0
victorrseloy

Consider It done. I have a large expertise regarding to sql server. It will be a pleasure work to you.

$400 USD in 3 days
(0 Reviews)
0.0
Rajmohan007

hi Expert SQL server professional here to help. I have done a lot of similar jobs before, and can complete your request quickly. Please refer PM for more details.

$444 USD in 3 days
(0 Reviews)
0.0
mrgn6ravi

Hi, I have good experience in SQL Server, DB Design, SQL writing, stored procedures, triggers and other db stuffs. I am willing to do this task and looking for the reply.

$445 USD in 7 days
(0 Reviews)
0.0