Relational model and diagram
Relational model
- Most databases are based on the relational model:
- Relational Model:
- try to make easier to update query. Make easier and faster to query data.
- first order logic to query the database
- Basic data type is a relation.
- For us, relation is a set.
- In SQL, relation is a table.
- A good review is available at http://en.wikipedia.org/wiki/Relational_databases
Informal definition of set
- Set as a "bag":
- Is a set that allows duplicate entities.
- You cannot specify how many times an element appears in a set. If
you could, it would be a multiset.
- Two set are equal iff they have the same elements.
- A and B sets are equal iff for all x belong A <=> x belong B
- EX: {2,3,4,5} {2,2,2,3,3,4,5} {2,3,{4,5},2}
Schemas
- A relation is a set of rows. Another word for row is tuple.
- The elements of each column are drawn from some specific domain.
- This table is a relation S(A,B).
- A relational schema defines a set of relations.
- To define a relation:
- We give a name (S)
- We choose the number of columns and we give the columns distinct names (A and B).
- We decide the domain for the elements in each column (Characters and Integers)
- We decide on any constraints
- Examples of possible constraints:
- A: all lowercase english letters B: positive integers less than 100.
- Constraints: any two tuple equal in value in A must be equal value in B.
- Since a relation is a set, if two tuples in a relation have the same value for a particular attribute (or a set of attributes) then they are the same.
Keys
- To define a relation, we will, in addition to the information described
above, define the following:
- Primary key
- Keys (Beyond primary)
- Foreign keys
- Additional Constraints
- Example:
- P(Name, ID, DOB, Grade) Q(Grade, Salary)
- Superkey: A set of columns in a relation is a superkey if and only
any two tuples that are equal on the elements of these columns are
(completely equal). Example: Name and ID are a sueprkey.
- A minimal superkey is called a key (or sometimes a candidate key).
- In the relation P(Name, ID, DOB, Grade)
- Name, ID is a superkey
- ID is a key
- AVS is a key
- Name, AVS is a superkey
- DOB, AVS is a superkey
- Pick one key to be primary key. All other keys are just keys.
Foreign Keys
- A foreign key is an attribute in a column that that uniquely identifies
a row of another table.
- A foreign key is a binary many-to-one relationship between tables
(partial function).
From ER to relational diagram
- ER:
- Relational diagram:
- To map an ER diagram relationship into the relational model:
- If the relationship is many to one or one to many, we add atributes as foregin keys to the "one" side. Example Born Employee or Child Employee.
- If the relationship is many to many we create a new table filled with
foreign keys attributes to each table in relationship. Example Likes
Employee and Animals.