After reviewing the case for Nature Bros. Ltd., answer the following questions. After reviewing this material, make a list of additional information which should be supplied to support the sales projections. Comment on objectives: Are they reasonable, optimistic, or conservative? What marketing mix would best support this growth rate? Evaluate the information supplied regarding a new product development and physical assets in light of the pro forma income statements Morris developed. Is the capital sought appropriate for the circumstances? If more information is needed, state what it is and how it could be obtained. What sources should Morris approach for this amount of capital? Based on the current balance sheet, how much equity should he give up for the investment?

NATURE BROS. LTD. BACKGROUND Thanksgiving Day 1993 is the day that Dale Morris remembers as the “public debut” of his creation, a new seasoned salt mix. Although he was a salesman by temperament and career, his hobby was cooking. Having experimented with both traditional home cooking and more exotic gourmet cooking, Morris had developed an appreciation for many herbs and spices. He had also done a lot of reading about the health hazards of the typical American diet. When his mother learned that she had high blood pressure, Morris decided it was time for some action. He created a low-salt seasoning mix, based on a nutritive yeast extract, that could be used to replace salt in most cases. This Thanksgiving dinner, prepared for 25 family members and friends, would be his final testing ground. He used his mix in all the recipes except the pumpkin pie—everything from the turkey and dressing to the vegetables and even the rolls. As the meal progressed, the verdict was unanimously in favor of his secret ingredient, although he had a hard time convincing them that it was his invention and was only 10 percent salt. Everyone wanted a sample to try at home. Over the next two years, Morris perfected his product. Experiments in new uses led to “tasting parties” for friends and neighbors, and the holiday season found the Morris kitchen transformed into a miniature assembly line producing gift-wrapped bottles of the mix. Morris became something of a celebrity in his small town, but it wasn’t until the Ladies’ Mission Society at his church approached him with the idea of allowing them to sell his mix as a fund-raiser that he realized the possibilities of his creation. His kitchen-scale operation could support the sales effort of the church women for a short time, but if he wanted to take advantage of a truly marketable product, he would have to make other arrangements. Morris agreed to “test-market” his product through the church group while he looked for ways to expand and commercialize his operation. The charity sale was a huge success (the best the women had ever experienced), and, based on this success, Morris moved to create his own company. Naming his product “Nature Bros. Old Fashioned Seasoning,” he incorporated the company in 1995 as Nature Bros. Ltd. Morris used most of his savings to develop and register the trademarks, for packaging, and for product displays. He researched the cost of manufacturing and bottling his product in large quantities and concluded that he just didn’t have the cash to get started. His first attempts to raise money, in the form of a personal bank loan, were unsuccessful, and he was forced to abandon the project. For several years, he concentrated on his career, becoming a regional vice president of the insurance company he worked for. He continued to make “Nature Bros. Seasoning” in small batches, mainly for his mother and business associates. These users eventually enabled Morris to get financial support for his company. To raise $65,000 to lease manufacturing equipment and building space, he sold stock to his mother and to two other regional vice presidents of the insurance company. For their contributions, each became the owner of 15 percent of Nature Bros. Ltd. The process of getting the product to the retail market began in August 2002, and the first grocery store sales started in March 2003. The initial marketing plan was fairly simple—to get the product in the hands of the consumer. Morris personally visited the managers of individual supermarkets, both chains and independents, and convinced many to allow a tasting demonstration booth to be set up in their stores. These demonstrations proved as popular as the first Thanksgiving dinner trial nearly 10 years earlier. Dale Morris’s product was a hit, and in a short time, he was able to contract with food brokerage firms to place his product in stores in a 10-state region.

PRESENT SITUATION As indicated in the balance sheet (see Exhibit 1), more capital is needed to support the current markets and expand both markets and products. Two new products are being developed: a salt-free version of the original product and an MSG-based flavor enhancer that will compete with Accent. Morris worked with a business consultant in drawing up a business plan to describe his company, its future growth, and its capital needs. OVERALL PROJECTIONS The first section discusses the objectives and sales projections for 2004 and 2005 (Exhibits 2 and 3). The resulting pro forma income statements for 2004 to 2005 are in Exhibits 4 and 5. 2004

OBJECTIVES The company’s objectives for 2004 are to stabilize its existing markets and to achieve a 5 percent market share in the category of seasoned salt, a 10 percent market share in salt substitutes, and a 5 percent market share in MSG products. Although the original product contains less than 10 percent salt, the company has developed a salt-free product to compete with other such products. The dollar volume for the seasoned salt category in the seven markets the company is in will amount to $7,931,889 in 2004. In 2003, sales of the company in the Oklahoma market were 5.5 percent of the total sales for that market for the eight-month period that the company was operational. Since these sales were accomplished with absolutely no advertising, the company can be even more successful in the future in all seven current markets with a fully developed and funded advertising campaign. The marketing approach will include advertisements in the print media, with ads on “food day” offering cents-off coupons. This program will take place in all seven markets, while stores will continue to use floor displays for demonstrations. Nearly 100 percent warehouse penetration should be achieved in 2004 in these markets. The goal for the category of salt substitutes for 2004 is 10 percent of the market share. This larger market share can be achieved since there are only a few competitors, Mrs. Dash, AMBI Inc. with Cordia Salt Alternative, and RCN with No Salt. The company’s product is superior in all respects and has a retail price advantage of 10 to 20 cents per can. In addition, the company’s product is much more versatile than competitors’ products. Aggressive marketing and advertising will emphasize the tremendous versatility of usage as well as the great taste and health benefits of the product. The informal consumer surveys at demonstrations indicated that consumers prefer Nature Bros. to competitors’ products by a wide margin. A new product, which is already developed, will be added during this time. Called “Enhance,” it too is a dry-mixed, non cooked, low-overhead, high-profit food product. Its category of MSG products has a dollar volume of $1,957,090 in these markets. This category includes only one main competitor, Accent, made by Pet Inc. Accent has not been heavily advertised, and it is a one-line product with little initial name recognition. The company’s new product will have a 10- to 20-cent per can retail price advantage to help achieve a 5 percent share of this category. In summary, 2004 will be spent solidifying the company’s present market positions. 2005

OBJECTIVES The company intends to open eight new markets in 2005 that include Los Angeles, Phoenix, Portland, Sacramento, Salt Lake City, San Francisco, Seattle, and Spokane. These new markets make up 17.1 percent of grocery store sales, according to the Progressive Grocer’s Marketing Guidebook, the industry standard. In the category of seasoned salt, these markets have a dollar volume of $15,218,886 a year. Salt substitutes sell at a volume of $10,064,028, and the MSG category $3,285,528. With proper advertising, the company’s shares forecast in our current markets will also be realized. A 5 percent penetration of the seasoned salt category is a very conservative projection considering the strong health consciousness of the West Coast. The products will be introduced in shippers, used in store demonstrations, and supported with media advertising to achieve at least a 5 percent market share. This would result in sales of $760,943 in that category. A 10 percent penetration is targeted in the salt-free category. Using aggressive marketing, price advantage at retail, and better packaging, the company will be well positioned against the lower-quality products of our competitors. With the dollar volume of this category at $10,064,028, a conservative estimate of our share would be $1,006,420. In the category of MSG, a 5 percent share will be achieved. The main competitor in this category does very little advertising. Again, attractive packaging, aggressive marketing, high quality, and a retail price advantage of 30–40 cents per unit will enable the company to realize a 5 percent market penetration. This share of the West Coast markets will generate sales of $164,276. Total sales of all three products in these eight new markets will be around $1,931,639. The company plans to continue to solidify the markets previously established through the use of coupons, co-op advertising, quality promotions, and word-of-mouth advertising. Market share in these original markets should increase by another 2.5 percent in 2005. The dollar volume of the seasoned salt category in 2005 should be around $9,522,472, and our market share at 7.5 percent would amount to $714,185. The dollar volume for the salt substitute category would be $6,220,748, giving sales at 12.5 percent of $775,593. In the MSG category, a 7.5 percent market share of the $2,055,864 volume would give sales of $154,189. The company’s total sales for the existing markets in 2005 will be in excess of $1,643,967. The totals for 2005 sales of Nature Bros. Old Fashioned Seasoning will be $1,475,128. Nature Bros. Salt-Free volume should be $1,784,013. The sales of Enhance, our MSG product, should be $318,465. This will give us a total sales volume of $3,557,606 for all three products in 2005.

