Database Management: Designing and Querying Tables, Exams of Distributed Database Management Systems

Download Database Management: Designing and Querying Tables and more Exams Distributed Database Management Systems in PDF only on Docsity! WGU D427 DATABASE MANAGEMENT APPLICATIONS EXAM PRE-ASSESSMENT AND OBJECTIVE ASSESMENT | ACCURATE CURRENTLY TESTING EXAMS | ACCURATE AND VERIFIED FOR GUARANTEED PASS | LATEST UPDATE How many attributes are present in the address fragment? - 1 - 2 - 3 - 4 3 2. The Patient table has the following columns: first_name varchar(20) last_name varchar (30) birthdate date patient_id int Which column should be designated as the primary key for the Patient table? - first_name - last_name - birthdate - patient_id patient_id 3. The Exam table has the following columns: exam_id int exam_date date exam_reason varchar(100) patient_number int Which column should be designated as the foreign key for the Exam table? - exam_id - exam_date - exam_reason - patient_id patient_id 4. Which data type represents numbers with fractional values: - varchar - integer - binary - decimal decimal 5. Which of the following is a DDL (Data Definition Language) command? - INSERT - SELECT - CREATE INDEX - UPDATE CREATE INDEX CREATE, ALTER, DROP 6. Which of the following is a DML (Data Manipulation Language) command? - CREATE VIEW - CREATE TABLE - INSERT - ALTER INDEX INSERT 7. Patient Table PK patient id first name last name birthdate Exam Table PK exam id exam date exam reason FK patient id CREATE TABLE Exam ( exam_id INT NOT NULL AUTO_INCREMENT, exam_date DATE NOT NULL, exam_reason VARCHAR(100), patient_id INT NOT NULL, - It is stored. - It must be refreshed whenever the base table changes. - The results are stored as a temporary table. It is stored It must be refreshed whenever the base table changes 11. The Student table will have the following columns: StudentID—positive integer FirstName—variable-length string with up to 50 characters MiddleInitial—fixed-length string with 1 character LastName—variable-length string with up to 50 characters DateOfBirth—date AccountBalance—positive decimal value representing a balance of up to $24,999, with 2 digits for cents How many attributes are present in the address fragment? - 1 - 2 - 3 - 4 4 The Package table has the following columns: Weight—decimal Description—optional variable length string LastChangedDate—date TrackingNumber— integer Which column should be designated the primary key for the Package table? - Weight - Description - LastChangedDate - TrackingNumber Tracking Number Which data type will store "2022-01-10 14:22:12" as a temporal value without loss of information? - DATE - DATETIME - DECIMAL - BIGINT DATETIME Which SQL command is an example of data definition language (DDL)? - UPDATE - ALTER - SELECT - DELETE CREATE, ALTER, OR DROP How would a database engine process an update that violates a RESTRICT referential integrity constraint? - The offending value would be set to the database default. - The update would be rejected by the database. - The offending value would be changed to NULL. - The updated value would flow to the primary key. The update would be rejected by the database. Which restriction applies when using a materialized view? - The users of the view must provide search terms. - The underlying data must be periodically refreshed. - The tables referenced in the view must be indexed. - The rows in the table must be compressed. The underlying data must be periodically refreshed. Which query illustrates performing an outer join of the Movie table with a different table? - SELECT M.Title, A.Actor FROM Movie M, Actor A WHERE M.ActorID = A.ID - SELECT M.Title, A.Actor FROM Movie M LEFT JOIN Movie MB ON M.ID, Actor A - SELECT M.Title, A.Actor FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.Id - SELECT M.Title, A.Actor FROM Movie M INNER JOIN Actor A ON M.ActorID = A.ID SELECT M.Title, A.Actor FROM Movie M LEFT JOIN Movie MB ON M.ID Actor A Assume there are two tables, A and B. Which rows will always be included in the result set if Table A is inner joined with Table B? - Only rows in Tables A and B that share the join condition - all rows in Table B - All rows in Table A - Only rows in Tables A and B that do not share the join condition Only rows in Table A and B that share the join condition 1. Portland, OR 97212 Write a SQL statement to create the Student table. Do not add any additional constraints to any column beyond what is stated. CREATE TABLE Student( StudentID INT UNSIGNED, FirstName VARCHAR(50), MiddleInitial CHAR(1), LastName VARCHAR(50), DateOfBirth DATE, AccountBalance DECIMAL(7,2) UNSIGNED ); 12. The Classification table has the following columns: ClassificationCode—integer, primary key ClassificationDescription—variable-length string The Vehicle table should have the following columns: Name—variable-length string, maximum 30 characters ClassificationCode—integer Title Speaker Minutes Rock Painting, Kecia McDonald, 25 Write a SQL statement to insert the indicated data into the Podcast table. INSERT INTO Podcast (Title, Speaker, Minutes) VALUES ('Rock Painting', ' Kecia McDonald', 25); 20. The Podcast table has the following columns: podcastID—integer, primary key, auto_increment Title—variable-length string Speaker—variable-length string Minutes—integer Write a SQL statement to delete the row with the ID value of 33 from the Podcast table. DELETE FROM Podcast WHERE ID = 33; 21. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer Write a SQL statement to update the Year value to be 2022 for all books with a Year value of 2020. UPDATE Song SET Year = 2022 WHERE Year = 2020; 22. Which query illustrates performing an outer join of the Movie table with a different table? - SELECT M.Title, A.Name FROM Movie M, Actor A WHERE M.ActorID = A.ActorID; - SELECT M.Title, A.Name FROM Movie M, Actor A WHERE M.ActorID = A.MovieID; - SELECT M.Title, A.Name FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.ActorID; - SELECT M.Title, A.Actor FROM Movie M INNER JOIN Actor A ON M.ActorID = A.ActorID SELECT M.Title, A.Name FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.ActorID; 23. Assume there are two tables, A and B. Which rows will always be included in the result set if Table A is inner joined with Table B? a. Only rows in Tables A and B that share the join condition b. All rows in Table B c. All rows in Table A d. Only rows in Tables A and B that do not share the join condition. Only rows in Tables A and B that share the join condition 24. The database contains a table named Book. Write a SQL query to return all data from the Book table without directly referencing any column names. SELECT * FROM Book; 25. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer Write a SQL query to retrieve the Title and Genre values for all records in the Book table with a Year value of 2020. Ensure your result set returns the columns in the order indicated. SELECT Title, Genre FROM Book WHERE Year = 2020; 26. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer Write a SQL query to display all Title values in alphabetical order A-Z. SELECT Title FROM Book ORDER BY Title ASC; 27. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer Write a SQL query to output the unique Genre values and the number of books with each genre value from the Book table as GenreCount. Sort the results by the Genre in alphabetical order A- Z. Ensure your result set returns the columns in the order indicated. SELECT Genre, COUNT(*) AS GenreCount FROM Book GROUP BY Genre, ORDER BY Genre ASC; 28. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer The YearSales table has the following columns: Year—integer TotalSales— bigint unsigned Releases— integer Write a SQL query to display both the Title and the TotalSales (if available) for all books. Ensureyour result set returns the columns in the order indicated. SELECT Title, TotalSales FROM Book LEFT JOIN YearStats ON Book.Year = YearStats.Year; 29. The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string Genre—variable-length string Year —integer Write a SQL query to return how many books have a Year value of 2019. SELECT COUNT (*) FROM Book WHERE Year = 2019; exam_id exam_date exam_reason patient_id Which query would produce a result set that listed all of the patients, regardless of whether they had an appointment in the Exam table or not. - SELECT patient_id, first_name, last_name, birthdate, exam_date FROM Patient RIGHT JOIN EXAM ON Patient.patient_id = Exam.patient_id; - SELECT patient_id, first_name, last_name, birthdate, exam_date FROM Patient INNER JOIN EXAM ON Patient.patient_id = Exam.patient_id; - SELECT patient_id, first_name, last_name, birthdate, exam_date FROM Patient INNER JOIN EXAM ON Patient.patient_id = Exam.exam_id; - SELECT patient_id, first_name, last_name, birthdate, exam_date FROM Patient LEFT JOIN EXAM ON Patient.patient_id = Exam.patient_id; SELECT patient_id, first_name, last_name, birthdate, exam_date FROM Patient LEFT JOIN EXAM ON Patient.patient_id = Exam.patient_id; 39. MODELS TABLE model_id lname birtdate model_type_id MODEL_TYPE model_type_id hourly_fee Which query would produce a result set that listed all of the Model Types, regardless of whether a model was assigned to that type. - SELECT model_id, lname, Models.model_type_id, hourly_fee FROM Models RIGHT JOIN Model_TypeON Models.model_type_id = Model_Type.model_type_id; - SELECT model_id, lname, Models.model_type_id, hourly_fee FROM Models LEFT JOIN Model_TypeON Models.model_type_id = Model_Type.model_type_id; - SELECT model_id, lname, Models.model_type_id, hourly_fee FROM Models INNER JOIN Model_TypeON Models.model_type_id = Model_Type.model_type_id; - SELECT model_id, lname, Models.model_type_id, hourly_fee FROM Models NATURAL JOIN Model_TypeON Models.model_type_id = Model_Type.model_type_id; SELECT model_id, lname, Models.model_type_id, hourly_fee FROM Models RIGHT JOIN Model_TypeON Models.model_type_id = Model_Type.model_type_id; 40. Write a query to list Models along with the model type they are assigned. Include all model information and the hourly fee. SELECT model_id, lname, birthdate, Models.model_type_id, hourly_fee FROM Models INNER JOIN Model_Type ON Models(model_type_id) = Model_Type(model_type_id); 41. Customer Table Customer_ID First_Name Last_Name Address City State Zip Mobile_Phone Write a query to delete Amy Lin from the Customer table show below. DELETE FROM Customer WHERE Customer_ID = 101; 42. Write a SQL statement to update Blanca Garcia's phone number in the Customer table below to 555-222-1234. UPDATE Customer SET Mobile_Phone = '555-222-1234' WHERE Customer_ID = 104; 43. Write a SQL statement to retrieve all of the Customers from Seattle from the table below. SELECT * FROM Customer WHERE City = 'Seattle'; 44. PATIENT TABLE PK patient_id first name last name birthdate EXAM TABLE PK exam_id exam date exam reason FK patient_id Write the CREATE TABLE statement to create the Exam table below. Make sure to designate the primary key and foreign key of the table. CREATE TABLE Exam ( exam_id INT, exam_date DATE, exam_reason VARCHAR(255),, patient_id INT, PRIMARY KEY (exam_id), FOREIGN KEY (patient_id) REFERENCES Patient (patient_id) ); 45. Write a query to pull all of the data from the Bagel table below. Order the result set alphabetically by Bagel ID. BAGEL TABLE Bagel ID Bagel Name Bagel Description Cost/Bagel SELECT * FROM Bagel ORDER BY Bagel_ID ASC; 46. Write a query to count the number of customers in each city CUSTOMER TABLE Customer ID model_id phone_number SELECT M.model_id, M.last_name, M.first_name, T.model_type_id, phone_number FROM Models M JOIN Model_Types T ON M.model_type_id = T.model_type_id, JOIN Phone_Numbers P ON M.model_id = P.model_id; 52. STUDENT_GRADES TABLE studentID lastName grade Refer to the given SELECT statement and the table above. SELECT grade, COUNT(*) AS gradeCount FROM student_grades GROUP BY grade; Which clause added to the statement finds grades with a count greater than 2? a. WITH gradeCount <> DUPLICATE b. NOT gradeCount = NULL c. HAVING gradeCount > 2 d. INCLUDING gradeCount > 2 HAVING gradeCount > 2 53. Using the student_grades table listed above, write a query to count how many students have each grade (A, B, and C). MODELS TABLE model_id SELECT grade, COUNT(*) FROM student_grades GROUP BY grade; 54. Using the Model_Types table in the E-R diagram above, write a query to find the highest model fee. SELECT MAX(fee) FROM Models; 55. Write a query to update the hourly_fee to 100.00 more for model types with an hourly_fee less than 600.00 UPDATE Model_Types SET hourly_fee = hourly_fee+100.00 WHERE hourly_fee < 600.00; 56.Which query illustrates performing an outer equijoin of the Movie table with a different table? a. SELECT M. Title, A.Actor FROM Movie M, Actor A WHERE M.ActorID = A.ID b. SELECT M.Title, A.Actor FROM Movie MLEFT JOIN Movie MB ON M.ID = MB.ID, Actor A c. SELECT M. Title, A.Actor FROM Movie M RIGHT JOIN Actor A ON M.ActorID = A.ID d. SELECT M. Title, A.Actor FROM Movie M INNER JOIN Actor A ON M. ActorID = A.ID Select M.Title, A.Actor FROM MOVIE M RIGHT JOIN Actor A ON M.ActorID = A.ID; 57. Which rows will always be included in the result set if Table A is right joined with Table B? - Only rows in Tales A and B that share the join condition - All rows in Table B - All rows in Table A - Only rows in Tables A and B that do not share the join condition All rows in Table B 58. The Dog table has the following columns: CREATE TABLE dog ( dogID INT NOT NULL, name VARCHAR(10) NOT NULL, breedID INT, birthdate DATE ); Modify the name column so that the variable length string has a maximum of 25 characters. ALTER TABLE Dog CHANGE name name VARCHAR(25); The Member table will have the following columns: ID— positive integer FirstName—variable-length string with up to 100 characters MiddleInitial—fixed-length string with 1 character LastName—variable-length string with up to 100 characters DateOfBirth—date AnnualPledge—positive decimal value representing a cost of up to $999,999, with 2 digits for cents Write a SQL statement to create the Member table. Do not add any additional constraints to any column beyond what is stated. - CORRECT ANSWER CREATE TABLE Member ( ID INT PRIMARY KEY, FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8, 2) ); The Rating table has the following columns: RatingCode—variable-length string, primary key RatingDescription—variable-length string The Movie table should have the following columns: Title—variable-length string, maximum 30 characters RatingCode—variable-length string, maximum 5 characters Write a SQL statement to create the Movie table. Designate the RatingCode column in the Movie table as a foreign key to the RatingCode column in the Rating table. - CORRECT ANSWER CREATE TABLE Rating ( RatingCode VARCHAR(100) PRIMARY KEY, RatingDescription VARCHAR(255) ); Title—variable-length string Genre —variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to create an index named idx_year on the Year column of the Movie table. - CORRECT ANSWER CREATE INDEX idx_year ON Movie (Year); The Movie table has the following columns: ID—integer, primary key, auto-increment Title—variable-length string Genre— variable-length string RatingCode— variable-length string Year—integer The following data needs to be added to the Movie table: Title Genre RatingCode Year Pride and Prejudice Romance G 2005 Write a SQL statement to insert the indicated data into the Movie table. - CORRECT ANSWER INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ('Pride and Prejudice', 'Romance', 'G', 2005); The Movie table has the following columns: ID—integer, primary key Title— variable-length string Genre— variable-length string RatingCode —variable-length string Year— integer Write a SQL statement to delete the row with the ID value of 3 from the Movie table. - CORRECT ANSWER SELECT Title, Genre FROM Movie WHERE Year = 2020; The Movie table has the following columns: ID—integer, primary key Title— variable-length string Genre— variable-length string RatingCode —variable-length string Year— integer Write a SQL query to display all Title values in alphabetical order A-Z. - CORRECT ANSWER SELECT Title FROM Movie ORDER BY Title ASC; SELECT RatingCode, COUNT(*) AS RatingCodeCount FROM Movie GROUP BY RatingCode ORDER BY RatingCode ASC; The Movie table has the following columns: ID—integer, primary key Title— variable-length string Genre— variable-length string RatingCode —variable-length string Year— integer Write a SQL query to return how many movies have a Year value of 2019. - CORRECT ANSWER SELECT COUNT(*) AS MovieCount FROM Movie WHERE Year = 2019; The Movie table has the following columns: ID - integer, primary key Title - variable-length string Genre - variable-length string RatingCode - variable-length string Year - integer The YearStats table has the following columns: Year - integer TotalGross - bigint unsigned Releases - integer Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated. - CORRECT ANSWER SELECT Movie.Title, YearStats.TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year; San Francisco, CA 94110 USA How many attributes are present in the address fragment? - CORRECT ANSWER 4 Attributes The Package table has the following columns: Weight—decimal Description—optional variable length string LastChangedDate—date TrackingNumber— integer Which column should be designated the primary key for the Package table? - CORRECT ANSWER TrackingNumber Which data type will store "2022-01-10 14:22:12" as a temporal value without loss of information? - CORRECT ANSWER DATETIME What is the DATETIME datatype syntax? - CORRECT ANSWER DATE: YYYY-MM-DD DATETIME: YYYY-MM-DD HH:MM:SS What does DECIMAL(3,2) indicate? - CORRECT ANSWER It will have 3 numbers before the decimal and 2 after. a series of commands stored on the database. This allows the reuse of long or detailed queries instead of writing them for each use. It also provides a safe way to deal with sensitive data, especially with those unfamiliar with SQL syntax. - CORRECT ANSWER Stored Procedures involves instructing the DBMS software on what tables will be in the database, what attributes will be in the tables, which attributes will be indexed, and so forth. - CORRECT ANSWER Data definition language (DDL) refers to the four basic operations that can and must be performed on data stored in any DBMS (or in any other data storage arrangement, for that matter): data retrieval, data update, insertion of new records, and deletion of existing records. - CORRECT ANSWER data manipulation languages (DMLs) What would the SELECT statement look like if you use a BETWEEN. - CORRECT ANSWER SELECT * FROM CUSTOMER WHERE CUSTNUMB BETWEEN 1 AND 2; What would the SELECT statement look like if you use a IN. - CORRECT ANSWER SELECT * FROM CUSTOMER WHERE HQCITY IN ('Atlanta', 'Chicago', 'Washington'); What would the SELECT statement look like if you use a LIKE. - CORRECT ANSWER SELECT * FROM CUSTOMER WHERE HQCITY LIKE 'W%'; What are the two ways of using the LIKE command? - CORRECT ANSWER A% - The "%" means that any string of characters can follow afterwards. The percent sign represents zero or more arbitrary regular characters A_ - The _ means that there will be exactly one letter following the A. The underscore represents a single arbitrary regular character. The default order for ORDER BY is ascending. How can you make it descending order? - CORRECT ANSWER The clause can include the term ASC at the end to make ascending explicit or it can include DESC for descending order. Command to use AVG/SUM/MIN/MAX/COUNT aggregate functions? - CORRECT ANSWER SELECT AVG/SUM/MIN/MAX/COUNT(Column_Name) FROM SALES WHERE Name = 'Matt'; There are two specifications to make in the SELECT statement to make a join work. - CORRECT ANSWER o One is that the tables to be joined must be listed in the FROM clause. o Two is that the join attributes in the tables being joined must be declared and matched to each other in the WHERE clause. Example of JOIN CAUSE. - CORRECT ANSWER SELECT SPNAME FROM SALESPERSON, CUSTOMER WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM In 1970, Dr. of IBM published in Communications of the ACM a paper entitled "A Relational Model of Data for Large Shared Data Banks." This paper marked the beginning of the field of relational databases. - CORRECT ANSWER Dr. CODD Commands to create and drop a table? - CORRECT ANSWER CREATE TABLE DROP TABLE The CREATE TABLE command lets you define constraints in two different places: - CORRECT ANSWER - When you create the column definition (known as a column constraint). - When you use the CONSTRAINT keyword (known as a table constraint). You can exclude rows with other types of conditions by using the operator. - CORRECT ANSWER NOT WHERE Location NOT IN ('WV', TN' 'CA'); WHERE Total NOT Between '1000', 2000'; WHERE Name NOT LIKE 'Ma%'; The command tells the database system which database you're going to be using for the upcoming commands. - CORRECT ANSWER USE USE MainDatabase You can use the statement to provide specified information about a database. - CORRECT ANSWER SHOW SHOW DATABASES - lists all databases in the database system instance. SHOW TABLES - lists all tables in the default database. SHOW COLUMNS FROM TableName - lists all columns in the TableName table of the default database. Code INT UNSIGNED, ManagerID INT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID), ON DELETE CASCADE, ON UPDATE SET NULL ); How do you set a column constraint to be unique? - CORRECT ANSWER CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), UNIQUE (Name) ); How to you implement a CHECK constraint? - CORRECT ANSWER CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) CHECK NAME (Name = 'Matt'); ); Table constraints may be named using the optional keyword, followed by the constraint name and declaration. If no name is provided, the database generates a default name. - CORRECT ANSWER CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), PRIMARY KEY (Code), CONSTRAINT DepartmentForeignKey FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ); An JOIN returns only those rows where the linking values match in both of the tables or in result sets. - CORRECT ANSWER INNER JOIN INNER JOIN Example - CORRECT ANSWER SELECT RecipeTitle, Preparation, RecipeClassDescription FROM Recipe_Classes INNER JOIN Recipes ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID An JOIN asks your database system to return not only the rows that match on the criteria you specify (as the case with INNER JOIN) but also the unmatched rows from either one or both of the two sets you want to link. - CORRECT ANSWER OUTER functions manipulate string values. - CORRECT ANSWER String For example, SELECT LOWER('MySQL'); would return mysql. The clause is used with the GROUP BY clause to filter group results. - CORRECT ANSWER HAVING SELECT CountryCode, SUM(Population) FROM City GROUP BY CountryCode HAVING SUM(Population) > 230000; To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional keyword. - CORRECT ANSWER AS SELECT EmployeeNames AS Names FROM Employee; FULL JOIN selects all left and right table rows, regardless of match.In a FULL JOIN result table, unmatched left table rows appear with NULL values in right table columns, and vice versa. What is the syntax of a full join? - CORRECT ANSWER SELECT Department.Name AS GROUP, Employee.Name AS SuperVisor FROM Department FULL JOIN Employee ON Manager = ID An compares columns of two tables with the = operator. - CORRECT ANSWER equijoin Most joins are equijoins. A compares columns with an operator other than =, such as < and >. - CORRECT ANSWER non-equijoin A joins a table to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types. In this scenario, aliases are necessary to distinguish left and right tables. - CORRECT ANSWER self-join a database operation that reads index blocks sequentially, in order to locate the needed table blocks. - CORRECT ANSWER index scan the percentage of table rows selected by a query. - CORRECT ANSWER Hit ratio When a SELECT query is executed, the database examines the WHERE clause and estimates hit ratio. If hit ratio is high, the database performs a table scan. If hit ratio is low, the query needs only a few table blocks, so a table scan would be inefficient. - Instead, the database: o Looks for an indexed column in the WHERE clause. o Scans the index. o Finds values that match the WHERE clause. o Reads the corresponding table blocks. § If the WHERE clause does not contain an indexed column, the database must perform a table scan. the database repeatedly splits the index in two until it finds the entry containing the search value: - CORRECT ANSWER binary search o The database first compares the search value to an entry in the middle of the index. o If the search value is less than the entry value, the search value is in the first half of the index. If not, the search value is in the second half. o The database now compares the search value to the entry in the middle of the selected half, to narrow the search to one quarter of the index. o The database continues in this manner until it finds the index block containing the search value. an index on a sort column - CORRECT ANSWER primary index, also called a clustering index an index that is not on the sort column. - CORRECT ANSWER secondary index, also called a nonclustering index An index that contains an entry for every table row. - CORRECT ANSWER Dense Index An index that contains an entry for every table block. - CORRECT ANSWER Sparse Index We refer to the way that we store the data for subsequent retrieval as the . - CORRECT ANSWER file organization The way that we retrieve the data, based on it being stored in a particular file organization, is called the . - CORRECT ANSWER access method The index in the back of a book has three key elements that are also characteristic of information systems indexes: - CORRECT ANSWER 1. The items of interest are copied over into the index but the original text is not disturbed in any way. 2. The items copied over into the index are sorted (alphabetized in the index at the back of a book). 3. Each item in the index is associated with a "pointer" (in a book index this is a page number) pointing to the place in the text where the item can be found. In an file, the file is stored on the disk in order based on a set of field values (in this case the salesperson numbers) and an index is built over that same field. - This allows both sequential and direct access by the key field, which can be an advantage when applications with different retrieval requirements share the file. - CORRECT ANSWER indexed-sequential TRUE/FALSE The simple linear index is not a good solution for indexing the records of a file. - CORRECT ANSWER True - In the method of hashing, we divide the key value of the record that we want to insert or retrieve by the number of record locations that we have reserved. - CORRECT ANSWER division-remainder TRUE/FALSE A file can only be hashed once, based on the values of a single field or a single combination of fields. - CORRECT ANSWER TRUE