UVA DEPARTMENT OF COMPUTER SCIENCERelational-1Relational ModelWhat is a relation?- the term relation is poor, but dominantly used- is not a mathematical relation, although it can be usedto implement one, or can be interpreted as N-ary relation- a two-dimensional table, representing a subset ofthe Cartesian product of a set of domainsSome specifics- entries in the table are single-valued (atomic)- entries in a single column (its heading is calledattribute) are of the same type and meaning- the type of an entry is called a domain: dom(Ai)- attribute name must be unique (order is irrelevant)<ex> Faculty (Name, Dept, Phone#, Phone#)Faculty (Name, Dept, Office-Ph#, Home-Ph#)UVA DEPARTMENT OF COMPUTER SCIENCERelational-2Relational ModelTerminology- attribute: column: field- tuple: row: record- value: entry: field value- relation: table: file- domain: domain: data typeA relation is not exactly a file- a relational schema is a set of attributes- a relation is a set of tuples- mathematically, elements of a set have no order amongthem, while records are physically stored, and therealways is an order among the records- relation tuples are unordered; no two tuples identicalUVA DEPARTMENT OF COMPUTER SCIENCERelational-3Notes on Relational DatabaseTime-invariant vs time-variant- relations are time-varying as tuples may beadded, deleted, or changed- assumption: relation schema is time-invariant(important when proving DB properties)<ex> keys must remain keys for all permissible data- integrity of DB must be preserved when updating data1) tuples must confirm to the schema2) values must be in the domain3) no two tuples have the same value in key attributesUVA DEPARTMENT OF COMPUTER SCIENCERelational-4Notes on Relational DatabaseNumber of relations- why do we need so many relations?- may be easier for user to consider only 1 relation<ex> combining two relations into oneDeposit (Branch-name, A#, Customer-name, Balance)Customer (Customer-name, Street, Customer-city)Acct (B-name, A#, C-name, Balance, Street, C-city)Potential problems- wasteful repetition- complicated update- null-value problem- information lostReducing the number of relations is not always beneficialUVA DEPARTMENT OF COMPUTER SCIENCERelational-5Notes on Relational DatabaseMulti-value domains- one way to represent N:M relationship<ex> Children-of (Name, Children)Doe, {John, Jane}Isaac, {Jacob}Kennedy, {Ted, Robert, John}- usually considered as a bad technique, requiringa complex DML to manipulate such relationsFirst normal form (1NF)- every domain value is atomic (not multi-valued)- can transform non-1NF into 1NF (normalize it)<ex> Child-of (Name, Child)Doe, JohnDoe, JaneIsaac, JacobUVA DEPARTMENT OF COMPUTER SCIENCERelational-6Data Manipulation LanguagesProcedural vs non-procedural- relational algebra- relational calculus- tuple calculus- domain calculusRelational algebra- procedural DML- operands: relations- relational operatorsbinary operators: union, intersection, set difference,join, Cartesian product, divisionunary operators: projection, selectionUVA DEPARTMENT OF COMPUTER SCIENCERelational-7Notes on Relational Algebra1) Each operator maps one or two relations into a new relationas a result.2) Some of the operators are basic, i.e., sufficient enoughto express any relational algebraic expressions:union, set difference, Cartesian product, projection,selection<ex> intersection can be performed by set difference:intersection (A, B) = A - (A - B)3) Some operations have requirements on operand relationsto allow the operation to successfully perform.<ex>