Relational Model Relational Model A relational database consists
Relational Model
Relational Model �A relational database consists of a collection of tables �a row in a table represents a relationship among a set of values. �close correspondence between the concept of table and the mathematical concept of relation tuple is simply a sequence (or list) of values A relationship between n values is represented mathematically by an n-tuple of values, i. e. , a tuple with n values, which corresponds to a row in a table.
Relational Model �the term relation is used to refer to a table �the term tuple is used to refer to a row �the term attribute refers to a column of a table.
Relational Model �relation instance refers to a specific instance of a relation, i. e. , containing a specific set of rows �The order in which tuples appear in a relation is irrelevant, since a relation is a set of tuples �For each attribute of a relation, there is a set of permitted values, called the domain of that attribute. domain of the salary attribute of the instructor relation is the set of all possible salary values, while the domain of the name attribute is the set of all possible instructor names
Domains , NULL values �for all relations r, the domains of all attributes of r be atomic A domain is atomic if elements of the domain are considered to be indivisible units. �Address of an employee – atomic / non-atomic ? �The null value is a special value that signifies that the value is unknown or does not exist. null values cause a number of difficulties when we access or update the database, and thus should be eliminated if at all possible
Database schema vs instance �Database schema is the logical design of the database, and �database instance, is a snapshot of the data in the database at a given instant in time. �a relation schema consists of a list of attributes and their corresponding domains the contents of a relation instance may change with time as the relation is updated the schema of a relation does not generally change
Relational schema Department Schema
Relational model �the attribute dept name appears in both the instructor schema and the department schema. This duplication is not a coincidence. Rather, using common attributes in relation schemas is one way of relating tuples of distinct relations. �Example find the information about all the instructors who work in the Watson building.
Keys �how tuples within a given relation are distinguished �A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation �Formally let R denote the set of attributes in the schema of relation r. subset K of R is a superkey for r no two distinct tuples have the same values on all attributes in K. i. e. , if t 1 and t 2 are in r and t 1 = t 2, then t 1. K = t 2. K
Candidate Key �A superkey may contain extraneous attributes {SSN, Name} {SSN, Name, Address} etc. . . �If K is a superkey, then so is any superset of K �superkeys for which no proper subset is a superkey is minimal superkeys are called candidate keys. Example � {SSN} � {Name, Department}
Primary Key �primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying tuples within a relation. �foreign key dependency A relation, say r 1, may include among its attributes the primary key of another relation, say r 2. This attribute is called a foreign key from r 1, referencing r 2. The relation r 1 is also called the referencing relation of the foreign key dependency, and r 2 is called the referenced relation of the foreign key. � dept name in instructor is a foreign key from instructor, referencing department, since dept name is the primary key of department.
Referential Integrity Constraint if a particular (course id, sec id, semester, year) combination appears in section, then the same combination must appear in teaches. However, this set of values does not form a primary key for teaches, since more than one instructor may teach one such section. If a section exists for a course, it must be taught by atleast one instructor. however, it could possibly be taught by more than one instructor we cannot declare a foreign key constraint from section to teaches
Referential Integrity Constraint �a referential integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation
schema diagrams A database schema, along with primary key and foreign key dependencies
Relation Schema, Relation State �Relation Intension Also known as relation schema Relation schema A relation schema 2 R, denoted by R(A 1, A 2, . . . , An), is made up of a relation name R and a list of attributes, A 1, A 2, . . . , An. Each attribute Ai is the name of a role played by some domain D in the relation schema R. D is called the domain of Ai and is denoted by dom(Ai). �Relation extension Also known as relation state
Mathematical Definition of relation �A relation (or relation state) r of the relation schema R(A 1, A 2, . . . , An), denoted by r(R), is a set of n-tuples r = {t 1, t 2, . . . , tm}. Each n-tuple t is an ordered list of n values t =<v 1, v 2, . . . , vn>, where each value vi, 1 ≤ i ≤ n, is an element of dom(Ai) or is a special NULL value. The ith value in tuple t, which corresponds to the attribute Ai, is referred to as t[Ai] or t. Ai
Mathematical Definition of relation �A relation (or relation state) r(R) is a mathematical relation of degree n on the domains dom(A 1), dom(A 2), . . . , dom(An), which is a subset of the Cartesian product (denoted by ×) of the domains that define R: r(R) ⊆ (dom(A 1) × dom(A 2) ×. . . × dom(An)) �The Cartesian product specifies all possible combinations of values from the underlying domains.
Relational model constraints � Domain Constraints Within each tuple, the value of each attribute A must be an atomic value from the domain dom(A) � Key Constraints relation is defined as a set of tuples. By definition, all elements of a set are distinct; hence, all tuples in a relation must also be distinct. no two tuples can have the same combination of values for all their attributes. Subset of attributes SK; � then for any two distinct tuples t 1 and t 2 in a relation state r of R, we have the constraint that: t 1[SK]≠ t 2[SK] SK is called a superkey of the relation schema R � Constraints on NULL Values
Relational model constraints �A database state that does not obey all the integrity constraints is called an invalid state �state that satisfies all the constraints in the defined set of integrity constraints is called a valid state
Integrity Constraints �Entity Integrity Constraint The entity integrity constraint states that no primary key value can be NULL �Referential Integrity Constraint specified between two relations and is used to maintain the consistency among tuples in the two relations a tuple in one relation refers to another relation must refer to an existing tuple in that relation
Definition of Foreign key �A set of attributes FK in relation schema R 1 is a foreign key of R 1 that references relation R 2 if it satisfies the following rules: The attributes in FK have the same domain(s) as the primary key attributes PK of R 2; A value of FK in a tuple t 1 of the current state r 1(R 1) either occurs as a value of PK for some tuple t 2 in the current state r 2(R 2) or is NULL. In the former � i. e. , t 1[FK] = t 2[PK] R 1 is called the referencing relation R 2 is the referenced relation. If these two conditions hold, a referential integrity constraint from R 1 to R 2 is said to hold.
Operations on Relational model & their violations �Insert – new tuples �Delete – remove tuples �Update – change values of some attributes �Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated.
Insert operation �Insert can violate any of the four types of constraints Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain or is not of the appropriate data type. Key constraints can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R). Entity integrity can be violated if any part of the primary key of the new tuple t is NULL. Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation. �If an insertion violates one or more constraints, the default option is to reject theinsertion
Delete Operation �The Delete operation can violate only referential integrity This occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database Example � Deletion of a tuple in Department relation
Delete Operation �Several options are available if a deletion operation causes a violation reject the deletion attempt to cascade (or propagate) the deletion � deleting tuples that reference the tuple that is being deleted Set null or set default � modify the referencing attribute values that cause the violation; each such value is either set to NULL or changed to reference another default valid tuple. � If a referencing attribute that causes a violation is part of the primary key, it cannot be set to NULL
Update operation �Updating an attribute that is neither part of a primary key nor of a foreign key usually causes no problems; �Modifying a primary key value is similar to deleting one tuple and inserting another in its place �If a foreign key attribute is modified, the DBMS must make sure that the new value refers to an existing tuple in the referenced relation (or is set to NULL)
Example
Relational Algebra
- Slides: 28