Relational Data Model - Terminology and Characteristics

1. RELATIONAL DATA MODEL - 1 DB: Relational Data Model - 1

2. Objectives • Terminology + • Characteristics of Relations + • Relational Data Model Notations + • Key constraints + • Others Constraints + DB: Relational Data Model - 1

3. - Terminology • A relation is a table with columns and rows. • Only applies to logical structure of the database, not the physical structure. • Attribute is a named column of a relation. • Domain is a set of allowable values for one or more attributes. • Tuple is a row of a relation. • Degree is a number of attributes in a relation. • Cardinality is a number of tuples in a relation. • Relational Database is a collection of relations. 8 DB: Relational Data Model - 1

4. - Terminology A relation is a table with columns and rows. Columns or Attributes Relation name t2[DOB] STUDENT Cardinality ID NAME NAT_ID DOB DEPTCODE 201111 KHALED 1111111111 16-05-1984 ICS 202222 ADEL 2222222222 23-09-1984 COE 203333 AHMED 3333333333 11-08-1984 COE 211111 HASSAN 4444444444 23-10-1985 SWE 204444 MUSTAFA 55555555555 16-11-1984 ICS Rows or Tuples Degree DB: Relational Data Model - 1

5. - Characteristics of Relations • Each relation in the same relational database schema has a distinct name • Each value in a tuple is atomic • Each attribute in a relation has a distinct name. • Values of an attribute are all from the same domain. • Each tuple is distinct. • Order of attributes has no significance. • Order of tuples has no significance, theoretically. DB: Relational Data Model - 1

6. - Relational Data Model Notations • The letters Q, R, S denote the abstract relation names. • R(A1, A2, A3, …., An) denotes a relation schema R of degree n. • Example: STUDENT(Name, Id, Phone, Address, Mobile, DOB) • Both t[Ai] and t.Ai refers to the value vi in t for attribute Ai • Example: in second tuple, both t[name] and t.name refers to “Adel” • Dom(DEPTCODE) = {‘COE’,’ICS’,’SWE’} DB: Relational Data Model - 1

7. Relation’s Definition Relation: A relation is a subset of Cartesian product of all domains R(r)  Dom(A1) x dom(A2) x …… x Dom(An) Where A1, A2, …., An represent attributes of a relation r. Evaluation: Given A1 = {1, 2}, A2 = {a}, A3 = {x, y} Then relation X = {(1, a, y), (2, a, x), (2, a, y)}; 3 tuples of a relation is a subset of Y = Dom(A1) x dom(A2) x Dom(A3) = {(1, a, x), (1, a, y), (2, a, x), (2, a, y)} Note: Degree of a relation is the number of attributes, in above case degree of relation is 3. Alternate Definition of Relation For each tuple ti is a mapping from R to D Where D = Dom(A1)  Dom(A2) …  Dom(An) And t[Ai]  Dom(Ai) Then a tuple can be considered as attribute and value. For example: The following representation shows a tuple definition t = <(id, 21587), (Name, Muhammad,), (Hphone, Null), (Wphone, 866-1141)> DB: Relational Data Model - 1

8. Example Evaluate Cartesian product of the following relations A & B: 1 2 3 a b c A = 4 5 6 B = x y z 7 8 9 1 2 3 a b c 1 2 3 x y z 4 5 6 a b c A x B= 4 5 6 x y z 7 8 9 a b c 7 8 9 x y z DB: Relational Data Model - 1

9. - Key Constraints • Superkey + • Candidate Key + • Primary Key + • Composite Keys + • Alternate Key + • Foreign Key + DB: Relational Data Model - 1

10. -- Superkey • An Attribute or a set of attributes that uniquely identify a tuple within a relation. • Example: • STU_ID, NAME, NAT_ID, DOB, DEPTCODE • STU_ID, NAME, NAT_ID • STU_ID • NAT_ID STUDENT STU_ID NAME NAT_ID DOB DEPTCODE DB: Relational Data Model - 1

