Getting started
This assignment reviews everything we have done so far in class. Open the script for the SoftwareTracker database. Select and copy all the text to a new query window in Sql Server. Run the script to create and populate the database
Here is the Entity Diagram for the database.
Creating tables
Create a new table called "SoftwareRequests." It should have the following structure:
| Column | Data Type | Constraints |
|---|---|---|
| RequestID | Nchar(5) | Primary Key |
| RequestDate | Datetime | none |
| SoftwareID | Nchar(5) | foreign Key |
| EmployeeID | Nchar(5) | foeign Key |
| RequestJustification | xml | none |
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
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 Software Price by the total Units to get the price per unit)
- Show just the years that software has been installed (use distinct)
Functions
- Return the software name and the company and the price concatenated into one column. Add a dollar sign to the License Price
- List the month, Year and Software name for each software installation
- Return the first five letters of each software name
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.
- Insert an new software request for Visual Studio. Use today's date. You can choose which employee. Use the following xml for the requestJustification field
<requestjustification> <reason> I need to develop some simple applications to manage databases </reason> </requestjustiication>
- 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 belongs to that schema and takes a employeeid as a parameter and returns all the software on that employee's machine
- Grant the user exec permissions on the schema
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. Also include the answers to the questions.