1 / 25

RA examples

RA examples. Let’s build a Family DB. Describe a person by it’s name, year and day of birth and gender: Person( Name , Byear , Bday , gender);. ERD. Name. Bday. Byear. Gender. Person. ERD. Let’s add parents: Parent( Cname , Cyear , Cday , Role , Pname , Pyear , Pday ) (.

israel
Download Presentation

RA examples

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. RA examples

  2. Let’s build a Family DB • Describe a person by it’s name, year and day of birth and gender: • Person(Name, Byear, Bday, gender);

  3. ERD Name Bday Byear Gender Person

  4. ERD Let’s add parents: Parent(Cname, Cyear, Cday, Role, Pname, Pyear, Pday) ( Name Bday Byear Gender Person Parent Role

  5. ERD Is this ERD perfect? Can you make it closer to the real world? Name Bday Byear Gender Person Parent Parent Role Role

  6. Building the basic family tree • Finding a person’s parents: • Finding a person’s children: (basically the same)

  7. Building the basic family tree • Finding a person’s sibling: • ) • And if we only wanted brothers?

  8. Grandparents: • How do you find a person grandparents:

  9. Grandparents: • How would you change the previous Query if you only wanted grandmothers: • )) • Or: • )) • The 2 queries will return the same result if the DB is sane

  10. Grandparents: • How would you change the previous Query if you only wanted the grandparents on the mother’s side: • ))

  11. : • Definition: (by the recursion) • is a person’s parent. • is a person’s grandparent • is a person’s great-grandparent • Etc… • By this definition, what is a person’s ? • The person!

  12. : • How do you find a person’s ?(for a given k) ) • The length of the query depends on K

  13. Ancestors • Definition: • A is an ancestor of B if there exist a K such that • How would write a query to find all ancestors? • This seemingly cannot be done in RA! • (can be proved)

  14. Cousins • Definition: • two people are cousins if they share a common grandparent. • How two find a person’s cousins: • (

  15. K-Cousins • Definition: • two people are k-cousins if they share a common . • 0-cousin = sibling • 1-cousin = cousin • 2-cousin = share a common great-grandparent • How two find a person’s k-cousins:(for a given k) • (

  16. K-Cousins m-times removed • Definition: • Person A and person B are k-cousins m-times removed if A’s is B’s k-cousin. • k-cousin 0-times removed = k-cousin • k-cousin 1-times removed = parent is k-cousin • How two find a person’s k-cousins m-times removed:(for a given k,m) • (

  17. First-Born children • Definition: • A person is a first born if at least one of it’s parents doesn’t have any children older than the person.

  18. First-Born children • Query for finding first-borns: • All pairs of siblings: • All pairs of siblings in which the first sibling is younger than the second sibling: • All people that have an older sibling:

  19. First-Born children • Query for finding first-borns: (cont.) • All people that have a sibling: • All people that are an only-child: • All people that don’t have an older sibling: • All first-borh children (either oldest or only-child):

  20. Let’s add spouses… • We can make our DB a bit more complicated: Spouse(Name, Byear, Bday, SpouseName, SpouseByear, SpouseBday) Name Bday Byear Gender Person Spouse Parent Parent Role Role

  21. In-laws • Definition: • A person’s <relation>-in-laws our the person’s spouse’s <relation> • Parent-in-law = spouse’s parent • Sibling-in-law = spouse’s sibling • …

  22. In-laws • :=

  23. Question • Get the names of all the mothers that gave birth every year during the 80’s • (clue: use division)

  24. Answer • Get all the parents and the years in which they add a child: • Get all the females: • Get all the mothers and the years in which they gave birth:

  25. Answer • We will define the set of years in the 80’s: • Compute the final result using division:

More Related