The Relational Model 1History of Relational ModelCodd’s Original PaperCodd’s ReasonsAlternative: Network ModelPowerPoint PresentationSlide 7Slide 8Slide 9Relational Data StructureThe DomainSlide 12Slide 13Seven Characteristics of a relationExample of the Student table.Other terms...Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23The Relational Data ModelEntity IntegritySlide 26Slide 27Slide 28Slide 29Slide 30Slide 31Slide 32Foreign KeyReferential IntegrityForeign Key RulesSlide 36Example of the Class table.Slide 38Relational instances in the Student relationIntegrity ConstraintsEnforcing Integrity ConstraintsRelation schemaSlide 43Slide 44The Relational Model 1Prof. Sin-Min LeeDepartment of Mathematics and Computer ScienceProf. Sin-Min LeeDepartment of Computer ScienceHistory of Relational Model• First proposed by a E.F. Codd in 1970. Codd proposed the Relational model in 1970.“ A relational model of data for large shared data banks.” •He linked the representation of data with that of mathematical sets.• First research started at IBM’s San Jose Research Laboratory. Prototype was called System R.• Commercial RDBMS’s started to appear in late 1970’s and early 1980’s. Most well known is Oracle.Codd’s Original Paper“A Relational Model of Data for Large Shared Data Banks”Communications of the ACM, Volume 13, Number 6, June 1970Lower level (basement) of the new Martin Luther King, Jr. LibraryGet to roll the shelves apart to access journals.Codd’s ReasonsData independence from database implementation such as machine representationNatural structure of dataCan be analyzed mathematically (Codd was a mathematician by training)Alternative: Network ModelCharles A. Bachman 1973 ACM Turing Award Lecture “The Programmer as Navigator”Communications of the ACM, Volume 16, Number 11, November 1973, pp. 653-657Data Models·Codd suggests that any data model has three components: the data structures; the integrity constraints; the data manipulation operators.Relational Data StructureEMPLOYEEEmp# Name Emp Sex Mgr Emp#E1 Jones Male E65E6 Smith Male E28E28 Jones Female -Relation AttributeHeadingBodyFemaleMaleGenderDomainThe DomainEmp#E1E2E3NameRedBrownBlackMgr#-E1E1E1, E2, E3,E4Red, BrownBlack, BlueAttributesDomainsEmployeePerson NameEmp#Seven Characteristics of a relation• The name of the relation is different from all others.• Each cell of the relation contains only one value• Each attribute (or field) has a name that is distinct.• All the values of a particular attribute are from the same domain.• The order of the attributes makes no difference.• There are no duplicate tuples• The order of the tuples makes no difference.Example of the Student table.SocialNum FirstName LastName PhoneNum Class#556-34-2832 John Smith 924-1000 32245839-32-1929 Jane Doe 924-1929 99839312-39-5193 Some Body 555-1000 11021493-33-2910 Any One 555-1020 49303Primary KeyOther terms...Cardinality = Number of rowsDegree = Number of columnsCardinality = 6Degree = 5The Relational Data Model·DATA STRUCTURES - domain, attribute, relation, tuple, primary key, degree, cardinality.INTEGRITY CONSTRAINTS - entity integrity and referential integrity.DATA MANIPULATION OPERATIONS - defined through relational algebra and equivalent relational calculus.Entity Integrity ·No component of the Primary Key of a base relation is allowed to accept nulls.SurnameRedBlackRedBlackGiven NameJohnFredSalary$40,000$50,000$60,000$70,000Foreign Key·A foreign key is an attribute or attribute combination of one relation R2 whose values are required to match those of the primary key of relation R1 where R1 and R2 are not necessarily distinct. Note that a foreign key and the corresponding primary key should be defined on the same domain(s).Emp#e1e2e3enameredbluebrownDeptd1d2d3Worksfordeptd1d2DnamePayTaxArtEmployeeDeptForeign keyReferential IntegrityIf base relation R2 includes a foreign key FK matching the primary key PK of some base relation R1 then every value of FK in R2 must either (a) be equal to the value of PK in some tuple of R1, or(b) be wholly null.Note that PK and FK may comprise more than one attribute and that R1 and R2 are not necessarily distinct.( Stated more simply a foreign key should be a valid primary key value or null.)Foreign Key RulesFor each foreign key three rules need to be answered:Can the foreign key accept nulls ?What should happen on an attempt to delete the target of a foreign key reference?What should happen on an attempt to update the target of a foreign key reference ?Emp#e1e2e3enameredbluebrownDeptd1d2d3Worksfordeptd1d2DnamePayTaxArtEmployee DeptForeign Key RulesWhen should foreign key rules be checked ?Dept (Dept#, Dname, Budget)Emp (Emp#, Ename, Salary, WorksforDept#) WorksforDept# References Dept delete cascades, update cascadesDepend (Emp#, Dependname, Date-of-birth) Emp# references Emp delete cascades, update cascadesExample of the Class table.Class# SectionNum Professor32245 2 Lee11021 1 AgostonPrimary KeyClass# SectionNum Professor32245 2 Lee11021 1 AgostonSocialNum FirstName LastName PhoneNum Class#556-34-2832 John Smith 924-1000 32245839-32-1929 Jane Doe 924-1929 99839312-39-5193 Some Body 555-1000 11021493-33-2910 Any One 555-1020 49303Foreign key exampleRelational instances in the Student relationThe content of the table (a grouping of rows) are called relational instancesThese instances are unordered, and no two rows can be exactly alikeA relationalinstanceIntegrity ConstraintsAll DBMS’ must have some form of ICs to prevent invalid data from being entered.Domain constraints specify the set of values which may be used for each field.Other constraints, such as key or tuple, may limit which values from the domain can be used for a given field in a given instance.Key constraints require that each set of fields in the key be unique for each entry.Enforcing Integrity ConstraintsEach DBMS should have means to resolve invlaid entries such as:What happens if an entry that duplicates a key entry is entered?What should be done if an entry of a foreign key is deleted?A foreign key is a key where at least one field depends on a field from a different table.What happens when an invalid entry is entered?Relation schema Associated with each attribute is a set of values, called a domain, that can be assigned to the
View Full Document
Unlocking...