1-2-4 Logical Design of Databases

Highlighted text? Practice this concept. Highlighted passages and figures open related questions.

Practice related questions (7)Progress & bookmarks

Current section

1-2-4 Logical Design of Databases

p. 252

Practice this section 7 linked exam questions with default filters

Progress

Not marked complete

1-2-4 Logical Design of Databases

Logical design of databases is the process of creating logical data models in consideration of the databases to be implemented. (Files/records/fields (items) are defined in a structured database, while tables/tuples/attributes are defined in a relational database.)

In the case of a relational database, table design is performed to decide which attributes of the table should be used for managing the data items represented in conceptual design. At that time, primary key and foreign key of each table should also be considered. · 1 question

Original textbook table, example or syntax box, with its complete original layout. Source text: • Primary key It is a field (or a combination of fields) that uniquely identifies each tuple (i.e., record) in a table. (Composite key made of multiple fields is also acceptable.) For primary key, there are constraints, such as non-NULL constraint that does not allow NULL (null value) and unique constraint that does not allow duplication of values in a table. • Foreign key — p. 252 · Enlarge

Primary key uniqueness and non-NULL constraints · 2 practice questions

p. 253

Original textbook table, example or syntax box, with its complete original layout. Source text: It is a field (or a combination of fields) for referencing a tuple (i.e., record) whose values match the primary key of another table. For a foreign key, it is possible to define referential constraint, which requires that the primary key of the table to be referenced must have a tuple (i.e., record) having the same value. Note: Non-NULL constraint, unique constraint, and referential constraint are also called consistency constraint for always maintaining the data in the correct status. — p. 253 · Enlarge

Foreign key referential constraint · 2 practice questions

In a general table design, required fields are listed in a table in the un-normalized form (UNF). Next, data normalization is performed in order to design efficient table. Functional dependency is the property where if a certain attribute is decided, other attributes is uniquely decided. · 1 question

Original textbook table, example or syntax box, with its complete original layout. Source text: [Data normalization procedure] 1) First normalization (Deliverable: Table in the first normal form) Iterative fields or derived fields (i.e., fields determined through calculation) are eliminated. 2) Second normalization (Deliverable: Table in the second normal form) When the primary key is a composite key, fields that are dependent on some of the fields constituting the primary key are split into a separate table. (This dependency is called partial functional dependency.) 3) Third normalization (Deliverable: Table in the third normal form) Fields dependent on the fields other than the primary key are split into a separate table. (This dependency is called transitive functional dependency.) — p. 253 · Enlarge

Original normalization procedure · 3 practice questions

By proceeding up to the third normalization as per the procedure, it is possible to design a table where all fields are dependent on the primary key. (This is called full functional dependency.) However, there are cases where the third normal form is not the best option. (For example, it may be more efficient to retain frequently used derived fields.) Therefore, it is necessary to consider including the operational aspects.