Lab -Groupish

This week's lab has 2 parts. Part 1 - Asking the database questions, Part 2 - Change management

Part 1 will have a group submission box

Part 2 will be individual

For both labs you will be working with the same system.

Asking the database questions

Purpose

The purpose of this assignment is to get you used to select statements in a way that allows you to make the connection between asking questions that are relevant to your users and query structure.

Overview of lab

You will be making a small database based off a script on Blackboard.  Before you run the script check it out.  What is it doing? 

What you need to do

-  Open the script in Blackboard

-  Look at it, what is it doing etc...  draw an ERD diagram and a database schema that shows the tables, how they are related, and the attributes of the relation as well as their datatypes.

Then, answer the following questions by querying the database using "SELECT" statements.Each group member should "own" one or more of these queries....your grade will be determined by the queries you "own." This is a group-ish project because you are allowed to work together on the queries. Your grade will be determined by the quality of the queries you "own"

-  What courses, if any, have never been taken

-  What courses are the most popular...meaning they've been taken the most times

-  Which courses are held in the same room?

-  Query the database so it shows who to contact (Department.admin) for each course

-  Show the top three students with the highest grades in the database in DESC order

-  Who has worked at the college for the longest amount of time?

-  Who has worked at the college for the shortest amount of time?

-  Courses with no instructors

- Courses with two instructors

- Three queries (similar to the ones we did in class....hint) that involve subqueries

Deliverables

- Documentation of your system (however you want to do this)

o Entity relationship Diagram

o Database Schema

- Tell me which of your group member's azure instance your database is on and I will look at it

- Your Script in one.txt documents

o   Queries

Should be the queries for the system

- A document FROM EACH GROUP MEMBER outlining which queries they "own" and the decisions they made regarding that query

Change Management

Overview:

This portion of the Lab is individual. You will be coming up with a change management plan for the data in the section above. 

Using the system above...

-  Fill out the change management template. You may "make up" information as long as it fits in and makes sense

Turn in a document answering the following questions:

-  Are there any changes that you might anticipate/be proactive about in a student registration system?

-  What would be affected by a change in business policy that each department must now have TWO department admins? Assume you have implemented all of the reports above for the registrar's office.