week 9

Question 1

relational database design

Answer 1

grouping of attributes to form a good relation schema

Question 2

Informal guideline 1 : semantics

Answer 2

Attributes ( eg Dname , Dno ) in a relation ( eg Department) should refer to one entity - in this case department
. Attributes referring to different entities should not be mixed in a relation
. ONLY foreign key can refer to a different entity

Question 3

Informal guideline 2 : Redundancy

Answer 3

Ensure database doesnt suffer from insertion , deletion update anomalies

Hence no redundant data

Question 4

informal guideline 3: Null values

Answer 4

Relations in tuples should be designed so they have the fewest null values

If a relation has an attribute that has frequent null values then another relation could be created with primary key of original relation and that attribute

Example: if 10% of employees have individual offices, attribute (OFFICE-NUMBER) should not be included in EMPLOYEE relation. Instead, use separate relation:
EMP_OFFICES (ESSN, OFFICE-NUMBER).

Question 5

informal guideline 4: erronius tuples due to bad joins

if confused:
https://www.youtube.com/watch?v=3kLbvBBNkTw
Title: CS208 PDD Mod 4.1 Database Design Guidelines

Answer 5

Design relation schemas that can be joined with equality
conditions on attributes that are either primary keys or
foreign keys in a way that guarantees that no spurious
tuples will be generated.

Avoid relations that contain matching attributes that are
not (foreign key, primary key) combinations, as joining on
such attributes will produce spurious tuples.

Question 6

Functional dependency

Answer 6

Functional dependency between attribute X and attribute Y if:
If two tuples have the same value for X , they must have the same value for Y

eg : X- College name , Y is College Rank

X à Y, if whenever two tuples have the same value for X, they must have the
same value for Y
n for any two tuples t1 and t2 in any relation instance r(R): if t1[X]=t2[X], then
t1[Y]=t2[Y]