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 lecture
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Constraints and Relational Algebra-Introduction to Databases-Tutorial 08-Computer Science, Study Guides, Projects, Research of Introduction to Database Management Systems
Constraints and Relational Algebra, Key Constraints, Multiple Relations, Relational Database Schema, Referential Integrity, Foreign Key, Relational Algebra, Relational Algebra Expression, SELECT, PROJECT, UNION, INTERSECTION, MINUS, CARTESIAN PRODUCT, JOIN, EQUIJOIN, NATURAL JOIN, DIVISION
Typology: Study Guides, Projects, Research
2011/2012
1 / 61
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Constraints and Relational Algebra-Introduction to Databases-Tutorial 08-Computer Science and more Study Guides, Projects, Research Introduction to Database Management Systems in PDF only on Docsity!
Announcements
•^
Today– Finish RDM (Chapter 5), begin relational algebra
•^
Reading– Sections 6.0-6.
•^
Program 2– Due Friday
•^
Exam– Tuesday Oct 16, in class– Closed book– Will cover material through Thursday’s lecture
Class 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;*
Constraints
•^
A key aspect of RDM is the ability to imposeconstraints on the database state
•^
A constraint on a single relation placesrestrictions 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 (doesnot possess the superkey uniqueness property)
Key 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 subjective
CAR table with two candidate keys –
LicenseNumber chosen as Primary Key
Relational 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 S
COMPANY Database Schema
Referential 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 displayedin a relational database schema as a directedarc 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.
Other 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 these
Update Operations on Relations
•^
In case of integrity violation, several actions canbe 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 routine
Possible 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 tuple