top of page

Some Initial Setup

Nov 1, 2022
2 min read

Before I start answering requests from DK Travel, I need to set up an initial customer database and due to GDPR cannot use real data. I have downloaded some English first names from the internet, I have randomly added them to real surnames from a clients database and used a random address generator online to produce addresses. I now have 1000 customers and have added them into a SQL Server 2019 database table called tblCustomers. I added an identity column as primary key and also added a town column and using T-SQL split out the town from the end of the address line. The T-SQL for this is below.



ALTER TABLE [DKTravel].[dbo].[addresses] ADD Town VARCHAR(100)

UPDATE  [DKTravel].[dbo].[addresses] SET Town =  TRIM(RIGHT(Address,CHARINDEX(',',REVERSE(Address))-1)) 

ALTER TABLE [DKTravel].[dbo].[addresses] ADD ID INT IDENTITY(10000,1) PRIMARY KEY


EXEC sp_rename '[addresses]', 'tblCustomers' 


The next step is to create my resorts table



CREATE TABLE tblResort (ID INT IDENTITY(1,1) PRIMARY KEY,  Resort VARCHAR(100), PostCode VARCHAR(10))	


and populate as




INSERT INTO tblResort (Resort) VALUES ('Scarborough')
INSERT INTO tblResort (Resort) VALUES ('Keswick')
INSERT INTO tblResort (Resort) VALUES ('Bamburgh')
INSERT INTO tblResort (Resort) VALUES ('Edinburgh')
INSERT INTO tblResort (Resort) VALUES ('Great Yarmouth')	



The next step is to create a bookings table.



CREATE TABLE tblBookings (

ID INT IDENTITY(1,1) PRIMARY KEY,

CustomerID INT CONSTRAINT FK_BookingToCustomer FOREIGN KEY REFERENCES

tblCustomers(ID),

BookingDate DATETIME,

BookingAmount MONEY,

PassengerCount SMALLINT,

DepartDate DATETIME,

ReturnDate DATETIME,

LeadTime INT,

ResortID INT CONSTRAINT FK_BookingToResort FOREIGN KEY REFERENCES

tblResort(ID)

)



I need some way of generating random bookings for a selection of customers across the date range DK Travel has been in business, lets say since 2018. This can be done in T-SQL or Python or even Python in and SQL Server Procedure. I will leave that to the next blog post.

 
 
 

Recent Posts

See All
Welcome to DK Travel

To demonstrate some of my skills, I have made up a ficticious travel agency called DK Travel and they are going to be asking me for...

 
 
 
Random Booking Data

As mentioned in the previous post, I can generate random data using T-SQL or Python. I have decided to do the work in Python and...

 
 
 
Case Study - Request 1

We at DK Travel would like to see some of our data displayed in a Tableau dashboard to allow us to get some basic insights such as :...

 
 
 

Comments


bottom of page