Elmasri_6e_GE Ch 3 (1).ppt

Chapter 3 The Basic (Flat) Relational Model Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 3 Outline  The Relational Data Model and Relational Database Constraints  Relational Model Constraints and R...

Chapter 3 The Basic (Flat) Relational Model Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 3 Outline  The Relational Data Model and Relational Database Constraints  Relational Model Constraints and Relational Database Schemas  Update Operations, Transactions, and Dealing with Constraint Violations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Pre-Relational model Hierarchical Model Organizes data in a tree-like structure, where records (or nodes) have a parent-child relationship. Each parent can have multiple children, but each child has only one parent, creating a rigid hierarchy. Are folders on a laptop relational or The data relationships are hierarchical? modeled using pointers. Folders on a laptop are structured in a hierarchical model rather than a relational model. Here's why: Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Pre-Relational model Network Model The Network Model extends the hierarchical model by allowing many-to-many relationships. Data is organized in a graph-like structure where records can Are flights in an airline system have multiple parents and relational or network? multiple children. Relationships between records In an airline system, many flights are maintained via sets and can serve many airports, and pointers. many airports can handle many flights. This creates a network of relationships between flights and airports. Copyright © 2011 Ramez Elmasri and Shamkant Navathe The Relational Data Model and Relational Database Constraints  Relational model  First commercial implementations available in early 1980s  Has been implemented in a large number of commercial system Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Concepts  Represents data as a collection of relations  Table of values  Table name (Relation Name)  Column names (attribute) Interpret the meaning of the values in each row  Row (Tuple) Represents a collection of related data values Fact that typically corresponds to a real-world entity or relationship Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Concepts (cont’d.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Domains, Attributes, Tuples, and Relations  Domain D  Set of atomic values an attributes can have Atomic: Each value indivisible  Each attribute in a table is associated with a specific domain that defines its permissible data types or range of values.  If you have a column for "Age," the domain could be integers ranging from 0 to 120.  For a "Gender" column, the domain might be restricted to values like "Male" and "Female." Copyright © 2011 Ramez Elmasri and Shamkant Navathe Domains, Attributes, Tuples, and Relations (cont’d.)  Relation schema R  Denoted by R(A1, A2,...,An)  Made up of a relation name R and a list of attributes, A1, A2,..., An  Attribute Ai  Name of a role played by some domain D in the relation schema R  Example:  Customers(CustomerID, Name, Email, PhoneNumber) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Domains, Attributes, Tuples, and Relations (cont’d.)  Degree (or arity) of a relation  Number of attributes n of its relation schema  What is the degree of the following relation schema?  Customers(CustomerID, Name, Email, PhoneNumber) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations  Tuples are unique  No two rows have exactly the same values for all attributes  Ordering of tuples in a relation  Relation defined as a set of tuples  Elements have no order among them Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations  Ordering of values within a tuple and an alternative definition of a relation  Order of attributes and values is not that important  As long as correspondence between attributes and values maintained (StudentID: 001, Name: Alice, Age: 20) (Name: Alice, StudentID: 001, Age: 20) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  Use the first definition of relation  Attributes and the values within tuples are ordered Students(StudentID, Name, Age) (001, Alice, 20)  Simpler notation (002, Bob, 22)  Alternative definition of a relation  Tuple considered as a set of (, ) pairs  Each pair gives the value of the mapping from an attribute Ai to a value vi from dom(Ai) (StudentID: 001, Name: Alice, Age: 20) (Name: Bob, StudentID: 002, Age: 22) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  Values in tuples  Each value in a tuple is atomic  Flat relational model Composite and multivalued attributes not allowed First normal form assumption Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  Multivalued attributes  Must be represented by separate relations  Let's say we have a Students relation where a student can enroll in multiple courses. The Courses attribute is multivalued.  Can we do the below? Students(StudentID, Name, Courses) (001, Alice, Math, Physics) (002, Bob, Chemistry) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  Multivalued attributes  Must be represented by separate relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  Composite attributes  Represented only by simple component attributes in basic relational model  Consider an attribute like Address, which is a composite attribute made up of Street, City, State, and ZipCode. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of Relations (cont’d.)  NULL values  Represent the values of attributes that may be unknown or may not apply to a tuple  Meanings for NULL values Value unknown Value exists but is not available Attribute does not apply to this tuple (also known as value undefined) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Notation  Name of a relation schema: STUDENT  Indicates the current set of tuples in that relation  Notation: STUDENT(Name, Ssn,...)  Refers only to relation schema  Attribute A can be qualified with the relation name R to which it belongs  Using the dot notation R.A Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints  Constraints  Restrictions on the actual values in a database state  Derived from the rules in the miniworld that the database represents  Inherent model-based constraints or implicit constraints  Inherent in the data model  Such as age needs to be positive, ID number must exist Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints (cont’d.)  Schema-based constraints or explicit constraints  Can be directly expressed in schemas of the data model  Examples: age >18, email is unique, primary key defined Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Model Constraints (cont’d.)  Application-based or semantic constraints or business rules  Cannot be directly expressed in schemas  Expressed and enforced by application program  Example: password length, account balance before doing a transaction Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Domain Constraints  Typically include:  Numeric data types for integers and real numbers  Characters  Booleans  Fixed-length strings  Variable-length strings  Date, time, timestamp  Money  Other special data types Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values  No two tuples can have the same combination of values for all their attributes.  Superkey  A superkey is any set of one or more attributes that can uniquely identify a tuple in a relation  A superkey may contain extra, unnecessary attributes that are not needed to uniquely identify the tuple  No two distinct tuples in any state r of R can have the same value for SK Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values  Consider a Students(StudentID, Name, Email) relation,  Possible Superkeys for this relation:  {StudentID}  {StudentID, Name}  {StudentID, Email}  {StudentID, Name, Email} Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.)  Key  The smallest set of attributes that can uniquely identify a tuple in a relation, with no unnecessary attributes.  Superkey of R  Minimal superkey  Removing any attribute A from K leaves a set of attributes K that is not a superkey of R any more. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.) Aspect Superkey Key (Candidate Key) A set of one or more A minimal superkey (no Definition attributes that can unnecessary attributes). uniquely identify a tuple. Can have more than the Minimal (smallest set of Size necessary attributes. attributes). Ensures uniqueness, but Ensures uniqueness with Uniqueness may include extra no extra columns. columns. {StudentID, Name}, Examples {StudentID}, {StudentID, {StudentID}, {Email} Name, Email} Every key is a superkey, Relationship Every key is a superkey. but not all superkeys are keys. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.)  Candidate key  Relation schema may have more than one key  Primary key of the relation  Designated among candidate keys  Underline attribute  Other candidate keys are designated as unique keys Copyright © 2011 Ramez Elmasri and Shamkant Navathe Key Constraints and Constraints on NULL Values (cont’d.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Integrity, Referential Integrity, and Foreign Keys (cont’d.)  Foreign key rules:  The attributes in FK have the same domain(s) as the primary key attributes PK  Value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is NULL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Integrity, Referential Integrity, and Foreign Keys  Entity integrity constraint  No primary key value can be NULL  Referential integrity constraint  Specified between two relations  Maintains consistency among tuples in two relations Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Relational Databases and Relational Database Schemas (cont’d.)  Invalid state  Does not obey all the integrity constraints  Valid state  Satisfies all the constraints in the defined set of integrity constraints IC Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Integrity, Referential Integrity, and Foreign Keys (cont’d.)  Diagrammatically display referential integrity constraints  Directed arc from each foreign key to the relation it references  All integrity constraints should be specified on relational database schema Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  CREATE TABLE CREATE TABLE employee ( fname varchar(15), minit varchar(1), lname varchar(15), ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), superssn char(9), dno number(4) ); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Update Operations, Transactions, and Dealing with Constraint Violations  Operations of the relational model can be categorized into retrievals and updates  Basic operations that change the states of relations in the database:  Insert  Delete  Update (or Modify) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Insert Operation  Provides a list of attribute values for a new tuple t that is to be inserted into a relation R  Can violate a constraint.  If an insertion violates one or more constraints  Default option is to reject the insertion Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Operation INSERT INTO employee VALUES ('James','E','Borg','888665555','10-NOV-27','450 Stone, Houston, TX','M',55000,null,null); INSERT INTO employee VALUES ('Franklin','T','Wong','333445555','08-DEC-45','638 Voss, Houston, TX','M',40000,'888665555',null); INSERT INTO employee VALUES ('Jennifer','S','Wallace','987654321','20-JUN-31','291 Berry, Bellaire, TX','F',43000,'888665555',null); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Question  Show if the following operations are correct or not. If not, show why:  INSERT INTO employee VALUES ('James','E','Borg','888665555','10-NOV- 27','450 Stone, Houston, TX','M',55000,null,null);  Salary should be decimal Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Question  Suppose this insert operation is applied directly to the database of Figure 3.6. Discuss all integrity constraints violated by the operation, if any, and the different ways of enforcing these constraints: Insert < 'Production’, 5, ‘773785555', '01- OCT-88' > into DEPARTMENT. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  INSERT Question Answer  Violates both the key constraint and referential integrity.  Violates the key constraint because there already exists a DEPARTMENT tuple with DNUMBER=5.  We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the new DEPARTMENT tuple to a value that does not violate the key constraint.  Violates referential integrity because Mgr_ssn= ' 773785555 ' and there is no tuple in the EMPLOYEE relation with SSN=' 773785555 '.  We may enforce the constraint by: (i) rejecting the insertion, (ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='773785555 '. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Delete Operation  Can violate only referential integrity  If tuple being deleted is referenced by foreign keys from other tuples  Restrict Reject the deletion  Cascade Propagate the deletion by deleting tuples that reference the tuple that is being deleted  Set null or set default Modify the referencing attribute values that cause the violation Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DELETE Operation DELETE FROM employee WHERE ssn = ‘888665555'; DELETE FROM employee WHERE ssn = ‘333445555'; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Delete Operation  Suppose this delete operation is applied directly to the database of Figure 3.6. Discuss all integrity constraints violated by the operation, if any, and the different ways of enforcing these constraints:  Delete the DEPARTMENT tuples with Dnumber = 1. Violates referential integrity because EMPLOYEE has a Dno =1. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Update Operation  Necessary to specify a condition on attributes of relation  Select the tuple (or tuples) to be modified  If attribute not part of a primary key nor of a foreign key  Usually causes no problems  Updating a primary/foreign key  Similar issues as with Insert/Delete Copyright © 2011 Ramez Elmasri and Shamkant Navathe  UPDATE Operation UPDATE employee SET fname = ‘Smith' WHERE ssn = ‘888665555'; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  UPDATE Question  Modify the Dno attribute of the EMPLOYEE tuple with Dno = ‘5' to ‘6'.  Violates referential integrity because the new value of Dno=‘6' and there is no tuple in the DEPARTMENT relation with Dno = ‘6'.  We may enforce the constraint by: (i) rejecting the deletion, or (ii) inserting a new DEPARTMENT tuple with Dno = ‘6'. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Summary  Characteristics differentiate relations from ordinary tables or files  Classify database constraints into:  Inherent model-based constraints, explicit schema-based constraints, and application- based constraints  Modification operations on the relational model:  Insert, Delete, and Update Copyright © 2011 Ramez Elmasri and Shamkant Navathe