1 / 18

Relational Data Model

Learn about relational data models and operations such as selection, projection, union, difference, intersection, cartesian product, join, views, and materialized views. Discover how these concepts are used to manipulate and retrieve data efficiently.

willow
Download Presentation

Relational Data Model

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. Relational Data Model Ervi Bongso Alan Fernando CS157A

  2. Outline • Sect. 6.4, Intro to Relational Algebra Comprise of: • Unary Operations:  (selection) and  (projection) • Binary Operations:  (union), - (difference),  (intersection), x (cartesian product) • Natural Join: * and Join: ⋈ • Sect. 6.5, Views and Materialized Views • Sect. 6.6, Info of Preservation Issue

  3. Unary:  (selection) Extract a horizontal subset of tuples from a relation that matches specified search criteria. Age≥34(Person) Person Age=Weight(Person)

  4. Unary:  (projection) Extract a vertical subset of columns from a relation. Employee ∏name(Employee)

  5.  (selection),  (projection) Employee ∏name(бSalary>80,000(Employee))

  6. Binary:  (union) Adds the tuples from 1 relation to those from a second relation to produce a third relation. Student Professor Student  Professor

  7. Binary: - (difference) Contains the tuples from the first relation that are not in the second relation. Student Professor Professor - Student Student - Professor

  8. Binary:  (intersection) Contains tuples common to the two relations. Student Professor Student  Professor

  9. Binary: x (cartesian product) • Does not have to be union compatible. • Produces a new element by combining every tuple from one set with every tuple from the other set. R×S = R(A1,A2,...,An) × S(B1,B2,...Bm) = Q(A1,A2,...,An,B1,B2,...Bm) with degree n + m attributes

  10. Join: ⋈ • Used to combine related tuples from two relations into single tuples. • Allows us to process relationships among relations. • Example: to retrieve the name of the manager of each department: Step1) Dept_mgr ← Dept ⋈ Mgr_Ssn=SsnEmployee Step2) Result ← πDname, Lname, Fnamer(Dept_mgr)

  11. Example  Join: ⋈

  12. Natural Join: * • Created to get rid of the second attribute. • Requires that the two join attributes have the same name. • If not, renaming must first be applied. • Example: DEPT_LOCS ← DEPARTMENT * DEPT_LOCATIONS

  13. Example  Natural Join: *

  14. Views and Materialized Views • Views • Allows the same data to be seen by different users in different ways at the same time. • Provides security • Hides data complexity from the user. • Growth and Restructuring • Growth is expansion of existing relation schema. • Restructuring is changes other than growth.

  15. Views and Materialized Views (cont.) • Materialized Views (Snapshot) • Stored in the database. • Refreshed when updates occur. • Used to freeze data. • Deleted when it is not used for a period of time.

  16. Issue of Info Reservation

  17. References • Elamstri and Navathe, Fundamentals of  Database Systems, 5th ed., Addison-Wesley, 2008 • Umanath and Scamell, Data Modeling and Database Design, Thomson 2007 • http://www.cs.sjsu.edu/faculty/lee/cs157/cs157a.html

  18. Thank You!

More Related