Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Write the following SQL statements in Microsoft Access by using the Books database from Week 2 Assignment 2 . Once complete, copy and paste the
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 IICourse Project
Course Project Part 3E-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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started