1 / 526

Temple University – CIS Dept. CIS331– Principles of Database Systems

A comprehensive review of the principles of database systems, including E-R diagrams, relational model, SQL, integrity constraints, normalization, and more.

ejustin
Download Presentation

Temple University – CIS Dept. CIS331– Principles of Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Temple University – CIS Dept.CIS331– Principles of Database Systems V. Megalooikonomou Review of cis331 material (based on notes by C. Faloutsos at CMU)

  2. General Overview • E-R diagrams • Relational model – SQL, QBE • Integrity Constraints • Functional Dependencies & Normalization • Physical Design; Indexing • Transaction processing • Advanced topics • Distributed Databases • OO- and OR-DBMSs • Query processing/optimization • Data Mining

  3. Overview • Concepts • Entities • Relationships • Attributes • Mapping constraints: cardinalities • Keys • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables

  4. The Tools Entities (‘entity sets’) N M Relationships (‘rel. sets’) and mapping constraints P attributes

  5. STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR Example

  6. E-R Diagrams • Rectangles represent entity sets • Diamonds represent relationship sets • Lines link attributes to entity sets and entity sets to relationship sets • Ellipses represent attributes • Double ellipses represent multivalued attributes • Dashed ellipses denote derived attributes • Underline indicates primary key attributes (will study later)

  7. manages 1 EMPLOYEE N More details • Entity sets of a relationship need not be distinct • self-relationships – example ? • Usually different “roles” are indicated by labeling the lines that connect diamonds to rectangles

  8. Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • E.g.: One-to-one relationship: • A customer is associated with at most one loan via the relationship borrower • A loan is associated with at most one customer via borrower

  9. More details • Binary relationships • 3-way and k-way relationships? N M EMPLOYEE TOOL uses P PROJECT

  10. More details - attributes • superkey: a set of one or more attributes whose values uniquely determine each entity (eg., (ssn, address) ) • candidate key: a minimal super key (eg., ssn; employee#) • primary key: a cand. key, chosen by DBA • multivalued or set-valued attributes (eg., ‘dependents’ for EMPLOYEE) • derived attributes (eg., 15% tip)

  11. eg., students: part time (#credit-hours) and full time (major) Extended ER features: Specialization name STUDENT ssn IS-A major FT-STUDENT PT-STUDENT #credits

  12. Another specialization example

  13. treat a relationship as an ‘abstract’ entity allows relationships between relationships rarely used Aggregation

  14. E-R Design Decisions  • The use of an attribute or entity set to represent an object • Whether a real-world concept is best expressed by an entity set or a relationship set • Use of a ternary relationship versus a pair of binary relationships • Use of a strong or weak entity set • Use of specialization/generalization – contributes to modularity in the design • Use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure

  15. E-R Diagram for a Banking Enterprise

  16. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables

  17. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables

  18. Summary • E-R Diagrams: a powerful, user-friendly tool for data modeling: • Entities (strong, weak) • Attributes (primary keys, discriminators, derived, multivalued) • Relationships (1:1, 1:N, N:M; multi-way) • Generalization/Specialization; Aggregation

  19. attribute (strong) entity set weak entity set relationship set identifying rel. set for weak entity Summary - cont’d multivalued attribute derived attribute

  20. A primary key discriminator IS-A A total generalization M l’:h’ N l:h cardinalities cardinalities with limits Summary - cont’d (e.t.c. - see book for alternative notations)

  21. Conclusions • E-R Diagrams: a powerful, user-friendly tool for data modeling

  22. Data-files catalog Overview of a DBMS Naïve user casual user DBA DML parser DDL parser DML precomp. trans. mgr buffer mgr

  23. Overview – Relational model • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  24. Concepts • Database: a set of relations (= tables) • rows: tuples • columns: attributes (or keys) • superkey, candidate key, primary key

  25. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  26. Formal query languages • How do we collect information? • Eg., find ssn’s of people in cis331 • (recall: everything is a set!) • One solution: Relational algebra, i.e., set operators (procedural language) • Q1: Which operators?? • Q2: What is a minimal set of operators?

  27. FUNDAMENTALRelational operators • selection • projection • cartesian product MALE x FEMALE • set union • set difference R - S R U S

  28. Relational ops • Surprisingly, they are enough, to help us answer almost any query we want!! • derived operators, for convenience • set intersection • join (theta join, equi-join, natural join) • ‘rename’ operator • division

  29. Joins • very popular: natural join: RS • like equi-join, but it drops duplicate columns: STUDENT(ssn, name, address) TAKES(ssn, cid, grade)

  30. Overview - rel. algebra • fundamental operators • derived operators • joins etc • rename • division • examples

  31. Extended Relational-Algebra-Operations • Generalized Projection • Outer Join • Aggregate Functions

  32. Modification of the Database • The content of the database may be modified using the following operations: • Deletion • Insertion • Updating • All these operations are expressed using the assignment operator

  33. Views • … if it is not desirable for all users to see the entire logical model (i.e., all the relations stored in the database) • A person who needs to know a customer’s loan number but has no need to see the loan amount can see the relation: customer-name, loan-number(borrower loan) • Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view.

  34. General Overview - rel. model • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  35. Overview - detailed • Relational tuple calculus • Why do we need it? • Details • Examples • Equivalence with rel. algebra • More examples; ‘safety’ of expressions • Rel. domain calculus + QBE

  36. Motivation • Q: weakness of rel. algebra? • A: procedural • describes the steps (i.e., ‘how’) • … still useful, for query optimization though

  37. Solution: rel. calculus • describes what we want • two equivalent flavors: • ‘tuple’ calculus and • ‘domain’ calculus • basis for SQL and QBE, resp.

  38. Rel. tuple calculus (RTC) • first order logic ‘Give me tuples ‘t’, satisfying predicate P – e.g.:

  39. ..or 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 r denotes that tuple t is in relation r • P is a formula similar to that of the predicate calculus

  40. Examples • find all student records output tuple of type ‘STUDENT’

  41. Examples • (selection) find student record with ssn=123

  42. Examples • (selection) find student record with ssn=123

  43. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B Reminder: our Mini-U db

  44. More examples • join: find names of students taking cis351

  45. More examples • join: find names of students taking cis351 join projection selection

  46. Safety of expressions • FORBIDDEN: It has infinite output!! • Instead, always use

  47. Safety of expressions • Possible to write tuple calculus expressions that generate infinite relations, e.g., {t |  t r } results in an infinite relation if the domain of any attribute of relation r is infinite • To guard against the problem, we restrict the set of allowable expressions to safe expressions. • An expression {t | P (t) }in the tuple relational calculus is safe if every component of t appears in one of the relations, tuples, or constants that appear in P

  48. General Overview - rel. model • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  49. Overview - detailed • rel. tuple calculus • why? • details • examples • equivalence with rel. algebra • more examples; ‘safety’ of expressions • rel. domain calculus + QBE

  50. Safety of expressions • FORBIDDEN: It has infinite output!! • Instead, always use

More Related