DOC PREVIEW
UCI ICS 184 - relational model

This preview shows page 1-2-3-22-23-24-45-46-47 out of 47 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 47 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 47 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 47 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 47 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 47 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 47 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 47 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 47 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 47 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 47 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Relational ModelProf. Sharad MehrotraInformation and ComputerScience DepartmentUniversity of California at IrvineChapter 3 and 6 from SKSChapter 3 in UWOutline Relational model basic modeling concepts for schema specification Mapping ER diagrams to Relational Model Relational Languages relational algebra (algebraic) basic operators, expressions in relational algebra relational calculus (logic based) /*will not becovered in class */3Relational Model -- Quick ExampleSsno Name Street cityNULL Frank 12 Main Champaign1200331 Cathy3 Neil Urbana2000312 Bill 45 W. Oak UrbanaAccountNumberCustid balance34 1000201 10,79945 1200331 1,34563 2000312 100,345 Key constraints:•ssno is the key for customertable•both accountno and custid arekeys for account tableReferential Integrity constraints:(foreign keys)The custid attribute in account tabletakes values from ssno in customertableNull Constraint:customer name cannottake null values A relational schema = tables and constraints. Tables: customer, account Constraints:4Relational Model Database schema consists of a set of relation schema a set of constraints over the relation schema Relational Schema = name(attributes).Graphically drawn as table. Example: employee(ssno, name, salary) Recall: relation is a subset of cartesian product ofsets relation is a set of n-tuples where n = degreeof the relationAttributes With each attribute a domain is specified In relational model attributes are atomic: the values are not divisible. That is, wecannot refer to or directly see a subpart of thevalue. an attribute can take a special null value Null value represents either attributeswhose value is not known, or do not exist6null in this case may mean that diagnosis is not complete anddisease has not been identified. Notice possibility of confusionthat null means that the patient has no disease! This is one ofthe reasons why using nulls is not a great idea! We will seeother reasons as well laterExample of a RelationDiagnosis: an example relation/tablePatient DiseaseJim SchizophrenicJane Obsessive-CompJerry ManicJoe null7Constraints What are they? represent the semantics of the domain being modeled. restrict the set of possible database states Why do we want to specify the constraints? Useful information to application programmers. Theycan write programs to prevent constraints violation constraint -- acct balance should not fall below 0 dollars programmer writing code for debit application shouldcheck at the end if the account gets overdrawn! DBMS might enforce specified constraints directlymaking task of application writer easier If DBMS guarantees that account does not get overdrawn,then debit application programmer need not worry aboutchecking for overdrawn account condition.8Constraints Why specify constraints? knowledge of some type of constraintsenables us to identify redundancy in schemasand hence specification of constraints helps indatabase design (we will see this later) Knowledge of some type of constraints canalso help the DBMS in query processingSpecifying Constraints in DataModels ER model domain and key constraints over entities participation and cardinality constraints overrelationships Relational Model domain constraints, entity identity, keyconstraint, functional dependencies --generalization of key constraints, referentialintegrity, inclusion dependencies --generalization of referential integrity.10Domain Constraint In the schema, every attribute isdeclared to have a type --- integer, float,date, boolean, string, etc. An insertion request can violate thedomain constraint. DBMS can check if insertion violatesdomain constraint and reject theinsertion.11Key Constraint Each relation has a primary key.Superkey: set of attributes such that if two tuples agreeon those attributes, then they agree on all theattributes of the relation Note: the set of all the attributes of a relationis always a superkey.Candidate key: superkey no subset of which i s a superkeyPrimary key: one of the candidate keys12Disallowing Null Values Some fields of a relation are tooimportant to contain null values. Example: in sales(customer, salesman, date, amount,saleID) we may not want ‘customer’ tocontain a null value.Entity Integrity Constraint A primary key must not contain a nullvalue. Else it may not be possible to identify sometuples. For Example, if more than one tuple has a nullvalue in its primary key, we may not be ableto distinguish them .14Foreign Key and ReferentialIntegrity Constraint Consider following 2 relation schemas: R1(A1, A2, …An) and R2(B1, B2, … Bm) Let PK be subset of {A1, …,An} be primary keyof R1 A set of attributes FK is a foreign key of R2 if: attributes in FK have same domain as the attributes inPK For all tuples t2 in R2, there exists a tuple t1in R1 suchthat t2[FK] = t1[PK]. A referential integrity constraint from attributesFK of R2 to R1 means that FK is a foreign thatrefers to the primary key of R1.15 student-grades student C# Semester grade Susan CS101 1-91 A Jane CS101 1-91 B LegalGrades GradeA we will have a referential integrity B constraint saying that C every value of student-grades.grade D must also be a value of F LegalGrades.grade, Audit ExExample of Referential Integrity16 Generalization of referential integrity constraint. Inclusion dependency R1[A1,...,An] ⊆==R2 [B1,...,Bn]means that the values in the first relation R1 refer tothe values in the second relation Formally, R1[A1,...,An] ⊆==R2 [B1,...,Bn] iff thefollowing holds: for all t1 in R1, there exists a t2 in R2 such that t1[A1, …, An]= t2[B1, …, Bn] Notice that referential integrity constraint is aninclusion dependency in which {B1, .. Bn} is theprimary key of R2.Inclusion DependenciesExample student-grade[Grade] ⊆=LegalGrade[Grade] CourseOffering[C#] ⊆==Courses[C#] Takes[S#] ⊆==Students[S#] CourseOffering[Professor] ⊆==UCEmployee[E#]18Data Modification and IntegrityConstraints Modification, insertion and deletion


View Full Document

UCI ICS 184 - relational model

Download relational model
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 relational model 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 relational model 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?