Unit2_Relational data model.pdf

Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1...

Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Unit 2: Relational Data Model Topics: Structure of relational databases, Domains Relations, Relational algebra-fundamental operators and syntax, relational algebra queries Entity Relationship Diagram Components of E-R Diagram (Entities, Attributes, Relationship) Mapping cardinalities Keys Extended E-R Features: Specialization, Generalization and Aggregation. Structure of relational databases Page | 12  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Relations: Relation is sometimes used to refer to a table in a relational database but is more commonly used to describe the relationships that can be created between those tables in a relational database. ❖ Relational algebra-fundamental operators and syntax Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator canbeeither unary or binary.Theyacceptrelationsastheirinputandyieldrelationsastheir output. Relational algebra is performed recursively on a relation and intermediate results are also considered relations. The fundamental operations of relational algebra are as follows − Select Project Union Setdifferent Cartesianproduct Rename We will discuss all these operations in the following sections. Page | 13  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Select Operation (σ) It selects tuples that satisfy the given predicate from a relation. Notation −σp(r) Where σ standsforselectionpredicateand r standsforrelation. p isprepositionallogic formulawhichmayuseconnectorslike and,or, and not.Thesetermsmayuserelational operatorslike−=, ≠, ≥, ,≤. Forexample − σsubject = "database"(Books) Output −Selectstuplesfrombookswheresubjectis'database'. σsubject = "database" and price = "450"(Books) Output −Selectstuplesfrombookswheresubjectis'database'and'price'is450. σsubject = "database" and price = "450" or year > "2010"(Books) Output −Selectstuplesfrombookswheresubjectis'database'and'price'is450orthose books published after 2010. Project Operation (∏) It projects column(s) that satisfy a given predicate. Notation−∏A1,A2,An (r) WhereA1,A2,An areattributenamesofrelation r. Duplicate rows are automatically eliminated, as relation is a set. Forexample − ∏subject, author (Books) Selects and projects columns named as subject and author from the relation Books. Union Operation ( ) Itperformsbinaryunionbetweentwogivenrelationsandisdefinedas− ∪ r ∪s={t|t∈rort∈s} Notation −rUs Where r and s areeitherdatabaserelationsorrelationresultset(temporaryrelation). For a union operation to be valid, the following conditions must hold − r,and s musthavethesamenumberofattributes. Attributedomainsmustbecompatible. Page | 14  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Duplicate tuples are automatically eliminated. ∏ author (Books) ∪∏author(Articles) Output −Projectsthenamesoftheauthorswhohaveeitherwrittenabookoranarticleor both. Set Difference (−) The result of set difference operation is tuples, which are present in one relation but are not in the second relation. Notation − r − s Findsallthetuplesthatarepresentin r butnotin s. ∏ author (Books) − ∏ author (Articles) Output −Providesthenameofauthorswhohavewrittenbooksbutnotarticles. Cartesian Product (Χ) Combines information of two different relations into one. Notation −rΧs Where r and s arerelationsandtheiroutputwillbedefinedas− r Χ s = { q t | q ∈randt∈s} σauthor='xyz'(BooksΧArticles) Output −Yieldsarelation,whichshowsallthebooksandarticleswrittenbyxyz. Rename Operation (ρ) The results of relational algebra are also relations but without any name. The rename operation allows us to rename the output relation. 'Rename' operation is denoted with small Greekletter rho ρ. Notation − ρ x (E) Wheretheresultofexpression E issavedwithnameof x. Additional operations are − Setintersection Assignment Naturaljoin Relational Calculus Page | 15  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 In contrast to Relational Algebra, Relational Calculus is a non-procedural query language, that is, it tells what to do but never explains how to do it. Relational calculus exists in two forms − Tuple Relational Calculus (TRC) Filtering variable ranges over tuples Notation −{T|Condition} Returns all tuples T that satisfies a condition. Forexample − { T.name | Author(T) AND T.article = 'database' } Output −Returnstupleswith'name'fromAuthorwhohaswrittenarticleon'database'. TRC can be quantified. We can use Existential ( ∃)andUniversalQuantifiers(∀). Forexample − {R| ∃T ∈Authors(T.article='database'ANDR.name=T.name)} Output −Theabovequerywillyieldthesameresultasthepreviousone. ❖ Entity Relationship Diagram The E-R model cannot express relationships among relationships. When would we need such a thing at that time aggregation is used? Consider a database with information about employees who work on a particular project and use a number of machines doing that work. Page | 16  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Fig. A Fig. B Relationship sets work and uses could be combined into a single set. We can combine them by using aggregation. Aggregation is an abstraction through which relationships are treated as higher-level entities. For our example, we treat the relationship set work and the entity sets employee and project as a higher-level entity set called work. Transforming an E-R diagram with aggregation into tabular form is easy. We create a table for each entity and relationship set as before. The table for relationship set uses contains a column for each attribute in the primary key of machinery and work. Step 1: Entities and Simple Attributes: An entity type within ER diagram is turned into a table. You may preferably keep the same name for the entity or give it a sensible name but avoid DBMS reserved words as well as avoid the use of special characters. Each attribute turns into a column (attribute) in the table. The key attribute of the entity is the primary key of the table which is usually underlined. It can be composite if required but can never be null. It is highly recommended that every table should start with its primary key attribute conventionally named as TablenameID. Consider the following simple ER diagram: The initial relational schema is expressed in the following format writing the table names with the attributes list inside a parentheses as shown below ✔ Persons( personid, name, address, email ) Person personid name address Email Persons and Phones are Tables and personid, name, address and email are Columns (Attributes). personid is the primary key for the table : Person Step 2: Multi-Valued Attributes A multi-valued attribute is usually represented with a double-line oval. Page | 17  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 If you have a multi-valued attribute, take that multi-valued attribute and turn it into a new entity or table of its own. Then make a 1:N relationship between the new entity and the existing one. In simple words. 1. Create a table for that multi-valued attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the new table as shown below: ✔ First table is Persons ( personid, name, address, email ) ✔ Second table is Phones ( phoneid , personid, phone ) personid within the table Phones is a foreign key referring to the personid of Persons Phone phoneid personid phone Step 3: 1:1 Relationship Let us consider the case where the Person has one wife. You can place the primary key of the wife table wifeid in the table Persons which we call in this Page | 18  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 case foreign key as shown below. ✔ Persons( personid, name, address, email , wifeid ) ✔ Wife ( wifeid , name ) Or vice versa to put the personid as a foreign key within the wife table as shown below: ✔ Persons( personid, name, address, email ) ✔ Wife ( wifeid , name , personid) For cases when the Person is not married i.e. has no wifeID, the attribute can set to NULL Persons personid name address email wifeid Wife wifeid name OR Persons personid name address email Wife wifeid name personid Step 4: 1:N Relationships Page | 19  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 For instance, the Person can have a House from zero to many, but a House can have only one Person. In such relationship place the primary key attribute of table having 1 mapping in to the table having many cardinality as a foreign key. To represent such relationship the personid as the Parent table must be placed within the Child table as a foreign key. It should convert to : ✔ Persons( personid, name, address, email ) ✔ House ( houseid, name , address, personid) Persons personid name address email House houseid name address personid Step 5: N:N Relationships For instance, The Person can live or work in many countries. Also, a country can have many people. To express this relationship within a relational schema we use a separate table as shown below: It should convert into : ✔ Persons( personid, name, address, email ) ✔ Countries ( countryid, name) ✔ HasRelat ( hasrelatid, personid , countryid) Page | 20  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Persons personid name address email Countries countryid name HasRelat hasrelatid personid countryid Page | 21 Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Page | 22  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 ❖ E-R model Entity-relationship (ER) model/diagram is a graphical representation of entities and their relationships to each other with their attributes. ❖ Mapping cardinalities In database management, cardinality plays an important role. Here cardinality represents the number of times an entity of an entity set participates in a relationship set. Or we can say that the cardinality of a relationship is the number of tuples (rows) in a relationship. Types of cardinality in between tables are: 1. one-to-one 2. one-to-many 3. many-to-one 4. many-to-many 1) One-to-One Cardinalities: Onetoonecardinalityisrepresentedbya 1:1 symbol.Inthis,thereisatmostone relationship from one entity to another entity. There are a lot of examples of one-to-one cardinality in real life databases. Page | 23  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Forexample, onestudentcanhaveonlyonestudentid,andonestudentidcanbelongtoonly one student. So, the relationship mapping between student and student id will be one to one cardinality mapping. 2) One-to-Many Cardinalities: In One-to-many cardinality mapping, from set 1, there can be a maximum single set that can make relationships with a single or more than one entity of set 2. Or we can also describe it as from set 2, more than one entity can make a relationship with only one entity of set 1. OnetoonecardinalityisthesubsetofOne-to-manyCardinality.Itcanberepresentedby 1: M. ForExample, inahospital,therecanbevariouscompounders,sotherelationshipbetween the hospital and compounders can be mapped through One-to-many Cardinality. Page | 24  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 3) Many-to-One Cardinality: In many to one cardinality mapping, from set 1, there can be multiple sets that can make relationships with a single entity of set 2. Or we can also describe it as from set 2, and one entity can make a relationship with more than one entity of set 1. OnetooneCardinalityisthesubsetofmanytooneCardinality.Itcanberepresentedby M:1. For example, there are multiple patients in a hospital who are served by a single doctor, so the relationship between patients and doctors can be represented by many to one Cardinality. 4) Many-to-Many Cardinalities: In many, many cardinalities mapping, there can be one or more than one entity that can associate with one or more than one entity of set 2. In the same way from the end of set 2, one or more than one entity can make a relation with one or more than one entity of set 1. Itisrepresentedby M:N or N:M. ❖ Keys Keys play an important role in the relational database. It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables. Page | 25  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Forexample, IDisusedasakeyintheStudenttablebecauseitisuniqueforeachstudent.In the PERSON table, passport_number, license_number, SSN are keys since they are unique for each person. Types of keys: 1. Primary key o It is the first key used to identify one and only oneinstanceofanentityuniquely.An entity can contain multiple keys, as we saw in the PERSON table. The key which is most suitable from those lists becomes a primary key. o In the EMPLOYEE table, ID can be the primary key since it is unique for each employee. In the EMPLOYEE table, we can even select License_Number and Passport_Number as primary keys since they are also unique. o Foreachentity,theprimarykeyselectionisbasedonrequirementsanddevelopers. 2. Candidate key o A candidate key is an attribute or set of attributes that can uniquely identify a tuple. o Except for the primary key, the remaining attributes are considered a candidate key. The candidate keys are as strong as the primary key. For example: IntheEMPLOYEEtable,idisbestsuitedfortheprimarykey.Therestofthe attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key. Page | 26  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 3. Super Key Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key. Forexample: IntheaboveEMPLOYEEtable,for(EMPLOEE_ID,EMPLOYEE_NAME), the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key. 4. Foreign key o Foreign keys are the column of the table used to point to the primary key of another table. Page | 27  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 o Every employee works in a specific department in a company, and employee and department are two different entities. So we can't store the department's information in the employee table. That's why we link these two tables through the primary key of one table. o We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute in the EMPLOYEE table. o In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related. 5. Alternate key There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these candidate keys, and the remaining candidatekey,ifitexists,istermedthealternatekey. Inotherwords, thetotalnumberofthe alternate keys is the total number of candidate keys minus the primary key. The alternate key may or may not exist. If there is only one candidate key in a relation, it does not have an alternate key. Forexample, employeerelationhastwoattributes,Employee_IdandPAN_No,thatactas candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other candidate key, PAN_No, acts as the Alternate key. Page | 28  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 6. Composite key Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key. Forexample, inemployeerelations,weassumethatanemployeemaybeassignedmultiple roles, and an employee may work on multiple projects simultaneously. So the primary key will be composed of all three attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So these attributes act as a composite key since the primary key comprises more than one attribute. ❖ Extended E-R Features: Specialization, Generalization and Aggregation ❖ Generalization o Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common. o In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further higher level entity. o Generalization is more like subclass and super class system, but the only difference is the approach. Generalization uses the bottom-up approach. o In generalization, entities are combined to form a more generalized entity, i.e., subclasses are combined to make a super class. Forexample, FacultyandStudententitiescanbegeneralizedandcreateahigher level entity Person. Page | 29  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 ❖ Specialization o Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one higher level entity can be broken down into two lower level entities. o Specialization is used to identify the subset of an entity set that shares some distinguishing characteristics. o Normally, the superclass is defined first, the subclass and its related attributes are defined next, and relationship set are then added. Forexample: InanEmployeemanagementsystem,EMPLOYEEentitycanbespecialized as TESTER or DEVELOPER based on what role they play in the company. ❖ Aggregation In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity. Forexample: CenterentityofferstheCourseentityactasasingleentityintherelationship which is in a relationship with another entity visitor. In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only or just about the Center instead he will ask the enquiry about both. Page | 30 Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 Page | 31