Aayush_Mallik_C70315183_Assignment_5
pdf
keyboard_arrow_up
School
Clark University *
*We aren’t endorsed by this school
Course
123
Subject
Information Systems
Date
Apr 3, 2024
Type
Pages
5
Uploaded by MateWolverine2181
12
th
November 2023 Aayush Mallik C70315183 Assignment 5 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. -
These are the primary keys that have been obtained: Primary Keys Desc client_id It is the primary key in Clients table state_id It is the primary key in State
table represents_id It is the primary key in Represents table cases_id It is the primary key in Cases
table lawyer_id It is the primary key in Lawyer
table case_notes_id It is the primary key in Case_notes
table location_id It is the primary key in Location
table 1)
Client.client_id 2)
State.state_id 3)
Represents.represents_id 4)
Cases.cases_id 5)
Lawyer.lawyer_id 6)
Case_notes.case_notes_id 7)
Location.location_id
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 i
n tablename.columnname format, indicate for each whether a unique or non‐unique index is needed, and explain your choice. -
These are the foreign keys that have been obtained: o
Represents Table Foreign Key Index Type Explanation Represents.client_id Non-Unique A client can be represented by multiple lawyers in different cases Represents.lawyer_id Non-Unique A lawyer can represent multiple clients in different cases Represents.cases_id Unique The combination of client, lawyer, and case should be unique, as a lawyer cannot represent the same client in the same case multiple times o
Cases Table Cases.location_id Non-Unique Multiple cases can occur at the same location o
Case_notes Table Case_notes.cases_id Non-Unique Multiple notes can be associated with the same case o
Client Table Client.state_id Non-Unique Multiple clients may reside in the same state
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 shoul
d be unique or non‐ unique, and to explain your choice. -
These are the columns which I think need to be indexed 1. Search Query for Specific Clients
: - Client.last_name (non-unique index): Allowing quick retrieval of clients based on last name. - Client.first_name (non-unique index): Supporting fast searches for clients by first name. Explanation
: - Non-unique indexes are chosen as names may not be unique, and multiple clients may have the same last or first name. 2. List Most Recent Open Cases Query
: - Cases.case_begin_date (non-unique index): Facilitating fast retrieval of cases based on the beginning date. - Cases.case_end_date (non-unique index): Supporting efficient searches for cases with no end date. - Case_notes.cases_id (non-unique index): Enabling quick access to case notes based on associated case IDs. Explanation
: - Non-unique indexes are chosen to accommodate scenarios where multiple cases may have the same begin or end date, and multiple case notes can be associated with the same case.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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 th
e index should be unique or non‐
unique, and justify that choice. -
These are the rest that should be indexed: 1. Client Table
: - Client.gender (non-unique index): Assuming there might be queries filtering clients based on gender. - Client.became_client_on_date (non-unique index): For queries involving client onboarding dates. Explanation
: - Non-unique indexes are chosen to accommodate potential queries with conditions that might return multiple results (e.g., clients of the same gender or becoming clients on the same date). 2. Cases Table
: - Cases.location_id (non-unique index): If queries involve filtering cases based on the location where they are held. Explanation
: - Non-unique index is chosen as multiple cases may occur in the same location. 3. Lawyer Table
: - Lawyer.state_registration_number (unique index): Assuming that state registration numbers are unique to each lawyer. Explanation
: - Unique index is chosen because state registration numbers are typically unique to individual lawyers, ensuring the uniqueness constraint.
4. Represents Table
: - Represents.cases_id (non-unique index): For queries involving the association of lawyers with cases. - Represents.client_id (non-unique index): For queries involving the association of lawyers with clients. Explanation
: - Non-unique indexes are chosen to accommodate associations where multiple lawyers may represent the same case or client. 5. Case_notes Table
: - Case_notes.cases_id (non-unique index): For queries involving case notes associated with specific cases. Explanation
: - Non-unique index is chosen as multiple case notes may be associated with the same case.