FINANCIAL NEEDS AND PROJECTIONS In this plan, Morris indicated a need for $100,000 equity infusion to expand sales, increase markets, and add new products. The money would be used to secure warehouse stocking space, do cooperative print advertising, give point-of-purchase display allowances, and pay operating expenses.

NEW PRODUCT DEVELOPMENT The company plans to continue an ongoing research and development program to introduce new and winning products. Four products are already developed that will be highly marketable and easily produced. Personnel are dedicated to building a large and profitable company and attracting quality brokers. The next new product targets a different market segment but can be brought online for about $25,000 by using our existing machinery, types of containers, and display pieces. A highly respected broker felt that the product would be a big success. The broker previously represented the only major producer of a similar product, Pet Inc., which had sales of $4.36 million in 1985. The company can achieve at least a 5 percent market share with this product in the first year. The company’s product will be at least equal in quality and offer a 17 percent price advantage to the consumer, while still making an excellent profit. Another new product would require slightly different equipment. This product would be initially produced by a private-label manufacturer. The product would be established before any major machinery was purchased. Many large companies use private-label manufacturers, or co-packers, as they are called in the trade. Consumer tests at demonstrations and food shows have indicated that each of these products will be strong. PLANT AND EQUIPMENT The company’s plant is located in a nearly new metal building in Rose, Oklahoma. The lease on the building limits payments to no more than $300 per month for the next seven years. The new computer-controlled filling equipment will be paid off in two months, and the seaming equipment is leased from the company’s container manufacturer for only $1 per year. The company has the capability of producing about 300,000 units a month with an additional $15,000 investment for an automatic conveyer system and a bigger product mixer. This production level would require two additional plant personnel, working one shift with no overtime. The company could double this production if needed with the addition of another shift. One of the main advantages of the company’s business is the very small overhead required to produce the products. The company can generate enough product to reach sales of approximately $4 million a year while maintaining a production payroll of only $37,000 a year. To meet the previously outlined production goals, the company will need to purchase another filling machine in 2005. This machine will be capable of filling two cans at once with an overall speed of 75 cans per minute, which would increase capacity to 720,000 units a month. A higher-speed seaming machine will also need to be purchased. The filling machine would cost approximately $22,000; a rebuilt seamer would cost $25,000, while a new one would cost $50,000. With the addition of these two machines, the company would have a capacity of 1,020,000 units per month on one shift. By 2006, the company will have to decide whether to continue the lease or buy the property where located and expand the facilities. The property has plenty of land for expansion for the next five years. The company has the flexibility to produce other types of products with the same equipment and can react quickly to changes in customer preferences and modify its production line to meet such demands as needed.

CASE 2-3: Starnes-Brenner Machine Tool Company: To Bribe or Not to Bribe?

