INFO 340 Lecture Notes - Lecture 4: Surrogate Key, Database Engine, Business Rule
INFO 340 Lecture 4
Identity
o Only one per table
o “surrogate key” – primary key values automatically created by database engine
- Computed columns (e.g., GPA)
o Do if changed infrequently
- Restricted data (aka Business Rules)
o Prevent business from completing transaction not in their best interest
▪ Legal issues – Minimum age to purchase alcohol
▪ Profitability – bulk discount, restricting business area, limit coupon
frequency
o Example:
/* Business rule: no order can exceed $300 */
CREATE FUNCTION fn_NoOrder300Bucks()
RETURNS INTEGER
AS
BEGIN
DECLARE @Ret INT = 0
IF EXISTS (SELECT *
FROM [ORDER]
WHERE OrderTotal > 300)
SET @Ret = 1
RETURN @Ret
END
GO
ALTER TABLE [ORDER]
ADD CONSTRAIN CK_OrdersLess300
CHECK (dbo.fn_NoOrder300Bucks() = 0)
Join commands – default is an inner join
- Inner join – returns rows that match both tables
- Outer join – returns all rows
- Left join – Shows only rows on left side of query, as well as overlapping rows
Document Summary
Identity: only one per table, (cid:862)surrogate key(cid:863) primary key values automatically created by database engine. Computed columns (e. g. , gpa: do if changed infrequently. Restricted data (aka business rules: prevent business from completing transaction not in their best interest, legal issues minimum age to purchase alcohol, profitability bulk discount, restricting business area, limit coupon frequency, example: /* business rule: no order can exceed */ Join commands default is an inner join. Inner join returns rows that match both tables. Left join shows only rows on left side of query, as well as overlapping rows. Right join shows only rows on right side of query, as well as overlapping rows. Cross join returns all rows of both tables when there is a match in one of the rows. Having like where, but used after group by. Systematic/sequential from one normal form to another. Iterative process, refined over time: separate conceptual + logical design phases, be aware of rules of normalization.
