jwAA4i1L

1alter table tableName
2add constraint constraintName
3check (attName in ('C1' ,'C2' ,'C3'));
4check (attName <= 5000);
5check ( NOT EXISTS (SELECT *
6FROM tableName t)
7	WHERE (t.att1 < 20) AND (t.att2 >= 50) );
8Example: Printer with smaller model number must always have a smaller price assuming both made by same manufacturer
9alter table Printer
10add constraint model_price_relationship
11check ( NOT EXISTS (SELECT *
12FROM Printer p1, Printer p2, Product po1, Product po2
13	WHERE (p1.model = po1.model) AND (p2.model=po2.model)
14	AND (po1.manufacturer= po2.manufacturer) AND (p1.model < p1.model)
15	AND (p1.price >= p2.price) ));
16
17CREATE OR REPLACE TRIGGER triggerName
18BEFORE INSERT OR UPDATE ON tableName   (OR BEFORE UPDATE OF attName ON tableName)
19REFERENCING OLD AS oldTuple NEW as newTuple 
20FOR EACH ROW
21DECLARE numCount INT; errorMsg VARCHAR(50);
22BEGIN
23	IF ((:new.att1 < 5) OR (:new.att2 IN ('C1' ,'C2' ,'C3')) ) THEN
24		RAISE_APPLICATION ERROR(-20004, 'this is the error message');
25	END IF;
26	IF (:new.att3 >5) THEN
27		SELECT COUNT(att?) INTO numCount
28		FROM tableName t
29		WHERE (t.att?  > :new.att4) AND NOT(t.att? = 'PC');
30		IF(numCount>0) THEN
31			RAISE_APPLICATION ERROR(-20004, 'this is the error message');
32		END IF;	
33	END IF;
34	IF(numCount!=0) THEN
35	errorMsg := 'soemthing' || 'something more'
36	INSERT INTO table2Name	SELECT FROM table1Name WHERE (TRUE) AND (TRUE);	INSERT INTO table2Name	VALUES (att1, att2, att3);
37INSERT INTO table2Name(model) VALUES(:new.model);
38	END IF;	
39END;
40
41CREATE TRIGGER salaryRestrictions
42AFTER INSERT OR UPDATE ON Professor
43FOR EACH ROW
44BEGIN
45IF (INSERTING AND :new.salary < 60000) THEN RAISE_APPLICATION_ERROR (-20004, 'below min salary'); END IF;
46IF (UPDATING AND :new.salary < :old.salary) THEN RAISE_APPLICATION_ERROR (-20004, ‘Salary Decreasing !!'); END IF;
47END;
48
49Updating tuples
50	UPDATE Student SET professor=‘ER’
51	WHERE sNumber=‘6’
52
53
54CREATE VIEW viewName AS (modelNum, costNameTag) SELECT model, price FROM Printer;
55Single-relation view, deletion will correctly proceed, insertion can unambigiously proceed as 'model' is the key of this relation, this insert will not cause any constrain violation. That is, all other fileds of the base relation not specified by this insert can be set to NULL.
56If primary key is NULL the insertion is rejected by the DBMS.
57Multi-relation view, the join view exposes the primary keys of both relations and because a key-foreign-key connection is used to conduct the join between those 2 tables. We are guaranteed that those keys 'model' serve as key in the view table. Hence, a delete would be 1-1 mapped to base tuples and thus can be unambigiously executed. insertion is allow because view exposes the primary keys of both relations, use a key-foreign-key connection to join  the two base tables and no other NOT-NULL constraints exist on attributes not visiblee via the view.
58
59HW4 Relational algrebra
60Suppose that R and S are bags and that tuple t appears n times in R and m time in S
61In the bag union R U S, , tuple t appears n +  m times
62Bag intersection R and S tuple t appears min(n, m) times
63difference R - S, max(0, n-m) times
64SELECT c and d of R = SELECTc of R intersect SELECTd of R true
65SELECT c or d of R = SELECTc of R union SELECTd of R false because of dumplicates
66
67Find out left outer joint
68HW4.2.3 and 4.2.4
69Copy whole hw will be the best idea.
70
71Powerpoint Notes
72Relation algebra
73Suppose tuple t appears in R1 m times, and in R2 n times. 
74Then in intersection, t appears min (m, n) times.
75Suppose tuple t appears in R1 m times & in R2 n times.
76Then in R1 – R2, t appears max (0, m - n) times
77Idempotent property :
78Operation applied twice gives same result as when applied once
79
80Example :
81Filter-BLUE ( Filter-BLUE (  images ))
82idempotent apply twice same effect
83For sets idepotent difference intersecgtion union
84for bag idepotent  intersection 
85
86Let A = AR n AS, and  
87R NATURAL JOIN S can be defined as 
88AR – A,  A,  AS - A  (sR.A1 = S.A1 AND R.A2 =S.A2 AND … R.An=S.An (R X S))
89
90SELECT sNumber || sName AS info
91
92For set semantics, use UNION, INTERSECT, EXCEPT.
93For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL
94
95
96SELECT * 
97FROM Student JOIN Professor
98ON professor=pNumber;
99
100
101SELECT *
102FROM Student , Professor
103WHERE Student.pnumber = Professor.pnumber ;
104SELECT *
105FROM Student NATURAL JOIN Professor
106
107
108SELECT * 
109FROM Student
110WHERE sNumber >= 1
111ORDER BY sNumber, sName
112
113SELECT * FROM Student
114WHERE professor = 
115	(SELECT pName
116	FROM Professor
117	WHERE pNumber=1)
118Note: The inner subquery returns a relation, but SQL runtime ensures that subquery returns a relation with one column and with one row, otherwise it is a run-time error.
119
120
121We can use IN, EXISTS,  NOT IN, and NOT EXISTS
122ALL, ANY can be used with comparisons
123
124
125SELECT * FROM Student
126WHERE (sNumber, professor) IN
127	(SELECT pNumber, pName 
128	 FROM Professor)
129
130	 
131SELECT * FROM Student
132WHERE sNumber > ALL
133	(SELECT pNumber
134       FROM Professor)
135
136Duplicate Elimination			.
137SELECT DISTINCT address
138FROM Student
139WHERE sNumber >= 1;
140
141SELECT address, COUNT (sNumber) 
142FROM Student
143WHERE sNumber > 1
144GROUP BY address
145HAVING COUNT (sNumber) > 1;
146
147SELECT [DISTINCT] a1, a2, …, an
148FROM R1, R2, …, Rm
149[WHERE C1]
150[GROUP BY g1, g2, …, gl  [HAVING C2]]
151[ORDER BY o1, o2, …, oj]
152
153Constraints can be added to an existing table.
154	ALTER TABLE ADD CONSTRAINT [<cName>] <cBody>
155
156Any constraint that has a name can be dropped
157	ALTER TABLE DROP CONSTRAINT <cName>
158Insert will succeed only if 
159The insert translates to insert into only one table.
160The key for the table to be inserted will also be a key for the view.