Tasks

1. Tasks • Database Installation and Mangement • Oracle 11g • SQL Server 2005/2008 • Database Design Topic • Development Tools: • Java: Eclipse + SSH • .Net: VS 2008 + ASP.NET 2.0 (C#) • PHP+MySQL (XAMPP) • Other Optional Soft: • ERWin, PowerDesigner, Visio

2. Chapter 2 Relational Model • 2.1 Structure of Relational Databases • 2.2 Relational Algebra • 2.3 Tuple Relational Calculus • 2.4 Domain Relational Calculus • 2.5 Views

3. Example of a Relation

4. Why Relations? • Simple. • Match for the way we think about our data. • Abstract model that underlies SQL, the most important language in DBMS.

5. 2.1 Structure of Relational Databases • 1) Table = relation. • 2) Column headers = attributes. • 3) Rows = tuples. • 4) Relation schema = name(attributes) + other structure info., • e.g., Course(Cid,Cname,Credit, cpno) • Order of attributes is arbitrary, but in practice we need to assume the order given in the relation schema. • 5) Relation instances = current set of rows for a relation schema. • 6) Database schema = collection of relation schemas.

6. 2.1.1 Relational Design Course(Cid,Cname)

7. 2.1.2 Basic Structure • Formally, given sets D1, D2, …. Dn，a relationr is a subset of D1 x D2 x … x Dn • Thus a relation is a set of n-tuples (a1, a2, …, an) where each ai Di

8. 2.1.2 Basic Structure • E.g., • ifSid= {S001,S002,S003}Sname = {Johnson,Smith,Julie}Ssex = {F,M} Sdep={Management,Physics } Thenr= { (S001,Johnson,M,Management), (S002,Smith,M,management), (S003,Julie,F,physics)} is a relation over Sid x Sname x Ssex x Sdep.

9. 2.1.3 Attribute Types • Each attribute of a relation has a name. • The set of allowed values for each attribute is called the domain of the attribute. • Attribute values are (normally) required to be atomic, that is, indivisible. • E.g., multivalued attribute values are not atomic. • E.g., composite attribute values are not atomic. • The special value null is a member of every domain.

10. 2.1.4 Relation Schema • A1, A2, …, Anare attributes. • R = (A1, A2, …, An ) is a relation schema • E.g.,Student-schema = (Sid,Sname,Ssex,Sdep) • r(R) is a relation on the relation schema R E.g., student(Student-schema)

11. 2.1.5 Relation Instance • The current values (relation instance) of a relation are specified by a table. • An element t of ris a tuple, represented by a row in a table. Student attributes (or columns) tuples (or rows)

12. 2.1.6 Relations are Unordered • Order of tuples is irrelevant (tuples may be stored in an arbitrary order) • E.g.，course relation with unordered tuples. Cid Cname C002 C003 C001 Data Structure Operation System Database

13. 2.1.7 Keys • Let K  R, K is a superkeyof R if values for K are sufficient to identify a unique tuple in relation r(R) . • {Sid,Sname} and {Sname}are both superkeys of Student, if no two students can possibly have the same name.

14. 2.1.7 Keys • K is a candidate key if K is minimal.Primary key • {Sname} is a candidate key for Student, since it is a superkey (assuming no two students can possibly have the same name), and no subset of it is a superkey.

15. 2.1.8 Get Keys from E-R Sets • The union of the primary keys of the related entity sets becomes a super key of the relation. • For many-to-one relationship sets, the primary key of the “many” entity set becomes the relation’s primary key. • For one-to-one relationship sets, the relation’s primary key can be that of either entity set. • For many-to-many relationship sets, the union of the primary keys becomes the relation’s primary key

16. E-R Diagram for the Choosing Courses n m n m

17. Schema Diagram for Choosing Courses

18. Query Languages • Language in which user requests information from the database. • Relational Algebra • Tuple Relational Calculus • Domain Relational Calculus

19. 2.2 Relational Algebra • Six basic operators • union -----  • Intersection -----  • set difference ---- – • Cartesian product ---- x • select ------  • Project ----  • Rename -----  • The operators take two or more relations as inputs and give a new relation as a result.

20. 2.2.1 Union Operation • Notation: r s • Defined as: r s = {t | t  r or t  s} • For r s to be valid. 1. r,s must have the same number of attributes 2. The attribute domains must be compatible (e.g., 2nd column of r deals with the same type of values as does the 2nd column of s)

21. Union Operation – Example • Relations r, s: r  s r s A B A B A B     1 2 1 3    1 2 1   2 3

22. Union Operation • To find the courses which to be chosen or be taught. • Cid (choose)  Cid (teach)

23. 2.2.2 Set-Intersection Operation • Notation: r s • Defined as: • rs ={ t | tr and ts } • Assume: • attributes of r and s are compatible • Note: • rs = r - (r - s)

24. Set-Intersection Operation - Example • Relation r, s: r r  s s A B A B A B    1 2 1    2 2 3

25. 2.2.3 Set Difference Operation • Notation: r – s • Defined as: r – s = {t | t r and t  s} • Set differences must be taken between compatiblerelations. • r and s must have the same number of attributes • attribute domains of r and s must be compatible

26. Set Difference Operation – Example • Relations r, s: r – s s r A B A B A B   1 1    1 2 1   2 3

