Appendix 5.6

Question 1

5.6 What is a relational database?

Answer 1

A relational database is a collection of interrelated two-dimensional tables, consisting of rows and columns.

+ [RD is NOT one big table—usually called a flat file.]
+ [Flat file? A file that contains all of the records and attributes. The design of a flat file entails far too much data redundancy.]
+ [A RD is usually designed with a number of related tables.]

Question 2

5.6 What do a relational database’s rows and columns represent.

Answer 2

Each row represents a record, and each column (or field) represents an attribute (or characteristic) of that record.

Question 3

5.6 What is a primary key, secondary and foreign key?

Answer 3

Every record in the database must contain at least one field that uniquely identifies that record so that it can be retrieved, updated, and sorted.

• This identifier field, or group of fields, is called the primary key. In some cases, locating a particular record requires the use of secondary keys.
• A secondary key is another field that has some identifying information, but typically does not uniquely identify the record.
• A foreign key is a field (or group of fields) in one table that matches the primary key value in a row of another table. A foreign key is used to establish and enforce a link between two tables.

Question 4

5.6 What is a data model?

Answer 4

Data model is a diagram that represents the entities in the database and their relationships.

Question 5

5.6 What is an entity?

Answer 5

Entity is a person, place, thing, or event about which an organization maintains information.

Question 6

5.6 What are the disadvantages of relational databases?

Answer 6

Large-scale databases can be composed of many interrelated tables.
Result: the overall design can be complex, leading to slow search and access times.

Question 7

5.6 What are the two types of query languages?

Answer 7

1. Structured Query Language (SQL)
2. Query by Example (QBE)

Question 8

5.6 Describe SQL.

Answer 8

Structured query language (SQL) is the most popular query language used for interacting with a database. SQL allows people to perform complicated searches by using relatively simple statements or key words.

Typical key words are SELECT (to choose a desired attribute), FROM (to specify the table or tables to be used), and WHERE (to specify conditions to apply in the query).

Question 9

5.6 Describe QBE.

Answer 9

In Query By Example, the user fills out a grid or template—also known as a form—to construct a sample or a description of the data desired.

Users can construct a query quickly and easily by using drag-and-drop features in a DBMS such as Microsoft Access.

Conducting queries in this manner is simpler than keying in SQL commands.

Question 10

5.6 What is entity relationship modeling?

Answer 10

The process of designing a database by organizing data entities to be used and by then identifying the relationships among them.

Question 11

5.6 What is an entity in ERM (entity relationship modeling)?

Answer 11

• Definable thing or concept within a system, such as a person/role (e.g. Student), object (e.g. Invoice), concept (e.g. Profile) or event (e.g. Transaction)
• In ER diagram, the term “entity” is often used instead of “table”, but they are the same.
• When determining entities, think of them as nouns

Question 12

5.6 What are entity attributes in ERM?

Answer 12

Also known as a column, an attribute is a property or characteristic of the entity that holds it.

Question 13

5.6 What are relationships in ERM?

Answer 13

Relationships illustrate an association between entities.

Question 14

5.6 What are business rules in ERM? Hint: PPP

Answer 14

Business rules are precise descriptions of policies, procedures, or principles in any organization that stores and uses data to generate information.

To properly identify entities, attributes, and relationships, database designers first identify the business rules for the particular data model.

Question 15

5.6 What is the degree of a relationship in ERM?

Answer 15

The degree of a relationship indicates the number of entities associated with a relationship.

Question 16

5.6 What is a binary relationship in ERM?

Answer 16

A binary relationship exists when two entities are associated.

Question 17

5.6 Entity relationships may be classified as…

Answer 17

• one-to-one
• one-to-many
• many-to-many

Question 18

5.6 What is cardinality?

Answer 18

Cardinality is indicator of business rules.

Cardinality refers to the maximum number of times an instance of one entity can be associated with an instance in the related entity.

Cardinality can be mandatory single, optional single, mandatory many, or optional many.

Question 19

5.6 What is data normalization?

Answer 19

Normalization is a method for analyzing and reducing a relational database to its most streamlined form to ensure minimum redundancy, maximum data integrity (accuracy, inconsistency), and optimal processing performance.

Data normalization is a methodology for organizing attributes into tables so that redundancy among the non-key attributes is eliminated.

Basically, we structure data before we work with it (ex.: we make sure each student has a unique student ID, not multiple)

Question 20

5.6 What are the five steps of normalization?

Answer 20

The first step in normalization is to determine the functional dependencies among the attributes. Then, compose the first normal form, the second normal form, and finally the third normal form. At the end, the join process produces an order amongst third normal form tables.

Question 21

5.6 What are functional dependencies?

Answer 21

Functional dependencies are a means of expressing that the value of one particular attribute is associated with a specific single value of another attribute.

Question 22

5.6 What is a null value?

Answer 22

Several attributes of each record have null values. A null value is an attribute with no data in it.

Question 23

5.6 What is the first normal form in normalization?

Answer 23

In the first normal form, the attributes under consideration are listed in one table and primary keys have been established.

This data file contains repeating groups and describes multiple entities. That is, this relation has data redundancy, a lack of data integrity, and the flat file would be difficult to use due to repeating groups. When data repeats itself, it is called repeating groups.

Question 24

5.6 What is the second normal form in normalization?

Answer 24

In the second normal form, we break the table into smaller tables to eliminate some of its data redundancy.

Second normal form does not allow partial functional dependencies. That is, in a table in second normal form, every non-key attribute must be functionally dependent on the entire primary key of that table.

However, the second normal form has not eliminated all the data redundancy.

Question 25

5.6 What is the third normal form in normalization?

Answer 25

In the third normal form, non-key attributes are not allowed to define other non-key attributes. That is, the third normal form does not allow transitive dependencies in which one non-key attribute is functionally dependent on another.

Third normal form structure has these important points:

• It is completely free of data redundancy.
• All foreign keys appear where needed to link related tables.

Question 26

5.6 What is the join operation?

Answer 26

The join operation combines records from two or more tables in a database to obtain information that is located in different tables.