This assignment has two parts:
- Hands-on lab
- 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:
|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)
- 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.
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.
- 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.