پاورپوینت کامل Chapter 4: Advanced SQL 65 اسلاید در PowerPoint

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

 پاورپوینت کامل Chapter 4: Advanced SQL 65 اسلاید در PowerPoint دارای ۶۵ اسلاید می باشد و دارای تنظیمات کامل در PowerPoint می باشد و آماده ارائه یا چاپ است

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

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

توجه : در صورت  مشاهده  بهم ریختگی احتمالی در متون زیر ،دلیل ان کپی کردن این مطالب از داخل فایل می باشد و در فایل اصلی پاورپوینت کامل Chapter 4: Advanced SQL 65 اسلاید در PowerPoint،به هیچ وجه بهم ریختگی وجود ندارد

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

پاورپوینت کامل Chapter 4: Advanced SQL 65 اسلاید در PowerPoint

اسلاید ۴: Build-in Data Types in SQL (Cont.)Can extract values of individual fields from date/time/timestampExample: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast <string-valued-expression> as dateExample: cast <string-valued-expression> as time

اسلاید ۵: User-Defined Typescreate type construct in SQL creates user-defined typecreate type Dollars as numeric (12,2) final create domain construct in SQL-92 creates user-defined domain typescreate domain person_name char(20) not nullTypes and domains are similar. Domains can have constraints, such as not null, specified on them.

اسلاید ۶: Domain ConstraintsDomain constraints are the most elementary form of integrity constraint. They test values inserted in the database, and test queries to ensure that the comparisons make sense. New domains can be created from existing data typesExample:create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2)We cannot assign or compare a value of type Dollars to a value of type Pounds. However, we can convert type as below (cast r.A as Pounds) (Should also multiply by the dollar-to-pound conversion-rate)

اسلاید ۷: Large-Object TypesLarge objects (photos, videos, CAD files, etc.) are stored as a large object:blob: binary large object — object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)clob: character large object — object is a large collection of character dataWhen a query returns a large object, a pointer is returned rather than the large object itself.

اسلاید ۸: Integrity ConstraintsIntegrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. A checking account must have a balance greater than $10,000.00A salary of a bank employee must be at least $4.00 an hourA customer must have a (non-null) phone number

اسلاید ۹: Constraints on a Single Relation not nullprimary keyuniquecheck (P ), where P is a predicate

اسلاید ۱۰: Not Null Constraint Declare branch_name for branch is not null branch_name char(15) not nullDeclare the domain Dollars to be not null create domain Dollars numeric(12,2) not null

اسلاید ۱۱: The Unique Constraintunique ( A1, A2, …, Am)The unique specification states that the attributes A1, A2, … AmForm a candidate key.Candidate keys are permitted to be non null (in contrastto primary keys).

اسلاید ۱۲: The check clausecheck (P ), where P is a predicateExample: Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative.create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0))

اسلاید ۱۳: The check clause (Cont.)The check clause in SQL-92 permits domains to be restricted:Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value.create domain hourly_wage numeric(5,2) constraint value_test check(value > = 4.00)The domain has a constraint that ensures that the hourly_wage is greater than 4.00The clause constraint value_test is optional; useful to indicate which constraint an update violated.

اسلاید ۱۴: Referential IntegrityEnsures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.Example: If “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”.Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement:The primary key clause lists attributes that comprise the primary key.The unique key clause lists attributes that comprise a candidate key.The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the referenced table.

اسلاید ۱۵: Referential Integrity in SQL – Examplecreate table customer (customer_namechar(20), customer_streetchar(30), customer_citychar(30), primary key (customer_name ))create table branch (branch_namechar(15), branch_citychar(30), assetsnumeric(12,2), primary key (branch_name ))

اسلاید ۱۶: Referential Integrity in SQL – Example (Cont.)create table account (account_numberchar(10), branch_namechar(15), balanceinteger, primary key (account_number), foreign key (branch_name) references branch )create table depositor (customer_namechar(20), account_numberchar(10), primary key (customer_name, account_number), foreign key (account_number ) references account, foreign key (customer_name ) references customer )

اسلاید ۱۷: AssertionsAn assertion is a predicate expressing a condition that we wish the database always to satisfy.An assertion in SQL takes the formcreate assertion <assertion-name> check <predicate>When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertionThis testing may introduce a significant amount of overhead; hence assertions should be used with great care.Asserting for all X, P(X) is achieved in a round-about fashion using not exists X such that not P(X)

اسلاید ۱۸: Assertion ExampleEvery loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertion balance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.account_number = account.account_number and account.balance >= 1000)))

اسلاید ۱۹: Assertion ExampleThe sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. create assertion sum_constraint check (not exists (select * from branch where (select sum(amount ) from loan where loan.branch_name = branch.branch_name ) >= (select sum (amount ) from account where loan.branch_name = branch.branch_name )))

اسلاید ۲۰: AuthorizationForms of authorization on parts of the database:Read – allows reading, but not modification of data.Insert – allows insertion of new data, but not modification of existing data.Update – allows modification, but not deletion of data.Delete – allows deletion of data.Forms of authorization to modify the database schema (covered in Chapter 8):Index – allows creation and deletion of indices.Resources – allows creation of new relations.Alteration – allows addition or deletion of attributes in a relation.Drop – allows deletion of relations.

اسلاید ۲۱: Authorization Specification in SQLThe grant statement is used to confer authorizationgrant <privilege list>on <relation name or view name> to <user list><user list> is:a user-idpublic, which allows all valid users the privilege grantedA role (more on this in Chapter 8)Granting a privilege on a view does not imply granting any privileges on the underlying relations.The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).

اسلاید ۲۲: Privileges in SQLselect: allows read access to relation,or the ability to query using the viewExample: grant users U1, U2, and U3 select authorization on the branch relation:grant select on branch to U1, U2, U3insert: the ability to insert tuplesupdate: the ability to update using the SQL update statementdelete: the ability to delete tuples.all privileges: used as a short form for all the allowable privilegesmore in Chapter 8

اسلاید ۲۳: Revoking Authorization in SQLThe revoke statement is used to revoke authorization.revoke <privilege list>on <relation name or view name> from <user list>Example:revoke select on branch from U1, U2, U3<privilege-list> may be all to revoke all privileges the revokee may hold.If <revokee-list> includes public, all users lose the privilege except those granted it explicitly.If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the