ITC 224 Assignments

Automart Auto Services

Automart is a chain of shops that provide a variety of services for automobiles of all kinds.

They pride themselves on being quick, efficient, reliable and less expensive than the competition. As an added bonus they want customers to be able to view the service history of their vehicles on line. They also want customers to take surveys on the quality of the work they received. All service given is recorded into a central database so that a vehicles history can be pulled up no matter what shop they go to.

A customers interaction with the database is primarily through the web. Anyone can view services and shop locations, but a customer must register to see their vehicle history. The user name is a valid email and the password is of the customers choosing. Customers are given a number (the ServiceHistoryKey) at the end of their visit. They can use it to go online and take a survey on the quality of their service. They do not have to register to do this--but they do need the key.

Employees use a windows application at the shop site. They can look up a vehicle based on its license plate. If the vehicle is not in the database, they must enter it before providing service. Each service and its price are recorded in the database.

Shop Managers can see all the services performed at their shop and all the employee info of those that work at that shop, plus summary reports of total numbers of vehicles served, total intake, etc. They can also see all surveys for service numbers related to their shop.

Assignments

These assignments are subject to change, depending on the course and conditions of the class throughout the quarter

Assignment One

For this assignment we are going to use the SQL Server Integration Services to create a package to insert some new customers, their vehicles and their registrations

copy the lines below into a new text file and name it "customers.txt"

Robin,Levitt,rlevit@comcast.com,rlpass,GHC212,Toyota Camry,2010
Leah,Jefferson,leah@yahoo.com,ljpass,HBD134,Ford Focus,2009
Taylor,Manning,tm@manning.org,tmpass,JUL352,Jeep Cherokee,1997
Mary,Brown,brown@hotmail.com,mbpass,WEB323,Honda Civic,2011
Nate,Thompson,nate@Thompson.net,natepass,TMC201,BMW,1999
Larry,Greogory,lg@yahoo.com,larrypass,MNB938,Ford Escape,2010
Morgan,Barth,morgan@freenet.net,morganpass,TTL204,Volkswagen Beetle,2007
Amy,Charleston,amch@hotmail.com,amypass,GGJ220,Ford Taurus,2005
Tom,Anderson,tomanderson@gmail.com,tompass,HKL236,Toyota Escapade,2010,
Wendy,Matthews,wendy@casper.org,wendypass,FGH123,Honda Civic,2000

This should be just like the tutorial on the blog except there are three tables involved rather than two.

The columns for the text file in order are FirstName, LastName, Email, CustomerPassword, LicenseNumber, VehicleMake, VehicleYear. The first two go to the Person table. Email and CustomerPassword go to the Customer.RegisteredCustomer table. VehicleMake and vehicleYear go to the Customer.Vehicle table. They are all tied together by the personkey, which will require a lookup.

To do and turn in

Create the SSIS package and import the records into the three tables

Describe the steps you took, the problems you encountered and how you solved them

run a query in SQL Server on the three tables and copy and paste the records to show that they are in the tables

Suggestions

This is a difficult assignment. It might be easier to work in small groups. Follow the pattern of the tutorial on the blog.

It may help to set the dataflows to ignore errors

Assignment Two

There are two schema built into the database currently: Customer and Employee. It is also safe to assume that there will be a management schema as well. See the description of Automart

Think about how each type of user needs to access the database

Create a list of views and stored procedures for each type of user. The list should include the the name of the procedure or view and a one or two sentence description of what it does.

Also, list any triggers or functions you think would help enforce the database rules.

Assignment Three--through five

we are going to make at least one view, one procedure and one function for the database. Each will count as a seperate assignment.

Create a view that shows all the registered customers and their vehicles

Create a procedure that allows a customer to log in. It should match the email and password and return the person key

To calculate the price of a service, you have to take the price of the service from the service table minus the price times the discount percent, unless the discount percent is 0. Write a function to do this that takes the price and discount percent as arguments.

Assignment Six

We Are going to look at XML, and XML Schema.

The goal is to insert three records into the CustomerSurvey table including the xmlcomment column.

Assignment seven

For this assignment we will read the xml of the service description, and load it into an asp.Net page using the xml control. We will also write an xslt document to control how it is displayed.

Here is the code for the xslt: <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:disc="http://www.automart.com/servicedescription" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" xmlns="http://www.w3.org/1999/xhtml" > <xsl:output method="xml" indent="yes"/> <xsl:template match="/"> <h2>Description</h2> <xsl:value-of select="disc:servicedescription/disc:description" /> <xsl:for-each select="//disc:parts/disc:part"> Part: <xsl:value-of select="disc:partname"/> Quantity: <xsl:value-of select="disc:quantity" /> Price Per Part: <xsl:value-of select="disc:perpartprice"/> </xsl:for-each> Labor: <xsl:value-of select="disc:servicedescription/disc:labor/disc:workers"/> Hours: <xsl:value-of select="disc:servicedescription/disc:labor/disc:hours"/> </xsl:template> </xsl:stylesheet>

Assignment Seven

For this assignment use LINQ (or possibly Entity modeling)to create a piece of the customer web application. A customer should log in--using the stored procedure created in an earlier assignment--and then be able to view the service history of their vehicle or vehicles.

Assignment Eight

For this assignment we will use SQL Server's Reporting Services to make and modify two reports

You can choose the content of the reports