Introduction to Databases and Databases Management Systems:Part 1 (CSIQ2634)

• GetRate Function A function is required that can determine if a discount rate is available between two given dates CREATE TABLE Rate ( RateID int IDENTITY(1,1) NOT NULL, Rate_StartDate datetime NULL, Rate_EndDAte datetime NULL, RateDesc_ID int NULL, PRIMARY KEY (RateID),

FOREIGN KEY (RateDesc_ID) REFERENCES Rate_Description ,

• PackageSetup Each package uses a certain amount of stock to allow for installation and setup. This could be printers, ethernet cable, hubs and vary other components. The stock that is required for a package will be passed as a single delimited list of StockIDs as a parameter – for example: 1,4,5 Write a single stored procedure that can update or insert a Package and its associated Package_Description (note the referential integrity that must be maintained). Then you must add the stock items into Stock_Package from the delimited list [Hint: use control statements and string manipulation – or find a better solution]. For adding the stock items, you can assume that the there are no stock items for the package as yet Write a single stored procedure that can update or delete any packages. [27]

CREATE TABLE Package ( PackageID int IDENTITY(1,1) NOT NULL, Package_Price numeric(18, 2) NULL, PDescID int NULL, PRIMARY KEY (PackageID),

FOREIGN KEY (PDescID) REFERENCES Package_Description, ) CREATE TABLE Package_Description ( PDescID int NOT NULL, PDesc_ShortDesc varchar(50) NULL, PDesc_Desc text NULL, PRIMARY KEY (PDescID) )