Part 1: Indexes /40

The last thing well be doing with our employee database is adding a few indexes. After adding each index, run the associated query/queries and record the performance (planning time and execution time). Also look at the explain plan of the queries. Youll probably need to rewrite the queries slightly to fit your database (if there are different columns or tables).

Index 1: Add an index to the employee_histories table first_name and last_name fields.

Index 2: Add an index to the employee_jobs table employee_id and job_id fields. Index 3: Add an index to the employees table birthdate field.

For each index, answer the following questions:

1. Fill out the tables below describing how adding the index affected the planning and execution timings.
2. Did adding the index change the explain plans? What changed?
3. Was this what you expected to happen for the timing and the execution plans? What is a possible reason for this change (or lack of change)?

Index 1

Execution Time	Without index	With index
Query 1
Query 2

Index 2

Execution Time	Without index	With index
Query 3
Index 3
Execution Time	Without index	With index
Query 4

Part 2: Normalization /40

Lets pretend that the company whose employees weve been managing so far is an engineering firm. The company manages multiple projects at a time, and assigns its employees to tasks on the different projects. Only one employee can be assigned to a project task. Below is some un-normalized data used to manage projects in a company. After analyzing this sample data, structure it in 1^st normal, 2^nd normal, and 3^rd normal form one step at a time, showing the results of each step. So you should have 3 diagram one for your data in 1^st normal, one for 2^nd normal, and one for 3^rd normal.

TeamMemberId	TeamMemberFirstName	TeamMemberLastName	Project Code	Project Name	Project Status	Project Manager	TaskNumber	Task Status
1	John	Smith	DDL	Darren & Darren Ltd	Active	Garth Butler	10132133134	ResolvedIn ProgressNot StartedIn Progress
2	Dave	Richter	DDL KMI	Darren &Darren Ltd Kristen MotorsInc.	Active Active	GarthButler Jim David	100110 1013	In ProgressNot Started Not StartedResolved
3	Janie	Klotter	KMI	Kristen MotorsInc.	Active	Jim David	1215	In ProgressResolvedResolved

Part 3: Concurrency /20

1. Scenario Transaction A and B are being run concurrently in separate sessions.

Below is the initial state of the Accounts table before any transaction is run

Account Number	Account Nickname	Account Balance
1	Chequing	450
2	Chequing	200

Transaction A	Transaction B
SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED;BEGIN SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; UPDATE accountsSET account_balance = 0WHERE account_number = 2; END;COMMIT;	SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED;BEGIN SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; UPDATE accountsSET account_balance = account_balance 100WHERE account_number = 1; UPDATE accountsSET account_balance = account_balance + 100WHERE account_number = 2; END;COMMIT;

1. What would the Accounts table look like after these transactions are finished?

Account Number	Account Nickname	Account Balance

1. What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?

2. Transaction C and D are being run concurrently in separate sessions Below is the initial state of the Accounts table before any transaction is run:

Account Number	Account Nickname	Account Balance
1	Chequing	450
2	Chequing	200

Transaction C	Transaction D
SET TRANSACTION ISOLATION LEVEL READCOMMITTED;BEGIN SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; END;COMMIT;	SET TRANSACTION ISOLATION LEVEL READCOMMITTED;BEGIN INSERT INTO accounts (account_number, account_nickname, account_balance)VALUES(3, Savings, 50); UPDATE accountsSET account_balance = 300WHERE account_number = 1; END;COMMIT;

1. What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?

3. Transaction E and F are being run concurrently in separate sessions

Below is the initial state of the Accounts table before any transaction is run:

Account Number	Account Nickname	Account Balance
1	Chequing	450
2	Chequing	200

Transaction E	Transaction F
SET TRANSACTION ISOLATION LEVEL UNCOMMITTEDREAD;BEGIN SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; UPDATE accountsSET account_balance = 300WHERE account_number = 1; SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; END;ROLLBACK ;	SET TRANSACTION ISOLATION LEVEL UNCOMMITTEDREAD; BEGIN SELECTa.account_number,a.account_nickname,a.account_balanceFROM accounts; INSERT INTO accounts (account_number, account_nickname, account_balance)VALUES(3, Savings, 50); END;COMMIT;

1. What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?