Other relational model sites:
The model was proposed by E.F. Codd in 1970 and is the basic philosophy behind most of today's modern databases. DB2, Oracle, Sybase, Ingres, DBASE IV, and Access use the relational model.
More formal definitions:
A Domain (D) is a set of atomic values (frequently suggested by a data type and format; sometimes a precise list or constraints are provided). For example, Month might be described as the set of integers from 1 to 12. First Name might be described as a set of alphabetic strings that are less than 25 characters.
An attribute Ai indicates the role of a domain in a relation. E.G. MonthStarted in an employee relation is an attribute that uses the domain Month. This attribute indicates the month the employee started employment.
dom(Ai) = domain of Ai
Let R be a relational specification (intension) and r(R) describe the extension (the list of actual rows in the table). If R(A1...An), then r(R) is a proper subset of (dom(A1) x dom(A2) x ...x dom(An).
Let ti (tuple i) be a particular row in R (vi1,vi2,vi3...vin) where vij stands for the value of Aj for tuple i. Also let ti[Aj]=vij. A key on a relation is an attribute K such that ti[K] not equal tm[K] for any i and m (i and m not equal)
A relation can be thought of a set of tuples. A relational database is a set of relations. Each relation has a key.
Relationships between table are implied by common attributes between the two tables.
Employee (EID, Name, Dept, Salary) EID Name Dept Salary 111 Parks Eng. 40,000 102 Jones Prod. 25,000 104 Bryan Man. 30,000 107 Thomas Man. 31,000 108 Rich Eng. 35,000A relation is a two dimensional table in which--
1. All entries in table are single valued
2. The entries in a column are all of the same kind (i.e.,
come from the same domain: D)
3. Each column has a unique name (attribute name)
4. Theoretically the order of the columns don't matter, but
in DBMS sometimes we say that they do matter.
5. No two rows in a table are identical.
6. Order of rows does not matter.
Other facts about relations.
1. The rows in a table are called tuples. The example
above has five tuples.
2. We say a relation is of degree n if there are n columns
(i.e., attributes). If a relation is of degree n, we say a
row is a n-tuple. In the relation above, the rows are 4-
tuples.
3. A collection of attributes that uniquely identify a
tuple is called a key. There maybe more than one possible
key. Each possible key is called a candidate key.
4. The candidate key which is chosen to uniquely identify a
tuple is called the primary key.
5. An attribute of a relation that is a key in a different
relation is called a foreign key.
6. Because a relation is a set of n-tuples, the query
language of a relational model can be based on set theory
and predicate logic.
College(*college name, dean, address)
Dept.(*dept name, college name, chairman, floor)
Note: * indicates primary key.
College College Name Dean Address Business Stem Box 4320 Engineering Smith Box 4222 Arts & Science Jones Box 3218 Dept. Dept Name College Name Chairman Floor ISQS Business Yadav 6 Finance Business Hein 9 Marketing Business Howell 8 EE Engineering Bart 2 CS Engineering Lakhani 2 Physics Arts & Science Einstein 3College Name in Dept. is a foreign key since it comes from the same domain as college name in College (and this is the primary key in college).
2. Simple Network. Assume that an employee belongs to one department and a department can have many employees. Also assume that, an employee can be part of one project and a project has many employees. When an entity has a one-to- many relationship with more than one entity type, we have a simple network. A simple network is represented similar to a tree (see 1 above). We could have
Dept. (*dept name, college, chairman, floor)
Project (*project id, project name, funding)
Employee (*EID, emp.name, d.name, p.id, salary)
where d.name comes from the same domain as dept name and thus is a foreign key. And p.id is a foreign key for project id.
Dept. Dept Name College Name Chairman Floor ISQS Business Yadav 6 Finance Business Hein 9 Marketing Business Howell 8 EE Engineering Bart 2 CS Engineering Lakhani 2 Physics Arts & Science Einstein 3 Project Project ID Project Name Funding P47 Fertilizer Simulation 100,000 P63 Solar Car 120,000 Employee EID EmpName D.name P.ID Salary 100 Bob Smith ISQS P47 50,000 125 Sally Jones Finance P47 55,000 135 Sarah Langston Physics P63 35,000 444 Ted Danson ISQS P63 55,0003. Complex Networks (many-to-many relation). Usually to form a complex network we need to create an intersection relation.
Student takes many classes and a class can have many students. In this case we cannot include the class id with student or the student id with class. (We cannot have multivalued attributes). These two entities and the relationship would create three relations.
Student(*SID,S.name,major)
Class(*Cname, Time, room)
Student_class(*SID,*Cname), where the composite SID and
Cname form the key.
Student SID S.name major 0110 Anderson accounting 1000 Glover Anthropology Class Cname Time Room BA482 MW3 c-150 BD445 TR2 c-213 BA491 TR3 c-141 AP150 MWF9 D-412 Student_class SID Cname 0110 BA482 0110 BD445 0110 BA491 1000 AP150 1000 BD445Sometimes we can add other attributes to intersection records. For example, in Student_class we could add grade.
4. Networks without intersection records. When common attributes are not primary keys, a complex network is created without an intersection record.
Customer (*Name, Address, Favorite Food)
Restaurant (*Rname, specialty)
(assume Name is unique)
Customer Name Address Favorite Food Smith 1134 5th Mexican Harvey 301 Elm Chinese Jones 141 7th American Thompsan 111 Apple Mexican Chung 215 8th Chinese Restaurant Rname Specialty Jorje Mexican Great Wall Chinese Steak House American Al's Mexican Chinese Kitchen ChineseTry a sample problem converting a relational model to an ER model.
Selection retrieves rows that meet some condition out of a relation. In the above restaurant example, Select all rows where favorite food = "Mexican" would retrieve, the Smith and Thompson row.
Projection retrieves the identified columns from a relation. Give me the name of all restaurants in town would list just the Rname column of the restaurant relation.
Join connects two relations. List the chairman of all employees would connect the d.name attribute in employees with the Dept. name attribute in department and then list the chairman with the employee. When a foreign key is joined with a primary key from another relation, the join is called a natural join.
Many times we use selection, projection and join in the same query. The query "find the chairman of Bob Smith," selects the Bob Smith row, joins this row with the Dept. relation and then only displays (projection) the chairman name.