![Page 1: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/1.jpg)
Databases 1
Defining Tables, Constraints
![Page 2: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/2.jpg)
DBMS
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides 2
![Page 3: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/3.jpg)
Rest of SQL
� Defining a Database Schema
� Primary Keys, Foreign Keys
� Local and Global Constraints
� Defining Views
� Triggers
3 DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 4: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/4.jpg)
4
Defining a Database Schema
� A database schema comprises declarations for the relations (“tables”) of the database.
� Many other kinds of elements may also appear in the database schema, including views, constraints and triggers.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 5: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/5.jpg)
5
Declaring a Relation
� Simplest form is:
� CREATE TABLE <name> (<list of elements>);
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 6: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/6.jpg)
6
Elements of Table Declarations
� The principal element is a pair consisting of an attribute and a type.
� The most common types are:
� INT or INTEGER (synonyms).
� REAL or FLOAT (synonyms).
� CHAR(n ) = fixed-length string of ncharacters.
� VARCHAR(n ) = variable-length string of up to n characters.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 7: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/7.jpg)
7
Dates and Times
� DATE and TIME are types in SQL.
� The form of a date value is:
DATE ‘yyyy-mm-dd’
� Example: DATE ‘2002-09-30’ for Sept. 30, 2002.
� There are functions to convert DATE types (e.g. TODATE)
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 8: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/8.jpg)
8
Times as Values
� The form of a time value is:
TIME ‘hh:mm:ss’
with an optional decimal point and fractions of a second following.
� Example: TIME ’15:30:02.5’ = two and a half seconds after 3:30PM.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 9: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/9.jpg)
9
Example: Create Table
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 10: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/10.jpg)
10
Example: Create Table from existing table(s)
CREATE TABLE AVG_PRICES AS
(SELECT beer, AVG(price)
FROM Sells
GROUP BY beer);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 11: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/11.jpg)
Remove a relation from schema
� Remove a relation from the database schema by:
� DROP TABLE <name>;
� Example:
DROP TABLE Sells;
11 DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 12: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/12.jpg)
12
Declaring Keys
� An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE.
� These each say the attribute(s) so declared functionally determine all the attributes of the relation schema.
� There are a few distinctions to be mentioned later.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 13: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/13.jpg)
13
Declaring Single-Attribute Keys
� Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.
� Example:
CREATE TABLE Beers (
nameCHAR(20) UNIQUE,
manfCHAR(20)
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 14: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/14.jpg)
14
Declaring Multiattribute Keys
� A key declaration can also be another element in the list of elements of a CREATE TABLE statement.
� This form is essential if the key consists of more than one attribute.
� May be used even for one-attribute keys.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 15: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/15.jpg)
15
Example: Multiattribute Key
� The bar and beer together are the key for Sells:
CREATE TABLE Sells (
bar CHAR(20),
beer VARCHAR(20),
price REAL,
PRIMARY KEY (bar, beer)
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 16: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/16.jpg)
16
PRIMARY KEY Versus UNIQUE
� The SQL standard allows DBMS implementers to make their own distinctions between PRIMARY KEY and UNIQUE.
� Example: some DBMS might automatically create an index (data structure to speed search) in response to PRIMARY KEY, but not UNIQUE.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 17: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/17.jpg)
17
Required Distinctions
� However, standard SQL requires these distinctions:
1. There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.
2. No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 18: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/18.jpg)
18
Other Declarations for Attributes
� Two other declarations we can make for an attribute are:
1. NOT NULL means that the value for this attribute may never be NULL.
2. DEFAULT <value> says that if there is no specific value known for this attribute’s component in some tuple, use the stated <value>.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 19: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/19.jpg)
19
Example: Default Values
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT ‘123 Sesame St.’,
phone CHAR(16)
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 20: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/20.jpg)
20
Effect of Defaults -- 1
� Suppose we insert the fact that Sally is a drinker, but we know neither her address nor her phone.
� An INSERT with a partial list of attributes makes the insertion possible:
INSERT INTO Drinkers(name)
VALUES(‘Sally’);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 21: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/21.jpg)
21
Effect of Defaults -- 2
� But what tuple appears in Drinkers?
name addr phone
‘Sally’ ‘ 123 Sesame St’ NULL
� If we had declared phone NOT NULL, this insertion would have been rejected.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 22: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/22.jpg)
22
Adding Attributes
� We may change a relation schema by adding a new attribute (“column”) by:
ALTER TABLE <name> ADD
<attribute declaration>;
� Example:
ALTER TABLE Bars ADD
phone CHAR(16)DEFAULT ‘unlisted’;
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 23: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/23.jpg)
23
Deleting/Renaming Attributes
� Remove an attribute from a relation schema by:
ALTER TABLE <name> DROP <attribute>;
� Example: we don’t really need the license attribute for bars:
ALTER TABLE Bars DROP license;
� Rename an attribute in a relation:
ALTER TABLE <name> RENAME COLUMN <attribute>
TO <attribute> ;
� Example: we would rename the license attribute bar_license
ALTER TABLE Bars RENAME COLUMN license
to bar_license;
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 24: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/24.jpg)
24
Kinds of Constraints
A constraint is a relationship among data elements that the DBMS is required to enforce.
� Key constraints, Foreign-key or referential-integrity.
� Value-based constraints.
� Constrain values of a particular attribute.
� Tuple-based constraints.
� Relationship among components.
� Easier to implement than many constraints.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 25: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/25.jpg)
25
Foreign Keys
� Consider Relation Sells(bar, beer, price).
� We might expect that a beer value is a real beer --- something appearing in Beers.name .
� A constraint that requires a beer in Sells to be a beer in Beers is called a foreign -keyconstraint.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 26: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/26.jpg)
26
Expressing Foreign Keys
� Use the keyword REFERENCES, either:
1. Within the declaration of an attribute, when only one attribute is involved.
2. As an element of the schema, as:
FOREIGN KEY ( <list of attributes> )
REFERENCES <relation> ( <attributes> )
� Referenced attributes must be declared PRIMARY KEY or UNIQUE.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 27: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/27.jpg)
27
Example: With Attribute
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20) );
CREATE TABLE Sells (
barCHAR(20),
beer CHAR(20) REFERENCES Beers(name),
price REAL );
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 28: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/28.jpg)
28
Example: As Element
CREATE TABLE Beers (
name CHAR(20) PRIMARY KEY,
manf CHAR(20) );
CREATE TABLE Sells (
barCHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY(beer) REFERENCES Beers(name));
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 29: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/29.jpg)
29
Enforcing Foreign-Key Constraints
� If there is a foreign-key constraint from attributes of relation R to the primary key of relation S, two violations are possible:
1. An insert or update to R introduces values not found in S.
2. A deletion or update to S causes some tuples of R to “dangle.”
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 30: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/30.jpg)
30
Actions Taken -- 1
� Suppose R = Sells, S = Beers.
� An insert or update to Sells that introduces a nonexistent beer must be rejected.
� A deletion or update to Beers that removes a beer value found in some tuples of Sells can be handled in three ways.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 31: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/31.jpg)
31
Actions Taken -- 2
� The three possible ways to handle beers that suddenly cease to exist are:
1. Default : Reject the modification.
2. Cascade : Make the same changes in Sells.
� Deleted beer: delete Sells tuple.
� Updated beer: change value in Sells.
3. Set NULL : Change the beer to NULL.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 32: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/32.jpg)
32
Example: Cascade
� Suppose we delete the Bud tuple from Beers.
� Then delete all tuples from Sells that have beer = ’Bud’.
� Suppose we update the Bud tuple by changing ’Bud’ to ’Budweiser’.
� Then change all Sells tuples with beer = ’Bud’ so that beer = ’Budweiser’.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 33: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/33.jpg)
33
Example: Set NULL
� Suppose we delete the Bud tuple from Beers.
� Change all tuples of Sells that have beer = ’Bud’ to have beer = NULL.
� Suppose we update the Bud tuple by changing ’Bud’ to ’Budweiser’.
� Same change.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 34: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/34.jpg)
34
Choosing a Policy
� When we declare a foreign key, we may choose policies SET NULL or CASCADE independently for deletions and updates.
� Follow the foreign-key declaration by:
ON [UPDATE, DELETE][SET NULL CASCADE]
� Two such clauses may be used.
� Otherwise, the default (reject) is used.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 35: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/35.jpg)
35
Example
CREATE TABLE Sells (
barCHAR(20),
beer CHAR(20),
price REAL,
FOREIGN KEY(beer)
REFERENCES Beers(name)
ON DELETE SET NULL
ON UPDATE CASCADE );
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 36: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/36.jpg)
36
Attribute-Based Checks
� Put a constraint on the value of a particular attribute.
� CHECK( <condition> ) must be added to the declaration for the attribute.
� The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 37: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/37.jpg)
37
Example
CREATE TABLE Sells (
barCHAR(20),
beer CHAR(20) CHECK ( beer IN
(SELECT name FROM Beers)),
price REAL CHECK ( price <= 5.00 )
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 38: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/38.jpg)
38
Timing of Checks
� An attribute-based check is checked only when a value for that attribute is inserted or updated.
� Example: CHECK (price <= 5.00) checks every new price and rejects it if it is more than $5.
� Example: CHECK (beer IN (SELECT name FROM Beers)) not checked if a beer is deleted from Beers (unlike foreign-keys).
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 39: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/39.jpg)
39
Tuple-Based Checks
� CHECK ( <condition> ) may be added as another element of a schema definition.
� The condition may refer to any attribute of the relation, but any other attributes or relations require a subquery.
� Checked on insert or update only.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 40: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/40.jpg)
40
Example: Tuple-Based Check
� Only Joe’s Bar can sell beer for more than $5:
CREATE TABLE Sells (
bar CHAR(20),
beer CHAR(20),
price REAL,
CHECK (bar = ’Joe’’s Bar’ OR
price <= 5.00)
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 41: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/41.jpg)
41
Assertions
� These are database-schema elements, like relations or views.
� Defined by:
CREATE ASSERTION <name>
CHECK ( <condition> );
� Condition may refer to any relation or attribute in the database schema.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 42: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/42.jpg)
42
Example: Assertion
� In Sells(bar, beer, price), no bar may charge an average of more than $5.
CREATE ASSERTION NoRipoffBars CHECK (
NOT EXISTS (
SELECT bar FROM Sells
GROUP BY bar
HAVING 5.00 < AVG(price)
));
Bars with anaverage priceabove $5
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 43: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/43.jpg)
43
Example: Assertion
� In Drinkers(name, addr, phone) and Bars(name, addr, license), there cannot be more bars than drinkers.
CREATE ASSERTION FewBar CHECK (
(SELECT COUNT(*) FROM Bars) <=
(SELECT COUNT(*) FROM Drinkers)
);
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 44: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/44.jpg)
44
Timing of Assertion Checks
� In principle, we must check every assertion after every modification to any relation of the database.
� A clever system can observe that only certain changes could cause a given assertion to be violated.
� Example: No change to Beers can affect FewBar. Neither can an insertion to Drinkers.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 45: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/45.jpg)
45
Triggers: Motivation
� Attribute- and tuple-based checks have limited capabilities.
� Assertions are sufficiently general for most constraint applications, but they are hard to implement efficiently.
� The DBMS must have real intelligence to avoid checking assertions that couldn’t possibly have been violated.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 46: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/46.jpg)
46
Views
� A view is a “virtual table,” a relation that is defined in terms of the contents of other tables and views.
� Declare by:
CREATE VIEW <name> AS <query>;
� In contrast, a relation whose value is really stored in the database is called a base table.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 47: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/47.jpg)
47
Example: View Definition
� CanDrink(drinker, beer) is a view “containing” the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer:
CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 48: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/48.jpg)
48
Example: Accessing a View
� You may query a view as if it were a base table.
� There is a limited ability to modify views if the modification makes sense as a modification of the underlying base table.
� Example:
SELECT beer FROM CanDrink
WHERE drinker = ‘Sally’;
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 49: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/49.jpg)
49
What Happens When a View Is Used?
� The DBMS starts by interpreting the query as if the view were a base table.
� Typical DBMS turns the query into something like relational algebra.
� The queries defining any views used by the query are also replaced by their algebraic equivalents, and “spliced into” the expression tree for the query.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 50: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/50.jpg)
50
Constraints and Triggers
� A constraint is a relationship among data elements that the DBMS is required to enforce.
� Example: key constraints.
� Triggers are only executed when a specified condition occurs, e.g., insertion of a tuple.
� Easier to implement than many constraints.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 51: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/51.jpg)
51
Triggers: Motivation
� Attribute- and tuple-based checks have limited capabilities.
� Assertions are sufficiently general for most constraint applications, but they are hard to implement efficiently.
� The DBMS must have real intelligence to avoid checking assertions that couldn’t possibly have been violated.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 52: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/52.jpg)
52
Triggers: Solution
� A trigger allows the user to specify when the check occurs.
� Like an assertion, a trigger has a general-purpose condition and also can perform any sequence of SQL database modifications.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 53: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/53.jpg)
53
Event-Condition-Action Rules
� Another name for “trigger” is ECA rule, or event-condition-action rule.
� Event : typically a type of database modification, e.g., “insert on Sells.”
� Condition : Any SQL boolean-valued expression.
� Action : Any SQL statements.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 54: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/54.jpg)
54
Example: A Trigger
� There are many details to learn about triggers.
� Here is an example to set the stage.
� Instead of using a foreign-key constraint and rejecting insertions into Sells(bar, beer, price) with unknown beers, a trigger can add that beer to Beers, with a NULL manufacturer.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 55: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/55.jpg)
55
Example: Trigger Definition
CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells
REFERENCING NEW ROW AS NewTuple
FOR EACH ROW
WHEN (NewTuple.beer NOT IN
(SELECT name FROM Beers))
INSERT INTO Beers(name)
VALUES(NewTuple.beer);
The event
The condition
The action
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 56: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/56.jpg)
56
Options: CREATE TRIGGER
� CREATE TRIGGER <name>
� Option:
CREATE OR REPLACE TRIGGER <name>
� Useful if there is a trigger with that name and you want to modify the trigger.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 57: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/57.jpg)
57
Options: The Condition
� AFTER can be BEFORE.
� Also, INSTEAD OF, if the relation is a view.
� A great way to execute view modifications: have triggers translate them to appropriate modifications on the base tables.
� INSERT can be DELETE or UPDATE.
� And UPDATE can be UPDATE . . . ON a particular attribute.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 58: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/58.jpg)
58
Options: FOR EACH ROW
� Triggers are either row-level or statement-level.
� FOR EACH ROW indicates row-level; its absence indicates statement-level.
� Row level triggers are executed once for each modified tuple.
� Statement-level triggers execute once for an SQL statement, regardless of how many tuples are modified.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 59: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/59.jpg)
59
Options: REFERENCING
� INSERT statements imply a new tuple (for row-level) or new set of tuples (for statement-level).
� DELETE implies an old tuple or table.
� UPDATE implies both.
� Refer to these by
[NEW OLD][TUPLE TABLE] AS <name>
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 60: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/60.jpg)
60
Options: The Condition
� Any boolean-valued condition is appropriate.
� It is evaluated before or after the triggering event, depending on whether BEFORE or AFTER is used in the event.
� Access the new/old tuple or set of tuples through the names declared in the REFERENCING clause.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 61: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/61.jpg)
61
Options: The Action
� There can be more than one SQL statement in the action.
� Surround by BEGIN . . . END if there is more than one.
� But queries make no sense in an action, so we are really limited to modifications.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 62: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/62.jpg)
62
Another Example
� Using Sells(bar, beer, price) and a unary relation RipoffBars(bar) created for the purpose, maintain a list of bars that raise the price of any beer by more than $1.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 63: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/63.jpg)
63
The Trigger
CREATE TRIGGER PriceTrig
AFTER UPDATE OF price ON Sells
REFERENCING
OLD ROW as old
NEW ROW as new
FOR EACH ROW
WHEN(new.price > old.price + 1.00)
INSERT INTO RipoffBars
VALUES(new.bar);
The event –only changesto prices
Updates let ustalk about oldand new tuples
We need to considereach price change
Condition:a raise inprice > $1
When the price changeis great enough, addthe bar to RipoffBars
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 64: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/64.jpg)
64
Triggers on Views
� Generally, it is impossible to modify a view, because it doesn’t exist.
� But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.
� Example: We’ll design a view Synergy that has (drinker, beer, bar) triples such that the bar serves the beer, the drinker frequents the bar and likes the beer.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 65: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/65.jpg)
65
Example: The View
CREATE VIEW Synergy AS
SELECT Likes.drinker, Likes.beer, Sells.bar
FROM Likes, Sells, Frequents
WHERE Likes.drinker = Frequents.drinker
AND Likes.beer = Sells.beer
AND Sells.bar = Frequents.bar;
Natural join of Likes,Sells, and Frequents
Pick one copy ofeach attribute
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 66: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/66.jpg)
66
Interpreting a View Insertion
� We cannot insert into Synergy --- it is a view.
� But we can use an INSTEAD OF trigger to turn a (drinker, beer, bar) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents.
� The Sells.price will have to be NULL.
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides
![Page 67: Databases 1DBMS 2 DB1Lect_07_SQL_DDL (Hajas, ELTE) ---based on Ullman‘s book and slides . Rest of SQL Defining a Database Schema Primary Keys, Foreign Keys Local and Global Constraints](https://reader034.vdocuments.us/reader034/viewer/2022042217/5ebffb85dbd442157d7c5847/html5/thumbnails/67.jpg)
67
The Trigger
CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
INSERT INTO LIKES VALUES(n.drinker, n.beer);
INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);
INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
END;
DB1Lect_07_SQL_DDL (Hajas, ELTE) --- based on Ullman‘s book and slides