Midterm 2 RevisionSlide 2Slide 3Slide 4Slide 5E-R Diagram for the Banking EnterpriseDetermining Keys from E-R SetsChapter 3: Relational ModelBasic StructureAttribute TypesRelation SchemaRelation InstanceRelations are UnorderedKeysQuery 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 33Slide 34Additional OperationsSet-Intersection OperationSet-Intersection Operation - ExampleNatural-Join OperationNatural Join Operation – ExampleDivision OperationDivision Operation – ExampleAnother Division ExampleDivision Operation (Cont.)Assignment OperationSlide 45Slide 46Extended Relational-Algebra-OperationsGeneralized ProjectionAggregate Functions and OperationsAggregate Operation – ExampleSlide 51Aggregate Functions (Cont.)Outer JoinOuter Join – ExampleSlide 55Slide 56Null ValuesSlide 58Slide 59Modification of the DatabaseDeletionDeletion ExamplesInsertionInsertion ExamplesUpdatingUpdate ExamplesSlide 67Slide 68Midterm 2 RevisionMidterm 2 RevisionProf. Sin-Min LeeDepartment of Computer ScienceSan Jose State University©Silberschatz, Korth and Sudarshan3.2Database System Concepts1. Know the difference between a database and a DBMS Functions/advantages and disadvantages of a DBMS 2.Understand the meaning of all of the E-R symbols .3.Know the basis of the mathematical relation and the properties of a relation. Understand and recognize symbols for Selection, projection, Cartesian product, union and set difference. Understand the difference between an inner join and an outerjoin 4.Know the characteristics of superkey, candidate key, primary key, and foreign key. 5.Know the rules of relational integrity and referential integrity. 6. Be able to recognize and read relational algebra statements with the primary operators. 7.Be able to recognized simple relational calculus statements (like the ones used in class) and understand the difference between the algebra and calculus. Materials cover in Exam.©Silberschatz, Korth and Sudarshan3.3Database System ConceptsMultiple Choice Problems©Silberschatz, Korth and Sudarshan3.4Database System Concepts©Silberschatz, Korth and Sudarshan3.5Database System Concepts©Silberschatz, Korth and Sudarshan3.6Database System ConceptsE-R Diagram for the Banking EnterpriseE-R Diagram for the Banking Enterprise©Silberschatz, Korth and Sudarshan3.7Database System ConceptsDetermining Keys from E-R SetsDetermining Keys from E-R SetsStrong entity set. The primary key of the entity set becomes the primary key of the relation.Weak entity set. The primary key of the relation consists of the union of the primary key of the strong entity set and the discriminator of the weak entity set.Relationship set. The union of the primary keys of the related entity sets becomes a super key of the relation.For binary many-to-one relationship sets, the primary key of the “many” entity set becomes the relation’s primary key.For one-to-one relationship sets, the relation’s primary key can be that of either entity set.For many-to-many relationship sets, the union of the primary keys becomes the relation’s primary key©Silberschatz, Korth and Sudarshan3.8Database System ConceptsChapter 3: Relational ModelChapter 3: Relational ModelStructure of Relational DatabasesRelational AlgebraTuple Relational CalculusDomain Relational CalculusExtended Relational-Algebra-Operations©Silberschatz, Korth and Sudarshan3.9Database System ConceptsBasic StructureBasic StructureFormally, 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 ai DiExample: ifcustomer-name = {Jones, Smith, Curry, Lindsay}customer-street = {Main, North, Park}customer-city = {Harrison, Rye, Pittsfield}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 Sudarshan3.10Database System ConceptsAttribute TypesAttribute TypesEach attribute of a relation has a nameThe set of allowed values for each attribute is called the domain of the attributeAttribute values are (normally) required to be atomic, that is, indivisibleE.g. multivalued attribute values are not atomicE.g. composite attribute values are not atomicThe special value null is a member of every domainThe 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 Sudarshan3.11Database System ConceptsRelation SchemaRelation SchemaA1, A2, …, An are attributesR = (A1, A2, …, An ) is a relation schemaE.g. Customer-schema = (customer-name, customer-street, customer-city)r(R) is a relation on the relation schema RE.g. customer (Customer-schema)©Silberschatz, Korth and Sudarshan3.12Database System ConceptsRelation InstanceRelation InstanceThe current values (relation instance) of a relation are specified by a tableAn element t of r is a tuple, represented by a row in a tableJonesSmithCurryLindsaycustomer-nameMainNorthNorthParkcustomer-streetHarrisonRyeRyePittsfieldcustomer-citycustomerattributestuples©Silberschatz, Korth and Sudarshan3.13Database System ConceptsRelations are UnorderedRelations are Unordered Order of tuples is irrelevant (tuples may be stored in an arbitrary order) E.g. account relation with unordered tuples©Silberschatz, Korth and Sudarshan3.14Database System ConceptsKeysKeysLet K RK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R) by “possible r” we mean a relation r that could exist in the enterprise we are modeling.Example: {customer-name, customer-street} and {customer-name} are both superkeys of Customer, if no two customers can possibly have the same name.K is a candidate key if K is minimalExample: {customer-name} is a candidate key for Customer, since it is a superkey {assuming no two customers can possibly have the same name), and no subset of it is a
View Full Document
Unlocking...