Relational Data Model

Prof P Sreenivasa Kumar Department of CS&E, IITM 1 Relational Model􀂃Proposed by Edgar. F. Codd(1923-2003) in the early seventies. [ Turing Award –1981 ]􀂃Most of the modern DBMS are relational.􀂃Simple and elegant model with a mathematical basis.􀂃Led to the development of a theory of data dependenciesand database design.􀂃Relational algebra operations –crucial role in query optimization and execution.􀂃Laid the foundation for the development of 􀂃Tuple relational calculus and then􀂃Database standard SQLIntroductionProf P Sreenivasa Kumar Department of CS&E, IITM 2 Relation Scheme􀂃Consists of relation name, and a set of attributes or field names or column names. Each attribute has an associated domain. 􀂃Example:student ( studentName: string,rollNumber: string,phoneNumber: integer,yearOfAdmission : integer,branchOfStudy : string )􀂃Domain–set of atomic(or indivisible) values –data type RelationnameAttributenamesdomainsProf P Sreenivasa Kumar Department of CS&E, IITM 3 Relation Instance􀂃A finite setof tuples constitute a relation instance.􀂃A tuple of relation with scheme R = (A1, A2, …, Am) is an ordered sequence of values(v1,v2, ... ,vm) such that vi∈domain (Ai), 1≤i ≤mNo duplicate tuples ( or rows ) in a relation instance.We shall later see that in SQL, duplicate rows would be allowed in tables.studentNamerollNumberyearOfAdmissionbranchOf StudyphoneNumberRavi TejaCS05B0152005CS9840110489studentRajeshCS04B1252004EC9840110490…Prof P Sreenivasa Kumar Department of CS&E, IITM 4 Another Relation Exampleenrollment (studentName, rollNo, courseNo, sectionNo)studentNamerollNumbercourseNosectionNoenrollmentRajeshRajeshSureshCS04B125CS04B125CS04B130212…CS320CS370CS320Prof P Sreenivasa Kumar Department of CS&E, IITM 5 Keys for a Relation (1/2)•Key: A set of attributes K, whose values uniquely identify a tuple in anyinstance. And none of the proper subsets of K has this propertyExample: {rollNumber} is a key for studentrelation.{rollNumber, name} –values can uniquely identify a tuple•but the set is not minimal •not a Key •A key can not be determined from any particular instance data􀂃it is an intrinsic property of a scheme􀂃it can only be determined from the meaning of attributesProf P Sreenivasa Kumar Department of CS&E, IITM 6 Keys for a Relation (2/2)􀂃A relation can have more than one key.􀂃Each of the keys is called a candidatekeyExample: book (isbnNo, authorName, title, publisher, year)(Assumption : books have only one author ) Keys: {isbnNo}, {authorName, title}􀂃A relation has at least one key-the set of all attributes, in case no proper subset is a key.􀂃Superkey: A set of attributes that contains any key as a subset.􀂃A key can also be defined as a minimal superkey􀂃Primary Key: One of the candidate keys chosen for indexingpurposes ( More details later…)Prof P Sreenivasa Kumar Department of CS&E, IITM 7 Relational Database Scheme and InstanceRelational database scheme: Dconsist of a finite no. of relation schemes and a set Iof integrity constraints.Integrity constraints: Necessary conditions to be satisfied bythe data values in the relational instances so that the setof data values constitute a meaningful database•domain constraints•key constraints•referential integrity constraintsDatabase instance: Collection of relational instances satisfyingthe integrity constraints. Prof P Sreenivasa Kumar Department of CS&E, IITM 8 Domain and Key Constraints•Domain Constraints: Attributes have associated domains Domain–set of atomic data values of a specific type. Constraint–stipulates that the actual values of an attribute in any tuple mustbelong to the declared domain.•Key Constraint: Relation scheme –associated keysConstraint –if Kis supposed to be a key for scheme R,any relation instance r on Rshould not have two tuples that have identical values for attributes in K. Also, none of the key attributes can have nullvalue.Prof P Sreenivasa Kumar Department of CS&E, IITM 9 Foreign Keys•Tuples in one relation, say r1(R1), often need to refer to tuples in another relation, say r2(R2)•to capture relationships between entities•Primary Key of R2: K= {B1, B2, …, Bj}•A set of attributes F= {A1, A2, …, Aj} of R1such that dom(Ai) = dom(Bi), 1≤i ≤j andwhose values are used to refer to tuples in r2is called a foreign keyin R1referringto R2.•R1, R2can be the same scheme also.•There can be more than one foreign key in a relation schemeProf P Sreenivasa Kumar Department of CS&E, IITM 10 Foreign Key –Examples(1/2)Foreign key attribute deptNoof courserelation refers toPrimary key attribute deptIDof departmentrelationCourse DepartmentcourseIdnamecreditsdeptNoCS635ALGORITHMS31CS636A.I41ES456D.S.P32ME650AERO DYNAMIC33deptIdnamehodphone1COMPUTER SCIENCE CS01225762352ELECTRICAL ENGGES01225762343MECHANICAL ENGGME0122576233Prof P Sreenivasa Kumar Department of CS&E, IITM 11 Foreign Key –Examples(2/2)It is possible for a foreign key in a relation to refer to the primary key of the relation itselfAn Example:univEmployee ( empNo, name, sex, salary, dept, reportsTo)reportsTois a foreign key referring to empNoof the same relationEvery employee in the university reports to some other employee for administrative purposes-except the vice-chancellor, of course!Prof P Sreenivasa Kumar Department of CS&E, IITM 12 Referential Integrity Constraint (RIC)•Let Fbe a foreign key in scheme R1referring to scheme R2and let Kbe the primary key of R2.•RIC: any relational instance r1on R1, r2on R2must be s.t for any tuple tin r1, either its F-attribute values are nullor they are identical to the K-attribute values of sometuple in r2.•RIC ensures that references to tuples in r2are for currently existingtuples.•That is, there are no danglingreferences. Prof P Sreenivasa Kumar Department of CS&E, IITM 13 Referential Integrity Constraint (RIC) -ExamplecourseIdnamecreditsdeptNoCS635ALGORITHMS31CS636A.I41ES456D.S.P32ME650AERO DYNAMIC33CE751MASS TRANSFER34deptIdnamehodphone1COMPUTER SCIENCECS01225762352ELECTRICAL ENGG.ES01225762343MECHANICAL ENGG.ME0122576233COURSE DEPARTMENTThe new course refers to a non-existent department and thusviolates the RICProf P Sreenivasa Kumar Department of CS&E, IITM 14 Example Relational Schemestudent (rollNo, name, degree, year, sex, deptNo, advisor)Here, degreeis the program ( B Tech, M Tech, M S, Ph D etc) for which the student has joined. Yearis the year of admission and advisoris the EmpIdof a faculty member identified as the student’s advisor.department (deptId, name, hod, phone) Here, phoneis that of the department’s office.professor (empId, name, sex, startYear, deptNo, phone)Here, startYearis the year when the faculty member hasjoined the department deptNo.Prof P Sreenivasa Kumar Department of CS&E, IITM 15 Example Relational Schemecourse (courseId, cname, credits, deptNo)Here, deptNoindicates the department that offers the course.enrollment (rollNo, courseId, sem, year, grade)Here, semcan be either “odd”or “even”indicating the two semesters of an academic year. The value of gradewill be null for the current semester and non-null for past semesters.teaching (empId, courseId, sem, year, classRoom)preRequisite(preReqCourse, courseID)Here, if (c1, c2) is a tuple, it indicates that c1 should besuccessfully completed before enrolling for c2.Prof P Sreenivasa Kumar Department of CS&E, IITM 16 Example Relational Schemestudent (rollNo, name, degree, year, sex, deptNo, advisor)department (deptId, name, hod, phone) professor (empId, name, sex, startYear, deptNo, phone)course (courseId, cname, credits, deptNo)enrollment (rollNo, courseId, sem, year, grade)teaching (empId, courseId, sem, year, classRoom)preRequisite(preReqCourse, courseID)queries-1queries-2queries-3TCQueryProf P Sreenivasa Kumar Department of CS&E, IITM 17 Example Relational Scheme with RIC’sshownstudent (rollNo, name, degree, year, sex, deptNo, advisor)department (deptId, name, hod, phone) professor (empId, name, sex, startYear, deptNo, phone)course (courseId, cname, credits, deptNo)enrollment (rollNo, courseId, sem, year, grade)teaching (empId, courseId, sem, year, classRoom)preRequisite(preReqCourse, courseID)Prof P Sreenivasa Kumar Department of CS&E, IITM 18 Relational Algebra􀂃A set of operators (unary and binary) that take relation instances as arguments and return new relations.􀂃Gives a procedural method of specifying a retrieval query.􀂃Forms the core component of a relational query engine.􀂃SQL queries are internally translated into RA expressions.􀂃Provides a framework for query optimization. RA operations:select(σ), project(π), cross product(×),union(⋃), intersection(∩), difference(−), join ( ⋈)Prof P Sreenivasa Kumar Department of CS&E, IITM 19 The select Operator􀂃Unary operator.􀂃can be used to selectthose tuples of a relation that satisfy a given condition.􀂃Notation: σθ ( r) σ : select operator ( read as sigma)θ: selection conditionr: relation name􀂃Result: a relation with the same schema as rconsisting of the tuples in rthat satisfy condition θ􀂃Select operation is commutative:σc1 (σc2( r)) = σc2 (σc1( r)) Prof P Sreenivasa Kumar Department of CS&E, IITM 20 Selection Condition•Select condition: Basic condition or Composite condition•Basic condition:Either Ai Ajor Ai c•Composite condition:Basic conditions combined with logical operators AND, OR and NOT appropriately.•Notation: : one of < , ≤, > , ≥, = , ≠Ai, Aj: attributes in the scheme Rof rc : constant of appropriate data typeProf P Sreenivasa Kumar Department of CS&E, IITM 21 Examples of selectexpressions1.Obtain information about a professor with name “giridhar”σname= “giridhar”(professor)2.Obtain information about professors who joined the university between 1980 and 1985σstartYear≥1980 ^ startYear < 1985(professor) Prof P Sreenivasa Kumar Department of CS&E, IITM 22 The projectOperator􀂃Unary operator. 􀂃Can be used to keep only the required attributes of arelation instance and throw away others.􀂃Notation: πA1,A2, …,Ak(r )where A1,A2, …,Akis a list L ofdesired attributes in the scheme of r. 􀂃Result = { (v1,v2, …,vk) | vi∈dom(Ai) , 1≤i ≤k and there is some tuple t in r s.tt.A1 = v1, t.A2 = v2, …, t.Ak = vk} 􀂃If r1= πL(r2) then scheme of r1is L Prof P Sreenivasa Kumar Department of CS&E, IITM 23 Examples of projectexpressionsrollNonamedegreeyearsexdeptNoadvisor CS04S001MaheshM.S2004M1CS01CS03S001RajeshM.S2003M1CS02CS04M002PiyushM.E2004M1CS01ES04M001DeepakM.E 2004M2ES01ME04M001LalithaM.E2004F3ME01ME03M002MaheshM.S2003M3ME01studentrollNonameCS04S001MaheshCS03S001RajeshCS04M002PiyushES04M001DeepakME04M001LalithaME03M002Maheshπname(σdegree= “M.S”(student))nameMaheshRajeshNote: Mahesh is displayed only once because project operation results in a set. πrollNo, name(student)Prof P Sreenivasa Kumar Department of CS&E, IITM 24 Size of projectexpression result􀂃If r1= πL(r2) then scheme of r1is L􀂃What about the number of tuples in r1?􀂃Two cases arise:􀂃Projection List L contains some key of r2􀂃Then |r1| =|r2|􀂃Projection List L does not contain any key of r2􀂃Then |r1| ≤|r2|Prof P Sreenivasa Kumar Department of CS&E, IITM 25 Set Operators on Relations•As relations are sets of tuples, set operations are applicableto them; but not in all cases.•Union Compatibility: Consider two schemes R1, R2where R1= (A1, A2, …, Ak); R2= (B1, B2, …, Bm)•R1and R2are called union-compatibleif•k = m and •dom(Ai)= dom(Bi)for 1 ≤i ≤k•Set operations–union, intersection, difference•Applicable to two relations if their schemes are union-compatible•If r3= r1⋃r2, scheme of r3is R1(as a convention)Prof P Sreenivasa Kumar Department of CS&E, IITM 26 Set Operationsr1-relation with scheme R1r2-relation with scheme R2-union compatible with R1r1⋃r2= {t | t ∈r1or t ∈r2};r1∩r2= {t | t ∈r1and t ∈r2}r1−r2= {t | t ∈r1and t ∉r2};By convention, in all the cases, the scheme of the resultis that of the first operand i.e r1.Prof P Sreenivasa Kumar Department of CS&E, IITM 27 Cross productOperation11121111211112121222....1112112..................mnmnmtttnaaabbbaaabbbaaabbb 21222111212122221222....2122212..................mnmnmtttnaaabbbaaabbbaaabbb 1212......mnAAABBB 112...mrAAA 1112121222......mmaaaaaa 12...sssmaaa 212...nrBBB 1112121222......nnbbbbbb 12...tttnbbb r1 : stuplesr2 : ttuplesr1 ×r2r1 ×r2 : s ×ttuples...Prof P Sreenivasa Kumar Department of CS&E, IITM 28 Example Query using cross productObtain the list of professors along with the name of their departments •profDetail(eId, pname,deptno) ←πempId, name, deptNo(professor)•deptDetail(dId,dname) ←πdeptId, name(department)•profDept←profDetail ×deptDetail•desiredProfDept←σdeptno= dId(profDept)•result ←πeld, pname, dname(desiredProfDept)Prof P Sreenivasa Kumar Department of CS&E, IITM 29 JoinOperation•Cross product: produces all combinations of tuples •often only certain combinations are meaningful•cross product is usually followed by selection•Join: combines tuples from two relations provided they satisfy a specified condition (join condition)•equivalent to performing cross productfollowed by selection•a very useful operation•Depending on the type of condition we have•theta join•equi join Prof P Sreenivasa Kumar Department of CS&E, IITM 30 Theta join•Let r1-relation with scheme R1= (A1,A2,…,Am)r2-relation with scheme R2= (B1,B2,…,Bn)and R1 ∩R2= φ•Notation for join expression : r1⋈θr2, θ-join conditionθis of the form : C1^ C2^ …^ CsCiis of the form : Aj Bk : = , ≠, < , ≤, > , ≥•Scheme of the result relationQ= (A1,A2,…,Am,B1,B2,…,Bn)•r = {(a1,a2,…,am,b1,b2,…,bn) (a1,a2,…,am) r1,(b1,b2,…,bn) r2and (a1,a2,…,am ,b1,b2,…,bn)satisfies θ} ∈∈Prof P Sreenivasa Kumar Department of CS&E, IITM 31 empIdnamesexstartYeardeptNophone CS01GIRIDHARM1984122576345CS02KESHAV MURTHYM1989122576346ES01RAJIV GUPTHAM1980222576244ME01TAHIR NAYYARM1999322576243ProfessorDepartmentdeptIdnamehodphone 1Computer ScienceCS01225762352Electrical Engg.ES01225762343Mechanical Engg.ME0122576233courseIdcnamecreditsdeptNoCS635Algorithms31CS636A.I41ES456D.S.P32ME650Aero Dynamics33CoursesProf P Sreenivasa Kumar Department of CS&E, IITM 32 ExamplesFor each department, find its name and the name, sex and phone number of the head of the department.Prof (empId, p-name, sex, deptNo, prof-phone) ←πempId, name, sex, deptNo, phone(professor)Result←πDeptId, name, hod, p-name, sex, prof-phone(Department ⋈(empId= hod) ^ (deptNo= deptId) Prof)deptIdnamehodp-namesexprof-phone1Computer ScienceCS01GiridherM225762352Electrical Engg.EE01Rajiv GupthaM225762343Mechanical Engg.ME01Tahir NayyarM22576233Prof P Sreenivasa Kumar Department of CS&E, IITM 33 Equi-joinand Natural join•Equi-join: Equality is the only comparison operator used in thejoin condition•Natural join: R1, R2-have common attributes, say X1,X2,X3•Join condition:(R1.X1= R2.X1) ^ (R1.X2= R2.X2) ^ (R1.X3= R2.X3)•values of common attributes should be equal•Schema for the result Q = R1 ⋃(R2-{X1, X2, X3 })•Only one copy of the common attributes is kept•Notation for natural join : r = r1* r2Prof P Sreenivasa Kumar Department of CS&E, IITM 34 Examples –Equi-joinFind courses offered by each departmentdeptIdnamecourseIdcnamecredits1Computer ScienceCS635Algorithms31Computer ScienceCS636A.I42Electrical Engg.ES456D.S.P33Mechanical Engg.ME650Aero Dynamics3πdeptId, name, courseId, cname, credits(Department ⋈(deptId= deptNo) Courses)Prof P Sreenivasa Kumar Department of CS&E, IITM 35 empIdcourseIdsemyearclassRoomCS01CS63512005BSB361CS02CS63612005BSB632ES01ES45622004ESB650ME650ME0112004MSB331TeachingTo find the courses handled by each professorProfessor * TeachingresultempIdnamesexstartYeardeptNophonecourseIdsemyearclassRoomCS01GiridharM1984122576345CS63512005BSB361CS02Keshav MurthyM1989122576346CS63612005BSB632ES01Rajiv GupthaM1989222576244ES45622004ESB650ME01Tahir NayyarM1999322576243ME65012004MSB331Prof P Sreenivasa Kumar Department of CS&E, IITM 36 Division operator􀂃The necessary condition to determine r soninstances r(R) and s(S) is S ⊆R􀂃The relation r s is a relation on schema R –S.A tuple t is in r s if and only if1) t is in πR-S(r)2) For every tuple tsin s, there is trin r satisfying botha) tr[S] = tsb) tr[R –S] = t•Another Definitionr = r1r2Division operator produces a relation R(X) that includes alltuples t[X] in R1(Z) that appear in R1in combination with every tuple from R2(Y) where Z = X ⋃Y ÷÷÷÷Prof P Sreenivasa Kumar Department of CS&E, IITM 37 R = (A, B, C, D), S = (A, B), X = (C, D)x = r s÷1 12 2A Ba ba b1 1112 2111 1221 1332 233A B C Da b c da b c da b c da b c da b c dsrx(c2, d2) is not present in the result of division as it does not appearin combination with allthe tuples of s in rc1 d1c3 d3C DProf P Sreenivasa Kumar Department of CS&E, IITM 38 Find those students who have registered for allcourses offeredin dept of Computer Science.Step1: Get the course enrollment information for all studentsstudEnroll←πname, courseId(student * enrollment)Step2: Get the course Ids of all courses offered by CS deptcsCourse←πcourseId(σdname= “computer science”(courses ⋈deptId= deptNodept))Result: studEnrollcsCourseQuery using division operation÷SchemaProf P Sreenivasa Kumar Department of CS&E, IITM 39 Suppose result of step 1 isnamecourseIdMaheshCS635MaheshCS636RajeshCS635PiyushCS636Piyush CS635DeepakES456LalithaME650MaheshME650courseIdCS635CS636result of step 2studEnrollcsCourseresultstudEnrollcsCoursenameMaheshPiyush÷Let’s assume for a moment that student names are unique!Prof P Sreenivasa Kumar Department of CS&E, IITM 40 Complete Set of Operators•Are all Relational Algebra operators essential ?Some operators can be realized through other operators•What is the minimal set of operators ?•The operators {σ, π, ×, ⋃, -}constitute a completeset of operators•Necessary and sufficient set of operators.•Intersection –union and difference•Join –cross product followed by selection•Division –project, cross product and difference Prof P Sreenivasa Kumar Department of CS&E, IITM 41 Example QueriesRetrieve the list of female PhD students σdegree = ‘phD’^ sex = ‘F’(student)Obtain the name and rollNo of all female Btech studentsπrollNo, name(σdegree = ‘BTech’^ sex = ‘F’(student))Obtain the rollNo of students who never obtained an ‘E’gradeπrollNo(σgrade ≠‘E’(enrollment)) is incorrect!! (what if some student gets E in one course and A in another?)πrollNo(student) –πrollNo(σgrade = ‘E’(enrollment))SchemaProf P Sreenivasa Kumar Department of CS&E, IITM 42 Obtain the department Ids for departments with no lady professor πdeptId(dept) –πdeptId(σsex = ‘F’(professor))Obtain the rollNo of girl students who have obtained at least one S gradeπrollNo(σsex = ‘F’(student)) ∩πrollNo(σgrade = ‘S’(enrollment))More Example QueriesProf P Sreenivasa Kumar Department of CS&E, IITM 43 Obtain the names, roll numbers of students who have got S grade in the CS370 course offered in 2006 odd semester along with his/her advisor name.reqStudsRollNo←πrollNo(σcourseId=‘CS370’& year =‘2006’& semester = ‘odd’&grade=‘S’(enrollment))reqStuds-Name-AdvId( rollNo, sName, advId) ←πrollNo, name, advisor(reqStudsRollNo*student)result( rollNo, studentName, advisorName) ←πrollNo, sName, name(reqStuds-Name-AdvId⋈advId=empIdprofessor)Another Example QuerySchemaProf P Sreenivasa Kumar Department of CS&E, IITM 44 Obtain the courses that are either direct or indirect prerequisites of the course CS767. •Indirect prerequisite –(prerequisite of )+a prerequisite course•Prerequisites at all levels are to be reportedlevelOnePrereq(cId1) ←πpreReqCourse(σcourseId=‘CS767’(preRequisite))levelTwoPrereq(cId2) ←πpreReqCourse(preRequisite⋈courseId=cId1 levelOnePrereq))Similarly, level kprerequisites can be obtained.But, prerequisites at all levels can not be obtained as there isno looping mechanism.Transitive Closure QueriesSchemaProf P Sreenivasa Kumar Department of CS&E, IITM 45 􀂃Theta join, equi-join, natural join are all called inner joins. Theresult of these operations contain only the matching tuples􀂃The set of operations called outer joinsare used when alltuples in relation ror relation sor both in rand shaveto be in result.There are 3 kinds of outer joins:Left outer joinRight outer joinFull outer join Outer Join Operation (1/2)Prof P Sreenivasa Kumar Department of CS&E, IITM 46 Left outer join: rsIt keeps all tuples in the first, or left relation rin the result. For some tuple tin r, if no matching tuple is found in sthen S-attributes of tare made null in the result.Right outer join: rsSame as above but tuples in the second relation are all keptin the result. If necessary, R-attributes are made null.Full outer join: rsAll the tuples in both the relations rand sare in the result.Outer Join Operation (2/2)Prof P Sreenivasa Kumar Department of CS&E, IITM 47 Instance Data for ExamplesrollNonamedegreeyearsexdeptNoadvisor CS04S001MaheshM.S2004M1CS01CS05S001AmrishM.S2003M1nullCS04M002PiyushM.E2004M1CS01ES04M001DeepakM.E 2004M2nullME04M001LalithaM.E2004F3ME01ME03M002MaheshM.S2003M3ME01StudentempIdnamesexstartYeardeptNophone CS01GIRIDHARM1984122576345CS02KESHAV MURTHYM1989122576346ES01RAJIV GUPTHAM1980222576244ME01TAHIR NAYYARM1999322576243ProfessorProf P Sreenivasa Kumar Department of CS&E, IITM 48 Left outer jointemp ←(student advisor = empIdprofessor)ρrollNo, name, advisor(πrollNo, student.name, professor.name(temp)) rollNonameadvisorCS04S001MaheshGiridharCS05S001AmrishNullCS04M002PiyushGiridharES04M001DeepakNullME04M001LalithaTahir NayyerME03M002MaheshTahir NayyerResultProf P Sreenivasa Kumar Department of CS&E, IITM 49 Right outer join temp ←(student advisor = empIdprofessor)ρrollNo, name, advisor(πrollNo, student.name, professor.name(temp)) rollNonameadvisorCS04S001MaheshGiridharCS04M002PiyushGiridharnullnullKeshav MurthynullnullRajiv GupthaME04M001LalithaTahir NayyerME03M002MaheshTahir NayyerResultProf P Sreenivasa Kumar Department of CS&E, IITM 50 Full outer join temp ←(student advisor = empIdprofessor)ρroll no, name, advisor(πroll No, student.name, professor.name(temp))rollNonameadvisorCS04S001MaheshGiridharCS04M002PiyushGiridharCS05S001AmrishNullES04M001DeepakNullnullnullKeshav MurthynullnullRajiv GupthaME04M001LalithaTahir NayyerME03M002MaheshTahir NayyerResultProf P Sreenivasa Kumar Department of CS&E, IITM 51 E/R diagrams to Relational Schema􀂃E/R model and the relational model are logical representationsof real world enterprises􀂃An E/R diagram can be converted to a collection of tables􀂃For each entity set and relationship set in E/R diagram we canhave a corresponding relational table with the same name as entity set /relationship set􀂃Each table will have multiple columns whose names are obtainedfrom the attributes of entity types/relationship typesProf P Sreenivasa Kumar Department of CS&E, IITM 52 Relational representation of strong entity sets􀂃Create a table Tifor each strong entity set Ei.􀂃Include simple attributes and simple componentsof composite attributes of entity set Eias attributes of Ti.􀂃Multi-valued attributes of entities are dealt with separately. 􀂃The primary key of Eiwill also be the primary key of Ti.􀂃The primary key can be referred to by other tables via foreign keys in them to capture relationships as we see laterProf P Sreenivasa Kumar Department of CS&E, IITM 53 Relational representation of weak entity sets􀂃Let E' be a weak entity owned by a strong/weak entity E􀂃E' is converted to a table, say R'􀂃Attributes of R'will be 􀂃Attributes of the weak entity set E' and􀂃Primary key attributesof the identifying strong entity E􀂃(Or, partial key of E + primary key of the owner of E, if E is itself a weak entity)•These attributes will also be a foreign key in R' referring to the table corresponding to E􀂃Key of R': partial key of E' + Key of E􀂃Multi-valued attributes are dealt separately as described laterProf P Sreenivasa Kumar Department of CS&E, IITM 54 ExampleCourseSectionhasSectionNameCourseIDCreditsSectionNoYearRoomNoProfessorCorresponding tables arecourseIdnamecreditssectionNocourseIdyearroomNoprofessorcoursesectionPrimary key of section= {courseId, sectionNo}Prof P Sreenivasa Kumar Department of CS&E, IITM 55 Relational representation of multi-valued attributes􀂃One table for each multi-valued attribute􀂃One column for this attribute and􀂃One column for the primary key attribute of entity /relationshipset for which this is an attribute. Student RollNoNameEmailIdrollNonameemailIdrollNoe.g.,studentmailIdsProf P Sreenivasa Kumar Department of CS&E, IITM 56 Handling Binary 1:1 Relationship􀂃Let S and T be entity sets in relationship R and S', T'be the tables corresponding to these entity sets􀂃Choose an entity set which has total participation if there isone (says, S)􀂃Include the primary key of T'as a foreign key in S' referring to relation T'􀂃Include all simple attributes (and simple components of composite attributes) of R as attributes of S' 􀂃We can do the other way round too–lot of null valuesProf P Sreenivasa Kumar Department of CS&E, IITM 57 ExampleSTUDENTHostelRoomresidesInRollNoNamehomeAddressHostelNameRoomNoNote: Assuming every student resides in hostel.S-STUDENT R-residesInT-Hostel RoomRollNoNamehomeAddressRoomIdStudentRoomNoHostelNameaddressHostelForeign key name need not be same as primary keyof the other relation11addressProf P Sreenivasa Kumar Department of CS&E, IITM 58 Handling 1:N Relationship􀂃Let S be the participating entity on the N-side and T the otherentity. Let S'and T'be the corresponding tables.􀂃Include primary key of T'as foreign key in S'􀂃Include any simple attribute (and simple components of composite attributes) of 1:N relation type as attributes of S' Prof P Sreenivasa Kumar Department of CS&E, IITM 59 ExampleProfessorStudentguidesPhoneNameProfIDNameRollNo1NRollNoNameguideProfIdNamephoneStudentProfessorProf P Sreenivasa Kumar Department of CS&E, IITM 60 Handling M:N relationship􀂃Make a separate table T for this relationship R between entitysets E1and E2. Let R1 and R2 be the tables corresponding to E1and E2. 􀂃Include primary key attributes of R1and R2as foreign keys in T. Their combination is the primary key in T.E1E2RMNR1R2PK1FK1FK2PK2TProf P Sreenivasa Kumar Department of CS&E, IITM 61 ExampleStudentCourseenrollsNameRollNoNameCourseIDMNnamerollNorollNocourseIdstudentenrollmentcoursePrimary key of enrollmenttable is {RollNo, CourseID}namecourseIDProf P Sreenivasa Kumar Department of CS&E, IITM 62 Handling Recursive relationships􀂃Make a table T for the participating entity set E ( this might already be existing) and one table for recursive relationship R.ExampleCourseCourseIDCreditsTimingisPreReqOfMNCourseTablepreReqCourseCourseIDCourseIDCreditsTimingPreRequisiteTable