Review Chapter 5: The Relational Data Model

Why are tuples in a relation not ordered?

A relation is defined as a set of tuples. Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order

Why do we designate one of the candidate keys of a relation to be the primary key?

A relation schema may have more than one key, each of the keys is called a candidate key Ex: A CAR relation has two candidate keys: License_number and Engine_serial_number It is common to designate one of the candidate keys as the primary key of the relation. This is the candidate key whose values are used to identify tuples in the relation.

What is a transaction? How does it differ from an Update operation?

A transaction is an executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database. They differ from update operations in that

Candidate Key

Candidate keys are defined as the minimal set of fields which can uniquely identify each record in a table. Constraints: - Can never be NULL or empty, values should be unique - can be more than one candidate key for a table - candidate key can be a combination of more than one columns(attributes) Ex: student_id and phone both are candidate keys for table

Composite key

Key that consists of two or more attributes that uniquely identify any record in a table is called Composite key Ex: Score table which stores the marks scored by a student in a particular subject. The student_id and subject_id together will form the primary key, hence it is a composite key.

Discuss the various reasons that lead to the occurrence of NULL values in the relations

NULL values, which are used to represent the values of attributes that may be unknown or may not apply to the tuple. In general, we can have several meaning for NULL values, such as - Value unknown - Value exists but is not available - or attribute does not apply to this tuple Ex: Visa_status to the STUDENT relation that applies only to the tuples representing foreign students

Primary Key

Primary key is a candidate key that is most appropriate to become the main key for any table. It is a key that can uniquely identify each record in a table. - comes from the candidate keys - A primary key, on the other hand, cannot have a null value. Ex: student_id

Super Key

Super Key is defined as a set of attributes within a table that can uniquely identify each record within a table. Super Key is a superset of Candidate key. Ex: student_id, (student_id, name), phone etc.

What is the difference between a key and a superkey?

Superkey: Can have redundant attributes, Every relation has at lease one default superkey - the set of all its attributes Key: Has no redundancy. - no two distinct tuples can have identical values for (all) the attributes in the key - minimal superkey, a superkey from which we cannot remove and still have uniqueness constraint hold

Secondary or Alternative key

The candidate key which are not selected as primary key are known as secondary keys or alternative keys.

Discuss the characteristics of relations that make them different from ordinary tables and files

The relational model represents the database as a collection of relations. When a relation is thought of as a table of values, each row in the table represents a collection of related data values. A row represents a fact that typically corresponds to a real-world entity or relationship. The table name and column names are used to help to interpret the meaning of the values in each row.

Define foreign key. What is this concept used for?

To define a referential integrity constraint. (A relationship between two entities) Two conditions: 1) the attributes FK are said to reference or refer to the relation R2 2) A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is NULL.

Discuss the entity integrity and referential integrity constraints. Why is each considered important?

entity integrity constraint: states that no primary key value can be NULL. - This is because the primary key value is used to identify individual tuples in a relation referential integrity constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation, define the concept of a foreign key

Foreign Key

establish relationships between two available tables. - A foreign key helps us to maintain data as well as referential integrity.

Unique Key

identify every record uniquely in a table. All the values in this key would have to be unique - Differs from primary key in that it is only capable of having one null value.

Why are duplicate tuples not allowed in a relation?

they create redundancy of data inside a data base which slows down the data processing like querying, inserting, deleting, updating etc. of the database.