1 / 63

Exam 2 Review

Exam 2 Review. Dr. Bernard Chen Ph.D. University of Central Arkansas. Relational Model Concepts. The relational model represents the database as a collection of relations Each relation resembles a table of values

hollye
Download Presentation

Exam 2 Review

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. Exam 2 Review Dr. Bernard Chen Ph.D. University of Central Arkansas

  2. Relational Model Concepts • The relational model represents the database as a collection of relations • Each relation resembles a table of values • When a relation is thought of as a table of values, each row in the table represents a collection of related data values

  3. Formal Terminology • A row is called a tuple • A column header is called an attribute • The table is called relation

  4. Domain • A Domain D is a set of atomic values. • Atomic means that each value in the domain is indivisible as far as the relational model is concerned • It means that if we separate an atomic value, the value itself become meaningless, for example: • SSN • Local_phone_number • Names • Employee_ages

  5. Domain Constrains • Each attribute A must be an atomic value from the dom(A) • The data types associated with domains typically include standard numeric data type for integers, real numbers, Characters, Booleans, fix-length strings, time, date, money or some special data types

  6. Key Constrains • A relation is defined as a set of tuples • By definition, all elements of a set are distinct • This means that no two tuples can have the same combination of values for all their attributes • Superkey: a set of attributes that no two distinct tuples in any state r of R have the same value • Every relation has at least one default superkey – the set of all its attributes

  7. Key Constrains • A superkey can have redundant attributes, so a more useful concept is that of a KEY which has no redundancy • Key satisfied two constrains: • Two distinct tuple in any state of the relation cannot have identical values for the attributes in the key • It is a minimal superkey

  8. Key Constrains • For example, consider STUDENT relation • The attribute set {SSN} is a key of STUDENT because no two student can have the same value for SSN • Any set of attributes that includes SSN – for example {SSN, Name, Age} – is a superkey

  9. Entity Integrity • Entity Integrity: • The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). • This is because primary key values are used to identify the individual tuples. • t[PK]  null for any tuple t in r(R) • If PK has several attributes, null is not allowed in any of these attributes • Note: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key.

  10. Referential Integrity Constraint • Referential Integrity Constraint is specified between two relations and is used to maintain the consistency among tuples in the two relations • Informally define the constrain: a tuple in one relation must refer to an existing tuple in that relation • For example, the Dno in EMPLOYEE gives the department number for which each employee works, this number must match the Dnumber value in DEPARTMENT

  11. Referential Integrity Constraint • Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2. • A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].

  12. Displaying a relational database schema and its constraints • Each relation schema can be displayed as a row of attribute names • The name of the relation is written above the attribute names • The primary key attribute (or attributes) will be underlined • A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table • Can also point the the primary key of the referenced relation for clarity • Next slide shows the COMPANY relational schema diagram

  13. Referential Integrity Constraints for COMPANY database

  14. Modification and Updates • In this section, we concentrate on the database Updates and Modification • There are threee basic operation: Insert, Delete and Modify • Insert is used to insert a new tuple or tuples in a relation • Delete is used to delete tuples • Update (or Modify) is used to change the values of some attributes

  15. Possible violations for each operation • INSERT may violate any of the constraints: • Domain constraint: • if one of the attribute values provided for the new tuple is not of the specified attribute domain • Key constraint: • if the value of a key attribute in the new tuple already exists in another tuple in the relation • Referential integrity: • if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation • Entity integrity: • if the primary key value is null in the new tuple

  16. Possible violations for each operation • DELETE may violate only referential integrity: • If the primary key value of the tuple being deleted is referenced from other tuples in the database • Can be remedied by several actions: RESTRICT, CASCADE, SET NULL • RESTRICT option: reject the deletion • CASCADE option: propagate the new primary key value into the foreign keys of the referencing tuples • SET NULL option: set the foreign keys of the referencing tuples to NULL • One of the above options must be specified during database design for each foreign key constraint

  17. Possible violations for each operation • UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified • Any of the other constraints may also be violated, depending on the attribute being updated: • Updating the primary key (PK): • Similar to a DELETE followed by an INSERT • Need to specify similar options to DELETE • Updating a foreign key (FK): • May violate referential integrity • Updating an ordinary attribute (neither PK nor FK): • Can only violate domain constraints

  18. Chapter Outline • ER-to-Relational Mapping Algorithm • Step 1: Mapping of Regular Entity Types • Step 2: Mapping of Weak Entity Types • Step 3: Mapping of Binary 1:1 Relation Types • Step 4: Mapping of Binary 1:N Relationship Types. • Step 5: Mapping of Binary M:N Relationship Types. • Step 6: Mapping of Multivalued attributes. • Step 7: Mapping of N-ary Relationship Types. • Mapping EER Model Constructs to Relations • Step 8: Options for Mapping Specialization or Generalization. • Step 9: Mapping of Union Types (Categories).

  19. Step 1: Mapping of Regular Entity Types • Step 1: Mapping of Regular Entity Types. • For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E. • Choose one of the key attributes of E as the primary key for R. • If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R.

  20. Step 2: Mapping of Weak Entity Types • For each weak entity type W in the ER schema with owner entity type E, create a relation R & include all simple attributes (or simple components of composite attributes) of W as attributes of R. • Also, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s). • The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.

  21. Step 3: Mapping of Binary 1:1 Relation Types • For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R.

  22. Step 3: Mapping of Binary 1:1 Relation Types • Foreign Key approach: Choose one of the relations-say S-and include a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. • Example: 1:1 relation MANAGES is mapped by choosing the participating entity type DEPARTMENT to serve in the role of S, because its participation in the MANAGES relationship type is total.

  23. Step 4: Mapping of Binary 1:N Relationship Types • For each regular binary 1:N relationship type R, identify the relation S that represent the participating entity type at the N-side of the relationship type. • Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R. • Include any simple attributes of the 1:N relation type as attributes of S.

  24. Step 5: Mapping of Binary M:N Relationship Types • For each regular binary M:N relationship type R, create a new relation S to represent R. • Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S. • Also include any simple attributes of the M:N relationship type (or simple components of composite attributes) as attributes of S.

  25. Step 6: Mapping of Multivalued attributes • For each multivalued attribute A, create a new relation R. • This relation R will include an attribute corresponding to A, plus the primary key attribute K-as a foreign key in R-of the relation that represents the entity type of relationship type that has A as an attribute. • The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.

  26. Step 7: Mapping of N-ary Relationship Types • For each n-ary relationship type R, where n>2, create a new relationship S to represent R. • Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. • Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S.

  27. Summary of Mapping constructs and constraints Table 7.1 Correspondence between ER and Relational Models ER Model Relational Model Entity type “Entity” relation 1:1 or 1:N relationship type Foreign key (or “relationship” relation) M:N relationship type “Relationship” relation and two foreign keys n-ary relationship type “Relationship” relation and n foreign keys Simple attribute Attribute Composite attribute Set of simple component attributes Multivalued attribute Relation and foreign key Value set Domain Key attribute Primary (or secondary) key

  28. Step8: Options for Mapping Specialization or Generalization • Option 8A: Multiple relations-Superclass and subclasses • Option 8B: Multiple relations-Subclass relations only • Option 8C: Single relation with one type attribute • Option 8D: Single relation with multiple type attributes

  29. Option 8A: Multiple relations-Superclass and subclasses

  30. Option 8B: Multiple relations-Subclass relations only

  31. Option 8C: Single relation with one type attribute

  32. Option 8D: Single relation with multiple type attributes

  33. Step 9: Mapping of Union Types (Categories) • For mapping a category whose defining superclass have different keys, it is customary to specify a new key attribute, called a surrogatekey, when creating a relation to correspond to the category

  34. Step 9: Mapping of Union Types (Categories)

  35. Step 9: Mapping of Union Types (Categories)

  36. Create Table example CREATE TABLE student (StudentId CHAR (5), Last VARCHAR2 (15) CONSTRAINT student_last_nn NOT NULL, First VARCHAR2 (15) CONSTRAINT student_first_nn NOT NULL, Street VARCHAR2 (25), City VARCHAR2 (15), State CHAR (2) DEFAULT 'NJ', Zip CHAR (5), StartTerm CHAR (4), BirthDate DATE, FacultyId NUMBER (3), MajorId NUMBER (3), Phone CHAR (10), CONSTRAINT student_studentid_pk PRIMARY KEY (StudentID));

  37. NAMING RULES AND CONVENTIONS • A table is an object that can store data in an Oracle database. • When you create a table, you must specify • the table name, • the name of each column, • the data type of each column, • and the size of each column.

  38. Data Types • When a table is created, each column in the table is assigned a data type. • Some important data types: • Varchar2 • Char • Number

  39. Types of Constraints There are two types of constraints: 1.Integrity constraints: define both the primary key and the foreign key with the table and primary key it references. 2. Value constraints: define if NULL values are disallowed, if UNIQUE values are required, and if only certain set of values are allowed in a column.

  40. Popular Constraint abbreviations • Primary Key pk • Foreign Key fk • Unique uk • Check ck • Not Null nn

  41. Defining a Constraint • A constraint can be created at the same time the table is created, or it can be added to the table afterward. There are two levels where a constraint is defined: • Column level. • Table level.

  42. Column level • A column-level constraint references a single column and is defined along with the definition of the column.  • Any constraint can be defined at the column level except for a FOREIGN KEY and COMPOSITE primary key constraints. Column datatype [CONSTRAINT constraint_name] constraint_type Example: Building VARCHAR2(7) CONSTRAINT location_building_nn NOT NULL

  43. Table level • A table-level constraint references one or more columns and is defined separately from the definitions of the columns. • Normally, it is written after all columns are defined. • All constraints can be defined at the table level except for the NOT NULL constraint. [CONSTRAINT constraint_name] constraint_typ (Column, . . .), Example: CONSTRAIN location_roomid_pk PRIMARY KEY(Roomid)

  44. The Primary Key Constrain • The PRIMARY KEY constraint is also known as the entity integrity constraint • It creates a primary key for the table. A table can have only one primary key constraint.  • If a table uses more than one column as its primary key (i.e., a composite key), the key can only be declared at the table level. 

  45. The Primary Key Constrain • At the column level, the constraints is defined by DeptId NUMBER (2) CONSTRAINT dept_deptid_pk PRIMARY KEY, • At the table level, the constraint is defined by CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId),

  46. The FOREIGN KEY Constraint • The FOREIGN KEY constraint is also known as the referential integrity constraint. • It uses a column or columns as a foreign key, and it establishes a relationship with the primary key of the same or another table. 

  47. The FOREIGN KEY Constraint • To establish a foreign key in a table, the other referenced table and its primary key must already exist.  • Foreign key and referenced primary key columns need not have the same name, but a foreign key value must match the value in the parent table’s primary key value or be NULL

  48. The FOREIGN KEY Constraint • At the table level ONLY CONSTRAINT student_facultyid_fk FOREIGN KEY(FacultyId) REFERENCES faculty (FacultyId),

  49. The NOT NULL Constraint • The NOT NULL constraint ensures that the column has a value and the value is not a null value • A space or a numeric zero is not a null value • At the column level ONLY, the constraint is defined by: Name VARCHAR2(15) CONSTRAINT faculty_name_nn NOT NULL,

More Related