Let's pretend the Student table in a university database is indexed by StudentID (the primary key), Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Assume the school needed a list of students from the 45462 zip code who are either over the age of 25, married, and majoring in MIS or computer science OR who are above the age of 25 and majoring in computer engineering and are single. How can indexes be used to restrict access to records that exactly match these parameters?

Let's pretend the Student table in a university database is indexed by StudentID (the primary key), Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Assume the school needed a list of students from the 45462 zip code who are either over the age of 25, married, and majoring in MIS or computer science OR who are above the age of 25 and majoring in computer engineering and are single. How can indexes be used to restrict access to records that exactly match these parameters?

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Similar questions

In this example, we will pretend that the student table in the university database is indexed by StudentID (the primary key), Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Let's pretend a university needed a list of students from the 45462 zip code who are either above the age of 25, married, and majoring in MIS or computer science OR who are under the age of 25 and majoring in computer engineering. Is there a way to use indexes to restrict results to to those entries that fulfill this condition?
Consider the insurance database of Figure 3.17, where the primary keys areunderlined. Construct the following SQL queries for this relational database. Update the damage amount for the car with license plate “AABB2000” in the accident with report number “AR2197” to $3000.
We have a relational database that contains the table ‘Staff’. The table ‘Staff’ consist of columns ‘Name’, ‘Department’ and ‘Date of Joining’. What query would you use to extract the Names and Department of all staff in the table? a. SELECT * FROM Staff b. a) SELECT Name, Department FROM Staff c. SELECT Name, Department IN Staff d. SELECT * IN Staff
The DBA denormalized certain data in the TAL Distributors database to improve speed, and one of the resultant tables looks like this: It has been determined that the Customer table is not in third normal form due to one or more of the following fields: CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, or RepName. In this scenario, what is the normal form of the denormalized table?
The DBA denormalized certain data in the TAL Distributors database to improve performance, and one of the resultant tables looks like this: Customer table is not in third normal form due to the following field(s): CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, RepName. What does a typical representation of this denormalized table look like?
This question is related to the Database:- Suppose you are creating a database for a library management system. Explain how you would make a table for the "books" entity and insert some sample data, and then alter the table to add a new column for the "authorID" attribute and update "authorID" with some data. Finally, make a SQL query to retrieve all the books that were published after the year 2000. Note that this book schema has several columns, including "book_id" as the primary key, "title", "authorN", "publisher", "publication_year", "isbn", "language", and "num_pages". It also includes "available" column that is set to "true" by default and can be used to track the availability of the book. Please don't use handwriting...
In terms of Big O notation, how long does it take for a database to execute a given query? I can handle just one more thing at this point. Can you tell me the total number of deletions that occur in a single operation? Anything, even if it were to be used for only one advertising. In response to a single online query?
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1. Given the preceding information, answer Questions a through e. How many database requests can you identify for an inventory update for both PRODUCT and PART? Using SQL, write each database request you identified in Step a. Write the complete transaction(s). Write the transaction log, using Table 10.1 as your template. Using the transaction log you created in Step d, trace its use in database recovery.
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1.Given the preceding information, answer Questions a through e.a. How many database requests can you identify for an inventory update for both PRODUCT and PART?b. Using SQL, write each database request you identified in Step a.c. Write the complete transaction(s).d. Write the transaction log, using Table 10.1 as your template.e. Using the transaction log you created in Step d, trace its use in database recovery.
QUESTION 2 Consider the database schema below for hotels in countries. A country have many hotels, and the same hotel chain (name) are available in different countries. Country (ID: integer, Name: String (unique)) Hotel (CountryID: Integer, HotelName: String, ManagerID: Integer) How many candidate Key(s) in the hotel relation?
For the big university database table, list FDs with the column StdCity as the determinant that are falsified by sample rows. For each FD, you should identify one falsification example or indicate that no falsification example exists in the sample rows. Remember that it takes two rows to falsify an FD in which the LHS is the same in both rows but the RHS is different.
WRITE THE CODE OF MYSQL IN PYTHON Create database and tables as given below by using Python. (a) Market database (b) Vendor: vendor_id integer, vendor_name varchar, license_start date, license_end date (c) Item: item_id integer, item_name varchar, item_type varchar, previous_price float, last_sold_date date (d) Auction: auction_id integer, vendor_id integer, item_id integer, price float Choose primary and foreign keys depending on the relations in Market database design.

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS