these pictures are the figures that are mentioned in the passage

Specialty Imports

Specialty Imports is an automobile dealership located on the "auto strip" in North-West Brampton (where several other dealerships are located as Brampton is currently moving many dealerships into this area). Specialty Imports is owned by Maurice Risner, who is also its general manager, and two brothers, Pete and Henry Preen. The Preen brothers together own 80% of the company and operate it as a subsidiary of a Ford dealership which is located in the center of Brampton.

Maurice Risner manages Specialty Imports autonomously at both the operational and tactical levels of the business. Maurice is very entrepreneurial and plans to use his sizable bonuses to buy out the Preen bothers' interest or to purchase another dealership independently. Maurice is very marketing oriented and believes that keeping track of customers (potential customers as well as purchasers) and keeping in contact with them are key elements in selling his line of vehicles.

SPECIALTY IMPORTS' PRODUCTS AND BUSINESS POSITION

Specialty Imports sells Acuras, Mercedes Benzs, Jaguars and Land Rovers (primarily passenger cars, but also some sport utilities). The company is only 4 years old, so it does not yet have a well-established customer base. Buyers of these type of imported vehicles tend to be very particular and will search nationally for the car of their choice. For example, one of Specialty Imports' customers flew from Edmonton in her private airplane just to buy a White Mercedes Benz.

Last year, the company sold 350 new cars, of which 176 were Acuras. In comparison, the parent company sold 1650. The after tax profit margin was less than 3% on all business, with a 30% margin on service work. Specialty Imports has been experiencing a 15% growth, and Maurice Risner believes that only supply limits his sales.

HISTORY OF COMPUTING

Computing services for Specialty Imports and the parent dealership are currently provided by a Data General minicomputer running UNIX and using primarily packaged software purchased from a major software supplier. Parts, inventory and payroll are the major applications that have been implemented. The use of turnkey packaged software and hardware systems is common in the automobile dealership business, but these systems have focused almost exclusively on accounting and record keeping functions. The computer is located at the parent dealership and is managed by the treasurer of the parent company; there is one programmer on staff. Both Specialty Imports and the parent dealership have wanted to expand computing services but slumping sales of new cars at the parent dealership have made this impossible. Sales at Specialty Imports are, however, growing rapidly and Maurice Risner is pushing for authority to unilaterally develop new information services for the company. One such activity is an arrangement with a service bureau of mail auto service reminders to customers.

BASIC OPERATIONS

Maurice Risner is a participatory manager. He frequently leaves the office, which overlooks the showroom, to greet customers, especially repeat customers. Maurice must approve each deal and he occasionally sells a car himself.

Specialty Imports has a traditional dealership organizational structure (see Figure 1). The parts & service manager operates a business within a business and has not been closely supervised. The accounting manager is primarily responsible for accounts receivable and other aspects of cash flow. Most service work is charged on bank cards and this paper work must be processed rapidly. The assistant managers for New Vehicle Sales and Used Vehicle Sales are floor sales personnel with the added duties of supervising other sales staff and approving sales in Maurice Risner's absence. Maurice would like these sales managers to be more involved in marketing and attracting new business.

Figure 1 Specialty Imports Organizational Structure

VEHICLE SALES

Basically, there are three working documents now used for a vehicle sale. The first is the sales invoice (see Figure 2), where the specifics of base price, trade-in, salesperson, add-on options and the like are detailed. This document is first hand-written by a salesperson while they are talking to a prospective customer; it is frequently changed as preferences change and as "deals" are made. After the customer and salesperson shake hands on the sale, the invoice is typed and signed by both the buyer and salesperson. Note in Figure 2 that insurance, loans, undercoating and other items are also considered options for the purposes of preparing the sales agreement and calculating the salesperson's commission.

The second working document is the Vehicle Inventory Record (see Figure 3). This document is used to describe vehicles in stock at Specialty Imports and is prepared only after receipt of a vehicle (either from a manufacturer or from trade-in); vehicles on order or being considered as trade-ins in sales being negotiated are not recorded here.

The third basic working document is the Vehicle Sales Accounting Supplement (see Figure 4), which Maurice Risner uses to calculate profit margins on vehicles during sales negotiations.

ACQUISITION OF CARS

Each month Specialty Imports' auto manufacturers inform the company what is available for shipment. This information takes two forms. First, each manufacturer indicates how many cars it is willing to ship to Specialty Imports within the next month. Second, the manufacturers indicate the choices available on options for the vehicle to be delivered (color, type of upholstery, engine size and so on).

Usually, the monthly allocation is from 8 to 15 cars from each manufacturer, so careful selection is crucial. Maurice Risner would like to be able to match potential car sales with his ordering as much as possible. Because of very long delivery times on custom orders (over 9 months), customers tend to shop around at different dealerships (of various imports) for a car with the desired features. Specialty Imports does buy vehicles to stock but to date the features of these cars are not systematically chosen.

As with any dealership, many cars are purchased, in part, by trade-in. Used car sales are not very profitable, so rapid turnover is important. Maurice can make an attractive deal on a new car sale if he knows that there is a potential buyer for the trade-in.

Specialty Imports also transships vehicles with other dealerships. Records of such transshipments are not important (manual bookkeeping is sufficient), but the ability to record changes in vehicle inventory due to this type of activity is necessary in any computer system.

SERVICE BUSINESS

Maurice is certain that periodic mailed reminders to drivers will not only generate service business but will also foster new sales as customers come into the dealership. One problem with tracking customers via sales only is that the buyer and the owner are often not the same person. Parents may buy cars for children and customers actively trade or sell cars elsewhere. Consequently, although tracking service customers to increase the service business (which is very active without much effort) could produce some marginal income, service visits are a more important source of information on current owners of vehicles. Figure 5 is an example of the Service Work Order now used at Specialty Imports, which services 15 to 20 vehicles a day. Service work is also experiencing a 15% annual growth.

CUSTOMER TRACKING AND MARKETING

Maurice Risner's primary concern is to keep track of existing and potential customers, the vehicles that they seek, the vehicles he has in stock and that they have purchased. Maurice believes significant sales are lost because of the following:

1. Potential customers' preferences for cars and car options are not remembered by the sales personnel.

2. Prior customers are not identified and not dealt with as "old friends".

In order to alleviate these problems, Maurice has identified several reports that would help to market his products better:

a) A listing in zip code order of the names and addresses of people who have bought cars from a specified salesperson. This is a standard selected mailing list. Maurice believes that duplicate mailings from the same salesperson would be annoying to the customer, but that "personalized" mailing from different salespersons is tolerable.

b) An on-line on-demand inquiry system that would yield the name, address and telephone number of prospective customers who have shown an interest in selected vehicles. The search criteria for finding such customers would include combinations of make, model, color, upholstery style and year of manufacture of their preferred vehicles. He has found that some customers will consider several possible cars, so multiple preferences must be handled.

c) A listing of the number and average dollar cost of service visits for selected kinds of vehicles. Maurice Risner has discovered that the service records of cars are an important item of information that buyers consider when purchasing a vehicle. The manufacturers of the cars that Specialty Imports sells do not provide sufficient data, so Maurice wants to be able to generate them from his own service work. He sees this as a marketing tool that a salesperson could use during the negotiation of a sale. The salesperson would respond to a service history inquiry by taking the customer to a computer terminal, entering the characteristics of the desired vehicle and immediately displaying the summary. Characteristics of make, model, year and engine type should be sufficient for qualifying most service inquiries.

and this the stuff about which this project is about (figure no. is mentioned under each picture) This assignment/project is to be submitted in the form of following files on Sate Dropbox. It is mandatory to submit group contract before Oct. 30th 11.59 pm. Otherwise 50 % of your marks will be deducted.

A word document file (normalization file, data structure diagram file and query screen shots file).

One SQL file having queries of the tables creation.

A file having video recording. To be submitted by each student individually.

The files must contain the names and student numbers of all group members.

Due Date: December 1 2020, 11.59 pm

1. Normalize all user views (word document).

o Show all steps of the process from unnormalized relations to 3NF.

o Produce one separate page only for each view/form.

o At top center of each page should be a name for the user view/form. Your name appears on each page.

2. Create a Data Structure Diagram (word document).

o Show the keys and all relationships.

o Submit one page only

3. Write SQL queries to create the all the 3 NF NORMALIZED tables (ONLY 3 NF tables) created in step 1. Submit one SQL file having all the queries and a word file having all the screen shots of all the queries.

4. Each group member must record a video (your face must be visible in recording) of 7 10 minutes, showing and explaining your contribution in the project.

The project is to be completed in groups. Each Group can contain 2-4 member. Following is the grading for the various parts of the case study. Everything together is worth 30% of your final grade.

SPECIALTY IMPORTS CASE STUDY MARKS

NORMALIZATION 15

DATA STRUCTURE DIAGRAM 7

TABLE CREATION 6

VIDEO RECORDING/PRESENTATION 2

TOTAL 30

NOTE: If any group member will not be able to submit his/her own recording, that student will be marked zero in the project.

There can be project interview(s) if required OR/AND if time permits.

(the most important thing i need is data structure diagrams)thanku

and please help as soon as possible

General Manager Maurice Risner Accounting Manager Parts and Service Manager New Vehicle Sales Manager Used Vehicle Sales Manager Cashier Parts Clerk Salespersons Bookkeeper Shop Supervisor Mechanics Figure 1 Specialty Imports Organizational Structure Via SPECIALTY IMPORTS SALES INVOICE Date Invoice No. SOLDTO Name: Address: City: Postal code: State: Telephone: Salesman: SaialNumber Make Model Year Color Fire & Theft Collision Insurance Coverage Includes Liability U U Property Darrage [] OPTIONS Description Code Price Serial No. TRADE-IN Mode! Make Allowmec TotalPrice Trade in Alkowice DIST Yehicle Inventory Record SerialNo. Make Model Year Exterior Color Trim Purchased From Purch Inv. No. Purchase Date PurchaseCost ListBasePrice Optional Equipment and Accessories - Factory Description Code List Price Figure3 Vehicle Inventory Record Page 3 VEHICLE SALES ACCOUNTING SUPPLEMENT Sakes Invoice No. Customer Date: Item Cost Salc Saris Freight&Prqanion Tax Licas Fees Other Commission TOTAL Tiguel Vehicle Sales Accounting Supplement om the company what is available for shipment. This ndicates how many cars it is willing to ship to Specialty ers indicate the choices available on options for the vehicle nd so on eridancollege.ca/d21/le/content/738795/viewContent/9955436/View SERVICE WORKORDER Date: Service Invoice No: Service for: Nape: Adress: Postalcode: city: Telephone Work Home Serial Number Make Model Year Color Work to be Done: Costs Pasts: Labor TA TO Figure Service Invoice ViewConte 136/View SPECIALTY IMPORTS SPECIALTY IMPORTS SALONYOLCE TeoNA siz Date Jay 14. 1997 SOLDTO: Name: Bugs Bunny Address: 24 Caroline LoveNo. 51242 SOLDTO: Name: Address: Date Jan 1997 Ex Bald 3191 bedste City: Looneywill Sede Cu Tdqhonc(890451 - 5021 Saks MdSaith Postal cock_55958 Posilood: 100.000 City: Lomwille State Gilla Tdqhone (891434.. 6443 Sam Jalan Serial Number Make Model Me701701 Macedes Benz JL San Ninter Make 1970X301 Jagter Model UX Year 1997 Colex Black Yea 1997 Color Red [X] U Inmurance Coverage Indude Fue&Thef Liability Callig Property Dange OPTIONS Descritica SrRoof Insurance Congeladodes Fire & That Laby Collision Property Damage 11 OPTIONS Description Stage Radio Code Pries 3490 Ees Jen TRADE-IN Model TRADE-IN SonN Mic Yen AS Scale Yea Allows 202800 Total Price: Trade Alle Dis : Na Ticces Tolle 3.200.00 67 118.00 Tial Price: 38.900.00 Trade All Ekset: 10.00 Net: 37.500.00 Tunes: Total Palle AR 10,072 20 Project - DRAS27198 Database D X Borjoming lobby heridancollege.ca/d21/e/content/38795/viewContent/9955436/View VEHICLE SALES ACCOUNTING SUPPLEMENT Seles Inv. Na S122 Castermarkede Due: 1992 Item Cost Side Serial #8 32000 7.10.00 YEHICLE SALES ACCOUNTING SLIPLEMENT Sales Ww.No. 51239 Owner: Buss Bum Date: 1994 Jean Cost Saria AX301 1000000 2900 SMA 32000 349.000 PS 120.00 140.00 4.200.00 4,000.00 400 DO Fright&P Tax 4.200.00 1625.00 Freight & Pequution Thes 300 10.07.20 1070 Lice Fees LiceFees Othe Odbe Common 34000 Conmission 61000 TOTAL 42.285.00 03.126.00 TOTAL 73.002.20 20.20 . tehendancollege.ca/d/e/content/733795/viewContent/9955436/View SPECIALTYEMPORIS SERVICE WORKORDER SPECIALTY IMPORTS SERVICE WORK ORDER Service lavcice No Date:1997/05/11 Servicelovkce No Dus 1991 Service for Name: Actress Bugs Bunny 24Camole Service fou: N: Address 24 CuotLX State:California Postal code 55555 City: Locsik Telephone Work Hote(3.5421 City: Looneyville Tebone Work State: California Postal code 55555 Home(90131.5421 Serial Number Make Model Yer Color Black Color 134865 Jaguar UX 1997 Serial Number Make Model 134865 Jaguar UX Work to be Done: Findsacak Year 1997 Black Work to be Done: Oil.com Tieteet Reloce los Costs Parts: 18.00 Costa Part 0.00 Labor 16.00 Tibor: 0.00 510 Tan 0.00 Total Total 0.00 WE Record Videlanty Bare Sen No Milice Merkel Year Puterior Color Trim MD7701 MaecosBan JL 1997 Red Bladicada Arcased on Puch Inv.Ne. Pudene Date Purdeos List Ease Price Marcos Bern N62145 Jan 18, 1997 31,000.00 38.900.00 Optional Ecalent and Acordes Factory Code Description List Price W31 Amantes 400.00 L12 Lightine Grap 390.00 Saint No. Mike Mode Yes Extra Cole Trees JY7UX301 JAK 19n E54 Purdused From Pud. Inv. No. Pas deccos List Best Price Jam 3294 Jan 5, 1997 19.000.00 2.999.00 Optimal Eqdammad Acerles Petery Code Description List Price J23 Liste Groep 899.00 R87 Postproofing 99800 AZ Tebal Content 78800 SERVICELOG PROSPECTLIST InvNo W21642 W21645 W21708 W22772 W22955 Date 1997/02/11 1997/02/12 1997/02/15 1997/05/11 1997/05/24 SerialNo 197UX301 LOGSR 201 A92AR401 197UX301 M97CL701 TotalCost 0.00 250.00 4,329.00 18.00 234.00 Nums Bugs Bunny Bugs Bunny Elmer Bed Sam Molare Want 1997 GreenLand Rover w/Power Steering (PT) 1992 White Jaguar UX Black Leather 1993 Mercedes Benz 1996Roda Alunist Wheels (32) General Manager Maurice Risner Accounting Manager Parts and Service Manager New Vehicle Sales Manager Used Vehicle Sales Manager Cashier Parts Clerk Salespersons Bookkeeper Shop Supervisor Mechanics Figure 1 Specialty Imports Organizational Structure Via SPECIALTY IMPORTS SALES INVOICE Date Invoice No. SOLDTO Name: Address: City: Postal code: State: Telephone: Salesman: SaialNumber Make Model Year Color Fire & Theft Collision Insurance Coverage Includes Liability U U Property Darrage [] OPTIONS Description Code Price Serial No. TRADE-IN Mode! Make Allowmec TotalPrice Trade in Alkowice DIST Yehicle Inventory Record SerialNo. Make Model Year Exterior Color Trim Purchased From Purch Inv. No. Purchase Date PurchaseCost ListBasePrice Optional Equipment and Accessories - Factory Description Code List Price Figure3 Vehicle Inventory Record Page 3 VEHICLE SALES ACCOUNTING SUPPLEMENT Sakes Invoice No. Customer Date: Item Cost Salc Saris Freight&Prqanion Tax Licas Fees Other Commission TOTAL Tiguel Vehicle Sales Accounting Supplement om the company what is available for shipment. This ndicates how many cars it is willing to ship to Specialty ers indicate the choices available on options for the vehicle nd so on eridancollege.ca/d21/le/content/738795/viewContent/9955436/View SERVICE WORKORDER Date: Service Invoice No: Service for: Nape: Adress: Postalcode: city: Telephone Work Home Serial Number Make Model Year Color Work to be Done: Costs Pasts: Labor TA TO Figure Service Invoice ViewConte 136/View SPECIALTY IMPORTS SPECIALTY IMPORTS SALONYOLCE TeoNA siz Date Jay 14. 1997 SOLDTO: Name: Bugs Bunny Address: 24 Caroline LoveNo. 51242 SOLDTO: Name: Address: Date Jan 1997 Ex Bald 3191 bedste City: Looneywill Sede Cu Tdqhonc(890451 - 5021 Saks MdSaith Postal cock_55958 Posilood: 100.000 City: Lomwille State Gilla Tdqhone (891434.. 6443 Sam Jalan Serial Number Make Model Me701701 Macedes Benz JL San Ninter Make 1970X301 Jagter Model UX Year 1997 Colex Black Yea 1997 Color Red [X] U Inmurance Coverage Indude Fue&Thef Liability Callig Property Dange OPTIONS Descritica SrRoof Insurance Congeladodes Fire & That Laby Collision Property Damage 11 OPTIONS Description Stage Radio Code Pries 3490 Ees Jen TRADE-IN Model TRADE-IN SonN Mic Yen AS Scale Yea Allows 202800 Total Price: Trade Alle Dis : Na Ticces Tolle 3.200.00 67 118.00 Tial Price: 38.900.00 Trade All Ekset: 10.00 Net: 37.500.00 Tunes: Total Palle AR 10,072 20 Project - DRAS27198 Database D X Borjoming lobby heridancollege.ca/d21/e/content/38795/viewContent/9955436/View VEHICLE SALES ACCOUNTING SUPPLEMENT Seles Inv. Na S122 Castermarkede Due: 1992 Item Cost Side Serial #8 32000 7.10.00 YEHICLE SALES ACCOUNTING SLIPLEMENT Sales Ww.No. 51239 Owner: Buss Bum Date: 1994 Jean Cost Saria AX301 1000000 2900 SMA 32000 349.000 PS 120.00 140.00 4.200.00 4,000.00 400 DO Fright&P Tax 4.200.00 1625.00 Freight & Pequution Thes 300 10.07.20 1070 Lice Fees LiceFees Othe Odbe Common 34000 Conmission 61000 TOTAL 42.285.00 03.126.00 TOTAL 73.002.20 20.20 . tehendancollege.ca/d/e/content/733795/viewContent/9955436/View SPECIALTYEMPORIS SERVICE WORKORDER SPECIALTY IMPORTS SERVICE WORK ORDER Service lavcice No Date:1997/05/11 Servicelovkce No Dus 1991 Service for Name: Actress Bugs Bunny 24Camole Service fou: N: Address 24 CuotLX State:California Postal code 55555 City: Locsik Telephone Work Hote(3.5421 City: Looneyville Tebone Work State: California Postal code 55555 Home(90131.5421 Serial Number Make Model Yer Color Black Color 134865 Jaguar UX 1997 Serial Number Make Model 134865 Jaguar UX Work to be Done: Findsacak Year 1997 Black Work to be Done: Oil.com Tieteet Reloce los Costs Parts: 18.00 Costa Part 0.00 Labor 16.00 Tibor: 0.00 510 Tan 0.00 Total Total 0.00 WE Record Videlanty Bare Sen No Milice Merkel Year Puterior Color Trim MD7701 MaecosBan JL 1997 Red Bladicada Arcased on Puch Inv.Ne. Pudene Date Purdeos List Ease Price Marcos Bern N62145 Jan 18, 1997 31,000.00 38.900.00 Optional Ecalent and Acordes Factory Code Description List Price W31 Amantes 400.00 L12 Lightine Grap 390.00 Saint No. Mike Mode Yes Extra Cole Trees JY7UX301 JAK 19n E54 Purdused From Pud. Inv. No. Pas deccos List Best Price Jam 3294 Jan 5, 1997 19.000.00 2.999.00 Optimal Eqdammad Acerles Petery Code Description List Price J23 Liste Groep 899.00 R87 Postproofing 99800 AZ Tebal Content 78800 SERVICELOG PROSPECTLIST InvNo W21642 W21645 W21708 W22772 W22955 Date 1997/02/11 1997/02/12 1997/02/15 1997/05/11 1997/05/24 SerialNo 197UX301 LOGSR 201 A92AR401 197UX301 M97CL701 TotalCost 0.00 250.00 4,329.00 18.00 234.00 Nums Bugs Bunny Bugs Bunny Elmer Bed Sam Molare Want 1997 GreenLand Rover w/Power Steering (PT) 1992 White Jaguar UX Black Leather 1993 Mercedes Benz 1996Roda Alunist Wheels (32)