27. 2.2.4 Cartesian-Product Operation • Notation: r x s • Defined as: r x s = {t q | t  r and q  s} • Assume that attributes of r(R) and s(S) are disjoint. (That is, R  S = ). • If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

28. Cartesian-Product Operation-Example Relations r, s r r x s s A B C D E A B C D E   1 2     10 10 20 10 a a b b         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b

29. 2.2.5 Select Operation • Notation: p(r) • p is called the selection predicate • Defined as: p(r) = {t | t  r and p(t)} • p is a formula in propositional calculus consisting of terms connected by :  (and),  (or),  (not)Each term is one of: <attribute> op <attribute> or <constant> • op is one of: =, , >, . <. 

30. Select Operation – Example r A=B ^ D > 5(r) A B C D A B C D         1 5 12 23 7 7 3 10     1 23 7 10 • Example of selection:Cname=“database”(Course)

31. 2.2.6 Project Operation • Notation:A1, A2 , …, Ak (r) • A1, A2 are attribute names and r is a relation name. • The result is defined as the relation of k columns obtained by erasing the columns that are not listed. • Duplicate rows removed from result, since relations are sets.

32. = Project Operation – Example A,C (r) r A B C A C A C     10 20 30 40 1 1 1 2     1 1 1 2    1 1 2

33. Project Operation – Example • To eliminate the Ssex attribute of Student. Sid,Sname,Sdep (Student)

34. Choosing Courses Example 1)Student(Sid,Sname,Ssex,Sdep) 2) Course(Cid,Cname) 3)Choose(Sid,Cid,Grade) 4) Teacher(Tid,Tname) 5) Teach(Cid,Tid,Room)

35. Example Queries • Find all students in management. • Sdep=“management”(Student) • Find the courses whose grade is over 80. • Cid (Grade>80 (Choose))

36. Example Queries • To find the courses which to be chosen or be taught. • Cid (Choose)  Cid (Teach)

37. Example Queries • Find the grade of the student whose name is Smith Grade (Sname=“Smith” (Student.Sid=Choose.Sid(Student x Choose))) • Find courses that Smith chooses and whose teacher is not decided yet. Cid (Sname=“Smith” (Student.Sid=Choose.Sid(Student x Choose))) – Cid(Teach)

38. Example Queries • Find the grade of the student whose name is Smith. Grade (Sname=“Smith” (Student.Sid=Choose.Sid(Student x Choose))) •  Query 2 • Grade(Student.Sid=Choose.Sid ( (Sname=“Smith”(Student)) x Choose))

39. Additional Operations • We define additional operations that do not add any power to the relational algebra, but that simplify common queries. • Natural join • Division • Assignment

40. 2.2.7 Natural-Join Operation • Notation: r s • Let r and s be relations on schemas R and S respectively. Then, r s is a relation on schema R S obtained as follows: • Consider each pair of tuples tr from r and ts from s. • If tr and ts have the same value on each of the attributes in RS, add a tuple t to the result, where • t has the same value as tr on r • t has the same value as ts on s

41. rs is defined as:r.A, r.B, r.C, r.D, s.E (r.B = s.B  r.D= s.D (r xs)) 2.2.7 Natural-Join Operation • E.g., R = (A, B, C, D), S = (E, B, D) • Result schema = (A, B, C, D, E)

42. r s Natural Join Operation • Relations r, s: s r B D E A B C D E A B C D      1 1 1 1 2      a a a a b      1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b

43. 2.2.8 Division Operation • Suited to queries that include the phrase “for all”. • Let r and s be relations on schemas R and S respectively where • R = (A1, …, Am, B1, …, Bn) • S = (B1, …, Bn) The result of R  S is a relation on schema R  S = (A1, …, Am). • Notation: R S R S = { t | t   R-S(r)   u  s ( tu  r ) }

44. Division Operation – Example r  s s r A B A B   1 2            1 2 3 1 1 1 3 4 6 1 2

45. Another Division Example s r A B C D E D E         a a a a a a a a         a a b a b a b b 1 1 1 1 3 1 1 1 a b 1 1 r  s A B C   a a  

46. Query 1 • Sname,Grade(Sdep=“management”(Student Choose))  • Sname,Grade(Sdep=“physics”(Student Choose)) • Query 2 • Sname,Sdep,Grade(Student Choose)  temp(Sdep) ({(“Management”), (“physics”)}) Example Queries • Find the grade of the students in school of management and physics.

47. Sname,Cid(Student Choose) Cid (Cname=“database”(Course)) Example Queries • Find all students who choose “database”.

48. 2.3 Tuple Relational Calculus • A nonprocedural query language, where each query is of the form {t | P (t) } • It is the set of all tuples t such that predicate P is true for t. • t is a tuple variable, t[A] denotes the value of tuple t on attribute A. • t rdenotes that tuple t is in relation r. • P is a formula similar to that of the predicate calculus.

49. 2.4 Domain Relational Calculus • Each query is an expression of the form: {  x1, x2, …, xn  | P(x1, x2, …, xn)} • x1, x2, …, xn represent domain variables • P represents a formula similar to that of the predicate calculus

50. Sname,Cid(Student Choose) 2.5 Views • In some cases, it is not desirable for all users to see the entire logical model (E.g., all the actual relations stored in the database.) • Consider a person who needs to know a student’s name and his courses but has no need to see grade. This person should see a relation described, in the relational algebra, by • Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view.