Key

Schema Diagram for the university of database

1-Primary-key attributes are shown underlined. 2-Foreign-key constraints appear as arrows from the foreign-key attributes of the referencing relation to the primary key of the referenced relation 3-use a two-headed arrow, instead of a single-headed arrow, to indicate a referential integrity constraint.

Foreign Key

A primary key of one table that appears as an attribute in another table and have same values between the two tables. For instance, Thus, in any database instance, given any tuple, say ta, from the instructor relation, there must be some tuple, say tb, in the department relation such that the value of the dept name attribute of ta is the same as the value of the primary key, dept name, of tb.

Referential Integrity Constraint(opposite of foreign key constraints)

A referential integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation. THE ATTRIBUTE FROM REFERENCED RELATIONAL IS NO NEED TO BE PRIMARY KEY. In fact, foreign-key constraints are a special case of referential integrity constraints, where the referenced attributes form the primary key of the referenced relation.

Super Key

A super-key is a set of one or more attributes(column) that, taken collectively, allow us to identify uniquely a tuple(row or data) in the relation .For example, the ID attribute of the relation instructor is sufficient to distinguish one instructor tuple from another. Thus, ID is a super-key. No two distinct tuples have the same values on all attributes in subset That is, if t1 and t2 are in relation,k=subet and t1 ≠ t2, then t1.K ≠ t2.K.

Candidate Key

An attribute, or combination of attributes, that uniquely identifies a row in a relation. In super-keys for which no proper subset is a super-key. Such minimal super-keys are called candidate keys. It is possible that several distinct sets of attributes could serve as a candidate key. Suppose that a combination of name and dept name is sufficient to distinguish among members of the instructor relation. Then, both {ID} and {name, dept name} are candidate keys.

Foreign key constraint

From attribute(s) A of relation r1 to the primary-key B of relation r2 states that on any database instance, the value of A for each tuple in r1 must also be the value of B for some tuple in r2 Attribute set A is called a foreign key from r1, referencing r2. Relational r1=referencing relation Relation r2=referenced relation(the referenced attribute(s) must be the primary key of the referenced relation.)

Primary Key or Primary Key Constraints

The term primary key to denote a candidate key that is chosen by the database designer as the principal means of identifying tuples within a relation. Any two individual tuples in the relation are prohibited from having the same value on the key attributes at the same time. primary keys are also referred to as primary key constraints. 1-It is customary to list the primary key attributes of a relation schema before the other attributes. Eg-classroom (building, room number, capacity) time slot (time slot id, day, start time, end time) From above two eg, in first one-Neither attribute by itself can uniquely identify a classroom, although together they uniquely identify a classroom. In second, three attributes together uniquely identify a time slot for a course. Note this-***The primary key should be chosen such that its attribute values are never, or are very rarely, changed*** AND Unique identifiers generated by enterprises generally do not change.

What is key?

a way to specify how tuples within a given relation are distinguished. The values of the attribute values of a tuple must be such that they can uniquely identify the tuple. In other words, no two tuples in a relation are allowed to have exactly the same value for all attributes

Schema of the university database(example)

classroom(building, room number, capacity) department(dept name, building, budget) course(course id, title, dept name, credits) instructor(ID, name, dept name, salary) section(course id, sec id, semester, year, building, room number, time slot id) teaches(ID, course id, sec id, semester, year) student(ID, name, dept name, tot cred) takes(ID, course id, sec id, semester, year, grade) advisor(s ID, i ID) time slot(time slot id, day, start time, end time) prereq(course id, prereq id)