1 / 23

The Relational Model

The Relational Model. CIS 8040 Database Management Systems. J.G. Zheng Jan 2010. Overview. What is the relational model? What are the most important practical elements of relational model?. Introduction. Edgar F. Codd (IBM), 1970

malo
Download Presentation

The Relational 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. The Relational Model CIS 8040 Database Management Systems J.G. Zheng Jan 2010

  2. Overview • What is the relational model? • What are the most important practical elements of relational model?

  3. Introduction • Edgar F. Codd (IBM), 1970 • One sentence to summarize relational database model (extremely brief): Data are organized in relations (tables), which are linked (relationship) by keys

  4. Introduction • What’s in a database? • Tables • Relationships • Metadata • How are data organized in a database? • Data are organized in tables, which are linked (relationship) by keys

  5. Relation • A relation is a two-dimensional table that has specific characteristics: • The table consist of rows and columns • Rows contain data about entity instances • All values in a row describes the same entity instance • Columns contain data about attributes of the entity • All values in a column are of the same kind

  6. Relation (continued) • Relation’s specific characteristics go on: • Each row is distinct • A Cell of the table hold a single value • Each column has a unique name • The order of the rows is unimportant • The order of the columns is unimportant

  7. Relation Example

  8. Non-Relation Examples (1)

  9. Non-Relation Examples (2)

  10. Are they the same?

  11. Terminology Contrast

  12. Keys • A key is one or morecolumns of a relation that is used to identify a record • Unique keys • Candidate key • Primary key • Surrogate key • Foreign key • Composite key

  13. Candidate Key/Primary Key • Candidate key • The minimum set of column(s) that uniquely identifies a single record (row) • Primary key • Is one of the candidate keys chosen to be the identifying key • There is only one primary key for a relation/table

  14. Primary Key • Primary key is a column/attribute that is used to uniquely identify a record • The value of this key column uniquely identifies a single record (row) • There is only one primary key for a table • Can the following attribute uniquely indentify a record? • Last Name • Student Number • Social Security Number • ISBN

  15. Artificial Primary Key/Surrogate Key • Very often it is difficult to have a natural attribute to identify one thing • A column is created arbitrarily and assign each record a unique number/id • Product Number, Product Id • Movie Id • Actor Id

  16. Relationship and Foreign Key • Relationship defines how tables (relations) are linked • Two tables are linked by a pair of keys • The primary key of one table • The foreign key in the linked table • These two keys are of the same kind (may be of different name)

  17. Relationship Example Primary Key (PK) Foreign Key (FK) Primary Key (PK)

  18. Foreign Key Example Primary Key Foreign Key Primary Key and Foreign Key are of the same type (string, number, etc.) and length, but they do not necessarily have the same name.

  19. Composite Key • Composite key: A key that contains two or more attributes (columns) • Example: • “FirstName” + “LastName” • “FirstName” + “LastName” + “BirthDate” • “FirstName” + “LastName” + “BirthDate” + “BirthCity” • … • All keys can be composite keys • True or False: The combination of all columns of a relation is a candidate key

  20. Referential Integrity • Every value of a foreign key must match a value of the primary key or be “null” • For example (slide #17) • In “Customer” table, “RepNum” is a foreign key (linked to the “Rep” table where “RepNum” is the primary key). • Then every value of “RepNum” in the “Customer” table must exist in the “Rep” table

  21. “Null” Value • “Null” means • value is inapplicable: e.g., Fax_Number = Null • value is unknown: e.g., Stud_Grade = Null • “Null” does not mean • zero • blank space • No primary key (or part of a composite primary key) can be null

  22. Common Data Types • Numeric • INTEGER, SMALLINT • DECIMAL(i,j), NUMBER(i,j) • Character/String • CHAR(n), VARCHAR(n), Text • Date • DATE, DATETIME • Data types reference • Access 2007: http://msdn.microsoft.com/en-us/library/bb208866.aspx • SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms187752.aspx

  23. Summary • Key concepts • Relation • Keys • Primary key, candidate key, surrogate key • Composite key • Foreign key • Referential integrity • Null

More Related