Understanding Relational Databases: Keys, Tuples, and Operations

1. Discussion #22Relational Data Model

2. Topics • Databases • Relational database • Relations and schemas • Keys • Tuples and predicates

3. Database • A persistent collection of information about various kinds of objects (persons, places, things, events). • Each object or entity can be characterized by a set of attributes or properties from a domain. e.g. person(SSN, Name, Address, Phone) • Each individual object has a value associated with it for each attribute; this is a tuple and forms a record. e.g. SSN: 123-45-6789 Name: Pat Carter Address: 12 Main Phone: 555-5555 . • A set of records is a file (relation, for a relational database). • A collection of files is a database (relations or tables, for a relational database). • Typical operations on a database: • insert – data retrieval • delete – summarize data (e.g. averages, totals, mins, …) • update – create/delete files • A database system helps people manage databases. • data storage • integrity / security • crash recovery

4. Relational Database Example snap cr cp cdh csg

5. Relational Schemas • Each relation has a schema • name • set of attributes • domain for each attribute • Example: • Names: snap, cp, cdh, cr, csg • Attributes: table headers • Domains: studentID: integer all the rest are strings, but we could be more specific (e.g. time, day, grade)

6. Relational Tables • Table tuples are n-tuples, where n is the arity or degree of the relation (i.e. the number of attributes). • Each n-tuple t D1  D2  …  Dn, where the Di’s are the domains. e.g. a 3-tuple t of cdh is an element string  string  string or string  day  time or course  day  time depending on how specific we make our domains. • A table is a set of tuples, all with the same schema e.g. cdh  Dcourse  Dday  Dhour

7. Tuple Sets (Relations, Tables) & Keys • Because a table is a set of tuples, there are no duplicates. Thus, there is always a set of attributes whose values uniquely identify a tuple (even if it is all of them). • A set of attributes whose values always uniquely identify a tuple constitutes a key. • Typically, one or two attributes make up a key. • Keys must be declared  we cannot assume uniqueness. e.g. Name is not a key  there could be another C. Brown • Some systems add a tuple identifier as the key.

8. Keys  Examples Table Key • snap StudentID Name, Address, Phone (possible key?) • cp Course Prerequisite • cdh Course Day Hour Can a course meet twice on the same day? If not: Course Day • cr Course Room Does a course always meet in the same room? If so: Course • csg Course StudentID

9. Predicates and Tuples • A table name for tuples of arity n is an n-place predicate. • cdh('CS101','M','9AM') • Asserts that CS101 meets on Monday at 9:00 am. • Predicates give each tuple a meaning in the ordinary sense of predicates. • The subset of D1 D2  …  Dn present in the database are those assigned T; all others are assigned F. (Closed-World Assumption) • Interpretation: • Domain • For each predicate and every substitution, T or F.

10. Database Tuples • Database tuples (strictly speaking) are not true subsets of D1 D2  …  Dn because we can alter the column order if we do so “correctly.” • More properly defined, a tuple in a relation is a set of attribute-value pairs. • e.g. {(Course, 'CS101'), (Day, 'M'), (Hour, '9AM') } = {(Day, 'M'), (Course, 'CS101'), (Hour, '9AM') } • Normally, we factor out the attribute and fix the order. • Implication: we can interchange columns. cr = Course Room = Room Course CS101 Turing Aud. Turing Aud. CS101 EE200 25 Ohm Hall 25 Ohm Hall EE200 PH100 Newton Lab. Newton Lab. PH100