last updated 5sep12
Microsoft’s Access, now most popular microcomputerbased DBMS, is relational
Oracle, DB2, Informix, Sybase, Microsoft’s SQL Server, MySQL, PostgreSQL most popular enterprise DBMSs, all relational
Relations are represented physically as tables
Rows (tuples) correspond to individual records
Columns correspond to attributes
Student 

stuId 
lastName 
firstName 
major 
credits 
S1001 
Smith 
Tom 
History 
90 
S1002 
Chin 
Ann 
Math 
36 
S1005 
Lee 
Perry 
History 
3 
S1010 
Burns 
Edward 
Art 
63 
S1013 
McCarthy 
Owen 
Math 
0 
S1015 
Jones 
Mary 
Math 
42 
S1020 
Rivera 
Jane 
CSC 
15 
Class 

classNumber 
facId 
schedule 
room 
ART103A 
F101 
MWF9 
H221 
CSC201A 
F105 
TuThF10 
M110 
CSC203A 
F105 
MThF12 
M110 
HST205A 
F115 
MWF11 
H221 
MTH101B 
F110 
MTuTh9 
H225 
MTH103C 
F110 
MWF11 
H225 
Faculty 

facId 
name 
department 
rank 
F101 
Adams 
Art 
Professor 
F105 
Tanaka 
CSC 
Instructor 
F110 
Byrne 
Math 
Assistant 
F115 
Smith 
History 
Associate 
F221 
Smith 
CSC 
Professor 
Enroll 

stuId 
classNumber 
grade 
S1001 
ART103A 
A 
S1001 
HST205A 
C 
S1002 
ART103A 
D 
S1002 
CSC201A 
F 
S1002 
MTH103C 
B 
S1010 
ART103A 

S1010 
MTH103C 

S1020 
CSC201A 
B 
S1020 
MTH101B 
A 
For two sets D_{1} and D_{2}, the Cartesian product, D_{1} X D_{2} , is the set of all ordered pairs in which the first element is from D_{1} and the second is from D_{2}
A relation is any subset of the Cartesian product
Can form Cartesian product of 3 sets; a relation is any subset of the ordered triples so formed
Can extend to n sets, using ntuples
A relation schema, R, is a set of attributes A_{1}, A_{2},…,A_{n} with their domains D_{1}, D_{2},…D_{n}
A relation r on relation schema R is a set of mappings from the attributes to their domains r is a set of ntuples (A_{1}:d_{1}, A_{2}:d_{2}, …, A_{n}:d_{n}) such that d_{1} D_{1}, d_{2} D_{2} , …, d_{n} D_{n}
In a table to represent the relation, list the A_{i} as column headings, and let the (d_{1}, d_{2}, …d_{n}) become the ntuples, the rows of the table
A schema defines the following
Relation name
Attribute names and domains
Integrity constraints  e.g.,:
 The values of a particular attribute in all tuples are unique
 The values of a particular attribute in all tuples are greater than 0
Default values
Example
TableName (attr_{1}:type, attr_{2}:type, ... ) is a simplified nonSQL description of the table.
A running example used in the course: Presidential DB
These constraints, determined by the database designer, are part of the schema. They represent restrictions on the state (or sequence of states) of data base. The constraints are enforced by the DBMS.
Intrarelational  involve only one relation  are part of the relation definition
Interrelational  involve several relations  are part of the relation schema or database schema
Static IC is a limitation on the state of the database
Dynamic IC is a limitation on the sequence of the database states (supported by some DBMSs, but not in current the SQL standard)
Relations never have duplicate tuples, so you can always tell tuples apart; implies there is always a key (which may be a composite of all attributes, in worst case)
Superkey: set of attributes that uniquely identifies tuples
Candidate key: superkey such that no proper subset of itself is also a superkey (i.e. it has no unnecessary attributes)
Primary key: candidate key chosen for unique identification of tuples
Cannot verify a key by looking at an instance; need to consider semantic information to ensure uniqueness
A foreign key is an attribute or combination of attributes that is the primary key of some relation (called its home relation). Usually the home relation is some other relation but there can be cases of selfreferencing (recursuve relationship)
Values in a column (or columns) of a relation are unique: at most one row in a relation instance can contain a particular value(s)
Key  set of attributes satisfying key constraint
Minimality  no subset of a key is a key. When you determine a key, this rule should be applied.
Superkey  set of attributes containing key
Every relation has a key. The goal is to determine the "best" key, but a relation can have several keys:
Referential integrity => Item named in one relation must correspond to tuple(s) in another that describes the item
Examples:
We say "a_{1} is a foreign key of R_{1} referring to a_{2} in R_{2}" meaining that "if v is the value of a_{1}, then there is a unique tuple in R_{2} in which a_{2} has the same value v
Example
Note the foreign key might consist of several columns:
In general, when R_{1}(a_{1}, …a_{n}) references R_{2}(b_{1}, …b_{n}):
Domain, primary key, and foreign key are examples of structural (syntactic) constraints
Semantic constraints express rules of application: