Lesson 13 Research Assignment Instructions How To Code Store

Lesson13researchassignmentinstructionshow To Code Stored Proced

Lesson 13 †Research Assignment Instructions: How to code stored procedures, functions, and triggers Exercises 1. Write a script that creates and calls a stored procedure named spInsertCategory. First, code a statement that creates a procedure that adds a new row to the Categories table. To do that, this procedure should have one parameter for the category name. Code at least two EXEC statements that test this procedure. (Note that this table doesn’t allow duplicate category names.) 2. Write a script that creates and calls a function named fnDiscountPrice that calculates the discount price of an item in the OrderItems table (discount amount subtracted from item price). To do that, this function should accept one parameter for the item ID, and it should return the value of the discount price for that item. 3. Write a script that creates and calls a function named fnItemTotal that calculates the total amount of an item in the OrderItems table (discount price multiplied by quantity). To do that, this function should accept one parameter for the item ID, it should use the DiscountPrice function that you created in exercise 2, and it should return the value of the total for that item. 4. Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent. This stored procedure should set the Description column to an empty string, and it should set the DateAdded column to the current date. If the value for the ListPrice column is a negative number, the stored procedure should raise an error that indicates that this column doesn’t accept negative numbers. Similarly, the procedure should raise an error if the value for the DiscountPercent column is a negative number. Code at least two EXEC statements that test this procedure. 5. Write a script that creates and calls a stored procedure named spUpdateProductDiscount that updates the DiscountPercent column in the Products table. This procedure should have one parameter for the product ID and another for the discount percent. If the value for the DiscountPercent column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number. Code at least two EXEC statements that test this procedure. 6. Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0. If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20. Test this trigger with an appropriate UPDATE statement. 7. Create a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table if the value for that column is null. Test this trigger with an appropriate INSERT statement. 8. Create a table named ProductsAudit. This table should have all columns of the Products table, except the Description column. Also, it should have an AuditID column for its primary key, and the DateAdded column should be changed to DateUpdated. Create a trigger named Products_UPDATE. This trigger should insert the old data about the product into the ProductsAudit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement. Instructions 1. You will be submitting a Word document: a. Open a new Word document b. As you proceed with assignment activities, capture print screen shots of the script as well as the outcome of the executed step— result. c. Once you complete all of the assignment steps, go over each assignment step and print screen shots on your Word document and explain in detail for each step as well as what the print screen shot represents. d. The document must conform to APA writing standards; in addition to detail assignment explanation and screen shots, include a cover page, an introduction section--what you learned from the activity, what areas you found challenging, etc. and sources of information. Note: for APA writing standards use PBSC Library online-- CREATE DATABASE MyGuitarShop; GO USE MyGuitarShop; -- create the tables for the database CREATE TABLE Categories ( CategoryID INT PRIMARY KEY IDENTITY, CategoryName VARCHAR(255) NOT NULL UNIQUE ); CREATE TABLE Products ( ProductID INT PRIMARY KEY IDENTITY, CategoryID INT REFERENCES Categories (CategoryID), ProductCode VARCHAR(10) NOT NULL UNIQUE, ProductName VARCHAR(255) NOT NULL, Description TEXT NOT NULL, ListPrice MONEY NOT NULL, DiscountPercent MONEY NOT NULL DEFAULT 0.00, DateAdded DATETIME DEFAULT NULL ); CREATE TABLE Customers ( CustomerID INT PRIMARY KEY IDENTITY, EmailAddress VARCHAR(255) NOT NULL UNIQUE, Password VARCHAR(60) NOT NULL, FirstName VARCHAR(60) NOT NULL, LastName VARCHAR(60) NOT NULL, ShippingAddressID INT DEFAULT NULL, BillingAddressID INT DEFAULT NULL ); CREATE TABLE Addresses ( AddressID INT PRIMARY KEY IDENTITY, CustomerID INT REFERENCES Customers (CustomerID), Line1 VARCHAR(60) NOT NULL, Line2 VARCHAR(60) DEFAULT NULL, City VARCHAR(40) NOT NULL, State VARCHAR(2) NOT NULL, ZipCode VARCHAR(10) NOT NULL, Phone VARCHAR(12) NOT NULL, Disabled INT NOT NULL DEFAULT 0 ); CREATE TABLE Orders ( OrderID INT PRIMARY KEY IDENTITY, CustomerID INT REFERENCES Customers (CustomerID), OrderDate DATETIME NOT NULL, ShipAmount MONEY NOT NULL, TaxAmount MONEY NOT NULL, ShipDate DATETIME DEFAULT NULL, ShipAddressID INT NOT NULL, CardType VARCHAR(50) NOT NULL, CardNumber CHAR(16) NOT NULL, CardExpires CHAR(7) NOT NULL, BillingAddressID INT NOT NULL ); CREATE TABLE OrderItems ( ItemID INT PRIMARY KEY IDENTITY, OrderID INT REFERENCES Orders (OrderID), ProductID INT REFERENCES Products (ProductID), ItemPrice MONEY NOT NULL, DiscountAmount MONEY NOT NULL, Quantity INT NOT NULL ); CREATE TABLE Administrators ( AdminID INT PRIMARY KEY IDENTITY, EmailAddress VARCHAR(255) NOT NULL, Password VARCHAR(255) NOT NULL, FirstName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL ); -- Insert data into the tables SET IDENTITY_INSERT Categories ON; INSERT INTO Categories (CategoryID, CategoryName) VALUES (1, 'Guitars'), (2, 'Basses'), (3, 'Drums'), (4, 'Keyboards'); SET IDENTITY_INSERT Categories OFF; SET IDENTITY_INSERT Products ON; INSERT INTO Products (ProductID, CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent, DateAdded) VALUES (1, 1, 'strat', 'Fender Stratocaster', 'The Fender Stratocaster is the electric guitar design that changed the world. New features include a tinted neck, parchment pickguard and control knobs, and a ''70s-style logo. Includes select alder body, 21-fret maple neck with your choice of a rosewood or maple fretboard, 3 single-coil pickups, vintage-style tremolo, and die-cast tuning keys. This guitar features a thicker bridge block for increased sustain and a more stable point of contact with the strings. At this low price, why play anything but the real thing?\r\n\r\nFeatures:\r\n\r\n New features:\r\n Thicker bridge block\r\n 3-ply parchment pick guard\r\n Tinted neck', '699.00', '30.00', ':32:40'), (2, 1, 'les_paul', 'Gibson Les Paul', 'This Les Paul guitar offers a carved top and humbucking pickups. It has a simple yet elegant design. Cutting-yet-rich tone?the hallmark of the Les Paul?pours out of the 490R and 498T Alnico II magnet humbucker pickups, which are mounted on a carved maple top with a mahogany back. The faded finish models are equipped with BurstBucker Pro pickups and a mahogany top. This guitar includes a Gibson hardshell case (Faded and satin finish models come with a gig bag) and a limited lifetime warranty.\r\n\r\nFeatures:\r\n\r\n Carved maple top and mahogany back (Mahogany top on faded finish models)\r\n Mahogany neck, ''59 Rounded Les Paul\r\n Rosewood fingerboard (Ebony on Alpine white)\r\n Tune-O-Matic bridge with stopbar\r\n Chrome or gold hardware\r\n 490R and 498T Alnico 2 magnet humbucker pickups (BurstBucker Pro on faded finish models)\r\n 2 volume and 2 tone knobs, 3-way switch', '1199.00', '30.00', ':33:13'), (3, 1, 'sg', 'Gibson SG', 'This Gibson SG electric guitar takes the best of the ''62 original and adds the longer and sturdier neck joint of the late ''60s models. All the classic features you''d expect from a historic guitar. Hot humbuckers go from rich, sweet lightning to warm, tingling waves of sustain. A silky-fast rosewood fretboard plays like a dream. The original-style beveled mahogany body looks like a million bucks. Plus, Tune-O-Matic bridge and chrome hardware. Limited lifetime warranty. Includes hardshell case.\r\n\r\nFeatures:\r\n\r\n Double-cutaway beveled mahogany body\r\n Set mahogany neck with rounded ''50s profile\r\n Bound rosewood fingerboard with trapezoid inlays\r\n Tune-O-Matic bridge with stopbar tailpiece\r\n Chrome hardware\r\n 490R humbucker in the neck position\r\n 498T humbucker in the bridge position\r\n 2 volume knobs, 2 tone knobs, 3-way switch\r\n 24-3/4" scale', '2517.00', '52.00', ':04:31'), (4, 1, 'fg700s', 'Yamaha FG700S', 'The Yamaha FG700S solid top acoustic guitar has the ultimate combo for projection and pure tone. The expertly braced spruce top speaks clearly atop the rosewood body. It has a rosewood fingerboard, rosewood bridge, die-cast tuners, body and neck binding, and a tortoise pickguard.\r\n\r\nFeatures:\r\n\r\n Solid Sitka spruce top\r\n Rosewood back and sides\r\n Rosewood fingerboard\r\n Rosewood bridge\r\n White/black body and neck binding\r\n Die-cast tuners\r\n Tortoise pickguard\r\n Limited lifetime warranty', '489.99', '38.00', ':12:59'), (5, 1, 'washburn', 'Washburn D10S', 'The Washburn D10S acoustic guitar is superbly crafted with a solid spruce top and mahogany back and sides for exceptional tone. A mahogany neck and rosewood fingerboard make fretwork a breeze, while chrome Grover-style machines keep you perfectly tuned. The Washburn D10S comes with a limited lifetime warranty.\r\n\r\nFeatures:\r\n\r\n Spruce top\r\n Mahogany back, sides\r\n Mahogany neck Rosewood fingerboard\r\n Chrome Grover-style machines', '299.00', '0.00', ':58:35'), (6, 1, 'rodriguez', 'Rodriguez Caballero 11', 'Featuring a carefully chosen, solid Canadian cedar top and laminated bubinga back and sides, the Caballero 11 classical guitar is a beauty to behold and play. The headstock and fretboard are of Indian rosewood. Nickel-plated tuners and Silver-plated frets are installed to last a lifetime. The body binding and wood rosette are exquisite.\r\n\r\nThe Rodriguez Guitar is hand crafted and glued to create precise balances. From the invisible careful sanding, even inside the body, that ensures the finished instrument''s purity of tone, to the beautifully unique rosette inlays around the soundhole and on the back of the neck, each guitar is a credit to its luthier and worthy of being handed down from one generation to another.\r\n\r\nThe tone, resonance and beauty of fine guitars are all dependent upon the wood from which they are made. The wood used in the construction of Rodriguez guitars is carefully chosen and aged to guarantee the highest quality. No wood is purchased before the tree has been cut down, and at least 2 years must elapse before the tree is turned into lumber. The wood has to be well cut from the log. The grain must be close and absolutely vertical. The shop is totally free from humidity.', '415.00', '39.00', ':12:41'), (7, 2, 'precision', 'Fender Precision', 'The Fender Precision bass guitar delivers the sound, look, and feel today''s bass players demand. This bass features that classic P-Bass old-school design. Each Precision bass boasts contemporary features and refinements that make it an excellent value. Featuring an alder body and a split single-coil pickup, this classic electric bass guitar lives up to its Fender legacy.\r\n\r\nFeatures:\r\n\r\n Body: Alder\r\n Neck: Maple, modern C shape, tinted satin urethane finish\r\n Fingerboard: Rosewood or maple (depending on color)\r\n 9-1/2" Radius (241 mm)\r\n Frets: 20 Medium-jumbo frets\r\n Pickups: 1 Standard Precision Bass split single-coil pickup (Mid)\r\n Controls: Volume, Tone\r\n Bridge: Standard vintage style with single groove saddles\r\n Machine heads: Standard\r\n Hardware: Chrome\r\n Pickguard: 3-Ply Parchment\r\n Scale Length: 34" (864 mm)\r\n Width at Nut: 1-5/8" (41.3 mm)\r\n Unique features: Knurled chrome P Bass knobs, Fender transition logo', '799.99', '30.00', ':29:35'), (8, 2, 'hofner', 'Hofner Icon', 'With authentic details inspired by the original, the Hofner Icon makes the legendary violin bass available to the rest of us. Don''t get the idea that this a just a "nowhere man" look-alike. This quality instrument features a real spruce top and beautiful flamed maple back and sides. The semi-hollow body and set neck will give you the warm, round tone you expect from the violin bass.\r\n\r\nFeatures:\r\n\r\n Authentic details inspired by the original\r\n Spruce top\r\n Flamed maple back and sides\r\n Set neck\r\n Rosewood fretboard\r\n 30" scale\r\n 22 frets\r\n Dot inlay', '499.99', '25.00', ':18:33'), (9, 3, 'ludwig', 'Ludwig 5-piece Drum Set with Cymbals', 'This product includes a Ludwig 5-piece drum set and a Zildjian starter cymbal pack. With the Ludwig drum set, you get famous Ludwig quality. This set features a bass drum, two toms, a floor tom, and a snare?each with a wrapped finish. Drum hardware includes LA214FP bass pedal, snare stand, cymbal stand, hi-hat stand, and a throne. With the Zildjian cymbal pack, you get a 14" crash, 18" crash/ride, and a pair of 13" hi-hats. Sound grooves and round hammer strikes in a simple circular pattern on the top surface of these cymbals magnify the basic sound of the distinctive alloy. Features: Famous Ludwig quality Wrapped finishes 22" x 16" kick drum 12" x 10" and 13" x 11" toms 16" x 16" floor tom 14" x 6-1/2" snare drum kick pedal Snare stand Straight cymbal stand hi-hat stand FREE throne', '699.99', '30.00', ':46:40'), (10, 3, 'tama', 'Tama 5-Piece Drum Set with Cymbals', 'The Tama 5-piece Drum Set is the most affordable Tama drum kit ever to incorporate so many high-end features. With over 40 years of experience, Tama knows what drummers really want. Which is why, no matter how long you''ve been playing the drums, no matter what budget you have to work with, Tama has the set you need, want, and can afford. Every aspect of the modern drum kit was exhaustively examined and reexamined and then improved before it was accepted as part of the Tama design. Which is why, if you start playing Tama now as a beginner, you''ll still enjoy playing it when you''ve achieved pro-status. That''s how good these groundbreaking new drums are. Only Tama comes with a complete set of genuine Meinl HCS cymbals. These high-quality brass cymbals are made in Germany and are sonically matched so they sound great together. They are even lathed for a more refined tonal character. The set includes 14" hi-hats, 16" crash cymbal, and a 20" ride cymbal. Features: 100% poplar 6-ply/7.5mm shells Precise bearing edges 100% glued finishes Original small lugs Drum heads Accu-tune bass drum hoops Spur brackets Tom holder Tom brackets', '799.99', '15.00', ':14:15'); SET IDENTITY_INSERT Products OFF; SET IDENTITY_INSERT Customers ON; INSERT INTO Customers (CustomerID, EmailAddress, Password, FirstName, LastName, ShippingAddressID, BillingAddressID) VALUES (1, ' [email protected] ', '650215acec746f0e32bdfff387439eefc', 'Allan', 'Sherwood', 1, 2), (2, ' [email protected] ', '3f563468d42a448cb1ef6e7bbe529cc7', 'Barry', 'Zimmer', 3, 3), (3, ' [email protected] ', 'ed19f5ca2f1e9e6f08a35d', 'Christine', 'Brown', 4, 4), (4, ' [email protected] ', 'b444ac06613fc8d63795be9ad0beafac', 'David', 'Goldstein', 5, 6), (5, ' [email protected] ', '109f4b