You have a table for a membership database that contains the following fields: MemberLastName,

You have a table for a membership database that contains the following fields: MemberLastName, MemberFirstName, Street, City, State, ZipCode, and InitiationFee. There are 75,000 records in the table. What indexes would you create for the table, and why would you create these indexes?

Expert Solution

Students should recognize that each table should have a primary key field that is indexed, and given there is no good primary key field candidate in the existing list, students should add one such as MemberID or MemberNumber. Given people are often sorted by their last name, an index MemberLastName would probably be very helpful. Given there are 75,000 records in the table, there are bound to be two or more members with the same last name, so an index on MemberLastName plus MemberFirstName would probably be very helpful. If several queries and reports are created by State, or City within State, ZipCode, or any other combination of address fields, those fields and combinations are also candidates for indexes. Finally, if several queries and lists are created based on the InitiationFee in ascending or descending order, that field is another index candidate. In all cases, the database administrator should set up tests to check the performance of common activities, queries, and processes both before and after the indexes are created given indexes themselves require overhead to create and maintain.

Tables need indexed primary keys like MemberID; MemberLastName and FirstName combo index aids sorting. Address and InitiationFee indexing enhances query speed.

Junaid answered 9 months ago

PHP The database should have at least two tables with, at minimum, the following fields: Table...

PHP The database should have at least two tables with, at minimum, the following fields: Table customers: Fields: Table billing: Fields: customer_ID customer_ID customer_L_Name customer_L_Name customer_F_Name service customer_Title (Mr, Ms, Dr. etc,) customer_bill street_Address amt_paid city_State_Zip bill_date customer_Phone date_paid customer_Email Create a PHP page that will extract a customer’s bill amount and the amount paid. Then it will calculate the amount due. If the amount due is greater than 0, an email should be generated and sent to the customer...

Assume that you have a Saudi league player database. In this database, you have a table...

Assume that you have a Saudi league player database. In this database, you have a table containing players’ attributes such as (Name, age, position, etc.) and you decided to add information about players’ agents. Would you represent the agent information as attributes in the player table or would you create an entity set for players’ agents? Justify your answer. I need clear answer please don't copy and paste

tableA is a table in a relational database with a composite prime key consisting of fields...

tableA is a table in a relational database with a composite prime key consisting of fields F1 and F2. You have determined that tableA is 1NF. There are four other fields in tableA. Two of them, F10 and F12, are functionally determined by F1. The other two, F50 and F55, are functionally determined by F2. Because all fields are functionally determined by at least a portion of the key, is tableA 2NF? If you believe the table is not yet...

Assume that you have a Saudi league player database. In this database, you have a table containing players’ attributes

Assume that you have a Saudi league player database. In this database, you have a table containing players’ attributes such as (Name, age, position, etc.) and you decided to add information about players’ agents. Would you represent the agent information as attributes in the player table or would you create an entity set for players’ agents? Justify your answer.

The database for a pet supply company includes the following table, named tblCollar, that contains product...

The database for a pet supply company includes the following table, named tblCollar, that contains product data for pet collars. The ItemDesc and Color fields contain text. The ItemNum, Price, and Quantity fields contain numbers. ItemNum ItemDesc Color Price Quantity 2358 leather studded collar black 30.00 35 2693 leather collar brown 25.00 25 3547 striped collar red 20.00 75 3855 striped collar blue 15.00 42 3764 striped collar green 15.00 48 5782 solid collar pink 12.00 36 5785 solid collar...

Write a SQL script to add another table to your database. Include these fields in your...

Write a SQL script to add another table to your database. Include these fields in your Product table: Field Name Description Data Type Sample Value ProductID Product ID integer 5 ProductName Product Name varchar(50) candle Description Product Description varchar(255) Bee’s wax candle picUrl Filename of the product’s picture varchar(50) candle.gif Price Product Price decimal 10.99 ProductID should be the Primary Key. It is an auto-increment field. The Price field stores prices to 7 significant digits and to 2...

You have a table called scholar where there are two fields id and create date. You...

You have a table called scholar where there are two fields id and create date. You want to create an ETL process that loads the data from scholar table into the big data platform. What are the different things that you will put on a requirement document for the developer to write code and implement the solution?

The tbl_employee contains information about the employee and its department, and it contains the following fields...

The tbl_employee contains information about the employee and its department, and it contains the following fields (it is possible to also include other missing fields): employee_id, employee_gender ,employee_salary, employee_annual_sallary, department_name, department_location, department head. Make use of the database normalization skills learned to normalize a database to the third normalization form. Organize the columns and tables to reduce data redundancy and arrange attributes based on the relationships. Provide a screen shot of the database design before the normalization. Create a heading...

Using SQL create a new database called school_app. Create a student table with fields id (auto...

Using SQL create a new database called school_app. Create a student table with fields id (auto increment), first_name, last_name. Create a course table with fields id (auto increment), course code (such as ITC or MTH), and course number (such as 100 or 295). Note that the relationship between student and course is many-to-many (n:m). Create a join table called student_course that implements the n:m relationship with fields id (auto increment), student_id, course_id, and grade (which has values 0, 1, 2,...

tableA is a table in a relational database with a composite prime key. You have determined...

tableA is a table in a relational database with a composite prime key. You have determined that the table is 2NF. Owing to the fact that it is 2NF and the key is composite, is it automatically 3NF? Explain your answer. (4 points) Lack of normalization of database tables may cause update, delete, and/or insert anomalies. You have a 2NF table, tableA, in a relational database. Give an example of an anomaly to which tableA might still be subject. Give...

Question