پاورپوینت کامل Chapter 5: Other Relational Languages 91 اسلاید در PowerPoint

توجه : این فایل به صورت فایل power point (پاور پوینت) ارائه میگردد

 پاورپوینت کامل Chapter 5: Other Relational Languages 91 اسلاید در PowerPoint دارای ۹۱ اسلاید می باشد و دارای تنظیمات کامل در PowerPoint می باشد و آماده ارائه یا چاپ است

شما با استفاده ازاین پاورپوینت میتوانید یک ارائه بسیارعالی و با شکوهی داشته باشید و همه حاضرین با اشتیاق به مطالب شما گوش خواهند داد.

لطفا نگران مطالب داخل پاورپوینت نباشید، مطالب داخل اسلاید ها بسیار ساده و قابل درک برای شما می باشد، ما عالی بودن این فایل رو تضمین می کنیم.

توجه : در صورت  مشاهده  بهم ریختگی احتمالی در متون زیر ،دلیل ان کپی کردن این مطالب از داخل فایل می باشد و در فایل اصلی پاورپوینت کامل Chapter 5: Other Relational Languages 91 اسلاید در PowerPoint،به هیچ وجه بهم ریختگی وجود ندارد

بخشی از مطالب داخلی اسلاید ها

پاورپوینت کامل Chapter 5: Other Relational Languages 91 اسلاید در PowerPoint

اسلاید ۴: Predicate Calculus Formula1.Set of attributes and constants2.Set of comparison operators: (e.g., , , , , , )3.Set of connectives: and (), or (v)‚ not ()4.Implication (): x y, if x if true, then y is truex y x v y5.Set of quantifiers:t r (Q (t )) ”there exists” a tuple in t in relation r such that predicate Q (t ) is truet r (Q (t )) Q is true “for all” tuples t in relation r

اسلاید ۵: Banking Examplebranch (branch_name, branch_city, assets ) customer (customer_name, customer_street, customer_city ) account (account_number, branch_name, balance ) loan (loan_number, branch_name, amount )depositor (customer_name, account_number )borrower (customer_name, loan_number )

اسلاید ۶: Example QueriesFind the loan_number, branch_name, and amount for loans of over $1200 Find the loan number for each loan of an amount greater than $1200 {t | s loan (t [loan_number ] = s [loan_number ] s [amount ] 1200)} Notice that a relation on schema [loan_number ] is implicitly defined by the query{t | t loan t [amount ] 1200}

