Relational Schemas and Predicate Logic: Notation

1. Relational Schemas and Predicate Logic: Notation

2. Relations • Let E1,..,En be sets of entities or objects (we may have Ei = Ej). • The Cartesian product E1 x … x En is the set of tuples of the form (e1,..,en) where ei is in set Ei. • A relation R on E1,..,En is a subset of the Cartesian product E1 x … x En. We say that n is the arity of the relation. Note that a relation may be unary.

3. Relational Schemas • A schema specifies a finite set of relations R1,..Rn with additional structure: • Each column/field in a relation gets a name (can also just use position) and a domain. • A subset of fields is identified as the key. • The non-key fields are often called (descriptive) attributes. The values of the attributes are determined by the values of the key field. • Common Notation • Student(Name:string,GPA:numeric,Age:integer) • Registered(Name:string,Course:string,grade:numeric)

4. name ssn lot Employees ER Model: Entities • Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. • Entity Set: A collection of similar entities. E.g., all employees. • All entities in an entity set have the same set of attributes. • Each entity set has a key. • Each attribute has a domain. • A key defines the values of attributes---the attributes are functions of the keys.

5. name ER Model: Relationships ssn lot Employees since name dname • Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department. • Relationship Set: Collection of similar relationships. • An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1, ..., en • Same entity set could participate in different relationship sets, or in different “roles” in same set. super-visor subor-dinate ssn budget lot did Reports_To Works_In Employees Departments

6. Notes on ER relationships • Note that relationships can have descriptive attributes too. • The values of the descriptive attributes are determined once we have identified the entities involved: grade(Joe,CMPT354). • The fields that identify the entities involved are callled foreign key pointers.

7. Relational Instance and Finite Models • A relational instance is a relational schema + set of tuples specified for each relation. • The list <R1,..,Rn> of relations (with tuples specified, but without field names and key constraints) is called a finite model in logic.

8. Graphical Visualization • A graph on set S is a binary symmetic relation over S x S. • If the relation instance contains only one binary symmetric relation, it can be visualized as a graph whose edges and nodes are annotated with the values of descriptive attributes. • Classic social network analysis considers only the graph structure, not the attributes.

9. Translating Schemas Into Logic: no functions • Each relation of arity n  predicate symbol with n arguments. • E.g., “A student with GPA 3.0 is younger than 40”  Student(S,G,A) AND G = 3.0  A < 40. • Pros: • Simple Translation. • Simple logic. • Cons: • Loses information about key fields. • Not always natural to read.

10. Translating Schemas Into Logic: Functions • Introduce one function symbol for each descriptive attribute. The arguments are the key fields. • E.g., Age(S), grade(S,C). Also: S.age,Registered.grade. • E.g., “A student with GPA 3.0 is younger than 40”  GPA(S) = 3.0  Age(S) < 40. • Pros: • Keeps information about keys and foreign keys. • Natural to read. • Cons: a bit more complex mathematically.

11. Formal definitions in logic • Use the function-free formulation, with predicate symbols R1,..,Rn. • The language contains a set of constants and variables. • A term is a constant or a variable. • An atom is: • a predicate symbol with the required numbers of terms, e.g. Student(N,G,40), Student(Jack,3.0,40). • A comparison of terms, e.g. X > 3, X = Y, 5 > 1. • A literal is an atom or a negated atom.

12. Clauses • A clause is a set of literals. • The negated literals are called the body, the positive ones the head. • A clause is often written in implication form: b1 AND b2  h1. • Also h1 :- b1,b2. • A clause with a single positive literal is a Horn clause.