Question

Write the following SQL statements in Microsoft Access by using the Books database from Week 2 Assignment 2. Once complete, copy and paste the SQL statements in a Microsoft Word document:

Write SQL statements:

• To update the publisher name from READ WITH US to READ FOR US
• To verify the updated name field for the publisher with ID 6
• To make the following updates to the Publisher table (be careful with WHERE):
  • Make Contact="John Travolta" for publisher with ID number 4
  • Make Phone=888-999-7777 for TEXT AND MORE
• To list the customer numbers and names of all individuals who have purchased books in the information systems category
• To identify the book written by an author with the last name Cain. Perform the search by using the author name
• To produce a list of all the customers who live in the state of Indiana and have ordered books on information systems
• To display a list of all the books in the Books table. If a customer has ordered a book, list the corresponding order number and the state in which the customer resides
• To identify the authors of the books ordered by Sheila Smith. Perform the search by using the customer name
• To list the customer numbers and names of all the individuals who have purchased books in the children category
• To add the following items to the Books table:

ISBN	Title	Author	PubDate	Cost	Retail	Category	PubID
1234567890	A Good Book	John Jeffries	10/11/2005	13.5	15.00	Non-Fiction	7
2314569874	Trains for Everyone	Corey Berkey	12/15/2011	10.25	25.25	Children	8
1598745682	Eating In	Susan Smythe	09/09/2012	20.00	30.50	Cooking	6
99954123654	Another Vampire Story	Ann Oats	09/05/2010	11.23	13.52	Fiction	4
1025874159	Databases are Fun!	Tony Stevens	01/01/2013	4.12	5.23	Information Systems	9

• To delete the last record added
• To update the name Susan Smythe to Susan Smythe-Brown

Part II—Course Project

Course Project Part 3—E-R Modeling

Now that you have defined your E-R model and project description in the project you started and worked on in Weeks 1 and 2, start converting your logical E-R model to a physical model.

In this week, complete the following tasks:

• In a Microsoft Excel spreadsheet, define columns with the following headings:
  • Primary Key
  • Name of Column
  • Null (NorY)
  • Datatype
  • Description
  • Foreign Key
  • Foreign Key Table
• Each row will contain your rows for this table.
• Fill out the columns row-wise.
• Using Microsoft Visio or Microsoft Paint, create an E-R model of the six tables from your spreadsheet for developing the business requirements and constraints that will maintain the business logic for your database.
  • The E-R model should be readable and should map to the spreadsheet and the project description.
  • It should have all the cardinality and optionality defined.

Answer 1