11. -- Candidate Key • A superkey (K) such that no proper subset is a superkey within the relation. • In each tuple of R, the values of K uniquely identify that tuple (uniqueness). • No proper subset of K has the uniqueness property (irreducibility). • Example: • STU_ID • NAT_ID STUDENT NAME NAT_ID DOB STU_ID DEPTCODE STU_ID is a candidate key alone because {STU_ID} is a subset of X = {STU_ID, NAT_ID} and X do not has uniqueness property, X is not candidate key but X is super key. DB: Relational Data Model - 1

12. -- Candidate Key – cont. Interested in SK for which no proper subset a SK. Example-1: Customer(cname, natid, address, city) cname, address, city can be duplicated individually. The following combinations distinguish customer records or tuples.  {cname, address} {natid} {natid, cname}  As {natid} {natid, cname}, then {natid, cname} is not candidate key cname is not SK because several cname might have the same name. Example-2: Branch(branch-name, assets, branch-city) SK: {branch-name}, {branch-name, branch-city}; PK: {branch-name} Candidate Key: {branch-name} because {branch-name}  {branch-name, branch-city} {branch-city} is not SK because two different branches can be in same city. Exercise: Course(cid, cname, deptno) Semester(sid, syear, startdate) ClassAllocation(sid, cid, sec#, building#, room#) Identify Superkeys, candidate keys, alternate keys of each of the above relations DB: Relational Data Model - 1

13. -- Primary Key • PK is a candidate key selected to identify tuples uniquely within a relation. • A primary key is a minimal identifier that is used to identify tuples uniquely. This means that no subset of the primary key is sufficient to provide unique identification of tuples. NULL value is not allowed in primary key attribute. Example: • STU_ID (Practically PK must have UNIQUE, NOT NULL, INDEXED on an attribute) STUDENT NAME NAT_ID DOB STU_ID DEPTCODE DB: Relational Data Model - 1

14. -- Composite Keys • Combination of primary keys of selected attributes gives concept of composite key. composite key is a extended form of primary key • Example: • {SID, CID, SEC#} COURSE-ALLOCATION CID SEC# BUILDING# SID ROOM# Alternatively, for all semesters SEM-ALLOCAT(SID, SEMID, CID, SEC#, BUILDING#, ROOM#) Following is not valid because duplicate rows and blank columns can be possible SEM-ALLOCAT(ID, SID, SEMID, CID, SEC#, BUILDING#, ROOM#) DB: Relational Data Model - 1

15. DB: Relational Data Model - 1

16. -- Alternate Key or Secondary Key • candidate keys that are not selected to be the primary key. • Example: • NAT_ID STUDENT NAME NAT_ID DOB STU_ID DEPTCODE DB: Relational Data Model - 1

17. -- Foreign Key • An attribute or a set of attributes within one relation that matches the candidate key (or PK) of some (possibly the same) relation. DEPARTMENT DEPTNAME DEPTCODE Foreign key STUDENT NAME NAT_ID DOB STU_ID DEPTCODE DB: Relational Data Model - 1

18. -- Foreign Key cont. Deptno is a PK in DEPT table* Empno is a PK in EMP table Deptno is a FK in EMP table* (it references to an attribute Deptno of DEPT table) NOTE: An attribute within one relation that matches the key (Primary/same) in the same relation, is called recursive relation DB: Relational Data Model - 1

19. - Other Constraints • Null: Represents a value of an attribute that is currently unknown or is no applicable for this tuple, means nothing. • Entity integrity Constraint: In a base relation, no attribute of a primary key can be null. • Referential Integrity Constraint: If a foreign key exists in a relation, either the foreign key value must match a candidate key (or PK) value of some tuple in its home relation or the foreign key value must be wholly null. • Domain Constraint: Specifies that the value of attribute A must be an atomic value from the domain DOM(A). • Additional Constraints: Default, NOT NULL, UNIQUE, CHECK; Business Constraints using DB triggers and client’s programming DB: Relational Data Model - 1