DOC PREVIEW
UW-Milwaukee COMPSCI 557 - Lecture Notes

This preview shows page 1-2-3-4-28-29-30-31-58-59-60-61 out of 61 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 61 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Announcements• Today– Finish RDM (Chapter 5), begin relational algebra• Reading– Sections 6.0-6.5•Program 2– Due Friday•Exam– Tuesday Oct 16, in class– Closed book– Will cover material through Thursday’s lectureClass bufMgr{...// Call DB object to allocate a run of new pages and // find a frame in the buffer pool for the first page// and pin it. If buffer is full, ask DB to deallocate// all these pages and return errorStatus newPage(int& firstPageId, Page*& firstpage,int howmany=1); // Check if this page is in buffer pool, otherwise// find a frame for this page, read in and pin it.// Also write out the old page if it's dirty before reading// if emptyPage==TRUE, then actually no read is done to bring// the page in.Status pinPage(int PageId_in_a_DB, Page*& page,int emptyPage=0, const char *filename=NULL);...}//// in your code//PageId pid;Page *pPageTmpSecondaryIndexHeaderPage *pHeaderPage;MINIBASE_BM->newPage( pid, pPageTmp );pHeaderPage = (SecondaryIndexHeaderPage*)pPageTmp;ConstraintsConstraints• A key aspect of RDM is the ability to impose constraintson the database state• A constraint on a single relation places restrictions on valid relation states– Examples: • two students can’t have same student ID number–Example of key constraint• Student name cannot be NULL – Domain constraints (implicit)Key Constraints• Superkey of R: – Is a set of attributes SK of R with the following condition:• No two tuples in any valid relation state r(R) will have the same value for SK• That is, for any distinct tuples t1 and t2 in r(R), t1[SK] ≠t2[SK]• This condition must hold in any valid state r(R)• Key of R:– A "minimal" superkey– That is, a key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property)Key Constraints (continued)• Example: Consider the CAR relation schema:– CAR(State, Reg#, SerialNo, Make, Model, Year)– CAR has two keys:• Key1 = {State, Reg#}• Key2 = {SerialNo}– Both are also superkeys of CAR– {SerialNo, Make} is a superkey but not a key.• In general:–Any key is a superkey (but not vice versa)– Any set of attributes that includes a key is a superkey–A minimal superkey is also a keyKey Constraints (continued)• If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. – The primary key attributes are underlined.• Example: Consider the CAR relation schema:– CAR(State, Reg#, SerialNo, Make, Model, Year)– We chose SerialNo as the primary key• The primary key value is used to uniquely identify each tuple in a relation– Provides the tuple identity• Also used to reference the tuple from another tuple– General rule: Choose as primary key the smallest of the candidate keys (in terms of size)– Not always applicable – choice is sometimes subjectiveCAR table with two candidate keys –LicenseNumber chosen as Primary KeyMultiple Relations• Typically, a RDB has many relationsRelational Database Schema• Relational Database Schema:– A set S of relation schemas that belong to the same database.– S is the name of the whole database schema– S = {R1, R2, ..., Rn}– R1, R2, …, Rn are the names of the individual relation schemas within the database SCOMPANY Database SchemaReferential Integrity Constraints involve Two Relations• Example: DEPT_LOCATION.Dnumber must refer to an existing tuple in DEPARTMENT• Operationalized through concept of foreign keyReferential Integrity• Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2.– A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].• A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.Referential Integrity (or foreign key) Constraint• Statement of the constraint– The value in the foreign key column (or columns) FK of the the referencing relation R1 can be either:• (1) a value of an existing primary key value of a corresponding primary key PK in the referenced relation R2, or•(2) a null.• In case (2), the FK in R1 should not be a part of its own primary key.Referential Integrity Constraints for COMPANY databaseOther Types of Constraints• Semantic Integrity Constraints:– based on application semantics and cannot be expressed by the model per se– Example: “the max. no. of hours per employee for all projects he or she works on is 56 hrs per week”•A constraint specification language may have to be used to express these• SQL-99 allows triggers and ASSERTIONS to express for some of theseUpdate Operations on RelationsMust not Violate Constraints• INSERT a tuple.• DELETE a tuple.• MODIFY a tuple.• Several update operations may have to be grouped together (a transaction)• Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.Update Operations on Relations• In case of integrity violation, several actions can be taken:– Cancel the operation that causes the violation (RESTRICT or REJECT option)– Perform the operation but inform the user of the violation– Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option)– Execute a user-specified error-correction routinePossible violations for each operation• INSERT may violate any of the constraints:– Domain constraint:• if one of the attribute values provided for the new tuple is not of the specified attribute domain– Key constraint:• if the value of a key attribute in the new tuple already exists in another tuple in the relation– Referential integrity:• if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation– Entity integrity:• if the primary key value is null in the new tuplePossible violations for each operation• DELETE may violate only referential integrity:– If the primary key value of the tuple being deleted is referenced from other tuples in the database• Can be remedied by several actions: RESTRICT, CASCADE, SET NULL (see Chapter 8 for more details)– RESTRICT option: reject the deletion– CASCADE option: propagate the new primary key value into the foreign keys of the referencing tuples– SET NULL option: set the foreign keys of the


View Full Document

UW-Milwaukee COMPSCI 557 - Lecture Notes

Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?