1 / 18

Relational Data Models

Relational Data Models. Matt Dube – Doctoral Student, Spatial Information Science and Engineering. Scenario.

dixie
Download Presentation

Relational Data Models

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 Models Matt Dube – Doctoral Student, Spatial Information Science and Engineering

  2. Scenario • Consider you are running a business. You are in the realms of marketing, advertising, and customer service. Since you are running the business, your time is stretched so thin based on the economic conditions… • In regards to an information system, what qualities do you need to make your marketing and advertising operations more efficient?

  3. What do I need and not need? • NEEDS: • Quick means for identification • Customer service • Required account data • Billing information • Differentiation • Strategic advertising • Information reduction • Only certain data important • Integrative • Larger scale company interaction • Purchase history • Who bought what? • TO AVOID: • Duplication • Resource allocation • Excessive table attributes • Difficulty in usage

  4. Relational Data Model • OMG! • That is a lot to worry about. • Thankfully, these things have already been designed

  5. Relational Databases • Definition of relation • Relations are tables that express data linkages • A set of operations on those relations • How to extract certain pieces from the relation • An algebra of relational operations • Most efficient ways to go about getting a particular result

  6. Relations • Since relations are tables, they have rows and columns • Each relation has a unique name • Each column represents an attribute about an entity • Each row represents a tuple of attributes linked to a particular entity

  7. Domain • Recall from mathematics what the domain is: • f(x) = y for x in X • X is the domain of the function • Domain is the set of possible values for a function • Domain in terms of a relational database is thus the relevant values to choose from for a particular attribute • Every attribute must have a domain, and that domain may differ for every attribute

  8. Example Domains • Let’s figure out the following relevant domains: • United States • Alphabet • MaineStreet Student ID Numbers • GPA • Baseball Positions • Land Use Types • Square Root of X • Message: every single idea you can conceive of has a set domain

  9. Domains vs. Types • Domain is the set of values that are permissible • Phone Numbers are ten digit numbers • Domains need not be unique to an attribute • Daytime Phone Numbers • Evening Phone Numbers • Type is the classification of an attribute • Number, String, Floating Point, etc. • Difference between these?

  10. Mathematical Definition of a Relation • A relation R is a subset of the Cartesian product of all of its n attribute domains Di • Cartesian product: think of the plane • A relation is a subset of D1 X D2 X … X Dn-1 X Dn

  11. The Set of Tuples • Recall that each row in a table is called a tuple • Since a relation is a table, it is a set of tuples • Important properties of tuples • All tuples are distinct • Order of tuples is not important, but can be imposed • Why are those properties important? Think in your disciplines…

  12. Red Sox Outfielders Relation

  13. Proof of Keys • Since all the tuples must be unique, there must be a unique identifier • Proof: • Select an arbitrary tupleA and a second arbitrary tupleB. Since each tuple is unique, there exists an attribute k such that A(k) ≠ B(k). Since A and B are arbitrary, all possible combinations of tuples are accounted for. Thus there exists a unique identifier (namely the whole tuple) to identify arbitrary A from arbitrary B.

  14. Keys (continued) • A key suggests there is a smaller way to query the relation to get the desired tuple • What conditions do we want for keys? • Small (fewest attributes possible) • Relevant (sensible for the relation’s purpose) • Multiple keys might exist

  15. Candidate Keys?

  16. Three Types of Keys • Candidate Key • A set of attributes known to be unique in the relation • Primary Key • Designer’s choice of the attribute identifier for its tuples • Composite Key • A key which involves more than one attribute

  17. Constraints for Relations • Attribute names are unique • What if they weren’t? • Domain values must be atomic • Think terminal symbols • What if they weren’t? • Every relation must have at least one key • We already proved that

  18. Wednesday • Relevant operators for relations • Constructing a relation out of code • HOMEWORK: Think of an example in your discipline where a relational data model could help. What attributes are important? What are their domains? What would you use to key the model? Why? • Bring to class on Wednesday to discuss (not collected)

More Related