The Starnes-Brenner Machine Tool Company of Iowa City, Iowa, has a small one-man sales offi ce headed by Frank Rothe in Latino, a major Latin American country. Frank has been in Latino for about 10 years and is retiring this year; his replacement is Bill Hunsaker, one of Starnes-Brenner’s top salespeople. Both will be in Latino for about eight months, during which time Frank will show Bill the ropes, introduce him to their principal customers, and, in general, prepare him to take over. Frank has been very successful as a foreign representative in spite of his unique style and, at times, complete refusal to follow company policy when it doesn’t suit him. The company hasn’t really done much about his method of operation, though from time to time he has angered some top company people. As President Jack McCaughey, who retired a couple of years ago, once remarked to a vice president who was complaining about Frank, “If he’s making money—and he is (more than any of the other foreign offi ces)—then leave the guy alone.” When McCaughey retired, the new chief immediately instituted organizational changes that gave more emphasis to the overseas operations, moving the company toward a truly worldwide operation into which a loner like Frank would probably not fi t. In fact, one of the key reasons for selecting Bill as Frank’s replacement, besides Bill’s record as a top salesperson, is Bill’s capacity to be an organization man. He understands the need for coordination among operations and will cooperate with the home offi ce so that the Latino offi ce can be expanded and brought into the mainstream. The company knows there is much to be learned from Frank, and Bill’s job is to learn everything possible. The company certainly doesn’t want to continue some of Frank’s practices, but much of his knowledge is vital for continued, smooth operation. Today, Starnes-Brenner’s foreign sales account for about 25 percent of the company’s total profi ts, compared with about 5 percent only 10 years ago. The company is actually changing character, from being principally an exporter, without any real concern for continuous foreign market representation, to having worldwide operations, where the foreign divisions are part of the total effort rather than a stepchild operation. In fact, Latino is one of the last operational divisions to be assimilated into the new organization. Rather than try to change Frank, the company has been waiting for him to retire before making any signifi cant adjustments in its Latino operations. Bill Hunsaker is 36 years old, with a wife and three children; he is a very good salesperson and administrator, though he has had no foreign experience. He has the reputation of being fair, honest, and a straight shooter. Some back at the home offi ce see his assignment as part of a grooming job for a top position, perhaps eventually the presidency. The Hunsakers are now settled in their new home after having been in Latino for about two weeks. Today is Bill’s fi rst day on the job. When Bill arrived at the offi ce, Frank was on his way to a local factory to inspect some Starnes-Brenner machines that had to have some adjustments made before being acceptable to the Latino. government agency buying them. Bill joined Frank for the plant visit. Later, after the visit, we join the two at lunch. Bill, tasting some chili, remarks, “Boy! This certainly isn’t like the chili we have in America.” “No, it isn’t, and there’s another difference, too. The Latinos are Americans and nothing angers a Latino more than to have a ‘Gringo’ refer to the United States as America as if to say that Latino isn’t part of America also. The Latinos rightly consider their country as part of America (take a look at the map), and people from the United States are North Americans at best. So, for future reference, refer to home either as the United States, States, or North America, but, for gosh sakes, not just America. Not to change the subject, Bill, but could you see that any change had been made in those S-27s from the standard model?” “No, they looked like the standard. Was there something out of whack when they arrived?” “No, I couldn’t see any problem—I suspect this is the best piece of sophisticated bribe taking I’ve come across yet. Most of the time the Latinos are more ‘honest’ about their mordidas than this.” “What’s a mordida ?” Bill asks. “You know, kumshaw , dash , bustarella , mordida ; they are all the same: a little grease to expedite the action. Mordida is the local word for a slight offering or, if you prefer, bribe,” says Frank. Bill quizzically responds, “Do we pay bribes to get sales?” “Oh, it depends on the situation, but it’s certainly something you have to be prepared to deal with.” Boy, what a greenhorn, Frank thinks to himself, as he continues, “Here’s the story. When the S-27s arrived last January, we began uncrating them and right away the jefe engineer (a government offi cial)— jefe , that’s the head man in charge—began extra-careful examination and declared there was a vital defect in the machines; he claimed the machinery would be dangerous and thus unacceptable if it wasn’t corrected. I looked it over but couldn’t see anything wrong, so I agreed to have our staff engineer check all the machines and correct any fl aws that might exist. Well, the jefe said there wasn’t enough time to wait for an engineer to come from the States, that the machines could be adjusted locally, and we could pay him and he would make all the necessary arrangements. So, what do you do? No adjustment his way and there would be an order canceled; and, maybe there was something out of line, those things have been known to happen. But for the life of me, I can’t see that anything had been done since the machines were supposedly fi xed. So, let’s face it, we just paid a bribe, and a pretty darn big bribe at that—about $1,200 per machine. What makes it so aggravating is that that’s the second one I’ve had to pay on this shipment.” “The second?” asks Bill. “Yeah, at the border, when we were transferring the machines to Latino trucks, it was hot and they were moving slow as molasses. It took them over an hour to transfer one machine to a Latino truck and we had ten others to go. It seemed that every time I spoke to the dock boss about speeding things up, they just got slower. Finally, out of desperation, I slipped him a fi stful of pesos. and, sure enough, in the next three hours they had the whole thing loaded. Just one of the local customs of doing business. Generally, though, it comes at the lower level where wages don’t cover living expenses too well.” There is a pause, and Bill asks, “What does that do to our profi ts?” “Runs them down, of course, but I look at it as just one of the many costs of doing business—I do my best not to pay, but when I have to, I do.” Hesitantly, Bill replies, “I don’t like it, Frank. We’ve got good products, they’re priced right, we give good service, and keep plenty of spare parts in the country, so why should we have to pay bribes? It’s just no way to do business. You’ve already had to pay two bribes on one shipment; if you keep it up, the word’s going to get around and you’ll be paying at every level. Then all the profi t goes out the window—you know, once you start, where do you stop? Besides that, where do we stand legally? The Foreign Bribery Act makes paying bribes like you’ve just paid illegal. I’d say the best policy is to never start: You might lose a few sales, but let it be known that there are no bribes; we sell the best, service the best at fair prices, and that’s all.” “You mean the Foreign Corrupt Practices Act, don’t you?” Frank asks, and continues, in an I’m-not-really-so-out-of-touch tone of voice, “Haven’t some of the provisions of the Foreign Corrupt Practices Act been softened somewhat?” “Yes, you’re right, the provisions on paying a mordida or grease have been softened, but paying the government offi cial is still illegal, softening or not,” replies Bill. Oh boy! Frank thinks to himself as he replies, “Look, what I did was just peanuts as far as the Foreign Corrupt Practices Act goes. The people we pay off are small, and, granted we give good service, but we’ve only been doing it for the last year or so. Before that I never knew when I was going to have equipment to sell. In fact, we only had products when there were surpluses stateside. I had to pay the right people to get sales, and besides, you’re not back in the States any longer. Things are just done different here. You follow that policy and I guarantee that you’ll have fewer sales because our competitors from Germany, Italy, and Japan will pay. Look, Bill, everybody does it here; it’s a way of life, and the costs are generally refl ected in the markup and overhead. There is even a code of behavior involved. We’re not actually encouraging it to spread, just perpetuating an accepted way of doing business.” Patiently and slightly condescendingly, Bill replies, “I know, Frank, but wrong is wrong and we want to operate differently now. We hope to set up an operation here on a continuous basis; we plan to operate in Latino just like we do in the United States. Really expand our operation and make a long-range market commitment, grow with the country! And one of the fi rst things we must avoid is unethical . . .” Frank interrupts, “But really, is it unethical? Everybody does it, the Latinos even pay mordidas to other Latinos; it’s a fact of life— is it really unethical? I think that the circumstances that exist in a country justify and dictate the behavior. Remember, man, ‘When in Rome, do as the Romans do.’” Almost shouting, Bill blurts out, “I can’t buy that. We know that our management practices and relationships are our strongest point. Really, all we have to differentiate us from the rest of our competition, Latino and others, is that we are better managed and, as far as I’m concerned, graft and other unethical behavior have got to be cut out to create a healthy industry. In the long run, it should strengthen our position. We can’t build our future on illegal and unethical practices.” Frank angrily replies, “Look, it’s done in the States all the time. What about the big dinners, drinks, and all the other hanky-panky that goes on? Not to mention PACs’ [Political Action Committee] payments to congressmen, and all those high speaking fees certain congressmen get from special interests. How many congressmen have gone to jail or lost reelection on those kinds of things? What is that, if it isn’t mordida the North American way? The only difference is that instead of cash only, in the United States we pay in merchandise and cash.” “That’s really not the same and you know it. Besides, we certainly get a lot of business transacted during those dinners even if we are paying the bill.” “Bull. The only difference is that here bribes go on in the open; they don’t hide it or dress it in foolish ritual that fools no one. It goes on in the United States and everyone denies the existence of it. That’s all the difference—in the United States we’re just more hypocritical about it all.” “Look,” Frank continues, almost shouting, “we are getting off on the wrong foot and we’ve got eight months to work together. Just keep your eyes and mind open and let’s talk about it again in a couple of months when you’ve seen how the whole country operates; perhaps then you won’t be so quick to judge it absolutely wrong.” Frank, lowering his voice, says thoughtfully, “I know it’s hard to take; probably the most disturbing problem in underdeveloped countries is the matter of graft. And, frankly, we don’t do much advance preparation so we can deal fi rmly with it. It bothered me at fi rst; but then I fi gured it makes its economic contribution, too, since the payoff is as much a part of the economic process as a payroll. What’s our real economic role, anyway, besides making a profi t, of course? Are we developers of wealth, helping to push the country to greater economic growth, or are we missionaries? Or should we be both? I really don’t know, but I don’t think we can be both simultaneously, and my feeling is that, as the company prospers, as higher salaries are paid, and better standards of living are reached, we’ll see better ethics. Until then, we’ve got to operate or leave, and if you are going to win the opposition over, you’d better join them and change them from within, not fi ght them.” Before Bill could reply, a Latino friend of Frank’s joined them, and they changed the topic of conversation.

QUESTIONS 1. Is what Frank did ethical? By whose ethics—those of Latino or the United States?

2. Are Frank’s two different payments legal under the Foreign Corrupt Practices Act as amended by the Omnibus Trade and Competitiveness Act of 1988?

3. Identify the types of payments made in the case; that is, are they lubrication, extortion, or subornation?

4. Frank seemed to imply that there is a similarity between what he was doing and what happens in the United States. Is there any difference? Explain.

5. Are there any legal differences between the money paid to the dockworkers and the money paid the jefe (government official)? Any ethical differences?

Describe in words what the function of each line is in the following SQL query

The lyrics database is provided under question 3 for context

1. select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300

2.

SELECT artistName

         FROM Artists

        WHERE artistID IN

                (SELECT artistID

                       FROM    Titles)

3. select m.lastname, m.firstname, s.lastname        

from members m inner join salespeople s using (salesID)        

order by m.lastname asc;

The lyrics database is provided below

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

Q1.Discuss Lewins model and Kotters model for change with reference to GE Motors.-7 marks

Q2.What are the two strategies adopted by GE Motors in order to bring change and gain market share.-8 marks.

Abstract— The main purpose of this article was to elaborate and bring to light the core concept of the organization change, how it works, diverse factors which moves organization to change, steps for change, resistance for change, change forces, change management approaches and last an example of General Motor (GM) has given that how change was taken place in the organization and what was the strategies for change management. Recommendations and conclusion form the last part of the paper. Keywords - Organization change, Factors, Resistance, GM.

ORGANIZATIONAL CHANGE: A BRIEF INTRODUCTION The business world to day is going very fast and new technology new methods of production and new taste of customers and new market trends as well as new strategies for best control of the organizations and motivation of employees are emerging and taking place from old to new methods, because the customers are the emperor of market and most of the company now spending billions of amount on research and development in the organization, by keeping in view all these things the managers and experts of the today businesses now compel to decide about the change management in the organizations, because business activities now are globalize, and every organization strive to sustained the loyal customers, trained the employees, introduce and adopt new methods of production and best control the activities of the organization, so from here the concept of change management or organization change starts. When the company feel that the activities which they are doing, the management, the way of administration, the use of technology, the human resource policies, the culture of the organization, the liking and disliking the contents and context of the organization by the employees, organization structure, group concept ,the product quality are continuously destroying the image and reputation of the organization the question arises that how will change the organization in present scenario, so when the expert specialist decides about all the situation and preparing for changing the organization it leads to the concept of organizational change or change management[1] . In the word of coetsee he said that it is the ability of the management that how they can get maximum benefits and support form change which reduces resistant from the side of employees and encourage appreciate acceptance and support. The process of changing the activities of the organization as well as the implementation of the procedures and technologies to achieve the desire objective of the organization, in simple words to change the environment of the business organization and to achieve a high profit from that change, usually change management includes different aspects such as control change, adaptation change and effecting change. The final goal of the change management is the long term sustainability of the organization. organizational change simply means to change the activities of the organization concern it may includes to change the culture of the organization, technology, business process, change of employees, rules and procedures, recruitment and selection, design of jobs, method of appraisal, and human resource techniques, physical environment of the organization, methods of training and development, job skill and knowledge etc. when the change of the concern organization is fundamental it is called organization transformations. Change management means when all the needed actions are taken to improve the present situation for future to implement the change strategies to get the maximum advantages and also see that the objectives of the organization is achieving or not.

Factors Behind The Organization Change As we have mentioned before that organization change occurs due to some factors that may be external or internal, such factors may also bring change in the activities of he organization and may also create problems to harder the change process, as every know that change creates resistance, and this resistance may creates huge problems, resistance to change is also from the old employees or middle level managers or people as they always appose to the change strategies due to their own way of thinking and perception regarding the change concept, this may be due to lake of knowledge about the situation or due to the self interest of thold employees but what ever may be the reasons but it is fact that change always bring resistance, now it is up to mangers that how they reduce the intensity of the resistance and implement the whole change strategic business model in the organization.

Change Forces The following are the main forces which bring change in the organization. These are as under but it may depend on the organization environment and the context of the organization. Change in new government policies and legislation, Change and development in new materials, Social and culture value change, Change in national and global economic condition and trade policies and regulation, Technology development, Change in customer taste and requirements, Development and innovation in manufacturing process, New products and services design innovation, New ideas about the products that how to deliver customers value and satisfaction, Office and factory relocation closer to customers, suppliers, and market, nature of the workforce, technology , economic shocks, completion, social trends, and world politics.

Resistance to Change Change creates resistance to change in every organization; it is the react response from the side of the old employees. When change strategies have implemented in the organization the employees quickly respond by voicing complaints, engaging in work slowdown, threatening to go on strike, etc. but care should be taken by the change management expert to overcome the resistance

Major force for resistance to change: resistance to change forces categorize into two main heading, 1) individual sources and 2) organizational sources

1). Individual Source Resistance To Change Includes The Following. Habit, security, selective information processing, economic factors, fear of the unknown.

2). Organizational Sources for Resistance to Change Include the Following. Limited focus on change, organization structural inertia, threat to expertise, threat to established power relationship, group inertia, threat to established resource allocation.

3). Overcoming to Resistance to Change. Overcoming to resistance to change means to use the tactics to reduce the intensity of the resistance to change, the change agents have the ability to use these tactics. are as under. a) Implementing change fairly. b) Selection people who accept change

c) Education and communication

d)Participation

e) Building support and commitment

f) Manipulation and cooptation

.Organizational Change Managing Approaches

When change management taken place in the organization, now the question is how best one can manage change. There are four approaches to change management. Lewins classic three step model of change process, kotters eight step plan, action research, and organizational development. According to the lewins model the organization must follow three steps for successful change management, which are. Unfreezing: the status quo, changing to overcome the pressure of both individual resistance and group conformity. Movement; desire end state, a change process that transforms the organization from the status quo to a desired end state. And refreezing the new change to make it permanent, stabilizing a change intervention by balancing driving and restraining forces. [4]

.Kotters Eight Step Plan. To more elaborate the lewins model kotters have develop eight steps which can be adopted to implement change.

These are 1) Establish a sense of urgency that why a change is needed. 2) Form enough power to lead the change 3) Create a new vision to direct the change and strategies for achieving the vision 4) Vision communication in organization 5) Empower others to act on vision by removing barriers 6) Plan for, create short term reward to move the organization toward the new vision 7) Continues improvement and make necessary adjustment in new programs. 8) Reinforce the change by demonstrating the relationship between new behaviors. (Source Stephen, 2005)

Organizational Development Techniques The change agent considers the following technique to bring organizational development. Sensitivity training, team building, process consultation, survey feed back, appreciative inquiry and inter group development. These are the important technique which should adopt by change specialist to bring effective development in the organization, because organizational development is vital for organizational change.

Change Management at General Motor

General motor established in 1908. that time the company was the sole carmaker dealer in the region, e.g. Michigan, first it was a holding Buick company, till 1920 it was becoming the world largest motor manufacturing company, the company got a tremendous success in time of Alfred salon, due to his leadership the company was producing new style and design car every year, and he had given such concept to the company. The other brand of the company is Chevrolet, Pontiac, Buick, and Cadillac. These were the different brand cars which were producing by company that time, and this way there were no other competitors to compete in the company different cars. But with emerging of the japans automakers the company felt threatened, specially the emerging of Toyota Japan, who with great extent disturbed the profitability of the GM, especially in the North American market. In 2001 the sale graph of the GM was in declined trend, because the Toyota had captured the market, this way the GM received loan form American government and Canadian government to support the company in that crises period. During 2009 the company had faced a bankruptcy and had closed several brand and sold out to china based company. Now the company again got his position in market by restructuring and making change in the company. Now the company is again operating business in the core brands in America such as Chevrolet, GMC, Buick, and Cadillac.

In this section we will highlight the reason and forces behind the change in general motor

External Forces

In external forces the GM which was greatly affected by the japans based company Toyota was the emerged competitors in that time, the north America is still the biggest market place for GM where the company sold out in recent year round about 2.9 million and the nearest competitor is Toyota and china based companies, these competitors with great extent disturbed the total profitability of the general motor, and the second external forces which the company faced a huge problem was financial crises which with great extent collapsed the cash flows of the company

Internal Forces.

The another force for change to GM was the high wages cost to employees as the company was paying $74 per hour as compared to Toyota $44 per hour, because GM was an agreement with trade union. And the GM was compelled to run the plant with minimum 80% capacity whether it was needed or not, these things play an important role in the bankruptcy of the company.

Types of Changes

By keeping in view the above discussion the company ultimate decided to bring or make change in the company, so the company decided to bring changes on some areas of the business, these were included, structural change, cost change, process change and cultural change.

Steps in the Change Management Process of General Motor.

While going on change management the GM, the company took some steps to adopt change these are the most recent change which the company had taken.

Cost Cutting.

The first steps which was taken by the GM is about cost cutting, the company has reduced its cost of some brands to maintain the profit level, such as the Saturn and hammer, by keeping the other company cost. Similarly the company also cut pay of employees which was the major problem to company. The company has achieved the target of cost cutting up to 15 billion in recent year.

Cultural Change

The general motor also changed the culture of the company, the GM removed it automotive product board, and automotive strategy up to 8 men board decision making team which were responsible to report directly to CEO. The main objective of such change is to speed up the day to day decision making process. The GM also changed the culture to improve the efficiency of the employees and make accountable and responsible one.

Problems to Change Process

In change management process the GM faced a variety of problems

Problems in Cultural Change The cultural plan was based on top down approach, which ignored totally the involvement of the employees as compared to other companies , some suggested that the company has not down top approach, in which employees feel satisfaction, so this regard the company empowered the employees by introducing in tailoring the down top approach. Rather than merely telling to employees what they do.

Problem with Cost Cutting As the cost cutting has an important place in the change management but it was faced great problem from the agreement of trade union, as the company was an agreement with not lowering the pay of the employees and maintain the capacity level.

Results of the Change Process As we have discussed that the GM had adopted changed previously also but these changes are recently those changes which adopted by the company in the year 2009. The results of he changes are as under.

Result of Cost Cutting The result of cost cutting of GM seems from its employment figure of 98 to 2009, it was reduced from 226000 to 101000 workers, and now the company is concentrating on sale rather than to further cut off, and also the company is deciding to reduce the worked force of the factory from 6oooo to 4oooo. And it will certainly lead to cost saving to the company.

Result of Cultural Change The general motor had also achieved good result from cultural change, and the employees now becoming aware about the responsibility and accountability, as well as the company also empowered the employees to give better productivity.

As we have discussed above that the general motor adopted tow main strategies for change management, recently one was cost cutting strategy for change management and other was cultural change management strategy, the company adopted two other change strategies but these are the most recent, by developing such strategies the company has achieved its market shares in north America again, as the company was threatened by the emerging of competitors in the automakers industry but the company decided to bring changes and now the company again in better position and he again maintained the brand of core products, beside of these the company also achieved the cost benefits by implementing these change strategies in the company.

Q1.Discuss Lewins model and Kotters model for change with reference to GE Motors.-7 marks

Q2.What are the two strategies adopted by GE Motors in order to bring change and gain market share.-8 marks.

Create the following SQL queries using the lyrics database below

1. List the first name, last name, and region of members who do not have an email.

2. List the first name, last name, and region of members who do not have an email and they either have a homephone ending with a 2 or a 3.

3. List the number of track titles that begin with the letter 's' and the average length of these tracks in seconds

4. Report the number of members by state and gender. Sort the results by the region

The lyrics database is below, thank you for help

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

Describe what each line does in the following SQL query

The lyrics database is provided under question 3 for context

1. select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300

2. select concat_ws(' ', firstname, lastname) as "Member Name"        

from members;

3. select m.lastname, m.firstname, s.lastname        

from members m inner join salespeople s using (salesID)        

order by m.lastname asc;

The lyrics database is provided below

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

the mysql lyrics database is provided below

1.)What is a primary key used for? Can you give me an example of a primary key in the Lyrics database?

2.)What is the purpose of doing a join between two tables. Ex. Why would I ever want to join the Tracks and Titles tables together?

3.)Why is isolation important in database design?

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
   ArtistID int,
   ArtistName varchar (50) NOT NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   EntryDate date NULL ,
   LeadSource varchar (10) NULL
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
   Genre varchar (15)
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
   MemberID int ,
   FirstName varchar (25) NULL ,
   LastName varchar (25) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   HomePhone varchar (16) NULL ,
   WorkPhone varchar (16) NULL ,
   EMail varchar (40) NULL ,
   Gender char (1) NULL ,
   Birthday date NULL ,
   SalesID smallint NULL
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);

CREATE TABLE SalesPeople (
   SalesID smallint ,
   FirstName varchar (20) NOT NULL ,
   LastName varchar (20) NOT NULL ,
   Initials varchar (3) NULL ,
   Base decimal(5,2) NULL,
   Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);  

CREATE TABLE Studios (
   StudioID int,
   StudioName varchar (40) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   Contact varchar (50) NULL ,
   EMail varchar (40) NULL ,
   Phone varchar (16) NULL ,
   SalesID smallint NULL
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);

CREATE TABLE Titles (
   TitleID int ,
   ArtistID int NULL ,
   Title varchar (50) NULL ,
   StudioID int NULL ,
   UPC varchar (13) NULL ,
   Genre varchar (15) NULL
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');

CREATE TABLE Tracks (
   TitleID int NOT NULL ,
   TrackNum smallint NOT NULL ,
   TrackTitle varchar (50) NULL ,
   LengthSeconds smallint NULL ,
   MP3 smallint NULL ,
   RealAud smallint NULL
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);

CREATE TABLE XrefArtistsMembers (
   MemberID int NOT NULL ,
   ArtistID int NOT NULL ,
   RespParty smallint NOT NULL
);

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

using the lyrics database schema in mysql provided below.

1.)List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail”?

2.)List the names of members in the artist called 'Today'.

3.)Report the total runtime in minutes FOR EACH album in the Titles table.

4.)List the firstname, lastname of members who are represented by the salesperson “Lisa Williams”

5.)List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents.

below is the lyric schema used on this assigment for mysql. copy paste it in the mysql console

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
   ArtistID int,
   ArtistName varchar (50) NOT NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   EntryDate date NULL ,
   LeadSource varchar (10) NULL
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
   Genre varchar (15)
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
   MemberID int ,
   FirstName varchar (25) NULL ,
   LastName varchar (25) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   HomePhone varchar (16) NULL ,
   WorkPhone varchar (16) NULL ,
   EMail varchar (40) NULL ,
   Gender char (1) NULL ,
   Birthday date NULL ,
   SalesID smallint NULL
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);

CREATE TABLE SalesPeople (
   SalesID smallint ,
   FirstName varchar (20) NOT NULL ,
   LastName varchar (20) NOT NULL ,
   Initials varchar (3) NULL ,
   Base decimal(5,2) NULL,
   Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);  

CREATE TABLE Studios (
   StudioID int,
   StudioName varchar (40) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   Contact varchar (50) NULL ,
   EMail varchar (40) NULL ,
   Phone varchar (16) NULL ,
   SalesID smallint NULL
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);

CREATE TABLE Titles (
   TitleID int ,
   ArtistID int NULL ,
   Title varchar (50) NULL ,
   StudioID int NULL ,
   UPC varchar (13) NULL ,
   Genre varchar (15) NULL
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');

CREATE TABLE Tracks (
   TitleID int NOT NULL ,
   TrackNum smallint NOT NULL ,
   TrackTitle varchar (50) NULL ,
   LengthSeconds smallint NULL ,
   MP3 smallint NULL ,
   RealAud smallint NULL
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);

CREATE TABLE XrefArtistsMembers (
   MemberID int NOT NULL ,
   ArtistID int NOT NULL ,
   RespParty smallint NOT NULL
);

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

Harold Blank, Vice President of Manufacturing for Herr Foods, Inc., was contemplating a capital investment that could improve production; however, this operations decision would force several of his recently hired employees into new jobs as positions were eliminated by automation.

Herr Foods took pride in achieving the highest quality in their finished products. Their top product line was potato chips, and the final physical inspection was a critical step. That was when several employees identified and removed around 75% of the discolored or burned chips before packaging. So, when Harold was introduced to the opti-scanner machine, which claimed to do the same step more effectively than humans, he was intrigued, but not convinced.

COMPANY HISTORY

In 1946, Jim Herr purchased Verna’s Potato Chips Company in Lancaster, Pennsylvania, for $1,750. The company’s assets included:

two iron kettles (each holding 100 pounds of lard),

a potato slicer for three potatoes,

a peeler that held 10 pounds of potatoes, and

a 1938 Dodge panel truck.

At 21 years old and with a $1,750 loan, Jim distributed potato chips in a wax paper bag to small grocery stores and other food outlets in southeastern Pennsylvania.  He and his wife, Miriam, worked long hours in front of the hot kettles perfecting their recipes and product quality. Building a customer base was equally difficult as there were similar operations selling potato chip products in Pennsylvania and Maryland.

In 1951, a fire destroyed the company. Jim, a religious man, believed God would lead them to a new location. They purchased a small farm in Nottingham—ideally located near the mission church where his family was active and closer to their distribution area.  With all of his available resources and help from the local bank, Jim reestablished Herr Foods and expanded the scope of its operations.

Nottingham—located in Chester County about 50 miles southwest of Philadelphia and 50 miles north of Baltimore—was a great location.  Even though it was in a rural area, nearly 50% of America’s population lived within a 500-mile radius.  The county itself had one of the highest per-capita incomes in the state and was one of the fastest-growing counties in the Philadelphia area.  Jim found that the people practiced good moral and ethical standards, were religious, and provided a dependable and stable work force.

Over the years, Jim and Mim’s faith and commitment to fair and ethical business practices paid off.  Jim was a man of his word.  A simple handshake often closed many complicated agreements between customers and suppliers.  Jim always made good on his promises and never forfeited on a debt obligation.  If someone was unethical in his business practice with him, Jim was compassionate and not vindictive.  This was not a sign of weakness, but an effort to reflect his Christian testimony in all areas of his business.

Herr Foods grew and prospered.  Currently, there were 600+ loyal employees and over 150 sku product line classifications.  Herr’s operated in a 10-state region between Massachusetts, Ohio, and Virginia, with 20 distribution centers and its own fleet of vehicles to distribute its products to retailers.  Revenues were approaching $100 million per year for this family-owned business with potato chips accounting for about 60% of revenues.  

The same values that Jim practiced in running the business were still evident as the second generation had assumed ownership and operation of the business.  Jim’s three sons, Jim, Ed, and Gene; his daughters, June and Martha; and his son-in-law, Daryl, were all active in the business and espoused the same strong Christian values and beliefs.  Son Jim stated, “I’ve agreed with my father’s philosophy of running the company to maintain a culture of integrity, fairness, and opportunity; to stress quality products and service; and to continue the growth of the company.”  

OPERATIONS

Given that there were 30 snack-food companies in Pennsylvania alone—along with large national companies like Frito Lay and Nabisco—having any level of success and growing market share was quite an accomplishment for Herr’s.  In addition to the extensive potato-chip product line, the company produced pretzels and other snack foods.  Each line offered a variety of products with varying sodium, fat, nutrients, and packaging specifications.  The company also distributed complementary product lines such as salsas, dips, and meat snacks.

Pretzel production had recently flourished.  Three hundred pounds of dough are mixed every 8 minutes and fed onto four different conveyer lines.  After the dough—either plain or sour—was blended into 10-pound sections, it was fed through a pretzel die to form its shape.  Depending upon the pretzel, 600 to 2,000 pounds per hour were baked in each of four ovens.  The operation runs 24 hours per day, Monday through Friday noon.  On Friday afternoon, the equipment was cleaned.   

The tortilla/corn line produces both corn and tortilla chips.  Two lines ran continuously at a rate of about 2,000 pounds of product per hour.  The corn was soaked for 8 to 10 hours and then cooked for 2 minutes to form a lumpy, creamed texture.  The product was cut on a sheeter and sent to a fryer for 15 seconds.  The hot chip runs through a tumbler where seasoning was applied. One day per week, onion rings were produced in this assembly area.  Onion rings were actually dehydrated potato flakes that were fried and covered with onion seasoning.

Cheese curls and popcorn represent smaller product lines.  For cheese curls, moisture was removed from corn meal, seasoning was applied, and the product was baked for 1 minute to create a puff. About 1,000 pounds were produced per hour.  For popcorn, yellow gourmet kernels were air popped, small and unpopped kernels are removed, and the remaining kernels were seasoned.

The potato chip line was the biggest operation.  Twenty tractor trailer loads of potatoes arrive every day with 50,000 pounds of potatoes per truck.  Each truck was hydraulically lifted to a 45-degree angle to dump the potatoes.

Potatoes were dropped into a washer, scrubbed, and sent by water flume to three slicers.  They were sliced at 24 slices per inch in less than 1 second.  They then go to the vegetable oil vats where they were cooked between 3 and 5 minutes at about 325° F.  Four pounds of potatoes made 1 pound of potato chips.  Around 56,000 pounds of potatoes per hour were processed through five fryer ovens 16 hours a day.

After they were dried and salted, a conveyor belt transports the chips past a final inspection point where four employees identify and remove overcooked or green chips.  Management estimated that these employees found and removed 75% of the defective chips, and defective chips represented about 0.5% of the entire output.  With this inspection, only about 10 to 15 defective chips out of about 10,000 will reach the final package.

Various stages along the conveyer belt sized the chips before they reached a packing machine, with smaller chips going to the smaller packages.  The tortilla chips, popcorn, cheese curls, and potato chips were packaged by weight using a bucket process to accumulate the product.  The product dropped into a waiting bag.  Seven million bags of potato chips of various sizes were produced per month along with a similar quantity of other products.  The production process was almost entirely automated until the bags were placed in cartons for shipment.   

Cartons of all the product lines were stacked by type in the warehouse. The entire warehouse inventory rotated out on a first-in-first-out basis 3 times per week.  The inventory turnover rate was critical for a product sensitive to freshness with about a 10-week shelf life.

Automation

From the time the potatoes were dumped off the truck until the packages of finished product were boxed, there was virtually no human contact during potato chip production.  Minimum personal contact with the food was desirable from a health perspective; however, the lack of observation and interaction was a quality control concern.  Management had always seen the importance of personal inspection to insure that defective products were identified and removed.  Harold was concerned that automation would replace the only stage—a critical stage—where people have an impact on quality.

Herr Foods’ top priority was quality.  Ideally, customers should not find even one chip that was defective in any way.  Inspectors removed chips that are green, black, and dark brown or have black and gray spots.  The green chips resulted when the potatoes did not have the proper sugar content, often found in unripe potatoes.  Black or dark brown chips occurred when the chips cook too long in the hot oil.  Black, gray, and hard spots were caused in the colder months when potatoes bruise in shipment.

Since Herr Foods prints “Satisfaction Guaranteed or Your Money Back” on each package, the company made every effort to avoid returns.  The company’s products have obviously met the consumer satisfaction test as only about 3 out of 100,000 bags of product were returned.

The new system could easily replace the existing system.  A 20-foot conveyer belt was used for the inspection process where two employees on both sides of the belt search for defective chips.  This section of the production line would be removed and replaced with a 5-foot belt moving at 60 miles per hour, followed by the opti-scanner, which took another 5 feet and, finally, a 10-foot conveyer belt moving at the original speed of 3 miles per hour toward the sorting and packing operations.

The opti-scanner spread out the chips and passed them under an optical sensor that recognized discolored chips.  As chips were scanned, a blast of air blew defective chips onto another belt moving at a 90-degree angle where they were disposed.

The opti-scanner manufacturer was convinced that quality will not suffer because of automation, but significantly improved the process of detecting and discarding defective chips from the current rate of 75% to a 95% success level.  However, the automated process would also lose about 1 good chip for every 4 bad chips.  As the bad chips were blown off the conveyer belt, an occasional good chip would be blown away along with it.  The manual inspection system also lost some good chips, but the amount was insignificant.  About 2.5 defective chips of every 10,000 chips would be missed through manual inspection; about 12 good chips of every 10,000 would be rejected by the opti-scanner.

While the manufacturer’s claims seemed impressive, Harold still had some significant concerns regarding this new technology and potential risks.  He was not aware of any other regional snack food companies that were planning on making this investment and only national companies like Frito Lay seemed to have the means to consider taking a risk of this magnitude on such unproven technology.

Cost

The new opti-scanner machine would cost $75,000; shipping, installation, and testing would be an additional $20,000.  It would cost $5,000 to dismantle the existing conveyer belt and prepare the area for the new system. To avoid disrupting production, management wanted to install the new systems—about a 16-hour process—over the next holiday weekend using existing maintenance personnel with technical staff from the manufacturer.  The life expectancy of the opti-scanner was five years for capital investment purposes with a zero salvage value.  The opti-scanner would probably incur an additional $1,200 per year in maintenance and insurance costs.

With the machine, the company would not need the 4 inspectors employed on each of 2 shifts.  These people worked 40 hours per week and received $10 per hour.  The company assumed benefit costs of an extra 25%.  The evening shift pay differential was an extra $0.50 per hour.

Harold had informed the staff of the company’s policy to not terminate employees due to automation.  Affected employees would be reassigned to other jobs within the company.  However, Harold believed that these positions could be eliminated within 6 months through attrition and reductions in new hires, which would be a savings to the company.

Inspectors tended to be the most recent hires.  While the work can be monotonous, it was critical for ensuring product quality.  The position experienced a higher turnover rate than other positions. The average employee stayed about 6 months to 1 year; then, 3 out of 4 transfer to other positions and 1 quits.  It costs about $300 per employee in hiring and training.

The inspector position also determined which employees proved capable of more skilled and technical positions.  The company had only a limited number of entry-level positions of this nature, and these positions provided a natural training ground.

To justify the acquisition to top management, the machine must give the company a payback of three years or less.  Harold believed the labor savings and quality improvement would easily justify and give a satisfactory return on the investment.  However, given the tight margins on all the product lines, a capital investment could impact cash flow, which may hurt the company’s credit rating and decrease its working capital.

Since the company was privately held, top management probably needed to borrow money to finance this capital acquisition.  Their long-standing association with the area banking community had allowed them to qualify for the lowest rate of 8.25% for this capital project. The company could also finance the equipment purchase from corporate earnings.  Last year, company owners earned a rate of return of 14% on book equity.  For planning purposes, Harold assumed that 80% of the opti-scanner would be funded by debt with the remaining funds coming from retained earnings.  Their current corporate tax rate is 40%.

CORPORATE CULTURE AND PHILOSOPHY

Jim Herr had always been a deeply religious man and believed that the corporate culture and philosophy should be grounded in Christian values and ethics.  Maintaining the highest levels of integrity, reputation, and excellence of Herr Foods in the eyes of customers, employees, suppliers, and other stakeholders was critical.  Therefore, every significant decision top management made must pass a test.

Required:

Discuss various types of capital budgeting methods available to Harold to help him in his decision. Suggest to Harold what method(s) he should use in making the decision.

Evaluate this capital acquisition proposal and recommend a course of action.

Project Assignment

Construct the Y_bus matrix of a given power network by computer programming, preferably MATLAB. Note that the necessary data are available in the IEEE common data format; and as the working data, you can use the IEEE 14-bus system data.

Due Date: December 26, 2019.

IEEE-Format Data for 14-Bus System

08/19/93 UW ARCHIVE           100.0  1962 W IEEE 14 Bus Test Case
BUS DATA FOLLOWS                            14 ITEMS
   1 Bus 1     HV  1  1  3 1.060    0.0      0.0      0.0    232.4   -16.9     0.0  1.060     0.0     0.0   0.0    0.0        0
   2 Bus 2     HV  1  1  2 1.045  -4.98     21.7     12.7     40.0    42.4     0.0  1.045    50.0   -40.0   0.0    0.0        0
   3 Bus 3     HV  1  1  2 1.010 -12.72     94.2     19.0      0.0    23.4     0.0  1.010    40.0     0.0   0.0    0.0        0
   4 Bus 4     HV  1  1  0 1.019 -10.33     47.8     -3.9      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
   5 Bus 5     HV  1  1  0 1.020  -8.78      7.6      1.6      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
   6 Bus 6     LV  1  1  2 1.070 -14.22     11.2      7.5      0.0    12.2     0.0  1.070    24.0    -6.0   0.0    0.0        0
   7 Bus 7     ZV  1  1  0 1.062 -13.37      0.0      0.0      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
   8 Bus 8     TV  1  1  2 1.090 -13.36      0.0      0.0      0.0    17.4     0.0  1.090    24.0    -6.0   0.0    0.0        0
   9 Bus 9     LV  1  1  0 1.056 -14.94     29.5     16.6      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.19       0
  10 Bus 10    LV  1  1  0 1.051 -15.10      9.0      5.8      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
  11 Bus 11    LV  1  1  0 1.057 -14.79      3.5      1.8      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
  12 Bus 12    LV  1  1  0 1.055 -15.07      6.1      1.6      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
  13 Bus 13    LV  1  1  0 1.050 -15.16     13.5      5.8      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
  14 Bus 14    LV  1  1  0 1.036 -16.04     14.9      5.0      0.0     0.0     0.0  0.0       0.0     0.0   0.0    0.0        0
-999 
BRANCH DATA FOLLOWS                         20 ITEMS
   1    2  1  1 1 0  0.01938   0.05917     0.0528     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   1    5  1  1 1 0  0.05403   0.22304     0.0492     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   2    3  1  1 1 0  0.04699   0.19797     0.0438     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   2    4  1  1 1 0  0.05811   0.17632     0.0340     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   2    5  1  1 1 0  0.05695   0.17388     0.0346     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   3    4  1  1 1 0  0.06701   0.17103     0.0128     0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   4    5  1  1 1 0  0.01335   0.04211     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   4    7  1  1 1 0  0.0       0.20912     0.0        0     0     0    0 0  0.978     0.0 0.0    0.0     0.0    0.0   0.0
   4    9  1  1 1 0  0.0       0.55618     0.0        0     0     0    0 0  0.969     0.0 0.0    0.0     0.0    0.0   0.0
   5    6  1  1 1 0  0.0       0.25202     0.0        0     0     0    0 0  0.932     0.0 0.0    0.0     0.0    0.0   0.0
   6   11  1  1 1 0  0.09498   0.19890     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   6   12  1  1 1 0  0.12291   0.25581     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   6   13  1  1 1 0  0.06615   0.13027     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   7    8  1  1 1 0  0.0       0.17615     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   7    9  1  1 1 0  0.0       0.11001     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   9   10  1  1 1 0  0.03181   0.08450     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
   9   14  1  1 1 0  0.12711   0.27038     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
  10   11  1  1 1 0  0.08205   0.19207     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
  12   13  1  1 1 0  0.22092   0.19988     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
  13   14  1  1 1 0  0.17093   0.34802     0.0        0     0     0    0 0  0.0       0.0 0.0    0.0     0.0    0.0   0.0
-999
LOSS ZONES FOLLOWS                     1 ITEMS
  1 IEEE 14 BUS
-99
INTERCHANGE DATA FOLLOWS                 1 ITEMS
 1    2 Bus 2     HV    0.0  999.99  IEEE14  IEEE 14 Bus Test Case
-9
TIE LINES FOLLOWS                     0 ITEMS
-999
END OF DATA

IEEE Common Data Format

Partial Description of the IEEE Common Data Format for the    
Exchange of Solved Load Flow Data

The complete description can be found in the paper "Common Data
Format for the Exchange of Solved Load Flow Data", Working Group on a
Common Format for the Exchange of Solved Load Flow Data, _IEEE
Transactions on Power Apparatus and Systems_, Vol. PAS-92, No. 6,
November/December 1973, pp. 1916-1925.

The data file has lines of up to 128 characters. The lines are grouped
into sections with section headers. Data items are entered in specific
columns. No blank items are allowed, enter zeros instead. Floating point
items should have explicit decimal point. No implicit decimal points
are used.

Data type codes: A - Alphanumeric (no special characters)
                 I - Integer
                 F - Floating point
                 * - Mandatory item

Title Data
==========

First card in file.

Columns  2- 9   Date, in format DD/MM/YY with leading zeros. If no date
                provided, use 0b/0b/0b where b is blank.

Columns 11-30   Originator's name (A)

Columns 32-37   MVA Base (F*)

Columns 39-42   Year (I)

Column  44      Season (S - Summer, W - Winter)

Column  46-73   Case identification (A)

Bus Data *
==========

Section start card *:
---------------------

Columns  1-16   BUS DATA FOLLOWS (not clear that any more than BUS in
                1-3 is significant) *

Columns  ?- ?   NNNNN ITEMS (column not clear, I would not count on this)

Bus data cards *:
-----------------

Columns  1- 4   Bus number (I) *
Columns  7-17   Name (A) (left justify) *
Columns 19-20   Load flow area number (I) Don't use zero! *
Columns 21-23   Loss zone number (I)
Columns 25-26   Type (I) *
                 0 - Unregulated (load, PQ)
                 1 - Hold MVAR generation within voltage limits, (PQ)
                 2 - Hold voltage within VAR limits (gen, PV)
                 3 - Hold voltage and angle (swing, V-Theta) (must always
                      have one)
Columns 28-33   Final voltage, p.u. (F) *
Columns 34-40   Final angle, degrees (F) *
Columns 41-49   Load MW (F) *
Columns 50-59   Load MVAR (F) *
Columns 60-67   Generation MW (F) *
Columns 68-75   Generation MVAR (F) *
Columns 77-83   Base KV (F)
Columns 85-90   Desired volts (pu) (F) (This is desired remote voltage if
                this bus is controlling another bus.
Columns 91-98   Maximum MVAR or voltage limit (F)
Columns 99-106  Minimum MVAR or voltage limit (F)
Columns 107-114 Shunt conductance G (per unit) (F) *
Columns 115-122 Shunt susceptance B (per unit) (F) *
Columns 124-127 Remote controlled bus number

Section end card:
-----------------

Columns  1- 4   -999

Branch Data *
=============

Section start card *:
---------------------

Columns  1-16   BRANCH DATA FOLLOWS (not clear that any more than BRANCH
                is significant) *

Columns 40?- ?  NNNNN ITEMS (column not clear, I would not count on this)

Branch data cards *:
--------------------

Columns  1- 4   Tap bus number (I) *
                 For transformers or phase shifters, the side of the model
                 the non-unity tap is on
Columns  6- 9   Z bus number (I) *
                 For transformers and phase shifters, the side of the model
                 the device impedance is on.
Columns 11-12   Load flow area (I)
Columns 13-14   Loss zone (I)
Column  17      Circuit (I) * (Use 1 for single lines)
Column  19      Type (I) *
                 0 - Transmission line
                 1 - Fixed tap
                 2 - Variable tap for voltage control (TCUL, LTC)
                 3 - Variable tap (turns ratio) for MVAR control
                 4 - Variable phase angle for MW control (phase shifter)
Columns 20-29   Branch resistance R, per unit (F) *
Columns 30-40   Branch reactance X, per unit (F) * No zero impedance lines
Columns 41-50   Line charging B, per unit (F) * (total line charging, +B)
Columns 51-55   Line MVA rating No 1 (I) Left justify!
Columns 57-61   Line MVA rating No 2 (I) Left justify!
Columns 63-67   Line MVA rating No 3 (I) Left justify!
Columns 69-72   Control bus number
Column  74      Side (I)
                 0 - Controlled bus is one of the terminals
                 1 - Controlled bus is near the tap side
                 2 - Controlled bus is near the impedance side (Z bus)
Columns 77-82   Transformer final turns ratio (F)
Columns 84-90   Transformer (phase shifter) final angle (F)
Columns 91-97   Minimum tap or phase shift (F)
Columns 98-104  Maximum tap or phase shift (F)
Columns 106-111 Step size (F)
Columns 113-119 Minimum voltage, MVAR or MW limit (F)
Columns 120-126 Maximum voltage, MVAR or MW limit (F)

Section end card:
-----------------

Columns  1- 4   -999

Loss Zone Data
==============

Section start card
------------------

Columns  1-16   LOSS ZONES FOLLOWS (not clear that any more than LOSS
                is significant)

Columns 40?- ?  NNNNN ITEMS (column not clear, I would not count on this)

Loss Zone Cards:
----------------

Columns  1- 3   Loss zone number  (I)
Columns  5-16   Loss zone name (A)

Section end card:
-----------------

Columns  1- 3   -99

Interchange Data *
==================

Section start card
------------------

Columns  1-16   INTERCHANGE DATA FOLLOWS (not clear that any more than 
                first word is significant).
Columns 40?- ?  NNNNN ITEMS (column not clear, I would not count on this)

Interchange Data Cards *:
-------------------------

Columns  1- 2   Area number (I) no zeros! *
Columns  4- 7   Interchange slack bus number (I) *
Columns  9-20   Alternate swing bus name (A)
Columns 21-28   Area interchange export, MW (F) (+ = out) *
Columns 30-35   Area interchange tolerance, MW (F) *
Columns 38-43   Area code (abbreviated name) (A) *
Columns 46-75   Area name (A)

Section end card:
-----------------

Columns  1- 2   -9

Tie Line Data
=============

Section start card
------------------

Columns  1-16   TIE LINES FOLLOW (not clear that any more than TIE
                is significant)

Columns 40?- ?  NNNNN ITEMS (column not clear, I would not count on this)

Tie Line Cards:
---------------

Columns  1- 4   Metered bus number (I)
Columns  7-8    Metered area number (I)
Columns  11-14  Non-metered bus number (I)
Columns  17-18  Non-metered area number (I)
Column   21     Circuit number

Section end card:
-----------------

Columns  1- 3   -999