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:
- Fill out the tables below describing how adding the index affected the planning and execution timings.
- Did adding the index change the explain plans? What changed?
- 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 1st normal, 2nd normal, and 3rd normal form one step at a time, showing the results of each step. So you should have 3 diagram one for your data in 1st normal, one for 2nd normal, and one for 3rd 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
- 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; |
- What would the Accounts table look like after these transactions are finished?
| Account Number | Account Nickname | Account Balance |
- What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?
- 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; |
- What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?
- 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; |
- What type(s) of data inconsistency is caused in this case (lost update, dirty read, nonrepeatable read, or phantom read)?
Reviews
There are no reviews yet.