Logical Database Design

Question 1

1) conceptual database design (CDD)

Answer 1

• process of constructing a model of the data used in an enterprise
• independent of all physical considerations

Question 2

2) logical database design (LDD)

Answer 2

• process of constructing a model of the data used in an enterprise based on a specific data model
• independent of a particular DBMS and other physical considerations

Question 3

3) physical database design (PDD)

Answer 3

• process of producing a description of the implementation of the database on secondary storage
• describes the base relations; file organizations, indexes used to achieve efficient access to the data, any associated integrity constraints and security measures

Question 4

DATABASE DESIGN METHODOLOGY

Answer 4

step 1: CDD (ER model, data dictionary)
step 2: LDD (relational schema)
step 3: PDD (base relations, indexes, user views, security mechanisms, de-normalized relations)

Question 5

checking the model for redundancy (CDD)

Answer 5

• to check for the presence of any redundancy in the model

Question 6

validate conceptual data model against user transactions (CDD)

Answer 6

• to ensure that the conceptual data model supports the required transactions
  > all required attributes are present in the data model
  > there is an identifiable relationship, either direct or indirect, between the 2 entities (where attributes have to be taken from than one entity, that there is a pathway between the 2 entities)

Question 7

derive relations for logical data model (LDD)

Answer 7

• examine the structures present in the data model
  1) strong entity
  2) weak entity
  3) 1..* binary relationship
  4) 1..1 binary relationship
  5) .. binary relationship
  6) superclass/subclass relationship
  7) multi-valued attributes

Question 8

strong entity types

Answer 8

• create a relation that includes all simple attributes of that entity
• for composite attributes include only constituent simple attributes

Question 9

weak entity types

Answer 9

• create a relation that includes all simple attributes of that entity
• primary key is partially or fully derived from each owner entity

Question 10

1..* binary relationship types

Answer 10

• entity on ‘one side’ is designated the parent entity and entity on ‘many side’ is the child entity
• post copy of the primary key attribute(s) of parent entity into relation representing child entity (as a foreign key)

Question 11

1..1 binary relationship types

Answer 11

• cardinality cannot be used to identify parent and child entities in a relationship
• participation used to decide whether to combine entities into one relation or to create two relations
  a) mandatory participation on both sides of the 1..1 relationship
  b) mandatory participation on one side of the 1..1 relationship
  c) optional participation on both sides of the 1..1 relationship

Question 12

a) mandatory participation on both sides of the 1..1 relationship

Answer 12

• combine entities involved into one relation (1..1 entity and 1..1 entity)
• choose one of the primary keys of original entities to be PK of new relation, while other (if it exists) is used as alt key

Question 13

b) mandatory participation on one side of the 1..1 relationship

Answer 13

1..1 entity (mandatory)
0..1 (optional) (FK is PK of 1..1 entity)

Question 14

c) optional participation on both sides of the 1..1 relationship

Answer 14

• choice of parent entity is arbitrary/random
• both entities have optional participation towards each other (either can be parent or inherit the PK of the other)