Database Principles

1. Database Principles College of Computer Science and Technology Chongqing University of Posts & Telecom.

2. Chapter 2The Relational Model of Data Data Models Relational Model Relation Schema Definition Relational Algebraic

3. What is Data Model? • A data model (数据模型)is a notation for describing data or information, which consists of three parts (三要素): • Structure of the data (数据结构) • Operation on the data (数据操作) • Constraints on the data (数据约束)

4. 数据模型的组成要素 • 数据结构 • 指所研究数据集合及数据间的联系 • 是对系统静态特性的描述 • 数据操作 • 对数据库中各类数据允许执行的操作及有关的操作规则 • 检索、更新（包括插入、删除、修改） • 是对系统动态特性的描述 • 数据的约束条件 • 一组数据及其联系所具有的制约规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容

5. Data Model should : • Provide simple approach to structuring data • Provide collection of operations on data • Simulate the real world comparatively truly • Be easy to understand • Be easy to be implemented by computer

6. 客观对象 数据模型 概念模型 机器世界 信息世界 现实世界 Abstraction and Modeling Two steps of the abstraction and modeling: • The objective objects in the real world is abstracted to a concept model. • Concept model is converted to a data model that supported by some DBMS.

7. The classification of the models • Concept model • Modeling data from the viewpoint of users. • Mainly used for a database design. • Data model • Modeling data from the viewpoint of computer. • Mainly used for a DBMS realization.

8. Traditional data models • Hierarchical Model • Network Model • Relational Model • Object-relational Model • Semistructured Model

9. Why Relational Model? • Very simple model. • Often matches how we think about data. • Abstract model that underlies SQL, the most important database language today.

10. Basic of the Relational Model • The relational model gives us a single way to represent data: as a two- dimensional table (二维表)called a relation (关系). The relation Movies

11. Attributes (属性) • Attributes of a relation serve us as names for the columns of the relation. • Usually, the attributes describes the meaning of entries in the column below.

12. Schemas（模式） • The name of a relation and the set of attributes for a relation is called the schema for that relation. • We show the schema for the relation with the relation name followed by a parenthesized list of its attributes. • A relational database consists of one or more relations, The set of schemas for the relations is called relationaldatabase schema.

13. Tuples（元组） • The rows of a relation,other than the header row containing the attributes, are called tuple. For example: • Relations, however, are sets of tuples, and it is impossible for a tuple to appear more than once in a given relation.

14. Domains（域） • The relational model requires that each component of each tuple be atomic; that is, it must be of some elementary type such as integers or string. • Each attributes of a relation has particular elementary type, thus domain is decided.

15. Example: Name Addr Tel N1 A1 T1 N1 A1 T2 N1 A1 T3 . . . N1 A1 T7 N1 A2 T1 N1 A3 T1 N2 A1 T1 Name Addr Tel N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T7 Attribute Tuple Domain

16. Equivalent Representations of a Relation • The attributes of the relation can be reordered without change the relation.

17. Relation Instance（关系实例） • A relation about movies is not static; rather, relations change over time. • It is not common for the schema of a relation to change. • We shall call a set of tuples for a given relation an instance of that relation.

18. Example: Relation Schema: Student (StudNo, StudName, StudAge, Depart) Relation Instance: StudNo StudName StudAge Depart 001 Bob 18 Computer 002 Bob 16 Telecom 003 Pat 20 Computer 004 Harry 17 Math 005 Sally 18 Telecom 006 Sally 16 Computer 007 Pat 19 Computer

19. Keys of Relations • A set of attributes forms a key for a relation if we do not allow two tuples in a relation instance to have the same values in all the attribute of the key. • We indicate the key by underlining the key attribute(s) Student (StudNo, StudName, StudAge, Depart) Movie (title, year, length, genre)

20. Example database schema about Movies • Movies(title, year, length, genre, studioName, producerC#) • MovieStar(name, address, gender, birthdate) • StarsIn(movieTitle, movieYear, starName) • MovieExec(name, address, cert#, netWorth) • Studio(name, address, presC#)

21. Defining a Relation Schema in SQL • SQL: language for describe and manipulate relational database. • SQL-99 standard • There are two aspects to SQL • DDL for declaring database schemas • DML for querying for modifying database

22. a b • 20 • 30 • 40 • … … • R Do You Know SQL? • Explain the difference between: SELECT b FROM R WHERE a<10 OR a>=10; and SELECT b FROM R;

23. And How About These? SELECT a FROM R, S WHERE R.b = S.b; SELECT a FROM R WHERE b IN (SELECT b FROM S);

24. Relations in SQL • Table (表)：stored relations • View (视图)：virtual table, do not stored, relations defined by a computation. • Temporary tables (临时表)

25. Creating (Declaring) a Relation • To create a relation: CREATE TABLE <name> ( <list of elements> ); • To delete a relation: DROP TABLE <name>; • To modify a relation: ALTER TABLE <name> ADD/DROP attribute;

26. Elements of Table Declarations • Most basic element: an attribute and its type. • The most common data types are: • CHAR(n ) / fixed-length string of n characters. • VARCHAR(n ) / variable-length string of up to n characters. • INT or INTEGER • REAL or FLOAT • DATE and TIME

27. Example: Create Table CREATE TABLE Student ( StudNo CHAR(6), StudName VARCHAR(20), StudAge INT, Depart VARCHAR(40) );

28. SQL Values • Integers and reals are represented as you expect. • Strings are too, except they require single quotes (单引号). • Two single quotes = real quote, e.g., ’Joe’’s Bar’. • Any value can be NULL.

29. Dates and Times • The form of a date value is: DATE ’yyyy-mm-dd’ • E.g.: DATE ’2007-09-30’ for Sept. 30, 2007. • The form of a time value is: TIME ’hh:mm:ss’ • E.g.: TIME ’15:30:02’ = two seconds after 3:30PM.

30. Declaring Keys • An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE. • Either says that no two tuples of the relation may agree in all the attribute(s) on the list. • There are a few distinctions to be mentioned later.

31. Declaring Single-Attribute Keys • Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute. • Example: CREATE TABLE Student ( StudNo CHAR(6) UNIQUE, StudName CHAR(20) … … );

32. Declaring Multiattribute Key • Example: CREATE TABLE StarsIn ( movieTitle CHAR(20), movieYear VARCHAR(20), starName REAL, PRIMARY KEY (movieTitle, movieYear, starName) );

33. Primary Key vs. Unique • There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes. • 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.

34. Exercises 1. Explain the following notations: Data Model, Relation, Attribute, Tuple, Domain, Schema, Instance, Key 2. (Exercises 2.3.1) Write the following declarations: Product (maker, model, type) PC (model, speed, ram, hd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) 3. An alteration to Printer schema to delete the attribute color.

35. Relational Algebra(关系代数) Operators（运算符） Expression Trees（表达树）

36. Algebraic Query Language • Algebraic Query Language • Language for operations on relations • Internal representation of a SQL query • Why do we need a special query language? • Ease of programming, and • The ability of the compiler to produce high optimized code

37. What is an “Algebra” • Mathematical system consisting of: • Operands --- variables or constants from which new values can be constructed. • Operators --- symbols denoting procedures that construct new values from given values.

38. What is Relational Algebra? • An algebra whose operands are relations or constants that represent relations. • Operators are designed to do the most common things that we need to do with relations in a database. • The result is an algebra that can be used as a query language for relations.

39. Core Relational Algebra • Union, intersection, and difference. • Usual set operations, but both operands have the same relation schema. • Selection: picking certain rows. • Projection: picking certain columns. • Products and joins: compositions of relations. • Renaming of relations and attributes.

40. Operators

41. Set Operators • R U S: union, the set of elements that are in R or S or both. • R S: intersection, the set of elements that are in both R and S. • R - S: difference, the set of elements that are in R but no in S. • Required • R and S must have schema with identical set of attributes, and • Before calculation, the columns of R and S must be ordered.

42. RS RS A B C A B C a1 b1 c1 a1 b2 c2 a2 b2 c1 a1 b3 c2 a1 b2 c2 a2 b2 c1 R S Ａ　　　Ｂ　　　Ｃ Ａ　　　Ｂ　　Ｃ a1 b1 c1 a1 b2 c2 a2 b2 c1 a1 b2 c2 a1 b3 c2 a2 b2 c1 R-S A B C a1 b1 c1

43. π Projection • L (R) • L is a list of attributes from the schema of R. • The result is a new relation that has only some of R’s columns. • Eliminate duplicate tuples, if any. • Example title, year, length (Movies)

44. σ Selection • C (R) • C is a condition (as in “if” statements) that refers to attributes of R. • The result is a new relation with a subset of R’s tuples that satisfy C. • Example lentgh>=100 AND studioName = ‘Fox’(Movies)

45. Cartesian Product • or just product • R  S • Pair each tuple t1 of R with each tuple t2 of S. • Result: a new relation with new tuples, each of them concatenation a pair of t1t2, the attributes of R and S are in ordered. • But beware attribute A of the same name in R and S: use R.A and S.A.

46. R S Ａ　　　Ｂ　　　Ｃ Ａ　　　Ｂ　　Ｃ a1 b1 c1 a1 b2 c2 a2 b2 c1 a1 b2 c2 a1 b3 c2 a2 b2 c1 RS A B C A B C a1 b1 c1 a1 b2 c2 a1 b1 c1 a1 b3 c2 a1 b1 c1 a2 b2 c1 a1 b2 c2 a1 b2 c2 a1 b2 c2 a1 b3 c2 a1 b2 c2 a2 b2 c1 a2 b2 c1 a1 b2 c2 a2 b2 c1 a1 b3 c2 a2 b2 c1 a2 b2 c1

47. Natural Joins • A frequent type of join connects two relations by: • Equating attributes of the same name, and • Projecting out one copy of each pair of equated attributes. • Called natural join. • Denoted: R1 R2

48. R AθB S Theta-Join • R C S • Take the product R x S. • Then apply C to the result. • C can be any boolean-valued condition. • Historic versions of this operator allowed only A theta B, where theta was =, <, etc.

49. RR.B=S.BS 等值连接 RC<ES R S A B C a1 b1 5 a1 b2 6 a2 b3 8 a2 b4 12 B E b1 3 b2 7 b3 10 b3 2 b5 2 A R.B C S.B E a1 b1 5 b2 7 a1 b1 5 b3 10 a1 b2 6 b2 7 a1 b2 6 b3 10 a2 b3 8 b3 10 A R.B C S.B E a1 b1 5 b1 3 a1 b2 6 b2 7 a2 b3 8 b3 10 a2 b3 8 b3 2 Ａ　 Ｂ　　 Ｃ　　Ｅ a1 b1 5 3 a1 b2 6 7 a2 b3 8 10 a2 b3 8 2 自然连接 RS

50. Combining Operations to Form Query • Algebras allow us to express sequences of operations in a natural way. • Example: in arithmetic --- (x + 4)*(y - 3). • Relational algebra allows the same. For example title, year( lentgh>=100 (Movie)studioName = ‘Fox’(Movie))