Section Two Learning by Doing In this section, you apply what you learned in the first section with a series of exercises. You will be deciding index placement for a large law firms relational schema and associated application. Overview The law firm represents each of its clients cases with one or more of its lawyers. A clients cases are tracked, with notes entered by the firm for the case as needed. The law firm handles cases in a large variety of locations, including court rooms throughout the state, and other locations as needed. The firms clients all do business in the state, but live all over the country. Entity Relationship Diagram The schemas ERD is listed below (in a modified UML class diagram). The Client table contains data for the clients represented by the law firm, including the clients name, birth date, gender, address, the date the client started working with the law firm, any special comments the law firm records about the client. The State table contains both a full name and an abbreviated name for the states in which a client can reside (some clients live out of state but do business in the state). The Lawyer table contains data for the lawyers employed by the firm, including their name and birth date, their registration number for the state, the number of years they have practiced law, and when they began employment with the firm. The Cases table contains data for the cases handled by the law firm, including when the case began and ended (case_end_date will be null until the case ends), and the location the case is held. The Location table contains any location where a case can be held, including names of courts and other locations. Th Page 12 of 13 (comments) about a case, which is useful for strategizing about the case, or recording special adjustments or areas that need special attention. Lastly, the Represents table keeps track of which lawyers represent which clients for which cases. Application The application developed for the firm, and which uses this schema, is named LawTrax. It is a day?to?day business system that allows the firm to track clients, their cases, and the lawyers that represent them. The data is updated throughout each day with a series of data entry screens as new clients hire the firm, as cases progress, and as lawyers join or leave the firm. Other than supporting the aforementioned data entry screens, LawTrax also supports searching for clients, cases, and lawyers. The application development team was able to pull out two queries to help you. The first is one of the queries used by the search screen that allows LawTrax users to search for specific clients by name. SELECT * FROM Client WHERE Client.last_name = ? AND Client.first_name = ?; Note that the question marks (?) in the query are parameters that are passed to the query dynamically at runtime. They are populated with whatever values the user enters on the search screen. The team managed to capture a screenshot for the screen as well, which is the following: The team also pulls out a query LawTrax uses to list out the most recent cases that have not yet been closed, along with the lawyers that represent the case. SELECT * FROM Cases JOIN Case_notes ON Case_notes.cases_id = Cases.cases_id JOIN Location ON Location.location_id = Cases.location_id JOIN Represents ON Represents.cases_id = Cases.cases_id JOIN Lawyer ON Lawyer.lawyer_id = Represents.lawyer_id WHERE Cases.case_begin_date > ? AND Cases.case_end_date IS NULL; Page 13 of 13 Again, the question mark (?) is used by LawTrax to dynamically put in a date at runtime, rather than hardcoding one specific date. When the system first launched, the performance of LawTrax was good, because not much data was present. As time has gone on, however, system performance has gradually decreased to the point where it is now unbearably slow, and the system has become mostly unusable. The law firm has hired you as a consultant to urgently help fix the performance issues. To your horror, a quick scan of the databases metadata reveals that the database has no indexes except the ones automatically created by the database. The database designer created no indexes at all! You quickly get to work deciding where to place the indexes. 1. To get started, list out all of the primary key columns in the schema. These have already been indexed by the database. Identify them using the standard tablename.columnname format as used in Examples 2 and 3 in the first section. 2. Next, you need to identify all of the foreign key columns in the schema. As described in the first section, these all need to be indexed. You will need to decide whether to make them unique indexes, or non?unique indexes. List out all foreign key columns in tablename.columnname format, indicate for each whether a unique or non?unique index is needed, and explain your choice. 3. You have been provided with some queries by the development team; they are a good resource to continue your index work. Indicate which columns would need to be indexed for the given queries using the tablename.columname format. Make sure to indicate whether the indexes should be unique or non? unique, and to explain your choice. 4. At this point, you have seen a couple of queries used by the system, and have read a description of what the system does. Your next step is to identify all remaining columns that should be indexed based upon this information. You may recall that the original database designer did not index any columns, so all remaining columns should be considered. In order to accomplish this, you will need to make reasonable assumptions about what kinds of queries the system uses in order to do its work. State your assumptions, then list all additional columns (in tablename.columnname format) that need an index. Explain why the index is beneficial, taking into account the factors described in the first section. Also identify whether the index should be unique or non?unique, and justify that choice. Congratulations! You have learned how to determine what columns need indexes, and this skill is extremely important for relational database design. Database developers and designers reg