Database Management System - Chapter 3: The Relational Data Model

Chapter 3The Relational Data ModelOutline Relational model basicsIntegrity rulesRules about referenced rowsRelational AlgebraTablesRelational database is a collection of tablesHeading: table name and column namesBody: rows, occurrences of dataStudentCREATE TABLE StatementCREATE TABLE Student( StdSSN CHAR(11), StdFirstName VARCHAR(50), StdLastName VARCHAR(50), StdCity VARCHAR(50), StdState CHAR(2), StdZip CHAR(10), StdMajor CHAR(6), StdClass CHAR(6), StdGPA DECIMAL(3,2) )Common Data TypesCHAR(L)VARCHAR(L)INTEGERFLOAT(P)Date/Time: DATE, TIME, TIMESTAMPDECIMAL(W, R)BOOLEANRelationshipsAlternative TerminologyTable-orientedSet-orientedRecord-orientedTableRelationRecord-type, fileRowTupleRecordColumnAttributeFieldIntegrity RulesEntity integrity: primary keys Each table has column(s) with unique valuesEnsures entities are traceableReferential integrity: foreign keysValues of a column in one table match values in a source tableEnsures valid references among tablesFormal Definitions ISuperkey: column(s) with unique valuesCandidate key: minimal superkeyNull value: special value meaning value unknown or inapplicablePrimary key: a designated candidate key; cannot contain null valuesForeign key: column(s) whose values must match the values in a candidate key of another tableFormal Definitions IIEntity integrityNo two rows with the same primary key valueNo null values in any part of a primary keyReferential integrityForeign keys must match candidate key of source tableForeign keys can be null in some casesIn SQL, foreign keys associated with primary keysCourse Table ExampleCREATE TABLE Course( CourseNo CHAR(6), CrsDesc VARCHAR(250), CrsUnits SMALLINT, CONSTRAINT PKCourse PRIMARY KEY(CourseNo), CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc) ) Enrollment Table ExampleCREATE TABLE Enrollment( OfferNo INTEGER, StdSSN CHAR(11), EnrGrade DECIMAL(3,2), CONSTRAINT PKEnrollment PRIMARY KEY (OfferNo, StdSSN),CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo) REFERENCES Offering,CONSTRAINT FKStdSSN FOREIGN KEY (StdSSN) REFERENCES Student ) Offering Table ExampleCREATE TABLE Offering( OfferNo INTEGER, CourseNo CHAR(6) CONSTRAINT OffCourseNoRequired NOT NULL, OffLocation VARCHAR(50), OffDays CHAR(6), OffTerm CHAR(6) CONSTRAINT OffTermRequired NOT NULL, OffYear INTEGER CONSTRAINT OffYearRequired NOT NULL, FacSSN CHAR(11), OffTime DATE,CONSTRAINT PKOffering PRIMARY KEY (OfferNo),CONSTRAINT FKCourseNo FOREIGN KEY (CourseNo) REFERENCES Course,CONSTRAINT FKFacSSN FOREIGN KEY (FacSSN) REFERENCES Faculty )Self-Referencing RelationshipsForeign key that references the same tableRepresents relationships among members of the same setNot common but important in specialized situationsFaculty DataHierarchical Data DisplayFaculty Table DefinitionCREATE TABLE Faculty( FacSSN 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), CONSTRAINT PKFaculty PRIMARY KEY (FacSSN),CONSTRAINT FKFacSupervisor FOREIGN KEY (FacSupervisor) REFERENCES Faculty ) Relationship Window with 1-M RelationshipsM-N RelationshipsRows of each table are related to multiple rows of the other tableNot directly represented in the relational modelUse two 1-M relationships and an associative tableReferenced RowsReferenced rowForeign keys reference rows in the associated primary key tableEnrollment rows refer to Student and OfferingActions on referenced rowsDelete a referenced rowChange the primary key of a referenced rowReferential integrity should not be violatedPossible ActionsRestrict: do not permit action on the referenced rowCascade: perform action on related rowsNullify: only valid if foreign keys accept null valuesDefault: set foreign keys to a default valueSQL Syntax for ActionsCREATE TABLE Enrollment( OfferNo INTEGER NOT NULL, StdSSN CHAR(11) NOT NULL, EnrGrade DECIMAL(3,2), CONSTRAINT PKEnrollment PRIMARY KEY(OfferNo, StdSSN),CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo) REFERENCES Offering ON DELETE RESTRICT ON UPDATE CASCADE,CONSTRAINT FKStdSSN FOREIGN KEY (StdSSN) REFERENCES Student ON DELETE RESTRICT ON UPDATE CASCADE ) Relational Algebra OverviewCollection of table operatorsTransform one or two tables into a new tableUnderstand operators in isolationClassificationTable specific operatorsTraditional set operatorsAdvanced operatorsSubset OperatorsSubset Operator NotesRestrictLogical expression as inputExample: OffDays = 'MW' AND OffTerm = 'SPRING' AND OffYear = 2006 ProjectList of columns is inputDuplicate rows eliminated if presentOften used togetherExtended Cross ProductBuilding block for join operatorBuilds a table consisting of all combinations of rows from each of the two input tablesProduces excessive dataSubset of cross product is useful (join)Extended Cross Product ExampleJoin OperatorMost databases have many tablesCombine tables using the join operatorSpecify matching conditionCan be any comparison but usually =PK = FK most common join conditionRelationship diagram useful when combining tablesNatural Join OperatorMost common join operatorRequirementsEquality matching conditionMatching columns with the same unqualified namesRemove one join column in the resultUsually performed on PK-FK join columnsNatural Join ExampleVisual Formulation of JoinOuter Join OverviewJoin excludes non matching rowsPreserving non matching rows is important in some business situations Outer join variationsFull outer joinOne-sided outer joinRight Outer JoinOuter Join OperatorsLeft Outer JoinJoinMatched rows using the join conditionUnmatched rows of the left tableUnmatched rows of the right tableFull outer joinFull Outer Join ExampleVisual Formulation of Outer JoinTraditional Set OperatorsA UNION BA INTERSECT BA MINUS BUnion CompatibilityRequirement for the traditional set operatorsStrong requirementSame number of columnsEach corresponding column is compatiblePositional correspondenceApply to similar tables by removing columns firstSummarize OperatorDecision-making operatorCompresses groups of rows into calculated valuesSimple statistical (aggregate) functionsNot part of original relational algebraSummarize ExampleDivide OperatorMatch on a subset of valuesSuppliers who supply all partsFaculty who teach every IS courseSpecialized operatorTypically applied to associative tables representing M-N relationshipsDivision ExampleRelational Algebra SummarySummaryRelational model is commercially dominantLearn primary keys, data types, and foreign keysVisualize relationshipsUnderstanding existing databases is crucial to query formulation