DBMS-2 (relational model)

schema vs. instance (more formally)

A domain D is a set of atomic values A relation schema R(A1, A2, .., An) is a: -> relation name (R) -> list of attributes (A) -> each attribute Ai is the name of a role played by some domain D in the relational schema R A relation r(R) is a subset of dom(A1) * dom(A2) * ... * dom(An) each element in a relation, called a TUPLE, is a collection of n values

Keys

A key is a minimal superkey; a superkey from which we cannot remove any attributes and still be able to uniquely identify tuples in a relation common keys -- ID number, social insurance number, etc A relational schema may have more than one key -> each key is called a candidate key -> one designated as the primary key

what is a Relation?

A relation is a 2-dimensional table of values (rows and columns) each row, or TUPLE, is a collection of related facts The degree of the relation is the number of attributes(or columns) in the relation each column represents an attribute each row is an instance of the relation So, a relation is a big table of facts -> each column contains the same attributes data with the same data type -> each row describes a real-world instance of the relation A relational database contains one or more relations (or tables)

modify operation

Change the value for one or more attributes in a relation Example: modify SALARY of Faculty where ID# = 1234 to 30000 • Modifying a primary key is like deleting a tuple and adding a new one. (Same violations may apply). ➡ this is why it is difficult to change a primary key such as a username on a website or a student id number.

insert may violate constraints

Key Constraint: insert values (554433,"Bob", 25143.56,"ENGL") into employee (Will fail if the employee number "554433" is already in the table) Entity Integrity Constraint: insert values (NULL,"Bob", 25143.56,"ENGL") into employee (primary key cannot be NULL) Referential Integrity Constraint: insert values (554433,"Bob", 25143.56,"ENGL") into employee (Will fail if the "ENGL" is not a code for a department)

schema vs. instance

The name of the relation and the set of attributes is called the schema (or the intentions) The current values in the relation represent an instance (or extension) of the data

delete operation

The only constraint which can be violated is the referential integrity constraint (i.e. A tuple in another relation references the tuple that is slated for deletion).

foreign keys

a foreign key in R is a set of attributes FK in R such that FK is a primary key of some other relation R' a foreign key is used to specify a referential integrity constraint

semantic integrity constraints

constraints on data values such as: -> the salary of an employee must not exceed that of his supervisor -> the total of available seats must be > 0 in order for a reservation to be made -> a person's DOB must be before the current date

types of constraints

domain constraints key constraints integrity constraints -> entity integrity constraint -> referential integrity constraint -> semantic integrity constraint

operations on relations

include: insert, delete, modify, and retrieval some operations can violate database constraints

Integrity constraints

integrity constraints are specified on a schema and hold for every instance of the schema entity integrity constraint -> no primary key value can be NULL referential integrity constraint -> if R1 refers to R2 then t1 ∈ r1(R1) must refer to an existing t2 ∈ r2(R2)

insert operation

provide a list of attribute values to be inserted (i.e., a new tuple) (Note: the system assumes that the values you are inserting are of the same number and in the same order that you created the table in the database. If you want a different number of attributes, or to change the order, you have to specify the order / value difference.)

why relational model is so popular

supported by a mathematical model relations (tables) are a good tool to use when communicating information to users and developers efficient implementations exist for the storing of relational information in the form of RDBMS

key constraints

the value of a key uniquely identifies a tuple in a relation a superkey K is subset of attributes of R such that: - no 2 tuples have same values for K Every relation has at least one superkey.

domain constraints

the value of each attribute, A, must be an atomic value from the domain of A so, if an attribute is from the domain of a phone number, then the attribute must be a phone number

characteristics of relations

tuples are no particular order ordering of attributes not important all values belonging to a particular attribute are from the same domain attributes are atomic attributes may have a NULL value