SQL: Insertion of Tuples

1. SQL: Insertion of Tuples • in general: • INSERT INTO R • VALUES(Vi, ..., Vk) • ex.: • INSERT INTO Supplies • VALUES (''Jack'',''Oysters'',.24) • null values: • INSERT INTO Supplies (Name, Product) • VALUES (''Jack'',''Oysters'') • nested insertions: • INSERT INTO Sales-Chris • SELECT Product, Price • FROM Supplies • WHERE Name = ''Chris'' O. Günther: Database Management Systems

2. SQL: Deletion of Tuples • in general: • DELETE FROM R • WHERE  • ex.: • DELETE FROM Supplies • WHERE Name = ''Chris'' • AND Product = ''Perrier'' • ex.: Delete all orders containing Brie O. Günther: Database Management Systems

3. SQL: Updating Tuples • in general: • UPDATE R • SET A1=x1, ..., Ak=xk • WHERE • ex.: • UPDATE Supplies • SET Price = 1.00 • WHERE Name = ''Chris'' • AND Product = ''Perrier'' • ex.: Chris reduces all prices by 10 percent.. O. Günther: Database Management Systems

4. DDL: Data Definition Language • so far we only discussed the DML - Data Manipulation Language • typical DDL command: CREATE TABLE • general format: • CREATE TABLE R(A1T1 [NOT NULL], ..., • AkTk [NOT NULL]) • ex.: • CREATE TABLE Supplies • (Name CHAR(20) NOT NULL, • Product CHAR(10) NOT NULL, • Price NUMBER (6,2)) • to delete a table: DROP TABLE Supplies SQL - DDL O. Günther: Database Management Systems

5. Views • logical relations • so far we only discussed physical relations (stored on disk), also called base relations • views serve to represent specific user views • view contents are not stored physically but computed on demand • one can query (i.e., read only) views just like base relations • updates (write access) are not so easy O. Günther: Database Management Systems

6. Views (cont.) • view definition - general form • CREATE VIEW V (A1, ... , Ak) AS • <SELECT Query> • Ex.: CREATE VIEW Offer - Chris (Product, Price) AS • SELECT Product, Price • FROM Supplies • WHERE Name = 'Chris' O. Günther: Database Management Systems

7. View Update Problem • ex.: Offer - Chris • DELETE • INSERT • UPDATE (Price) • UPDATE (Product) • more complex example.: • CREATE VIEW Customer-Order (Name, Date, Product, Amount) AS • SELECT Customer, Date, Product, Amount • FROM Orders, Contains • WHERE Orders.O_No = Contains.O_No • - DELETE • - INSERT • - UPDATE (Name) • - UPDATE (Date) • - UPDATE (Product) • - UPDATE (Amount) O. Günther: Database Management Systems

8. View Update Problem (cont.) • ex.: CREATE VIEW X AS • SELECT Product, AVG(Price) DP • FROM Supplies • GROUP BY Product • - UPDATE (DP) • - UPDATE (Product) • - INSERT • - DELETE O. Günther: Database Management Systems

9. View Update Problem (cont.) • ex.: CREATE VIEW Y AS • SELECT C2.Name, C2.Address • FROM Customers C1, Customers C2 • WHERE C2.Balance < C1.Balance • AND C1.Name = 'Jane' • - INSERT • - DELETE • - UPDATE (Name) • - UPDATE (Address) O. Günther: Database Management Systems

10. View Update Problem (cont.) • Views can be updated if • (1) the corresponding base relations can be updated (i.e., no • non-updatable views) • (2) the SELECT command is a combination of only projections • (column subsets) and selections (row subsets) (i.e., no joins, • subqueries, tuple variables, aggregates, etc.). In case of projections, • the key has to be preserved. O. Günther: Database Management Systems

11. View Update Problem (cont.) all possible views views that can be updated views according to (1) and (2) views that can be updated in SQL (version-dependent) O. Günther: Database Management Systems

12. Views - Summary • logical relations • defined using physical base relations (and possibly other views) • (typically) not stored physically but computed on demand using • the current content of the base relations • same data can be „viewed“ in different shapes • supports different user groups and privacy • view updates: problematic because not all updates can be mapped • to base relations O. Günther: Database Management Systems

