4 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 - The Relational Data Model Outline for the Relational Data Model Definitions  Relations  Attributes  Tuples  Relations  Primary Keys Comparison to other models Components Relation Properties Kinds of Relations Objectives of the Model4 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relations Introduced by Dr. E.F. (“Ted”) Codd in 1970… based on set theory SUPPLIER Relation name Tuples Attributes Attribute value Attribute name Attributes Attribute name All attribute names must be unique within a relation. Attribute domain A set of all possible values that can be taken by an attribute. Dsname = DStatus = DCity = Attribute values Values contained currently in an attribute Attribute values describe; Attributes do not Value set – A subset of the Domain. Actual values found in the org. DB. Relation degree The number of attributes in a relation4 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Tuples Aggregation of attribute values tuple1 = ( s1, ‘Smith', 20. ‘London’) tuple2 = ( s3, ‘Blake’, 30, ‘Paris' ) Cardinality The number of tuples in a relation (Not to be confused with cardinality in the E-R model) SUPPLIER The Objectives of the Relational Model  Data Independence - Logical/Physical  Communicability - The use of an easy to convey, simple structure  Set Processing - Operations performed on “chunks” of data4 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relational vs. Other Models  Terminology - we use the terms tuple, attribute, table etc. to signify we are using a totally different model (mathematical)  Implementation Independence - a separate logical model from physical  Logical Key Pointers - implicit in the values of the attributes  High-level Programming Language - e.g. query languages  Normalization - a tool for improving the integrity of the database Components of the Relational Model To be truly relational all three must be present:  Structural  All data represented as a two-dimensional table  Integrity  Referential - non-null foreign keys must refer to a tuple that exists  Entity - no part of the primary key may be null  User-defined - “roll your own”  Manipulative  Operators used to transform relations to other relations  E.g. - Relational Algegra, Relational Calculus, QBE  Must have a DDL  Must have a DML4 - 5 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relation Properties (Structural)  Each tuple has the same number of attributes as all other tuples in the same relation (even if a value is “null”)  Each column has a distinct name (attribute)  Each column contains values about the same attribute  Each attribute is atomic. I.e. it can’t be further decomposed. E.g., Name -> FirstName, MI, LastName  Each tuple is distinct or unique  Each tuple has a unique identifier  The order of the attributes is immaterial  The order of the tuples is immaterial  All attributes must contain atomic values only. I.e., a “grades” field can only contain one value per tuple. Relational DBMS Rules (Codd) 0. The DBMS may be managed entirely through relational capabilities 1. The Information Rule - all data and metadata (data about data) are represented in one way, as values in a two-dimensional table 2. The Guaranteed Access Rule - in a relational database you can retrieve any value in any table (because all the tuples are unique) 3. Systematic Treatment of Null Values - e.g. a payrate of 0 is not the same as someone without a payrate (null value) 4. An active on-line catalog based on the relational model. (Users can query metadata just like data) 5. The Comprehensive Data Sub-language Rule - there is a minimum, comprehensive character-string syntax language to define the database 6. The View Updating Rule - data may be change through a new view4 - 6 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relational DBMS Rules (Cont’d) 7. High-level insert, update, and delete - set processing also applies not only to looking at data, but also to changing data 8. Physical Data Independence - the relational model is a logical model i.e. it is not constrained by implementation 9. Logical Data Independence -the separation of views of data from schema (definition of data). 10. Integrity Independence - relational model doesn’t have built in constraints, they are to be maintained by the DBMS 11. Distribution Independence - the users don’t need to know where the data resides 12. The Non-subversion Rule - can’t violate any of the rules in a relational database Primary Keys Candidate key -- One or a group of attributes K that satisfy  Uniqueness. No two tuples have the same value for K.  Minimality. If K is composite, no component of K can be eliminated without destroying the Uniqueness property. Primary key -- One of the candidate keys Alternate keys -- Other candidate keys In-class Exercise: Employee( SSN, DateOfBirth, DriverLicenseNo, Name, Salary ) Student (SID, Name, Age, Address, GPA) Section (sect#, InstructorID, Course#, Time, Date) Schedule (Room#, Building, Time, Date, Course#)4 - 7 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Foreign Key Is an attribute that references a key attribute of other relation FK may or may not be an key attribute In-class Exercise: Salesrep(SR#, Name, Address, Commrate) Customer(C#, CNAme, Caddress, Balance, Credlim, SR#) Orders(Ord#, Orddate, C#)