Garcia-Molina, Ullman, and Widom

1. Garcia-Molina, Ullman, and Widom Database Systems Chapter 2

2. Data Models • A data model is a notation for describing the structure of the data in a database , • along with the constraints on that data. • a day of the week is an integer between 1 and 7 • The data model also normally provides a notation for describing operations on that data : • queries and data modifications.

3. Relational Model • Relations are tables representing information . • The set of tuples for a given relation is an instance of that relation • Columns are headed by attributes ; • each attribute has an associated domain , or data type. • Rows are called tuples , and • a tuple has one component for each attribute of the relation.

4. Schemas • A relation name , together with the attributes of that relation and their types , form the relation schema. • Movies(title, year, length, genre) • A collection of relation schemas forms a database schema. • Particular data for a relation or collection of relations is called an instance of that relation schema or database schema.

5. Keys • An important type of constraint on relations is the assertion that an attribute or set of attributes forms a key for the relation. • known as key constraints • No two tuples of a relation can agree on all attributes of the key , • although they can agree on some of the key attributes.

6. Semistructured Data Model • In this model , data is organized in a tree or graph structure . • XML is an important example of a semistructured data model • Tags similar to those used in HTML define the role played by different pieces of data • much as the column headers do in the relational model

7. SQL • The language SQL is the principal query language for relational database systems . • The current standard is called SQL-99 . • Commercial systems generally vary from this standard but adhere to much of it .

8. Relation implementation • Stored relations, called tables, exist in the database and can be modified by changing its tuples, as well as queried. • Views are relations defined by a computation • these relations are not stored, but are constructed, in whole or in part, when needed • Temporary tables are constructed by SQL during the execution of queries and data modification • after the transaction these are thrown away

9. Data Definition • SQL has statements to declare elements of a database schema . • The CREATE TABLE statement allows us to declare the schema for stored relations • specifying the attributes , their types , default values , and keys.

10. Altering Relation Schemas • We can change parts of the database schema with an ALTER statement . • These changes include • adding and removing attributes from relation schemas and • changing the default value associated with an attribute . • We may also use a DROP statement to completely eliminate relations or other schema elements.

11. Modifying relations examples DROP TABLE R; Relation R is no longer part of the database schema, and its tuples can no longer be accessed ALTER TABLE MovieStar ADD phone CHAR(16); ALTER TABLE MovieStar DROP birthdate; CREATE TABLE MovieStar ( name CHAR(30), address VARCHAR(255), gender CHAR(1), birthdate DATE, PRIMARY KEY (name) );

12. Algebra • An algebra consists of operators and atomic operands. • In relational algebra, the atomic operands are • Variables that stand for relations • Constants which are finite relations

13. Relational Algebra • This algebra underlies most query languages for the relational model. • Its principal operators are • union , intersection, difference , selection , • projection , Cartesian product , • natural join , theta-join , and • renaming .

14. Section 2.4 Relational algebra and SQL • From the text • Relational algebra is not used today as a query language in commercial DBMS’s, although some of the early prototypes did use this algebra directly. • SQL incorporates relational algebra at its center, and many SQL programs are really “syntactically sugared” expressions of relational algebra.

15. Some SQL CREATE TABLE Product ( maker CHAR(30), model CHAR(10) PRIMARY KEY, type CHAR(15) ); CREATE TABLE PC ( model CHAR(30), speed DECIMAL(4,2), ram INTEGER, hd INTEGER, price DECIMAL(7,2) );

16. CREATE TABLE Laptop ( model CHAR(30), speed DECIMAL(4,2), ram INTEGER, hd INTEGER, screen DECIMAL(3,1), price DECIMAL(7,2) ); CREATE TABLE Printer ( model CHAR(30), color BOOLEAN, type CHAR (10), price DECIMAL(7,2) );

17. Selection and Projection • The selection operator produces a result consisting of all tuples of the argument relation that satisfy the selection condition . • Projection removes undesired columns from the argument relation to produce the result

18. Some Data for Product M M T a o y k d p e e e r l A 1001 pc A 1002 pc A 1003 pc A 2004 laptop A 2005 laptop A 2006 laptop B 1004 pc B 1005 pc B 1006 pc B 2007 laptop C 1007 pc D 1008 pc D 1009 pc D 1010 pc D 3004 printer D 3005 printer E 1011 pc E 1012 pc E 1013 pc E 2001laptop E 2002 laptop E 2003 laptop E 3001 printer E 3002 printer E 3003 printer F 2008 laptop F 2009 laptop G 2010 laptop H 3006 printer H 3007 printer

19. Some data for PC model speed ram hd price 1001 2.66 1024 250 2114 1002 2.10 512 250 995 1003 1.42 512 80 478 1004 2.80 1024 250 649 1005 3.20 512 250 630 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 1008 2.20 2048 250 770 1009 2.00 1024 250 650 1010 2.80 2048 300 770 1011 1.86 2048 160 959 1012 2.80 1024 160 649 1013 3.06 512 80 529

20. What PC models have a speed of at least 3.00? R1 := σspeed ≥ 3.00 (PC) R2 := πmodel(R1)

21. Some data for Laptop model speed ram hd screen price 2001 2.00 2048 240 20.1 3673 2002 1.73 1024 80 17.0 949 2003 1.80 512 60 15.4 549 2004 2.00 512 60 13.3 1150 2005 2.16 1024 120 17.0 2500 2006 2.00 2048 80 15.4 1700 2007 1.83 1024 120 13.3 1429 2008 1.60 1024 100 15.4 900 2009 1.60 512 80 14.1 680 2010 2.00 2048 160 15.4 2300

22. Which manufacturers make laptops with a hard disk of at least 100GB? R1 := σhd ≥ 100 (Laptop) R2 := Product (R1) R3 := πmaker (R2)

23. Joins • We join two relations by comparing tuples , one from each relation. • In a natural join , we splice together those pairs of tuples that agree on all attributes common to the two relations. • In a theta-join , pairs of tuples are concatenated if they meet a selection condition associated with the theta-join.

24. Constraints in Relational Algebra • Many common kinds of constraints can be expressed as • the containment of one relational algebra expression in another , or • as the equality of a relational algebra expression to the empty set.