180 likes | 257 Views
Relational Data Models. Matt Dube – Doctoral Student, Spatial Information Science and Engineering. Scenario.
E N D
Relational Data Models Matt Dube – Doctoral Student, Spatial Information Science and Engineering
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?
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
Relational Data Model • OMG! • That is a lot to worry about. • Thankfully, these things have already been designed
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
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
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
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
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?
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
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…
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.
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
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
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
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)