1 / 46

CHAPTER 3 THE RELATIONAL MODEL

CHAPTER 3 THE RELATIONAL MODEL. THE DBMS OF A RELATIONAL MODEL OF DATA (RMD). As stated previously, a DBMS includes A collection of databases ( which can communicate with each other ). DDL  QL  SQL DML 

corina
Download Presentation

CHAPTER 3 THE RELATIONAL MODEL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CHAPTER 3 THE RELATIONAL MODEL

  2. THE DBMS OF A RELATIONAL MODEL OF DATA (RMD) As stated previously, a DBMS includes • A collection of databases (which can communicate with each other). • DDL  • QL  SQL • DML  N. B. Within the RMD, the words ‘Database’, ‘Relation’, and ‘Table’ are essentially synonymous.

  3. THE RELATIONS OF A RMD Each relation includes (as part of its definition): • Database Name (permanent – created by DDL - referenced explicitly in each command) • Schema (permanent - created explicitely by DDL) • Instance (variable – created and modified by the DML) • Integrity Constraints (permanent – both implicit and explicit, created by the DDL) • Semantic Meaning (permanent – implicit) • Views (subsets of the database – permanent – created by the DDL)

  4. DDL STATEMENT USED FOR CREATING A RELATION SCHEMA CREATE TABLEStudents (sid CHAR (20), name CHAR (30), login CHAR (20), age INTEGER, gpa REAL ) Relation Name Attribute Names Attribute Types

  5. DML STATEMENTS USED TO CREATE AND MODIFY INSTANCES • INSERT INTO Students (sid, name, login, age, gpa) VALUES (5368, ‘Smith’, ‘mith@ee’, 18, 3.2) • DELETE FROM Students S WHERE S.name = ‘Smith’ • UPDATE Students S SET S.age = S.age + 1, S.gpa = S.gpa – 1 Range WHERE S.sid = 53688 Variable • UPDATE Students S SET S.gpa = S.gpa – 0.1 WHERE S.gpa >= 3.3

  6. TYPICAL INSTANCE OF AN RMD RELATION

  7. PROPERTIES OF A RELATION INSTANCE • A database instance of a relation is a setwhose elements are the rows of the relation. • Each row is a list whose elements are the attribute values in the columns of the relation. • Thus, a database instance is a set of lists. • Example: The instance of the relation ‘Students’ shown on the previous slide may be written as {<50000, Dave, dave@vs, 19, 3.3>, <53666, Jones, jones@cs, 18, 3.4> ……………………………………. <53832, Guldu, guldu@music, 12, 2.0>}

  8. INTEGRITY CONSTRAINTS REQUIREMENTS A DML which properly handles integrity constraints must provide: • Means for specifying certain preselected ICs • Means for enforcing these ICs • If possible, means for specifying and enforcing new ICs

  9. THE CONSTRAINT PROBLEM • Integrity constraints correspond to general declarative statements. These statements can be formalized in first order predicate logic. • There is no mechanism in the RMD for making general declarative statements. • SQL includes capabilities for specifying only a certain number of integrity constraints.

  10. General View of RDBM Constraints There is only a limited capacity for stating integrity constraints in the RDBM. A ‘folk’ classification: N. B. In the figure shown above, the word ‘domain’ refers to what we call type and the word ‘range’ refers to what we call domain.

  11. CONSTRAINTS Proper handling of constraint requires the following capabilities: 1. Constraint definition. 2. Detection of constraint violation 3. Specification of proper response in case of violations. 4. Time of detection of violation .

  12. I. TYPE CONSTRAINTS (1) 1. Constraint specification: (a) Implicit specification of standard (built-in) types through the CREATE statement which creates a schema. Example CREATE TABLE Students ( name CHAR(20), sid INTEGER, gpa REAL ) (b) Explicit specification of new types Example (see section 5.7.2) CREATE TYPE ratingtype AS INTEGER - Permits comparison with other objects of type ‘ratingtype’ but not others. Functions can also be defined for new types (section 23.4.1).

  13. I. TYPE CONSTRAINTS (2) 2. Violation Detection Automatic detection by the system. 3. Action Performed by the System upon Violation Detection Rejection of faulty command with suitable notification.

  14. II. DOMAIN CONSTRAINTS (1) 1. Constraint Specification (a) Implicit for standard built-in types. The actual domain is system dependent. (b) Explicit specification of new domains (see section 5.7.2): Example CREATE DOMAIN ratingval INTEGER DEFAULT 1 CHECK ( VALUE >= 1 AND VALUE <= 1) - Here INTEGER is the source type. - Henceforth can use ‘ratingval’ as type definition in CREATE statement. - Here the default value of 1 is used if no value is entered for this column in an inserted tuple.

  15. II. DOMAIN CONSTRAINTS (2) 2. Violation Detection Automatic. 3. Action upon Detection of Violation of Domain Constraint Rejection of faulty command with proper notification.

  16. III. KEY CONSTRAINTS (1) - A key constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple. - A set of fields that uniquely identifies a tuple is called a candidate key. There may be several candidate keys in a relation. - A primary key is the single candidate key used in a relation to reference uniquely tuples. There are two kinds of key constraint statements in a relation. (1) The UNIQUE statements which identify all candidate keys. There may be several of these. (2) The PRIMARY statement which identifies the primary key. There can only be one such statement per relation. 1. Statement of Key Constraints in SQL-92 Example (see p. 66) CREATE TABLE Students ( sid CHAR(20), name CHAR(20), login CHAR(20), age INTEGER, gpa REAL, UNIQUE(name, age) CONSTRAINT StudentsKey PRIMARY KEY (sid)) This is the name of the constraint (optional)

  17. III. KEY CONSTRAINTS (2) Example of key constraint violation (see section 3.3): Given the schema and instance shown in slide #6, issue the command INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, ‘Mike’, ‘mike@ee’. 17, 3.4) 2. Detection of key constraint violation: Automatic. 3. Action upon detection of key constraint violation: Automatic rejection of command and notification

  18. IV. REFERENTIAL INTEGRITY AND FOREIGN KEY CONSTRAINTS Presumably, the ‘Enrolled’ relation’s primary key is {cid, sid}

  19. IV. FOREIGN KEY CONSTRAINTS (1) 1.Constraint Specification (see p. 68): CREATE TABLE Enrolled ( studid CHAR (20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students ) Example of Foreign Key Constraint Violation (see p. 70): Given the ‘Students’ instance shown on slide #6 (figure 3.4), the following DML command violates the foreign key constraint shown above because there is no sid value ‘51111’ in Students. INSERT INTO Enrolled VALUES (‘Hindi101’, ‘B’, 51111)

  20. IV. FOREIGN KEY CONSTRAINTS (2) 2. Detection of Constraint Violation Automatic. 3. Action Taken upon Detection of Violation (see section 3.3): There are various possible alternative actions depending on the situation: (A) Insertion: Attempt to insert a row wherein the foreign key constraint is violated. Example: In figure 3.4, try to insert an ‘Enrolled’ row whose ‘studid’ value does not appear in ‘Students’ In this case the INSERT command is simply rejected. (B) Deletion: Attempt to delete a row in the referenced relation which contains a value that is referenced in the referencing relation. There are four possible options: (i) Delete all rows of the referencing relation which refer to the deleted row of the referenced relation. Example: In figure 3.4, delete all ‘Enrolled’ rows that refer to the deleted ‘Students’ row (ii) Disallow the requested deletion.

  21. IV. FOREIGN KEY CONSTRAINTS (3) (iii) In the referencing relation, set the row value which references a deleted row of the referenced relation to some ‘default’ row value of the referenced relation. Example: In figure 3.4 set the studid column to the sid of some (existing) ‘default’ student for every ‘Enrolled’ row that refers to the deleted ‘Students’ row. [This option seems strange, at least for the example of figure 3.4; however it may be suitable in some other situations (see p.71- bottom)]. (iv) In the referencing relation set to null every row value which references a deleted row of the referenced relation. However, this option cannot be used if null values are not allowed in that attribute of the referencing relation; in that case, the delete command must be rejected. (C) Update: Attempt to update the primary key value of a row of the referenced relation which is referenced by the referencing relation. The text states (see p. 71) “The options here are similar to the previous case.” This is rather strange since any attempt to modify the primary key attribute of a relation will completely destroy the uniqueness of a row.

  22. IV. FOREIGN KEY CONSTRAINTS (4) (3) Specification of actions to be taken in case of violation (cont ‘d) Any of the aforementioned four actions can be specified in the CREATE statement. Example (see p.71) Deletion/Update: Option (i) CREATE TABLE Enrolled ( studid CHAR(20), cid CHAR(20). grade CHAR(20), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students ON DELETE CASCADE default action -may be left out ON UPDATE NO ACTION ) - NO ACTION means that the command is rejected. - CASCADE means that if a ‘Students’ row is deleted, all ‘Enrolled’ rows referring to it are to be deleted.

  23. IV. FOREIGN KEY CONSTRAINTS (5) Option (iii): CREATE TABLE Enrolled ( studid CHAR(20) DEFAULT ‘53666’ cid CHAR(20). grade CHAR(20), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students ON DELETE SET DEFAULT ON UPDATE NO ACTION ) Option (iv): CREATE TABLE Enrolled ( studid CHAR(20), cid CHAR(20). grade CHAR(20), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students ON DELETE SET NULL ON UPDATE NO ACTION )

  24. SPECIFICATION OF TIME OF VERIFICATION OF CONSTRAINT VIOLATION Transaction: a program running against a database possibly containing several statements (queries, inserts, updates, etc.) that access the database. Question: check on constraint violation after each statement or at end of transaction? Answer: - Default: check at end of every statement that could cause violation, rejecting it if necessary. - SQL also allows a choice between immediate or deferred constraint checking until the end of the transaction. Example: - Assuming that the RELEVANT constraint was named ‘ConstraintFoo’, the following statement permits deferred checking: SET CONSTRAINT ConstraintFoo DEFERRED

  25. QUERYING RELATIONAL DATA INTRODUCTION - There are several types of linguistic queries: - ‘Yes/No’ queries (Is the following declarative statement true?). - ‘W’ queries (who/what/when – fill in the correct answer). - ‘How/Why’ queries (ambiguous). - Erotetic Logic was developed in an effort to deal systematically with such queries. Unfortunately it has proven unsatisfactory. - A relational database query is a very special kind of question (I called it a list query). It may be paraphrased as “Give me a list of the entities satisfying such and such properties as well as certain of their properties”. Example Give me a list of the names of all students having gpa greater than 3.2 and their ages. - This type of query bypasses the problem of logical description as the query is formulated in such a way that it specifies indirectly the operations to be performed by the system in order to provide an answer. - The answer provided by the system is in the form of a relation.

  26. SQL QUERIES (1) SQL queries consist of three clauses: 1. The SELECT clause 2. The FROM clause 3. The WHERE clause (optional) Example SELECT name, age FROM Students WHERE gpa > 3.2 Applying this query against the Students database shown in figure 3.4 yields the following answer: N.B. The SELECT and FROM clauses should really be reversed since you need to know first the relation name and then the attributes to seek. However, we follow the traditional approach here.

  27. SQL QUERIES (2) Remarks - The use of the special character asterisk “*” in the SELECT clause instead of an attribute name yields an answer relation containing all the attributes of the original relation. - The select clause can specify a ‘range variable’ as a synonym for a relation name. This range variable is only used in our simple examples as a diminutive synonym. However it becomes essential in certain complex queries as we shall see later. - Example: To obtain the names of all students aho obtained an “A” and the id of the course in which they got an “A”, we could write the query: SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid = E.studid AND E.grade = ‘A’

  28. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (1) (1) ENTITY SETS TO TABLES Giventhe following ER diagram: We can translate it directly into an RMD model by the following SQL statement: CREATE TABLE Employees ( ssn CHAR (11), name CHAR (30), lot INTEGER’ PRMARY KEY (ssn)

  29. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (2) (2) Relationship Sets (without Constraints) to Tables (see section 3.5.2) Given the ER diagram shown below, we can specify the two entity sets as in the previous slide, while the relationship set is translated into an RMD relation as follows: CREATE TABLE Works-In2 ( ssn CHAR (11), did INTEGER, address CHAR (20), since DATE, PRIMARY KEY ( ssn, did, address), FOREIGN KEY (ssn) REFERENCES Employees FOREIGN KEY (address) REFERENCES Locations, FOREIGN KEY (did) REFERENCES Departments)

  30. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (3) The following ER diagram: is translated into an RMD relation by the following SQL statement: CREATE TABLE Reports_To (supervisor_ssn CHAR(11), subordinate_ssn CHAR(11). PRIMARY KEY (supervisor_ssn, subordinate_ssn), FOREIGN KEY (supervisor_ssn) REFERENCES Employees(ssn), FOREIGN KEY (subordinate_ssn) REFERENCES Employees (ssn) )

  31. TRANSLATING RELATIONSHIP SETS WITH KEY CONSTRAINTS (1) The following ER diagram: Can be into translated into relations using either of two approaches: (1) Defining three relations as follows: - A separate relation for the entity set ‘Employees’. - A separate relation for the entity set ‘Departments’. - A separate relation for the relationship set ‘Manages’. (2) Defining two relations as follows: - A separate relation for ‘Employees’. - A common relation for ‘Employees’ and for ‘Manages’.

  32. TRANSLATING RELATIONSHIP SETS WITH KEY CONSTRAINTS (2) - Approach (1) is obtained with the following SQL statement for the relationship set ‘Manages’ (in addition to two SQL statements for the two Entity sets): CREATE TABLE Manages ( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did) FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments )

  33. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (5) Approach (2) utilizes the fact that there can only be exactly one manager per department to combine the entity set ‘Department’ with the relationship set ‘Manages’ into a single relation named ‘Dept_Mgr’ using the following SQL statement: CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees) Remarks: - Note that ssn can take on null values indicating thereby that the concerned department has no manager which is permissible since there is no total participation constraint on the ‘Departments’ entity set.

  34. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (4) (3) Translating Relationship Sets with Participation Constraints Consider the following ER diagram: - Here we translate the ‘Manages’ relationship set into an RMD relation by the following SQL statement: CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR (20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE NO ACTION)

  35. LOGICAL DATABASE DESIGN: ER TO RELATIONAL (4)(cont’d) Remarks (1) We are using here the second approach mentioned in the previous slide because the constraint that every department must have a manager cannot be captured with the first approach. (2) Since the ER diagram shows a total participation constraint on the Department entity set as well as a key constraint, there must be exactly one manager per department. Therefore the attribute ssn cannot take on null values. (3) The NO ACTION specification (which is not really needed since it is the default) ensures that an Employee tuple cannot be deleted while it is pointed to by a ‘Dept_Mgr’ tuple. (4) The total participation constrained shown in the ‘Works_In’ relationship set cannot be specified in the above SQL statement unless we use tableconstraints or assertions which are considered later (see section 5.7).

  36. TRANSLATING WEAK ENTITY SETS INTO RELATIONS The following ER diagram: can be translated into a relation by the following SQL statement which uses the second approach described previously (combining ‘Policy’ and ‘Dependents’) CREATE TABLE Dep_Policy ( pname CHAR (20), age INTEGER, cost REAL, ssn CHAR(11), PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE )

  37. TRANSLATING CLASS HIERARCHIES (1) Given the ER diagram shown below: There are two basic approaches for translating it into the RMD: (1) Each of the three entity sets is mapped to an individual relation. (2) The two subclass entity sets only are each mapped to an individual relation,

  38. TRANSLATING CLASS HIERARCHIES (2) - Approach (1): - The superclass Employees is treated as before - The two subclasses are both treated in a similar fashion; therefore we need only consider the SQL statement for one of them, say ‘Hourly_Emps’. CREATE TABLE Hourly_Emps ( ssn CHAR(11), hours_worked INTEGER, hourly_wages REAL, PRIMARY KEY (ssn), FOREIGN KEY (ssn) REFERENCING Employees, ON DELETE CASCADE

  39. TRANSLATING CLASS HIERARCHIES (3) - Approach (2): CREATE TABLE Hourly_Emps (ssn CHAR(11), name CHAR(20), lot INTEGER, hours_worked INTEGER, hourly_wages REAL, PRIMARY KEY (ssn) ) Remarks: - (1) The first approach is general and always applicable. However queries which request information about one of the two subclasses may require combining the superclass relation with one of the subclasses. This is done with an expensive join operation. - (2) The second approach cannot be used if there is no covering constraint. It may also lead to certain anomalies if there is an overlap constraint.

  40. TRANSLATING ER DIAGRAMS WITH AGGREGATION Given the following ER diagram Translating it into relations can be done as follows: (1) The three entity sets are constructed in the usual fashion. (2) The ‘Monitors’ relationship set is described in a standard fashion by a relation with the following attributes: - the key attributes of ‘Employees’ (ssn) and of ‘Sponsors’ (did, pid) - the descriptive attributes of ‘Monitors’ (until) (3) The ‘Sponsors’ relationship set is described by a relation with attributes - the key attributes of the two Entity sets, (pid) and (did) - the descriptive attribute of ‘Sponsors’ (since)

  41. TRANSLATING ER DIAGRAMS WITH AGGREGATION (cont’d) Thus we get CREATE TABLE Sponsors ( pid CHAR(20), did INTEGER, since DATE, PRIMARY KEY (pid, did) ) Remarks: - Generally (and here in particular) ‘Sponsors’ is needed because (1) of the need to record the since attribute. (2) there is no total participation of ‘Sponsors’ in ‘Monitors’ (as indicated by the presence of a thin line drawn from ‘Monitors’ to the aggregate). - However, if the two conditions stated above are not present, the ‘Sponsors’ relation may be dropped.

  42. ER TO RELATIONAL: ADDITIONAL EXAMPLES (1) (2) (3) The above ER diagram can be translated into the RMD as follows: - (1) create a relation for ‘Employees’ in the usual way - (2) combine ‘Purchaser’ and ‘Policies’ thanks to the total participation and key constraint from ‘Policies’ to ‘Purchases’ - (3) combine ‘Beneficiary’ and ‘Dependents’ since the latter is a weak entity set Thus yielding the relations shown on the next slide.

  43. ER TO RELATIONAL: ADDITIONAL EXAMPLES (cont’d) (2) CREATE TABLE Policies ( policyid INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (policyid), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE ) (3) CREATE TABLE Dependents ( pname CHAR(20), ssn CHAR(11), age INTEGER, policyid INTEGER NOT NULL, PRIMARY KEY (pname, policyid, ssn), FOREIGN KEY (policyid, ssn) REFERENCES Policies, ON DELETE CASCADE )

  44. VIEWS (1) INTRODUCTION - Views are created to limit and control access by various classes of users to certain relations and attributes of the databases for reasons of confidentiality, security and logical data independence. - Views are not tables whose rows are explicitly stored in the database, but are computed as needed from a view definition. - Views can be used just like base tables (i.e. explicitly stored tables), in defining new queries or views. - Conceptually, whenever a view is used in a query, the view definition is first evaluated to obtain the necessary instance, and is then treated like other instances for answering the query. - However, the DDL and DML statements available to view users must be strictly controlled. Example (see section 3.6): CREATE TABLE B-Students (name, sid, course), AS SELECT S.name, S.sid, E.cid FROM Students S, Enrolled E WHERE S.sid = E.studid AND E.grade = ‘B’

  45. VIEWS (2) Updates on Views (see section 3.6) - Queries can be applied on views as on ordinary relations. - Updates on views are more problematic: - Allowing users to perform updates through views may cause serious problems (see sections 3.6.2 and 3.7). - SQL-92 and SQL:99 provide different capabilities (this is usually a sign of trouble). - I believe that updates on views by ordinary users should not be allowed unless they are performed through fixed rigorously defined transactions (as in the case of a bank or a travel reservation system). - Similar problems can arise wrt destruction and alteration of tables (see section 3.7). Such special operations should not be allowed to ordinary users, but should be limited to the Database Administration Group.

More Related