25 pts - Q&A about relations and keys
Download the Word document KeysQuestions which contains questions taken from (Video) Data Normalization Intro | PowerPoint especially the digression on keys. Answer the questions in the downloaded document and attach your completed copy of the Word document to this assignment in D2L.
35 pts - 3NF Database Build
In the course VM create and populate with data a SQL Server database named {JagNetID}AccRdr (e.g., mine would be named tschultzAccRdr) which matches the final design and sample data from Data Normalization PowerPoint deck (e.g., see AccRdr 3NF). In addition to the sample data, add yourself as a student in the 18th grade (the 18th grade has a 25% or 0.25 ARFactor), Watson Data Management as a book with 5000 points, and a test you took on the Watson book with a TestPoints you make up. Leave this database in the course VM for grading.
Develop a SQL query for this database which reproduces closely the 1NF relation from the same PowerPoint (e.g., see AccRdr 1NF). Name your SQL file AccRdr1NF.sql, saving a copy in your Documents folder in the course VM but also attaching a copy to this assignment in D2L.
30 pts - Work with SQL INSERT, UPDATE and DELETE
Note: If you haven't already, please create your own copy of the Northwind sample database using guidance from Create a copy of Northwind DB.
Ref: (Video) The Northwind DB & CRUD | Demo SQL
In the course VM, download the SQL script BasicNWCrud.sql and open it in SQL Server Management Studio. Create T-SQL script which performs the actions described in the script's comments. When properly prepared the script file undoes any changes made, so it can be run multiple times without running into errors; you should test this for yourself. Your completed script should produce results and messages similar to the linked images. Leave a copy of your completed BasicNWCrud.sql in your Documents folder on the VM but also attach a copy to this assignment in D2L.
Note: As you prepare and test your script you may end up with some partial results (e.g., a correctly inserted Territory but something else in the script didn't work) which you need to clean up before trying again. Don't hesitate to use the tools in SSMS to get back to a clean starting point. Your final script should be able to run and be re-run without issues but in getting to that point, take advantage of the SSMS GUI as needed.
35 pts - Introduction to XML
Ref: (Video) XML Intro and Demo | XML Walkthru Guide
Ref: Files used in the example (extracted from Watson Chapter 13): WatsonCDLibXML.txt (XML data), WatsonCDLibXSD.txt (XML schema), WatsonCDLibXSL.txt (XML stylesheet)
Using Visual Studio in the course VM, implement Watson's CD Library example as outlined in the video and guide referenced above. Be sure the project is in a folder named CDLibrary in your Documents on the remote VM and you have named the files CDLib.xml, CDLib.xsd, and CDLib.xslt as directed. Update the project files to add the CD genre to the data, schema, and stylesheet as demonstrated in the video.
Add an element cdreaction to the XML data which is a few word or sentence reaction (in your judgment) to the CD (it needn't be true ... "Best I ever heard" or "Too discordant for my taste" are the kind of thing). Adjust the schema to include this new element and find a place in the stylesheet so it appears when the XML is formatted per the stylesheet.
Add an additional CD of your choosing (real or imagined) containing at least 3 tracks. Data must meet the schema requirements but don't need to be actual CDs and tracks from 'real life' (i.e., I won't be checking your data against reality).
Leave your assets in the course VM where I can validate them for grading; nothing to upload in D2L.