اسلاید ۷: Example QueriesFind the names of all customers having a loan, an account, or both at the bank{t | s borrower ( t [customer_name ] = s [customer_name ]) u depositor ( t [customer_name ] = u [customer_name] ) Find the names of all customers who have a loan and an account at the bank{t | s borrower ( t [customer_name ] = s [customer_name ]) u depositor ( t [customer_name ] = u [customer_name ])

اسلاید ۸: Example QueriesFind the names of all customers having a loan at the Perryridge branch{t | s borrower (t [customer_name ] = s [customer_name ] u loan (u [branch_name ] = “Perryridge” u [loan_number ] = s [loan_number ])) not v depositor (v [customer_name ] = t [customer_name ])} Find the names of all customers who have a loan at the Perryridge branch, but no account at any branch of the bank{t | s borrower (t [customer_name ] = s [customer_name ] u loan (u [branch_name ] = “Perryridge” u [loan_number ] = s [loan_number ]))}

اسلاید ۹: Example QueriesFind the names of all customers having a loan from the Perryridge branch, and the cities in which they live{t | s loan (s [branch_name ] = “Perryridge” u borrower (u [loan_number ] = s [loan_number ] t [customer_name ] = u [customer_name ]) v customer (u [customer_name ] = v [customer_name ] t [customer_city ] = v [customer_city ])))}

اسلاید ۱۰: Example QueriesFind the names of all customers who have an account at all branches located in Brooklyn:{t | r customer (t [customer_name ] = r [customer_name ]) ( u branch (u [branch_city ] = “Brooklyn” s depositor (t [customer_name ] = s [customer_name ] w account ( w[account_number ] = s [account_number ] ( w [branch_name ] = u [branch_name ]))))}

اسلاید ۱۱: Safety of ExpressionsIt is possible to write tuple calculus expressions that generate infinite relations.For example, { t | t r } results in an infinite relation if the domain of any attribute of relation r is infiniteTo guard against the problem, we restrict the set of allowable expressions to safe expressions.An expression {t | P (t )} in the tuple relational calculus is safe if every component of t appears in one of the relations, tuples, or constants that appear in PNOTE: this is more than just a syntax condition. E.g. { t | t [A] = 5 true } is not safe — it defines an infinite set with attribute values that do not appear in any relation or tuples or constants in P.

اسلاید ۱۲: Domain Relational CalculusA nonprocedural query language equivalent in power to the tuple relational calculusEach query is an expression of the form:{ x1, x2, …, xn | P (x1, x2, …, xn)} x1, x2, …, xn represent domain variablesP represents a formula similar to that of the predicate calculus

اسلاید ۱۳: Example QueriesFind the loan_number, branch_name, and amount for loans of over $1200 Find the names of all customers who have a loan from the Perryridge branch and the loan amount:{ c, a | l ( c, l borrower b ( l, b, a loan b = “Perryridge”))}{ c, a | l ( c, l borrower l, “ Perryridge”, a loan)} { c | l, b, a ( c, l borrower l, b, a loan a > 1200)}Find the names of all customers who have a loan of over $1200 { l, b, a | l, b, a loan a > 1200}

اسلاید ۱۴: Example QueriesFind the names of all customers having a loan, an account, or both at the Perryridge branch:{ c | s,n ( c, s, n customer) x,y,z ( x, y, z branch y = “Brooklyn”) a,b ( x, y, z account c,a depositor)} Find the names of all customers who have an account at all branches located in Brooklyn:{ c | l ( c, l borrower b,a ( l, b, a loan b = “Perryridge”)) a ( c, a depositor b,n ( a, b, n account b = “Perryridge”))}

اسلاید ۱۵: Safety of ExpressionsThe expression:{ x1, x2, …, xn | P (x1, x2, …, xn )} is safe if all of the following hold:All values that appear in tuples of the expression are values from dom (P ) (that is, the values appear either in P or in a tuple of a relation mentioned in P ).For every “there exists” subformula of the form x (P1(x )), the subformula is true if and only if there is a value of x in dom (P1)such that P1(x ) is true.For every “for all” subformula of the form x (P1 (x )), the subformula is true if and only if P1(x ) is true for all values x from dom (P1).

اسلاید ۱۶: Query-by-Example (QBE)Basic StructureQueries on One RelationQueries on Several RelationsThe Condition BoxThe Result RelationOrdering the Display of TuplesAggregate Operations Modification of the Database

اسلاید ۱۷: QBE — Basic StructureA graphical query language which is based (roughly) on the domain relational calculusTwo dimensional syntax – system creates templates of relations that are requested by usersQueries are expressed “by example”

اسلاید ۱۸: QBE Skeleton Tables for the Bank Example

اسلاید ۱۹: QBE Skeleton Tables (Cont.)

اسلاید ۲۰: Queries on One RelationFind all loan numbers at the Perryridge branch. _x is a variable (optional; can be omitted in above query) P. means print (display) duplicates are removed by default To retain duplicates use P.ALL

اسلاید ۲۱: Queries on One Relation (Cont.)Display full details of all loansP._xP._yP._zMethod 1:Method 2: Shorthand notation

اسلاید ۲۲: Queries on One Relation (Cont.)Find names of all branches that are not located in Brooklyn Find the loan number of all loans with a loan amount of more than $700

اسلاید ۲۳: Queries on One Relation (Cont.)Find the loan numbers of all loans made jointly to Smith and Jones.Find all customers who live in the same city as Jones

اسلاید ۲۴: Queries on Several RelationsFind the names of all customers who have a loan from the Perryridge branch.

اسلاید ۲۵: Queries on Several Relations (Cont.)Find the names of all customers who have both an account and a loan at the bank.

اسلاید ۲۶: Negation in QBEFind the names of all customers who have an account at the bank, but do not have a loan from the bank.¬ means “there does not exist”

اسلاید ۲۷: Negation in QBE (Cont.)Find all customers who have at least two accounts.¬ means “not equal to”

اسلاید ۲۸: The Condition BoxAllows the expression of constraints on domain variables that are either inconvenient or impossible to express within the skeletonplex conditions can be used in condition boxesExample: Find the loan numbers of all loans made to Smith, to Jones, or to both jointly

اسلاید ۲۹: Condition Box (Cont.)QBE supports an interesting syntax for expressing alternative values

اسلاید ۳۰: Condition Box (Cont.)Find all account numbers with a balance greater than $1,300 and less than $1,500 Find all account numbers with a balance greater than $1,300 and less than $2,000 but not exactly $1,500.

اسلاید ۳۱: Condition Box (Cont.)Find all branches that have assets greater than those of at least one branch located in Brooklyn

اسلاید ۳۲: The Result RelationFind the customer_name, account_number, and balance for all customers who have an account at the Perryridge branch.We need to:Join depositor and account.Project customer_name, account_number and balance.To accomplish this we:Create a skeleton table, called result, with attributes customer_name, account_number, and balance.Write the query.

اسلاید ۳۳: The Result Relation (Cont.)The resulting query is:

اسلاید ۳۴: Ordering the Display of TuplesAO = ascending order; DO = descending order.Example: list in ascendi