1 / 58

The Relational Data Model

Learn about the relational data model, integrity rules, and relational algebra in database systems. Understand primary and foreign keys, relationships, and formal definitions in the relational database model.

moreland
Download Presentation

The Relational Data 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. COT 4730: Applied Database Systems The Relational Data Model

  2. Outline • Relational model basics • Integrity rules • Rules about referenced rows • Relational Algebra

  3. INTRODUCTION • Relational database model - logical database model that represents a database as a collection of related tables • Relational schema - visual depiction of the relational database model • Most contemporary commercial DBMS software packages, are relational DBMS (RDBMS) software packages

  4. Tables • Relational database is a collection of tables • Heading: table name and column names • Body: rows, occurrences of data Student

  5. INTRODUCTION • Relation - table in a relational database • A table containing rows and columns • The main construct in the relational database model • Every relation is a table, not every table is a relation

  6. INTRODUCTION • Relation - table in a relational database • In order for a table to be a relation the following conditions must hold: • Each column must have a name (within one table, each column name must be unique) • Within one table, each row must be unique • Within each row, each value in each column must be single valued (multiple values of the content represented by the column are not allowed in any rows of the table) • All values in each column must be from the same (predefined) domain • Order of columns is irrelevant • Order of rows is irrelevant

  7. INTRODUCTION • Relational database - collection of related relations within which each relation has a unique name

  8. CREATE TABLE Statement CREATETABLE Student ( StdNoCHAR(11), StdFirstNameVARCHAR2(50), StdLastNameVARCHAR2(50), StdCityVARCHAR2(50), StdStateCHAR(2), StdZipCHAR(10), StdMajorCHAR(6), StdClassCHAR(6), StdGPADECIMAL(3,2) )

  9. Common Data Types • CHAR(L) • VARCHAR(L) OR VARCHAR2(L) • INTEGER • FLOAT(P) • Date/Time: DATE, TIME, TIMESTAMP • DECIMAL(W, R) • BOOLEAN

  10. Relationships

  11. Alternative Terminology

  12. PRIMARY KEY • Primary key - column (or a set of columns) whose value is unique for each row • Each relation must have a primary key • The name of the primary key column is underlined in order to distinguish it from the other columns in the relation

  13. PRIMARY KEY Relation with the primary key underlined

  14. COMPOSITE PRIMARY KEY • Composite primary key - a primary key that is composed of multiple columns • Column names of a composite primary key are underlined, because combined together they form the primary key

  15. FOREIGN KEY • Foreign key - column in a relation that refers to a primary key (or candidate key) column in another (referred) relation • A mechanism that is used to depict relationships in the relational database model • For every occurrence of a foreign key, the relational schema contains a line pointing from the foreign key to the corresponding primary key

  16. FOREIGN KEY • Example - Mapping a 1:M relationship

  17. FOREIGN KEY • Example - Mapping a 1:M relationship

  18. FOREIGN KEY • Example - Mapping a M:N relationship

  19. FOREIGN KEY • Example - Mapping a 1:1 relationship

  20. Integrity Rules • Entity integrity: primary keys • Each table has column(s) with unique values • Ensures entities are traceable • Referential integrity: foreign keys • Values of a column in one table match values in a source table • Ensures valid references among tables

  21. ENTITY INTEGRITY CONSTRAINT Entity integrity constraint — another compliance and violation example

  22. REFERENTIAL INTEGRITY CONSTRAINT Referentialintegrity constraint — compliance and violation examples

  23. Formal Definitions I • Superkey: column(s) with unique values • Candidate key: minimal superkey • Null value: special value meaning unknown or inapplicable • Primary key: a designated candidate key; cannot contain null values • Foreign key: column(s) whose values must match the values in a candidate key of another table

  24. Formal Definitions II • Entity integrity • No two rows with the same primary key value • No null values in any part of a primary key • Referential integrity • Foreign keys must match candidate key of source table • Foreign keys can be null in some cases • In SQL, foreign keys associated with primary keys

  25. Course Table Example CREATETABLE Course ( CourseNo CHAR(6), CrsDesc VARCHAR(250), CrsUnits SMALLINT, CONSTRAINT PKCourse PRIMARY KEY(CourseNo), CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc) )

  26. Enrollment Table Example CREATETABLE Enrollment ( OfferNo INTEGER, StdNo CHAR(11), EnrGrade DECIMAL(3,2), CONSTRAINTPKEnrollmentPRIMARY KEY(OfferNo, StdNo), CONSTRAINTFKOfferNoFOREIGN KEY(OfferNo) REFERENCES Offering(OfferNo), CONSTRAINTFKStdNoFOREIGN KEY(StdNo) REFERENCES Student(StdNo)

  27. Offering Table Example CREATE TABLE Offering ( OfferNo INTEGER, CourseNo CHAR(6) CONSTRAINTOffCourseNoRequiredNOT NULL, OffLocation VARCHAR(50), OffDays CHAR(6), OffTerm CHAR(6) CONSTRAINTOffTermRequiredNOT NULL, OffYear INTEGER CONSTRAINTOffYearRequiredNOT NULL, FacNo CHAR(11), OffTime DATE, CONSTRAINTPKOfferingPRIMARY KEY(OfferNo), CONSTRAINTFKCourseNoFOREIGN KEY (CourseNo) REFERENCES Course (CourseNo), CONSTRAINTFKFacNoFOREIGN KEY (FacNo) REFERENCES Faculty (facNo))

  28. Self-Referencing Relationships • Foreign key that references the same table • Represents relationships among members of the same set • Not common but important in specialized situations

  29. Faculty Data

  30. Hierarchical Data Display

  31. Faculty Table Definition CREATE TABLE Faculty ( FacNo CHAR(11), FacFirstName VARCHAR(50) NOT NULL, FacLastName VARCHAR(50) NOT NULL, FacCity VARCHAR(50) NOT NULL, FacState CHAR(2) NOT NULL, FacZipCode CHAR(10)NOT NULL, FacHireDate DATE, FacDept CHAR(6), FacSupervisor CHAR(11), CONSTRAINTPKFacultyPRIMARY KEY(FacNo), CONSTRAINTFKFacSupervisorFOREIGN KEY(FacSupervisor) REFERENCES Faculty (FacNo) )

  32. Relationship Window with 1-M Relationships

  33. M-N Relationships • Rows of each table are related to multiple rows of the other table • Not directly represented in the relational model • Use two 1-M relationships and an associative table

  34. Referenced Rows • Referenced row • Foreign keys reference rows in the associated primary key table • Enrollment rows refer to Student and Offering • Actions on referenced rows • Delete a referenced row (i.e., FK) • Change the primary key of a referenced row • Referential integrity should not be violated

  35. Possible Actions • Restrict: do not permit action on the referenced row • Cascade: perform action on related rows • Nullify: only valid if foreign keys accept null values • Default: set foreign keys to a default value

  36. SQL Syntax for Actions CREATE TABLE Enrollment ( OfferNo INTEGER, StdNo CHAR(11), EnrGrade DECIMAL(3,2), CONSTRAINTPKEnrollmentPRIMARY KEY(OfferNo, StdNo), CONSTRAINTFKOfferNoFOREIGN KEY (OfferNo) REFERENCES Offering ON UPDATECASCADE, CONSTRAINTFKStdNoFOREIGN KEY (StdNo) REFERENCES Student ON UPDATE CASCADE )

  37. Relational Algebra Overview • Collection of table operators • Transform one or two tables into a new table • Understand operators in isolation • Classification • Table specific operators • Traditional set operators • Advanced operators

  38. Subset Operators

  39. Subset Operator Notes • Restrict (or Selection) • Logical expression as input • Example: OffDays = 'MW' ANDOffTerm = 'SPRING' AND OffYear = 2017 • Project • List of columns is input • Duplicate rows eliminated if present • Often used together

  40. Subset Operator Notes Student

  41. Extended Cross Product • Building block for join operator • Builds a table consisting of all combinations of rows from each of the two input tables • Produces excessive data • Subset of cross product is useful (join)

  42. Extended Cross Product Example

  43. Join Operator • Most databases have many tables • Combine tables using the join operator • Specify matching condition • Can be any comparison but usually = • PK = FK most common join condition • Relationship diagram useful when combining tables

  44. Natural Join Operator • Most common join operator • Requirements • Equality matching condition • Matching columns with the same unqualified names • Remove one join column in the result • Usually performed on PK-FK join columns

  45. Natural Join Example

  46. Visual Formulation of Join

  47. Outer Join Overview • Join excludes nonmatching rows • Preserving nonmatching rows is important in some business situations • Outer join variations • Full outer join • One-sided outer join

  48. Outer Join Operators Full outer join Left Outer Join Right Outer Join Join Unmatched rows of the left table Matched rows using the join condition Unmatched rows of the right table

  49. Full Outer Join Example

  50. Visual Formulation of Outer Join

More Related