Description
This assignment sums up most of what we have covered throughout the quarter. It has you create a simple database that tracks which software is on which computers. It has you populate the tables with a little data and then generate some queries, a view, a couple of indexes, a stored procedure and a trigger.
To Do
Create Tables
Below is a Diagram of the Database SoftwareTracker.
Create the database. Use the diagram and the following notes to create the tables. Add all the Primary and Foreign keys, and use the data types and precision specified
Altering Tables
In the Employee table drop the column "EmployeePhone."
Add a check constraint to Software that limits software type to either "seat", or "Site"
Add a check constraint to Computer that no computer purchase date can be before 1/1/2000
Inserts
Here is a list of all Inserts. Copy this script to the query analyzer and run it.
--inserts into employee
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00001', 'Stafford', 'Robert', 'IT', '00003')
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00002', 'Lamier', 'Tracy', 'IT', '00003')
Insert into Employee(EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00003', 'Martin', 'Jeff', 'IT', null)
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00004', 'Anderson', 'William', 'ACC', null)
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00005', 'Sanders', 'Jennifer', 'ACC', '00004')
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00006', 'Carlson', 'Jeremy', 'PSN', '00007')
Insert into Employee (EmployeeID, EmployeeLastName, EmployeeFirstName,
EmployeeDept,EmployeeSupervisor)
Values('00007', 'Stafford', 'Robert', 'IT', null)
--Inserts for computer
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23001','Dell','Pentium 4','4/24/2004',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23002','Dell','Pentium 4','5/09/2004',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23003','Gateway','Pentium 2','2/20/2001','6/1/2004')
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23004','MAC','G4','1/24/2003',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23005','Gateway','Pentium 3','4/24/2002',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23006','Compact','486','2/13/2000','11/2/2002')
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23007','Dell','Pentium 4','4/24/2004',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23008','Dell','Pentium 4','1/1/2005',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23009','Dell','Pentium 4','5/05/2004',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23010','Dell','Pentium 4','3/01/2005',null)
Insert into Computer (ComputerID, ComputerMake, ComputerCPU,
DatePurchased, DateRetired)
values('23011','HP','Pentium 2','4/24/2002','3/2/2005')
--Inserts for Locations
Insert into location (LocationID, LocationName, LocationBuilding)
values ('M200', 'Accounting office', 'Main')
Insert into location (LocationID, LocationName, LocationBuilding)
values ('M201', 'IT office', 'Main')
Insert into location (LocationID, LocationName, LocationBuilding)
values ('W100', 'Warehouse office', 'Warehouse')
--Inserts for Software
Insert into software (SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('W2000','Windows 2000 workstation', 'Microsoft','site',10,null, 800.95)
Insert into software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('W2Srv','Windows 2000 Server', 'Microsoft','Seat',1,0, 2400.00)
Insert into software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('WOFFC','Microsoft Office 2003', 'Microsoft','site',10,null, 750.95)
Insert into software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('DRWVR','DreamWeaver XP', 'Macromedia','seat',2,1, 299.95)
Insert into software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('PHTOS','PhotoSho', 'Adobe','seat',1,0, 665.50)
Insert into software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
Values ('PCHTR','Peachtree Accounting', 'Peachtree','seat',3,0, 400.95)
Insert into Software(SoftwareID, SoftwareName, SoftwareCompany,
SoftwareLicenseType,SoftwareTotalUnits, SoftwareAvailableUnits,
SoftwareLicensePrice)
values ('SQLSV', 'SQL Server 2000', 'Microsoft', 'seat',1,0,1750.90)
--Inserts for User Computer
Insert into UserComputer(ComputerID, EmployeeID)
Values('23004','00006')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23001','00004')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23002','00003')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23005','00007')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23008','00001')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23009','00002')
Insert into UserComputer(ComputerID, EmployeeID)
Values('23010','00005')
--inserts for SoftwareComputer
Insert into SoftwareComputer (SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2Srv','23007', '5/1/2002',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('SQLSV','23007', '5/1/2002',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2000','23001', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('WOFFC','23001', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('PCHTR','23001', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2000','23010', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('WOFFC','23010', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('PCHTR','23010', '5/1/2004',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('PHTOS','23004', '5/1/2002',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('DRWVR','23004', '5/1/2002',null, 'M200')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2000','23008', '5/1/2004',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2000','23009', '5/1/2004',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('W2000','23002', '5/1/2004',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('WOFFC','23008', '5/1/2004',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('WOFFC','23009', '5/1/2004',null, 'M201')
Insert into SoftwareComputer(SoftwareID, ComputerID, InstallationDate,
RemovalDate, LocationID)
Values('WOFFC','23002', '5/1/2004',null, 'M201')
Simple Queries
Create the queries to answer these questions: These do not involve Joins of any kind.
What software is on computer 23007?
Which software is on a site license?
Which computers have been retired?
Which software prices are greater than 1000?
What is the per unit price of each software? (Divide the total units by the software price to get the price per unit)
Functions
Show just the years that software has been installed (use distinct)
Return the software name and the company and the price concatenated into one column. Add a dollar sign to the License Price
Aggregate Functions
What is the total amount spent on software (the software price represents the total cost for that software)
What is the average price for software?
What is the minimum price?
How many pieces of software are on each machine?
Inner Joins
What software does Jennifer Sanders have on her computer, return the names of the software packages?
In what location is Jeremy Carlson's computer?
Return each employee's last name and the make of their computer .
Outer Joins
Which computers are not assigned to a user?
Self Join
List the last name and first name of each employee and the last name of their supervisor.
Sub Queries
Which software has the highest price
Find which computers are not assigned to a user by means of subquery.
Inserts,Updates, Deletes
Change employee 00006's last name to "Carlton" and his first name to "Jerid"
Put today's date as the removal date for SoftwareID 'Photos' and ComputerID '23004'.
Add a new Software Package (of your choosing). add it to One of the Employees Machines.
Remove retired computer '23011'
Views
create a view that shows what software each employee has on his or her computer. Make sure it returns the employee name and the name of the software plus the installation date.
Indexes
Create an index on software name in software
Create an index on last name in Employee.
Stored Procedures
Create a stored procedure that takes the software name and employee name and adds the software to that employee's computer and, if it is a seat license, removes one copy from the available units of that software. You should include a transaction and error checking. (In the script you turn in include the SQL you use to test you procedure.)
Trigger
Place an trigger on Software so after updates if the available units is 0 (or less) a warning is generated.
Logins and Schema
Create a login with this database as the default database
Create a schema called manager
Create a user mapped to your login with manager as a default schema
Grant your user execute on the schema
Create a simple stored procedure that takes a employeeid as a parameter and returns all the software on that employee's machine
XML
Output the Employees table as xml elements with a root element of "Employees," a record grouping element of "employee"
To turn in
Turn in the SQL code for the whole database and all the queries. You do not need to include the inserts I provided. Also include the answers to the questions.