Module 2

Question 1

4 basic concepts of the relational model?

Answer 1

• relations
• attributes
• domains
• tuples

Question 2

relation?

Answer 2

the main construct for representing data. Informally, it is a set of records and similar to a table with columns and rows

Question 3

is every table a relation?

Answer 3

no, every relation is a table but not every table is a relation

Question 4

what are the key components of a relation?

Answer 4

• relation name
• attribute names
• tuples (rows)
• attribute values from the same domain

Question 5

domain types?

Answer 5

the data type or format that a certain attribute will accept.

E.g. only accepting positive integers

Question 6

attributes?

Answer 6

attribute is the name of a role played by some domain in the relation

E.g. the attribute salary must belong to the domain of salary

The number of attributes in a relation R is called the degree of R

Question 7

domain/attribute restrictions?

Answer 7

same attribute name does not necessarily imply same domain

E.g. attribute ID exists for two entities but the format/range is different (1int vs. 4ints)

vice versa

different attribute name does not necessarily imply different domain

E.g. ID vs managerID (different attribute names but both are 4ints)

Question 8

tuples?

Answer 8

tuple (t) is an ordered list of n values

where each value is an element of the corresponding attribute or the special NULL value

E.g. (1234, Ephraim, 23, NULL) is a 4-tuple

Question 9

relation schema?

Answer 9

denoted by R[A1, A2, A3, …, A(n)] includes a relation name (R) and list of attributes A1, A2, A3, …, A(n)

E.g. A relation schema of degree 5
Employee [id, name, sex, salary, department]

integer n is “degree of relation” (how many attributes)

Question 10

relation instance?

Answer 10

relation instance (r) of the relation schema (R), denoted by r(R), is a set of n-tuples r = {t1, t2, …, t(m)}

in normal English, a set (instance) of tuples of relational type R

E.g. a table of the same type of tuple (employee table)

Question 11

ordering of tuples?

Answer 11

mathematically, no implied order

semantically, order irrelevant

physically, reside in blocks of storage with partial ordering therefore TUPLES HAVE AN ORDERING

Question 12

ordering of values within a tuple?

Answer 12

syntactically, all tuples in a relation have same order

semantically, order chosen is irrelevant as long as the values are matched with the correct attribute

Question 13

database integrity constraints?

Answer 13

rules, that every instance, of the schema must hold (as well as the transitions of the schema)

• domain
• key
• entity integrity
• referential integrity
• semantic integrity

Question 14

domain constraints?

Answer 14

using incorrect data type

E.g. using string when attribute is type integer

Question 15

key contraint?

Answer 15

keys must remain unique at all times

Question 16

entity integrity constraint?

Answer 16

no primary key can be NULL

for primary keys with multiple attributes, no part of the primary key may be NULL

Question 17

referential integrity constraint?

Answer 17

one relation references something that doesn’t exist in another relation

E.g. student goes to school called ACIT but ACIT doesn’t exist in the school table/relation

Question 18

semantic integrity constraint?

Answer 18

known as business rules

cannot be directly expressed in the schema of data model

E.g. “salary of employee should not exceed supervisor’s salary”

Question 19

uniqueness constraint?

Answer 19

all tuples in a relation must be distinct

Question 20

superkey?

Answer 20

subset of attributes that make the tuple unique

every relation has AT LEAST ONE SUPERKEY

E.g. combination of attributes that can uniquely identify a tuple aka set of all attributes (assuming uniqueness constraint isn’t broken)

Question 21

key?

Answer 21

also known as minimal superkey

smallest set of attributes that uniquely identify a tuple

schema may have more than one key, these are known as CANDIDATE KEYS

ONE candidate key is selected as the primary key

Question 22

foreign key?

Answer 22

FK in R1 is a foreign key referencing PK in R2

FK and PK have same domain

Question 23

self referential relations?

Answer 23

possible for table to reference itself

E.g. employee relation has attribute ID and attribute ManagerID, some employees will have NULL ManagerID’s since they are a manager

Question 24

relations with composite keys?

Answer 24

FK’s to relations with multi-attribute PK’s is possible

E.g. different parts of a multi-attribute PK reference different relations

Question 25

constraints effect of operations?

Answer 25

constraints ensure database remains consistent

if a suggested change violates any constraints it must be rejected

DBMS enforces constraints

Question 26

potential effect of deletion on constraints?

Answer 26

referential integrity may potentially be broken

tuple being deleted is referenced in another relation

Question 27

transaction concept

Answer 27

an executing program that includes some type of database interaction

E.g. insert, delete, update, etc…

END of transaction must leave database in valid or consistent state

E.g. between two valid + consistent states some constraints may be broken in between but the FINAL state must be valid + consistent

transactions allow execution of a suite of queries where constraint violation in INTERMEDIATE STEPS are allowed

Question 28

7 + 1 steps for mapping?

Answer 28

1. entity mapping
2. weak entity mapping
3. binary relationship 1: 1 mapping
4. binary relationship 1:M mapping
5. binary relationship N:M mapping
6. mulit-valued attribute mapping
7. n-ary relationship mapping
8. super & subclasses

1 - 7 mapping of ER
8 mapping of EER

Question 29

entity mapping?

Answer 29

non-weak entity with simple attributes

DON’T include multi-valued and derived attributes

CHOOSE ONE primary key attribute

Question 30

weak entity mapping?

Answer 30

create relation for weak entity and their corresponding owner entity

include FK attributes as weak entity PK with dotted underline

PK of weak entity is combination of owner entity + weak entity PK’s

rules do not change when multiple owners exist

DON’T FORGET WEAK ENTITIES AND THEIR RELATIONS ARE REPRESENTED WITH DOUBLE LINES