Logical Data Model & Normalisation

Question 1

what does translating the conceptual model into a logic model involve

Answer 1

• translating ER diagrams
• normalising these relations

Question 2

what are the 7 relationships that can be derived

Answer 2

1. strong entity
2. weak entity
3. one-to-many (1:*)
4. one-to-one (1:1)
5. one-to-one recursive (1:1 recursive)
6. many-to-many (:)
7. complex relationships

Question 3

what is a strong entity & how to derive it

Answer 3

• create a relation that includes all the simple attributes of that entity
• primary key of the relation is the primary key of the entity

Question 4

what is a weak entity & how do you derive it

Answer 4

• create a relation that includes all simple attributes
• primary key is partially or fully derived from each owner entity after all relationships with the owner entities have been mapped

Question 5

what is a 1:* relationship

Answer 5

the entity on the ‘one side’ is designated as the parent entity and the entity on the ‘many side’ is designated as the child entity

Question 6

how to represent a 1:* relationship

Answer 6

a copy of the primary key attribute(s) of the parent entity is placed in the relation for the child as a foreign key

Question 7

why is a 1:1 relationship complex

Answer 7

cardinality cannot be used to identify the parent and child entities in a relationship

Question 8

what are used to define how 1:1 cases are implemented

Answer 8

participation constraints

Question 9

what are the 3 participation constraints of 1:1 and 1:1 recursive relationships

Answer 9

1. mandatory participation on both sides
2. mandatory participation on one side
3. optional participation on both sides

Question 10

how are mandatory participation on both sides represented in a 1:1 relationship

Answer 10

combine entities into one relation and choose one of the primary keys of the original entities to be primary key and the other (if exists) as an alternate key

Question 11

how is mandatory participation on one side represented in a 1:1 relationship

Answer 11

entity with optional participation is designated as parent entity, and entity with mandatory participation is designated as child entity. a copy of primary key of the parent entity is placed in the relation for the child entity as the foreign key.

Question 12

how is optional participation on both sides represented in a 1:1 relationship

Answer 12

arbitrary designation of the parent and child entities unless we can find out more about the relationship that can help a decision to be made one way or another

Question 13

how is mandatory participation on both sides represented in a 1:1 recursive relationship

Answer 13

represent the recursive relationship as a single relation with two copies of the primary key. one copy represents a foreign key and should be renamed to indicate the relationship it represents

Question 14

how is mandatory participation on one side represented in a 1:1 recursive relationship

Answer 14

either a single relation with two copies of the primary key (like mobs) or create a new relation to represent the relationship. this relation would have only two attributes which are both copies of the primary key with one renamed to act as a foreign key.

Question 15

how is optional participation on both sides represented in a 1:1 recursive relationship

Answer 15

create a single relation with two copies of the primary (like mobs)

Question 16

how is a : relationship represented

Answer 16

• create a relation for the relationship with all attributes of the relationship, with a copy of the primary key of the participating entities as foreign keys
  these foreign keys will also form the primary key of the new relation

Question 17

how are complex relationships represented

Answer 17

• create a relation to represent the relationship. Copy the primary key of the participating entities as foreign keys
• any foreign keys that represent a ‘many’ relationship generally will also form the primary key of this new relation

Question 18

what 3 things does normalisation produce

Answer 18

1. minimal number of attributes necessary to support the data requirements
2. attributes with a close logical relationship placed in the same relation
3. minimal redundancy, with each attribute represented only once, except those forms all or part of foreign keys

Question 19

what are the 3 normal forms

Answer 19

1. 1NF
2. 2NF
3. 3NF

Question 20

what is 1NF

Answer 20

a relation is in 1NF when intersection of each row and column contains one value only
- atomicity

Question 21

what is a relating group

Answer 21

a column containing multiple values for a row

Question 22

what 2 ways do we deal with repeating groups

Answer 22

• place each value in a new row and fill in the other columns with the nonrepeating data
  OR
• place the repeating data in a separate relation together with a copy of the key attribute

Question 23

what is 2NF

Answer 23

• if the primary key of a relation consists of a single attribute, it is automatically in at least 2NF
• a relation in 2NF is a relation that is in 1NF and every non-primary key attribute is fully functionally dependent on the primary key
• removal of partial dependencies - when a non-primary key attribute is dependent on only part of a primary key

Question 24

how do we remove partial dependencies

Answer 24

if they exist - remove the partially dependent attributes from the relation and place them in a new relation with a copy of the primary key attribute they are dependent on. primary key attribute is called their determinant

Question 25

what is 3NF

Answer 25

a relation is in 3NF if it is in 1NF and 2NF, and no non-primary key attribute is transitively dependent on the primary key

Question 26

how do we deal with transitive dependency

Answer 26

if there is a transitive dependency, remove the transitively dependent attributes and place them in a new relation together with a copy of the determinant

Question 27

what is transitive dependency

Answer 27

occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key