assignment 10 13

Q1 Add an index for the customer’s last name

Q2 Add an index on the number of days it took to ship the order. If shipdate is unavailable, use January 1, 2020 for the calculations.

Q3 There seems to be some confusion in data entry about book titles due to similar titles. For example, Golden Son by Pierce Brown vs. The Golden Son by Shilpi Somaya Gowda. Add a unique constraint for the book title. Can you add a unique constraint to a column containing duplicate data? If you run into problems, please provide queries that allow you to fix the inconsistencies. If you need to update or delete any data in BOOKS feel free to do so! You may need to change one of the book titles (hint: the one by Shilpi Somaya Gowda), use UPDATE. One of the “Database Implementation” records seem to have no link to an author, use DELETE.
Do you think it may be a good idea to replace FK constraint in ORDERIITEMS to FK with ON DELETE CASCADE option?

Q4 Make sure that all book records have a title, publisher, cost, and retail, and that all publishers have contact and phone. Think about new constraints to add by ALTERing the books table.

Q5 Add a constraint so that all retail prices have to be between $2 and $200

Attach and submit your completed assignments in RTF, TXT or SQL format. Please do not include any results from your queries.

ONLY submit the SQL scripts using the following naming convention: HW#_Q#_YourInitials.

For example: