Of all of the courses I took in the CIS program at UofL the Database Design class was by far the most interesting for me. I thoroughly enjoyed the material that dealt with relation database management and SQL primarily through the use of Microsoft SQL Server Management Studio.
The following is an example of the SQL code I wrote in CIS 310 Database Design as well as a collection of assignments with accompanying code and design documents.
TRIGGER
USE [CIS31028]
GO
alter TRIGGER [dbo].[A9]
ON [dbo].[DETAILRENTAL]
AFTER UPDATE
AS
BEGIN
DECLARE @RENT_NUM INT
DECLARE @VID_NUM INT
DECLARE @PRIOR_RETURN_DATE DATETIME
DECLARE @AFTER_RETURN_DATE DATETIME
DECLARE @PRIOR_DUE_DATE DATETIME
DECLARE @AFTER_DUE_DATE DATETIME
DECLARE @PRIOR_DAILY_LATEFEE DECIMAL
DECLARE @AFTER_DAILY_LATEFEE DECIMAL
DECLARE @TOTAL_PRIOR_FEE DECIMAL
DECLARE @TOTAL_AFTER_FEE DECIMAL
DECLARE @CHANGE_VALUE DECIMAL
IF (EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted))
BEGIN
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT I.Rent_Num, I.Vid_Num, I.Detail_ReturnDate, I.Detail_DueDate, I.Detail_DailyLateFee,
D.Detail_ReturnDate, D.Detail_DueDate, D.Detail_DailyLateFee
FROM inserted I INNER JOIN DELETED D ON I.Rent_Num = D.Rent_Num AND I.Vid_Num = D.Vid_Num
OPEN UPDATE_CURSOR
FETCH NEXT FROM UPDATE_CURSOR
INTO @RENT_NUM, @VID_NUM, @AFTER_RETURN_DATE, @AFTER_DUE_DATE, @AFTER_DAILY_LATEFEE,
@PRIOR_RETURN_DATE, @PRIOR_DUE_DATE, @PRIOR_DAILY_LATEFEE
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF (@PRIOR_RETURN_DATE = @AFTER_RETURN_DATE)
SELECT @CHANGE_VALUE = 0
ELSE
IF(@PRIOR_RETURN_DATE = NULL)
SELECT @PRIOR_RETURN_DATE = @PRIOR_DUE_DATE
IF(@PRIOR_RETURN_DATE > @PRIOR_DUE_DATE)
SELECT @TOTAL_PRIOR_FEE = DATEDIFF(DAY, @PRIOR_DUE_DATE, @PRIOR_RETURN_DATE) * @PRIOR_DAILY_LATEFEE;
ELSE
SELECT @TOTAL_PRIOR_FEE = 0;
IF (@AFTER_DUE_DATE > @AFTER_RETURN_DATE)
SELECT @TOTAL_AFTER_FEE = DATEDIFF(DAY, @AFTER_DUE_DATE, @AFTER_RETURN_DATE) * @AFTER_DAILY_LATEFEE;
ELSE
SELECT @TOTAL_AFTER_FEE = 0;
IF (@AFTER_RETURN_DATE > @PRIOR_RETURN_DATE)
SELECT @CHANGE_VALUE = @TOTAL_AFTER_FEE - @TOTAL_PRIOR_FEE
ELSE
IF (@AFTER_RETURN_DATE < @PRIOR_RETURN_DATE)
SELECT @CHANGE_VALUE = @TOTAL_PRIOR_FEE - @TOTAL_AFTER_FEE
ELSE
SELECT @CHANGE_VALUE = 0
IF(@CHANGE_VALUE <> 0)
BEGIN
DECLARE @MEMBER_NUM INT;
SELECT @MEMBER_NUM = M.MEM_NUM
FROM RENTAL R INNER JOIN MEMBERSHIP M ON R.Mem_Num = M.Mem_Num AND R.Rent_Num = @RENT_NUM
--UPDATE DETAILRENTAL
--SET Detail_Fee = Detail_Fee + @CHANGE_VALUE
--WHERE Rent_Num = @RENT_NUM AND Vid_Num = @VID_NUM
UPDATE MEMBERSHIP
SET Mem_Balance = Mem_Balance - @CHANGE_VALUE
WHERE Mem_Num = @MEMBER_NUM
END
FETCH NEXT FROM UPDATE_CURSOR
INTO @RENT_NUM, @VID_NUM, @AFTER_RETURN_DATE, @AFTER_DUE_DATE, @AFTER_DAILY_LATEFEE,
@PRIOR_RETURN_DATE, @PRIOR_DUE_DATE, @PRIOR_DAILY_LATEFEE
END
CLOSE UPDATE_CURSOR
DEALLOCATE UPDATE_CURSOR
END
END
TEST QUERY
–A8 Test Queries
–Show Tables
SELECT *
FROM DETAILRENTAL
SELECT *
FROM MEMBERSHIP
–Month Later
UPDATE DETAILRENTAL
SET Detail_ReturnDate = ‘4-12-2013’
WHERE Rent_Num = 1006 OR Rent_Num = 1007
–Due Earlier
UPDATE DETAILRENTAL
SET Detail_DueDate = ‘3-2-2013’
WHERE Rent_Num = 1006 OR Rent_Num = 1007
An example of crows feet diagramming and business rules analysis of the requirements of a relational database:
An example of using normalization to reduce data redundancy in a relational database:
An example of using basic SQL to extract information from a relational database:
An example of using GROUP BY, HAVING, AND ORDER BY along with basic SQL to extract information from a relational database:
An example of using a TRIGGER to automatically extract information from a relational database when changes are made to the data within:
An example of creating and altering a table and inserting data into that tables of a relational database:
An example of creating and altering a stored procedure for a relational database:
Bryce C Green 