Database HW

I attached 1 HW file and 2 relevant files.

HW is basically describing 5 queries.

Describe at least 5 “interesting” queries in plain English. Note: do not implement these queries in SQL at this stage. Describe in detail what each query will return (include mathematical notation if relevant) and justify each query: how will it be useful for your client? Interesting queries should be both helpful to your client and demonstrate skill with SQL (going beyond basic conditional selects). Ideally, queries should apply management theories and/or methods, for example: computing Economic Order Quantity, Retailer Discount Values, Forecasting Demand, Lead Time, Profit, Inventory using Exponential Smoothing, Ranking employees by a productivity metric, Ranking products by profitability, fitting stochastic models for expected demand, scheduling employees or transportation, locating events (to minimize travel time), ranking advertisements based on return on investment, displaying seasonal trends, computing correlations, setting warranty periods based on Mean Time Between Failures. etc. Your queries should demonstrate the power of your database to answer questions of value to your client. Each query must be justified in terms of organizational needs and described in plain text (not SQL yet!).

Company Introduction

Phoenix Computer Consultants is a small IT group located in Glendale, Arizona. Run by Dhondi Wiegel and his two employees, PCC sets up, supports, and services information technology for many small businesses across the state of Arizona, as well as even a few clients in other parts of the United States.

 

This company was chosen because one of the Data Aces, Michael Birch, is currently employed there, and access to the data is streamlined enough that our creation of the database shouldn’t be too terribly tedious.

 

Currently, most of this data resides in a couple different computer systems (SpiceWorks, client address list, etc.), but it is not consolidated into one spot completely. Of course, being an IT company, there is a competent network structure in place as well as a normal domain environment with Microsoft technology implemented all over the place.

 

Dhondi is quite willing to assist us in the execution of this project, and PCC owns a couple of servers with room for extra virtual machines if we need somewhere to actually host the database. On a scale of 1-10, we estimate Dhondi’s enthusiasm at about a 7.5-8 or so.

 

In the near future (less than a year), PCC hopes to grow enough in client base that they can move out of their current office space, which is shared with another company. The goal with Dhondi’s two current employees is that they can eventually be more like partners than employees in the company, with their own personal lists of clientele to service.

Application Overview

The goal of this application is to create a searchable database of all relevant customer information as it pertains to PCC. For example, Dhondi wishes to be able to find all of the computers that are out of their warranty period, or maybe every office which uses Microsoft Office Professional Plus.

The scope of this project is fairly simple, in that the database does not include a whole lot of data—everything should be reasonably available, and there shouldn’t be too much of it. Essentially, if there is a working, searchable database at the end, with an easy way for someone besides one of the Data Aces to maintain the data, we will have done our job.

WORK IN PROGRESS

Business rules first draft for the Data Aces and Phoenix Computer Consultants

PCC services several small businesses located mostly in Phoenix, but a few of these clients are located in other parts of the United States as well.

Some offices have multiple locations.

Some offices have a server installed, but not all have this.

Some attributes of each computer (important attributes are bolded):

· Operating system

· RAM capacity

· # of processor cores

· Warranty expiration date

· Hard drive capacity

· Manufacturer

· Type (server/laptop/desktop)

· Special peripherals

· Model

· Service Code

· Etc.

Some people have multiple computers (laptop, desktop, etc.), and some computers are used by multiple people.

Many offices own software. Some software attributes include:

· Manufacturer

· Software name

· Software type

· Activation key #1

· Activation key #2

· Activation key #3

· Notes