Chapter 2: Relational ModelChapter 2: Relational ModelExample of a RelationBasic StructureAttribute TypesRelation SchemaRelation InstanceRelations are UnorderedDatabaseThe customer RelationThe depositor RelationKeysKeys (Cont.)Foreign KeysQuery LanguagesRelational AlgebraSelect Operation – ExampleSelect OperationProject Operation – ExampleProject OperationUnion Operation – ExampleUnion OperationSet Difference Operation – ExampleSet Difference OperationCartesian-Product Operation – ExampleCartesian-Product OperationComposition of OperationsRename OperationBanking ExampleExample QueriesSlide 31Slide 32Slide 33Formal DefinitionAdditional OperationsSet-Intersection OperationSet-Intersection Operation – ExampleNatural-Join OperationNatural Join Operation – ExampleDivision OperationDivision Operation – ExampleAnother Division ExampleDivision Operation (Cont.)Assignment OperationBank Example QueriesSlide 46Slide 47Extended Relational-Algebra-OperationsGeneralized ProjectionAggregate Functions and OperationsAggregate Operation – ExampleSlide 52Aggregate Functions (Cont.)Outer JoinOuter Join – ExampleSlide 56Slide 57Null ValuesSlide 59Modification of the DatabaseDeletionDeletion ExamplesInsertionInsertion ExamplesUpdatingUpdate ExamplesSlide 67Slide 68Slide 69Slide 70End of Chapter 2Figure 2.3. The branch relationFigure 2.6: The loan relationFigure 2.7: The borrower relationFigure 2.9 Result of branch_name = “Perryridge” (loan)Figure 2.10: Loan number and the amount of the loanFigure 2.11: Names of all customers who have either an account or an loanFigure 2.12: Customers with an account but no loanFigure 2.13: Result of borrower |X| loanFigure 2.14Figure 2.15Figure 2.16Figure 2.17 Largest account balance in the bankFigure 2.18: Customers who live on the same street and in the same city as SmithFigure 2.19: Customers with both an account and a loan at the bankFigure 2.20Figure 2.21Figure 2.22Figure 2.23Figure 2.24: The credit_info relationFigure 2.25Figure 2.26: The pt_works relationFigure 2.27 The pt_works relation after regroupingFigure 2.28Figure 2.29Figure 2.30 The employee and ft_works relationsFigure 2.31Figure 2.32Figure 2.33Figure 2.34Database System Concepts, 5th Ed.©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Chapter 2: Relational ModelChapter 2: Relational Model©Silberschatz, Korth and Sudarshan2.2Database System Concepts - 5th Edition, Oct 5, 2006Chapter 2: Relational ModelChapter 2: Relational ModelStructure of Relational DatabasesFundamental Relational-Algebra-OperationsAdditional Relational-Algebra-OperationsExtended Relational-Algebra-OperationsNull ValuesModification of the Database©Silberschatz, Korth and Sudarshan2.3Database System Concepts - 5th Edition, Oct 5, 2006Example of a RelationExample of a Relation©Silberschatz, Korth and Sudarshan2.4Database System Concepts - 5th Edition, Oct 5, 2006Basic StructureBasic StructureFormally, given sets D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x DnThus, a relation is a set of n-tuples (a1, a2, …, an) where each ai  DiExample: Ifcustomer_name = {Jones, Smith, Curry, Lindsay, …} /* Set of all customer names */customer_street = {Main, North, Park, …} /* set of all street names*/customer_city = {Harrison, Rye, Pittsfield, …} /* set of all city names */Then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield) } is a relation over customer_name x customer_street x customer_city©Silberschatz, Korth and Sudarshan2.5Database System Concepts - 5th Edition, Oct 5, 2006Attribute TypesAttribute TypesEach attribute of a relation has a nameThe set of allowed values for each attribute is called the domain of the attributeAttribute values are (normally) required to be atomic; that is, indivisibleE.g. the value of an attribute can be an account number, but cannot be a set of account numbersDomain is said to be atomic if all its members are atomicThe special value null is a member of every domainThe null value causes complications in the definition of many operationsWe shall ignore the effect of null values in our main presentation and consider their effect later©Silberschatz, Korth and Sudarshan2.6Database System Concepts - 5th Edition, Oct 5, 2006Relation SchemaRelation SchemaA1, A2, …, An are attributesR = (A1, A2, …, An ) is a relation schemaExample:Customer_schema = (customer_name, customer_street, customer_city)r(R) denotes a relation r on the relation schema RExample:customer (Customer_schema)©Silberschatz, Korth and Sudarshan2.7Database System Concepts - 5th Edition, Oct 5, 2006Relation InstanceRelation InstanceThe current values (relation instance) of a relation are specified by a tableAn element t of r is a tuple, represented by a row in a tableJonesSmithCurryLindsaycustomer_nameMainNorthNorthParkcustomer_streetHarrisonRyeRyePittsfieldcustomer_citycustomerattributes(or columns)tuples(or rows)©Silberschatz, Korth and Sudarshan2.8Database System Concepts - 5th Edition, Oct 5, 2006Relations are UnorderedRelations are Unordered Order of tuples is irrelevant (tuples may be stored in an arbitrary order) Example: account relation with unordered tuples©Silberschatz, Korth and Sudarshan2.9Database System Concepts - 5th Edition, Oct 5, 2006DatabaseDatabaseA database consists of multiple relationsInformation about an enterprise is broken up into parts, with each relation storing one part of the informationaccount : stores information about accounts depositor : stores information about which customer owns which account customer : stores information about customersStoring all information as a single relation such as bank(account_number, balance, customer_name, ..)results inrepetition of information e.g.,if two customers own an account (What gets repeated?)the need for null values e.g., to represent a customer without an accountNormalization theory (Chapter 7) deals with how to design relational schemas©Silberschatz, Korth and Sudarshan2.10Database System Concepts - 5th Edition, Oct 5, 2006The The customer customer RelationRelation©Silberschatz, Korth and Sudarshan2.11Database System Concepts - 5th Edition, Oct 5, 2006The The depositor depositor RelationRelation©Silberschatz, Korth and Sudarshan2.12Database System Concepts -