I will attach the database file when question is accepted.
Part 3: Data Queries
ManyBooks.com was developed by JK Rowling as an open marketplace for books online, a one-stop e-commerce search engine where you can search through more than 100 million new, used, rare, and out-of-print books for sale.
Ms. Rowling understands that you have expertise in databases. She needs help developing some custom queries for her sales and marketing team members. She has provided you with a sample data file, DA233_S1X-II.accdb. Specifically, what Jane needs is:
- Create a table showing the telephone number of each author. Streamline your database so you don’t have too much redundancy. Be sure to include the input mask for the telephone number. Make sure the formats are correct. You may make up your own telephone numbers. Save the table, giving it and the fields appropriate names. Hint: The input mask can be set if you choose the text data type for telephone number. (2 pts)
- Create relationships for the tables and enforce referential integrity. You should be able to generate the necessary relationships for all tables. Hint: To create relationships, click database tools at on the ribbon and you’ll see relationships. Drag the tables you want into the design canvas and build the relationships accordingly. You should be able to tell where the relationships are based on the fields. (2 pts)
- A query of all authors who have written a book that costs more than $10 and is a novel. The query needs to have the author’s First Name, Last Name, Title of Book, Price, Category, and Rep displayed. Save the query as AuthorsQuery. (2 pts)
- A query that calculates a 15 percent increase in the purchase price of each book. Sort the query on the new calculated field. Save the query as BookPricesQuery. (3 pts)
- A query that updates the price of each book by subtracting 50 cents. Save the query as Less50CentsQuery. (3 pts)