Social Model

Relational model l.jpg
1 / 51
1359 days ago, 480 views
PowerPoint PPT Presentation

Presentation Transcript

Slide 1

Social Model Naveen Ashish Calit2 & Information and Computer Science Department University of California at Irvine

Slide 2

Outline Relational model essential displaying ideas for pattern detail Mapping ER graphs to Relational Model Relational Languages social variable based math (mathematical) fundamental administrators, expressions in social polynomial math social analytics (rationale based)/*will not be secured in class */

Slide 3

Relational Model - Quick Example A social diagram = tables and limitations. Tables: client, account Constraints: Key imperatives : ssno is the key for client table both accountno and custid are keys for record table Null Constraint: client name can't take invalid qualities Referential Integrity requirements : (remote keys) The custid property in record table takes values from ssno in client table

Slide 4

Aside journals/blog-cm/craigmullins/

Slide 5

Relational Model Database mapping comprises of a set of connection composition an arrangement of limitations over the connection diagram Relational Schema = name(attributes). Graphically drawn as table. Illustration: employee(ssno, name, pay) Recall: connection is a subset of cartesian result of sets connection is an arrangement of n-tuples where n = level of the connection

Slide 6

Attributes With every property a space is determined In social model properties are nuclear: the qualities are not distinguishable. That is, we can't allude to or specifically observe a subpart of the esteem. a quality can take a unique invalid esteem Null esteem speaks to either properties whose esteem is not known, or don't exist

Slide 7

Wardrobe Set Shirt, Trousers, Tie, Jacket, Shoes Domain Shirt From an arrangement of shirts (say apprehended) Set of shoes … NULL imperatives Shirt can't be NULL  You MUST wear a shirt ! Coat might be NULL OK to be without a coat

Slide 8

Example of a Relation Diagnosis: an illustration connection/table Patient Disease Jim Schizophrenic Jane Obsessive-Comp Jerry Manic Joe invalid for this situation may imply that finding is not finished and ailment has not been distinguished. See plausibility of disarray that invalid implies that the patient has no infection! This is one reason why utilizing nulls is not an incredible thought! We will see different reasons too later

Slide 9

Constraints What are they? speak to the semantics of the space being displayed. confine the arrangement of conceivable database states Why would we like to indicate the imperatives? Helpful data to application developers. They can compose projects to avert imperatives infringement requirement - acct adjust ought not fall beneath 0 dollars software engineer composing code for charge application ought to check toward the end if the record gets overdrawn! DBMS may implement indicated imperatives specifically making undertaking of use essayist simpler If DBMS ensures that record does not get overdrawn, then charge application developer require not stress over checking for overdrawn account condition.

Slide 10

Analogy Fuse board

Slide 11

Constraints Why determine requirements? information of some sort of requirements empowers us to distinguish excess in compositions and subsequently detail of imperatives aides in database plan (we will see this later) Knowledge of some kind of limitations can likewise help the DBMS in question preparing

Slide 12

Specifying Constraints in Data Models ER display space and key requirements over substances cooperation and cardinality limitations over connections Relational Model area limitations, element personality, scratch requirement, utilitarian conditions - speculation of key limitations, referential respectability, incorporation conditions - speculation of referential uprightness.

Slide 13

Domain Constraint In the construction, each ascribe is pronounced to have a sort - whole number, skim, date, boolean, string, and so on. An inclusion demand can damage the space imperative. DBMS can check if addition disregards space limitation and reject the inclusion. MUST wear calfskin shoes

Slide 14

Key Constraint Each connection has an essential key. Superkey: set of properties with the end goal that if two tuples concur on those qualities, then they concede to every one of the characteristics of the connection Note: the arrangement of the considerable number of traits of a connection is dependably a superkey. Hopeful key: superkey no subset of which is a superkey Primary key: one of the competitor keys

Slide 15

Disallowing Null Values Some fields of a connection are excessively essential, making it impossible to contain invalid qualities. Case: in sales(customer, sales representative, date, sum, saleID) we may not need "client" to contain an invalid esteem.

Slide 16

Entity Integrity Constraint An essential key must not contain an invalid esteem. Else it may not be conceivable to recognize some tuples For Example, if more than one tuple has an invalid esteem in its essential key, we will be unable to recognize them

Slide 17

Back to the Wardrobe Say the Tie is exceptional Tie decides everything else

Slide 18

Foreign Key and Referential Integrity Constraint Consider taking after 2 connection patterns: R1(A1, A2, … An) and R2(B1, B2, … Bm) Let PK be subset of {A1, … ,An} be essential key of R1 An arrangement of qualities FK is an outside key of R2 if: properties in FK have same space as the characteristics in PK For all tuples t2 in R2, there exists a tuple t1in R1 with the end goal that t2[FK] = t1[PK]. A referential uprightness imperative from credits FK of R2 to R1 implies that FK is a remote key that alludes to the essential key of R1.

Slide 19

Example of Referential Integrity understudy grades understudy C# Semester review Susan CS101 1-91 A Jane CS101 1-91 B LegalGrades Grade A we will have a referential trustworthiness B limitation saying that C each estimation of understudy grades.grade D should likewise be an estimation of F LegalGrades.grade, Audit Ex

Slide 20

Inclusion Dependencies Generalization of referential honesty requirement. Incorporation reliance R1[A1,...,An] Í R2 [B1,...,Bn] implies that the qualities in the principal connection R1 allude to the qualities in the second connection Formally, R1[A1,...,An] Í R2 [B1,...,Bn] iff the accompanying holds: for all t1 in R1, there exists a t2 in R2 with the end goal that t1[A1, … , An] = t2[B1, … , Bn] Notice that referential respectability imperative is a consideration reliance in which {B1, .. Bn} is the essential key of R2.

Slide 21

Example understudy grade[Grade] Í LegalGrade[Grade] CourseOffering[C#] Í Courses[C#] Takes[S#] Í Students[S#] CourseOffering[Professor] Í UCEmployee[E#]

Slide 22

Data Modification and Integrity Constraints Modification, inclusion and erasure solicitations can prompt to infringement of respectability requirements. Key requirement, element character, invalid esteem limitation, referential respectability, consideration conditions, practical conditions, multivalued conditions. must check for infringement at end of every operation and appropriately permit or refuse operation. Effect of information adjustment on consideration conditions can be here and there dubious!

Slide 23

Example Relations CourseOfferings(C#, semester, educator) Takes(S#, C#, semester, review) Referential Integrity Constraint: Takes(C#,semester) Í CourseOffering(C#,semester) Consider dropping a course. Erase from courseOfferings where c# = "CS101" AND Semester = "2-91"; What ought to happen to tuples in Takes that allude to CS101 and semester 2-91??

Slide 24

Example (cont) Takes S# C# Semester Grade 1001 CS101 2-91 ^ 1002 CS101 2-91 ^ 1003 CS101 1-91 A Possible Solutions: 1) dismiss overhaul ( prematurely end ) - or - 2) erase tuples from "Takes" that allude to 'CS101, 2-91' ( course )

Slide 25

Functional Dependencies FDs is a speculation of idea of keys. Given a connection R with characteristics A 1 ,...,A n ,B 1 ,...,B m ,C 1 ,...,C l , we say that A 1 ,...,A n practically decide B 1 ,...,B m (A 1 ,...,A n B 1 ,...,B m ) if at whatever point two tuples concur on their qualities for A 1 ,...,A n , they concede to B 1 ,… ,Bm The key of a connection practically decides every one of the traits of the connection. (by meaning of a key)

Slide 26

Example Takes(C#, S#, semester, review). Key = (C#,S#,semester) C# S# Semester review CS101 13146 1-91 A CS101 13146 1-91 B unlawful since it abuses FD that C#,S#,Semester practically decide review

Slide 27

Logical Implication of Functional Dependencies Consider R(A,B,C) Let the accompanying useful conditions hold: A B (f1) B C (f2) We can demonstrate that f1 and f2 together legitimately suggest that the accompanying utilitarian reliance additionally holds: A C (f3)

Slide 28

Proof say f3 does not hold. at that point there exists tuples t1, t2 in R to such an extent that t1[A] = t2[A] and t1[C] is not equivalent to t2[C] Since f1 holds and since t1[A] = t2[A], the reality of the situation must prove that t1[B] = t2[B] Hence since t1[B] = t2[B] and f2 holds, the facts must confirm that t1[C] = t2[C] This is an inconsistency! Henceforth, f3 should likewise hold!

Slide 29

Closure of Functional Dependency Set Definition: Let R be a connection plan, and F be the arrangement of useful conditions characterized over R. F+ means the arrangement of all utilitarian conditions that hold over R. That is, F+ = { X Y | F sensibly implies X Y} Example: Let F = {A B, B C} then A C is in F+ is known as the conclusion of F

Slide 30

Inferring Functional Dependencies Given an arrangement of fds F over a connection