Relational Data Model

1. Relational Data Model

2. Relational Database Terminologies • Relation is a table with columns and rows • Attribute  named column of a relation • Domain set of allowable values for one or more attributes. • Each attribute has values taken from a domain. • For example, the domain of Name is string and that for salary is real • Tuple a row of the relation • Degree:- number of columns of the relation. Ex: the above table has degree 4 • Cardinality:- Number of tuples in a relation. Ex: the above table has cardinality=1 • Remark: often data type is taken for the domain; like char for the attribute gender, string(Text) for the attributes stID & stName attributes Tuple

3. Cont’d…

4. Formal Definition of a relation • Given a collection of n types of domain Ti (i=1,2, …, n) not necessarily all distinct, r is a relation on those types if it consists of two parts, a heading & a body, where: • Theheading is a set of n attributes of the form Ai: Ti, where the Ai (which must be distinct) are the attributes of r and the Ti are the corresponding type names ( i=1,2,..,n) • The body is a set of m tuples t, where t in turn is aset of components of the form Ai:Vi in which Vi is a value of type Ti

5. Cont’d… • Example: Consider the following • Heading= {StID: string, stName: string, score:Number} • Body= { {stID: 001, stName:Abebe, score:90}stID: 002, stName:Taye, score:50 }} • Q: the above relation has cardinality _______ and degree ____

6. Property of a relation • A relation should have a unique name • A relation should not contain duplicate tuples • Tuples are unordered (may not be ordered) • Similar to the set: {a, 2}={2,a} • Attributes are unordered • Each tuple contains exactly one value for each attribute

7. Relational Keys • Keys are used to enforce integrity rules • The 1st type of key is called super key • Super key:- an attribute, or set of attributes that uniquely identifies a tuple with in a relation Possible supper keys 1. {stID} 2. {stID, stName} 3. {stID, score} 4. {stID, stName, score} • Candidate key:- is a super key such that no proper subset of it is a super key with in the relation • Ex: {stID}

8. Cont’d… • Candidate Key (Key Attribute) • An attribute or set of attributes that uniquely identifies individual occurrences of an entity type. • Example: ID. No of a student, SSN of a person • Key attributes are very important in databases • A candidate key, k, for a relation R has two properties: • Uniqueness:- in each tuple of R, the values of k uniquely identify that tuple • Irreducibility:- no proper subset of k has the uniqueness property • Primary key:- it is a candidate key that is selected to identify tuples uniquely with in a relation • Ex: primary key: Region# or RegionName

9. Cont’d… • Primary Key • An entity type may have one or more possible candidate keys, one of which is selected to be the primary key. • Foreign Key:-A foreign key is a field in a relational table that matches the primary key column of another table. • The foreign key can be used to cross-reference tables. • Alternative terminologies • Relation table file • Tuple Row Record • Attribute Column Field

10. Cont’d… • Setting primary key will improve performance, for example one can search records by primary key • Foreign key:- is a set of attributes whose values are required to match values of some candidate key of the home or base relation • stID in the Grade relation is a foreign key and stID in the student relation is a candidate key Referencing relation Home relation

11. Cont’d… • A foreign key can contain null value • A foreign key can be simple or composite (Composite involves many attributes) • If a foreign key is composite then the home relation should contain composite candidate key • The foreign – to- candidate key match – “glue”

12. Relational Data Integrity • Data integrity rules ensures that the data is accurate and consistent • Three integrity rules • Domain Constraint • Entity integrity rule:- says a primary key can not contain a null value • Referential integrity rule:- if a freign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation (referenced relation) or the foreign key value must be wholly null • Null value:- is not a value. It is not zero or it is not the null string (“”)

13. Cont’d… • Causes for null value: • The value is not known at the time of data entry • The value for that attribute is not applicable for the current tuple • Example: Null value

14. Referential Action • Consider update and Delete operations • There are three rules: • CASCADE:- The update or Delete on the home relation (Referenced relation) cascades to update or delete the matching tuples in the referencing relation • If we delete B01 in Branch relation the 1st two records of Employee will be deleted • Updates if we update the home relation ( For example if B01 is changed to B05 in the Branch relation, then all B01’s in the Employee relation will be updated to B05)

15. Cont’d… • RESTRICTIVE (Enforce Referential Integrity) • The UPDATE or DELETE on the home relation (Referenced relation) will be restricted to the case where there are no such matching in the referencing relation • We can delete the 3rd record of the Branch relation • NO ACTION • The delete or update are performed exactly as requested, no more and no less • Can potentially cause referential integrity violation

16. Views • Base Relation: • A named relation corresponding to an entity in the conceptual schemas whose tuples are physically stored in the database • View: • The dynamic result of one or more relational operations operating on the base relations to produce another relation • A view is a virtual relation • Its definition is stored in the database but the records are not

17. SELECTstName, Address FROM student WHERE gender = “F” • is a definition stored in the database and becomes a table during runtime

18. Cont’d… • Views are important for:- • Security • For efficient use of memory • Easiness • Base relation is much faster than the views (low accessing speed) • A view is a relation that appears to the user as if it exists, manipulated as if it is a base relation • When we modify a view that modification will be propagated to the base relation and • Updating the base relation will propagate to the view

19. Advantages of Views • Provide security • To provide customized data • It simplifies complex operations • Views can provide logical data independence

20. Cont’d… • A view should be defined using simple query on a single or multiple relations SELECTstName, DeptName FROM student, Department WHEREstudent.DeptNo=Department.DeptNoAND gender = “F” Q. What is the result of the above operation (SQL statement)?

21. Next: Conceptual Data Model (ER Model)