13. Databases - Programming Languages • collision of two different paradigms • - PL: one tuple at a time • - DB: many tuples at a time • interface tuple - variable: communication via „cursors“ (buffer) • queries are preformulated using variables • instantiation at run-time with real values O. Günther: Database Management Systems

14. Ex: Embedded SQL exec sql begin declare section; int O_No, Amount; char Date [10], Customer [20], Product [10]; exec sql end declare section; exec sql connect; exec sql prepare order-insert from insert into Orders values (:O_No, :Date, :Customer); exec sql prepare cont-insert from insert into Contains values (:O_No, :Product, :Amount); write (‚Enter Order No., Date, and Customer‘); read (O_No); read (Date); read (Customer); exec sql execute order-insert using :O_No, :Date, :Customer; write (‚Enter a list of tuples ‚Product-Amount‘, terminate with ´end´´); read (Product); while (Product ! = 'end') { read (Amount); exec sql execute cont_insert using :O_No, :Product, :Amount; read (Product); } O. Günther: Database Management Systems

15. Integrity in Databases • maintenance of a correct relationship database - real world • (possibly automatical) identification of invalidstates of the database • (i.e., states without correspondence in the real world) • three kinds of integrity • domain-specific integrity (application-specific, ex.: date) • key integrity • schema integrity O. Günther: Database Management Systems

16. Integrity in Databases (cont.) • key integrity • - rule 1 (entity integrity): • each relation must have a key, and each tuple in the relation must have • a key value that is unique and non-NULL. • - rule 2 (referential integrity): • for each foreign key FK there is another relation with a primary key • PK such that each non-NULL value of FK is identical to an existing • value of PK. • - Ex.: • foreign key O_No in relation Contains, • foreign key Customer in relation Orders • schema integrity O. Günther: Database Management Systems

17. Database Design • ex. for bad database design: • Suppliers - Info • disadvantages • redundancies • update anomalies • insertion anomalies (ex: supplier without products) • deletion anomalies (NULL in key) O. Günther: Database Management Systems

18. Database Design by Decomposition • approach: • decomposition into relations with less columns • Careful: no information loss • Ex.: Suppliers (L-Name, L-Address) • Supplies (L-Name, Product, Price) • disadvantage: may require additional join operations at query time O. Günther: Database Management Systems

19. Functional Dependencies • logical dependencies between columns • causes many of the problems discussed above • - redundancies • - update anomalies • - ... • Definition: If for a relation R there is a functional dependency (FD) • X  Y (where X and Y may represent one or several columns of R) • then the following holds for two arbitrary tuples t1 and t2 in R: • t1 [X] = t2 [X]  t1 [Y] = t2 [Y] . • A functional dependency defined on relation R holds for all instances of R O. Günther: Database Management Systems

20. Functional Dependencies (cont.) • Ex.: • Customers: Name  Address • Name  Balance • Orders: O_No  Date • O_No  Customer • Customers: Address  Address • Supplies: {Name, Product}  Price • for each key S of a relation R and each subset T of columns of R • we have: • S  T • Some FDs imply other FDs • Ex.: F = {A  B, B  C} |= A  C O. Günther: Database Management Systems

21. Closure of FD Sets • F+:= {X  Y: there is an FD A B in F: A B |= X  Y} • the closure F+ of a set F of FDs contains all functional dependencies • implied by the FDs in F • Ex.: • F = {A  B; B  C; AB  C} • F+ = O. Günther: Database Management Systems

22. Minimal Cover of a Set F of FDs • given a set F of FDs, F is a minimal cover of F if and only if: • (1) F+ = F+, i.e., all FDs F are implied by the FDs in F. • F and F are equivalent. • (2) the right side of each FD in Fis a single attribute • (3) there is no (X  A)  F : (F -{X  A})+= F+, • i.e., there are no superfluous FDs in F • (4) there is no (X  A)  F, Z  X: F - (X  A)  (Z  A))+= F+, • i.e., no FD in F can be replaced by a simpler FD O. Günther: Database Management Systems

23. FDs and Database Design • potential problem: too many FDs in a relation • may lead to anomalies and redundancies • solution: decomposition into several simple relations • Ri R (i = 1,..., k) • R = R1 || R2|| ... || Rk • less redundancies but possibly more joins • important for preservation of information: • one has to be able to re-assemble R by joining the Ri • (lossless join) • the FDs defined in R have to be definable on the Ri • (preservation of dependencies) O. Günther: Database Management Systems