1) You are implementing a Relational Database for a company t that sells its pro
ID: 3824896 • Letter: 1
Question
1) You are implementing a Relational Database for a company t that sells its products on internet. The Database maintains the Customer information in a table called “CUSTOMERS”. The primary key of this table is a column called “CustomerID”. There are also columns “LastName”, “FirstName” , “City” , and “ZipCode” representing last name, first name, city in which the Customer lives and the 5 digit zip code of the customer respectively. When a customer calls the customer service department with any inquiries, the customer service representative asks the customer for his or her Customer ID and pulls the information related to that customer from the Database. However, if a caller does not remember his/her Customer ID, the Customer service representative asks the customer for his/her last name and the 5 digit zip code, pulls the information related to all the customers from the database with matching last name & zip code combination and then further narrows down the search for the customer record based on some other identifiable information about the customer. The CUSTOMER table is expected to have millions of records and the system needs to quickly retrieve a set of matching customer records based on Customer’s last name and 5 digit zip code combinations. As the Database Designer, how could you speed up the retrieval of data from the CUSTOMER table for a given combination of customer’s last name and zip code?
1) You are implementing a Relational Database for a company t that sells its products on internet The Database maintains the Customer information in a table called "CUSTOMERS". The primary key of this table is a column called "CustomerID". There are also columns "LastName", "First Name", "City", and "ZipCode" representing last name, first name, city in which the Customer lives and the 5 digit zip code of the customer respectively When a customer calls the customer service department with any inquiries, the customer service representative asks the customer for his or her Customer ID and pulls the information related to that customer from the Database. However, if a caller does not remember his/her Customer ID, the Customer service representative asks the customer for his/her last name and the 5 digit zip code, pulls the information related to all the customers from the database with matching last name & zip code combination and then further narrows down the search for the customer record based on some other identifiable information about the customer. The CUSTOMER table is expected to have millions of records and the system needs to quickly retrieve a set of matching customer records based on Customer's last name and 5 digit zip code combinations As the Database Designer, how could you speed up the retrieval of data from the CUSTOMER table for a given combination of customer's last name and zip code?Explanation / Answer
By creating non clustered index on lastname and zipcode columns we can retrieve the data faster. We can have upto 249 non clustered indexes on the table. In this when we create the non clustered index on columns on lastname and zipcode the database will maintain the page with lastname and zipcode seperatly form the actual table without making anychanges in the actual table.
In the nonclustered index the columns data will be stored in the alphabetical order but in the actual table data is not stored in the alphabetical order. Because of this when we made any data retrieval query on the actual table it needs to search row by row from the table as it doesn;t know exactly where the requested row is stored. But in case of clustered index data in this is stored in the alphabetical form so it directly goes to the row.
After it identifies the required row in the non-clustered index based on the cluster value it retrieves the other columns from the actual table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.