COP4709 Spring 2018 Assignments

No surprise - this class has weekly assignments. Each assignment is designed to build you abilities as a database administrator and your ability to manipulate, query, and analyze data. Along the way we will learn key concepts of databases and database programming.

 Denotes the Assignment is ready to complete

	Assignment	Description	Associated Lectures
	COP4709 Assignment 1 - Database Review	Create a database in SQL Server - Getting SQL Server up and running and building a database in the environment. You will need to have your computer or a cloud service like Azure running SQL server and install SQL server management studio. You will be downloading some software from Dreamspark (dreamspark.com)	Topic - Review of Database I
	COP4709 Assignment 2B - Import and Analyze Data	Import Data into a Database  - You will start by importing and analyzing data. One of the most important things you will have to do as you work with databases is to get data from external sources into your database. This involves acquiring the data, cleaning the data, and importing the data. All three of these steps will be done in this assignment.	Topic - Importing Data
	COP4709 Assignment 3A - Stored Procedures	Write a Stored Procedure - You will start writing stored  procedures in this assignment. Stored procedures will be one of the most important and useful tools for you as a database manager or developer. The beauty of stored procedures is just that - they are stored in the database and are accessible encapsulation of common database tasks and queries.	Topic - Stored Procedures Part 1
	COP4709 Assignment 4A - Stored Functions	Write a Stored Function - You will now write a stored function that returns a table and another returning a scalar. These are the 2 common types of stored functions. Functions vary slightly from stored procedures - with a huge advantage, they can be used inline with queries.
	COP4709 Assignment 5A - Transactions	Create a Transaction  - You will create a transaction stored procedure and discuss how it meets the requirements of transaction management. Managing transactions are important to the work of a database manager - bottom line, everything happens - or nothing happens.
	COP4709 Assignment 6B - Cursors	Use Cursors in Stored Procedures - Learn to use cursors in stored procedures. Cursors allow you to make stored procedures much more like real programming and allows you to literally loop through a query.	Topic - Stored Procedures Part 2
	COP4709A Assignment 7A - Reports	Create a Database Report  - Learn to use Report Writer to create a report. There are all sorts of reporting services software like Crystal Reports which is the most popular. However, they all work essentially the same - they allow for very nicely formatted reports which can be static or interactive.
	COP4709 Assignment 8A - Lookup Tables	Use Lookup Tables - A common database practice is coded fields that rely on lookup tables
	SELECT ANY 7 TO COMPLETE  ASSIGNMENTS	At this stage you now have all the basic tools to do pretty much everything with a database. We did not cover triggers (which may be useful) but a little bit of looking at documentation you will be quite capable of using them if needed. I avoid them as they often have side effects.  The next set of assignments will have you solving some relatively complex data questions. We will be using real data. In each case you will need to get data into your system, manipulate it, format queries, and provide a report of results. Where most of the first half assignments were pretty straightforward - these are much more complex.
	COP4709 Assignment 9A - Food Analysis   COP4709 Assignment 9B - Race Rankings	These assignments demonstrate the ability to use aggregate operators in SQL. They are both interesting assignments and have multiple solutions. You should complete at least one of these assignments to demonstrate the use of COUNT,  SUM,  GROUP BY,  ORDER BY and possibly other operators and their use in SQL.
	COP4709 Assignment 10A - Business Reports	Using real grades and some analysis you will generate a business report to a set of high level questions.
	COP4709 Assignment 11A - Full text indexing	Full text indexing opens up an entirely new world of querying
	COP4709 Assignment 12A - Performance of queries	Learn how to use indexes and increase system performance
	COP4709 Assignment 13A - Security	Learn about SQL Injection, security, and how to harden a database
	Assignments 14 & 15	Assignments 14 and 15 are student "Pot Luck" assignments. You can either use some of the suggested assignments below - or - you can document solving a real problem of your own by writing a report on the problem and the solution. Think - questions from data you want to explore, complex queries you want to solve.
	COP4709 Assignment 14A - DynamoDB   COP4709 Assignment 15A - Hadoop   COP4709 Assignment 15B - Review Paper	Create a database using Amazon Web Services     15A - Create a Hadoop Instance using Azure Web Services.     15B - Review Paper

Past Assignments

COP 4709 Spring 2012 Assignments

COP4709 Spring 2013 Assignments

COP4709 Spring 2014 Assignments