Jun 21 2022 01:55 PM
Jun 21 2022 01:55 PM
In a database named “DesignTest”, create database tables to represent the following data.
(A “point in time” database architecture is required, so that we can track every change that
is made to the data without using separate audit tables. Make sure to refer to all the later
questions to fully appreciate what data will be required to be retrieved in later stages.)
We have a number of people who work for different companies. Each person only
works for one company at a time, but may move from one company to another.
Against each person, we need to store their forename, surname, salutation (Mr,
Mrs, etc.), date of birth and which company they work for. The company data should
just include the name of the company.
2. Create any required stored procedures to add and amend the data in these tables. See the
“Test1” script later on to fully understand the scope of these stored procedures.
3. Create a stored procedure to retrieve the details for a person, including details of the
company they work for. The stored procedure should take two parameters which represent
the ID of the person and the point in time (date/time) of the data that we wish to be
returned. If this parameter is null, the current details (i.e. the most recently updated) should
4. Create a test SQL script named “Test1” to perform the following actions:
Clear out all existing data in the database.
Insert the following details into the database using the stored procedures created
o Mr John Smith, born 5th June 1975, working for HRS Ltd.
o Mrs Jane Doe, born 12th December 1982, working for HRS Ltd.
o Mr Tom Smith, born 18th January, 1990, working for MyCorp Ltd.
Perform the following updates using the stored procedures created earlier, with a 2
second pause before each stored procedure call to simulate transactions happening over
time. Also, record the current time of each operation as indicated below, so we can use
this time later on:
o Record the current date/time in a local variable named “@Time1”.
o Update John Smith’s date of birth to 5th June 1977.
o Record the current date/time in a local variable named “@Time2”.
o Update HRS Ltd’s name to Human Recognition Systems Ltd.
o Record the current date/time in a local variable named “@Time3”.
o Move Jane Doe to the company MyCorp Ltd.