The Relational Model CIS 8040 Database Management Systems

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 One sentence to summarize relational database model (extremely brief): Data are organized in relations (tables), which are linked (relationship) by keys

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

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

Relation (continued) Relation’s specific characteristics go on: n n n 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

Relation Example

Non-Relation Examples (1)

Non-Relation Examples (2) Emp_Name Emp_Age Emp_Phone Emp_Email Jordan Hail 45 404 -652 -6325 678 -584 -1754 JH@work. com, JH@home. com Jack Parsons 28 404 -652 -8745 404 -321 -5687 JP@work. com 770 -415 -8415 TJ@work. com, TJ@home. com 404 -697 -2587 RM@work. com, RM@home. com 678 -584 -1754 JH@work. com, JH@home. com Tom Jackson Liz Marsten Jordan Hail 23 678 -854 -1245 Thirty 404 -884 -8753 45 404 -652 -6325

Are they the same?

Terminology Contrast Database industry Table Row Column File processing File Record Field Academic Relation Tuple Attribute

Keys A key is one or more columns of a relation that is used to identify a record n Unique keys w Candidate key w Primary key w Surrogate key n n Foreign key Composite key

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

Primary Key Primary key is a column/attribute that is used to uniquely identify a record n n 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? n n Last Name Student Number Social Security Number ISBN

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 n n n Product Number, Product Id Movie Id Actor Id

Relationship and Foreign Key Relationship defines how tables (relations) are linked Two tables are linked by a pair of keys n n n 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)

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

Foreign Key Example Department Dept. ID Employee Primary Key Emp. ID Dept. Name Emp. Name Location Department 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.

Composite Key Composite key: A key that contains two or more attributes (columns) Example: n n “First. Name” + “Last. Name” + “Birth. Date” + “Birth. City” … All keys can be composite keys True or False: The combination of all columns of a relation is a candidate key

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

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

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

Summary Key concepts n n Relation Keys w Primary key, candidate key, surrogate key w Composite key w Foreign key n n Referential integrity Null