Week 2

Question 1

What is a relationship made up of

Answer 1

1. Instance: a table with rows and columns.
   #Rows = cardinality, #fields/attributes = degree / arity.
2. Relation Schema: specifies the relation’s name and type (domain) of each column
   E.G. Students(sid:string, name:string, login: string, age:integer, gpa: real)

Question 2

What is the mathematical notation that can be use to represent a relation schema?

Answer 2

S(A1,A2, A3,… An) Where S is the schema’s name, A is an attribute.
* E.g. Students(sid:string, name:string, login:string, age:integer, gpa:real).

Question 3

What is the Integrity of data?

Answer 3

It is the state of data in which data obeys the constraints set by DBA.

Question 4

What is the Domain constraint?

Answer 4

Values in tuples should obey types of attributes.

Question 5

What is the Key constraint?

Answer 5

Keys of a relation must be unique, non-redundant, and not Null (entity integrity constant).

Question 6

What is referential integrity?

Answer 6

If two relational schemas are related to each other,
1. The relation must be made by using keys
2. The DBMS must preserve this relation.
3. Foreign keys must have the same type and values in the current relation.

Question 7

What is a Foreign key?

Answer 7

Set of fields in one relation that is used to ‘refer’ to a tuple/row in another relation. Must correspond to the primary key of the referring relation. Like a ‘logical pointer’.

Question 8

What is the 4 options on deletes and updates in SQL/92

Answer 8

1. Default is NO ACTION (delete/update is rejected)
2. CASCADE (also delete all tuples that refer to deleted tuple)
3. SET NULL / SET DEFAULT (sets foreign key value of a referencing tuple)

Question 9

What is an Entity integrity constraint in Key constraints?

Answer 9

A key value cannot be null

Question 10

What is a superkey?

Answer 10

A set of attributes such that its subset is a key for the relation.

Question 11

What is the Relation schema in this relationship? How do we link the relationship in the table? What is the integrity constraint?

Answer 11

Question 12

What is ER-to-Relational Mapping in a 1:1 relationship?

Answer 12

For each 1:1 binary relationship in ER schema, identify relations S and T (in relational schema) that correspond to entity types participating in relationship
Choose on of relations for example, S and include primary key of T as foreign key in S

Question 13

What is ER-to-Relational Mapping in a 1:N relationship?

Answer 13

For each 1:N binary relationship in ER schema, identify relation S (in relational schema) that represents participating entity at N-side of relationship
Include primary key of relation T as foreign key in S (where T represents entity at 1-side of relationship)
Each entity instance on N-side is related to at most one entity instance on 1-side (primary ke constraint)

Question 14

What is ER-to-Relational Mapping in a M:N relationship?

Answer 14

For each M:N binary relationship in ER schema, create new relation R to represent many to many relationship
* Include primary keys of relations that represent participating entity types as foreign key attributes in R
* The combination of primary keys of the two relations representing participating entities will form primary key of R