1 / 21

Announcements

Learn about the benefits of using the relational model in database management systems, including mathematical notation, separation of logical and physical levels, and expressive data operations.

lswint
Download Presentation

Announcements

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. Announcements • Reading for Monday • 4.6 • Homework 3 – Due 9/29

  2. The Relational Model Lecture 7

  3. Advantages of Relational Model • Based on mathematical notion of relation • Can use power of mathematical abstraction • Can develop body of results using theorem and proof method of mathematics – results then apply to many different applications • Can use expressive, exact mathematical notation • Theory provides tools for improving design • Basic structure is simple, easy to understand • Separates logical from physical level • Data operations easy to express, using a few powerful commands • Operations do not require user to know storage structures used

  4. Data Structures • Relations are represented physically as tables • Table • Rows • Column • Domains

  5. Properties of Tables • Each cell • Each column • Values in a column all come from the same domain • Each tuple • Order of tuples is immaterial

  6. Example of Relational Model • Student table tells facts about students • Faculty table shows facts about faculty • Class table shows facts about classes, including what faculty member teaches each • Enroll table relates students to classes

  7. Mathematical Relations • Cartesian product Let D1 = { +, - } Let D2 = {1,2,3} Calculate the Cartesian product

  8. Mathematical Relations • Relation • Example

  9. Describing Relations • Give rules for selection of the tuples that will be part of the relation

  10. Three sets • Could form Cartesian product of 3 sets; relation is any subset of the ordered triples so formed • A relation where the third element is the sum of the first two

  11. n-tuples • Could extend to n sets

  12. Database Relations • Relation schema, R, • Relation, r,

  13. Database Relations • r is a set of n-tuples (A1:d1, A2:d2, …, An:dn) such that d1є D1, d2єD2 , …, dnєDn

  14. Database Relations • In a table to represent the relation, list the Ai as column headings, and let the (d1, d2, …dn) become the n-tuples, the rows of the table

  15. Properties of Relations • Degree: • Cardinality: • Keys • Integrity constraints

  16. Relation Keys • Superkey: • Candidate key:

  17. Relation Keys • Primary key: • Cannot verify a key by looking at an instance; need to consider semantic information to ensure uniqueness • Foreign key

  18. Integrity Constraints • Integrity:

  19. Types of constraints • Domain constraint: • Entity integrity: • Referential integrity: • General constraints or business rules:

  20. Representing Relational Database Schemas • Can have any number of relation schemas • For each relation schema list name of relation followed by list of attributes in parentheses • Underline primary key in each relation schema • Indicate foreign keys (We use italics – arrows are best) • Database schema actually includes domains, views, character sets, constraints, stored procedures, authorizations, etc.

  21. University database schema Student (stuId, lastName, firstName, major, credits) Class (classNumber, facId, schedule, room) Faculty (facId, name, department, rank) Enroll(stuId,classNumber,grade)

More Related