question 3 12

Instructions

This assignment has two parts:

  1. Hands-on lab
  2. Course project

Part I—Hands-on Lab

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.

Assignment Deliverable

After you complete the lab and course project, collate the information from both in a 3- to 4-page report in a Microsoft Word document.

Submission Details

  • Name your report SU_ITS3102_W3_A2_LastName_FirstInitial.doc.
  • Name your Excel spreadsheet SU_ITS3102_W3_A2_LastName_FirstInitial.xls.
  • If you use Microsoft Paint to draw your E-R diagram, name your E-R diagram file SU_ITS3102_W3_A2_LastName_FirstInitial.bmp.
  • If you use Microsoft Visio to draw your E-R diagram, name your E-R diagram file SU_ITS3102_W3_A2_LastName_FirstInitial.vsd.
  • Submit your report, spreadsheet, and E-R diagram to the Submissions Area by the due date assigned.

"Is this question part of your assignment? We can help"

ORDER NOW
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *