BIS II - Relational Databases

Question 1

Relational Database Model

Answer 1

• Named, two-dimensional table of data
• Each relation consists of a set of named columns and an arbitrary number of unnamed rows
  Properties:
  o Entries In cells are simple
  o Entries in columns are from the same set of values
  o Each row is unique
  o The sequence of columns can be interchanged without changing the meaning or use of the relation
  o The rows may be interchanged or stored in any sequence

Question 2

Primary Key

Answer 2

o An attribute whose value is unique across all occurrences of a relation

Question 3

Transforming E-R Diagrams into Relations: Represent Entities

Answer 3

• Each regular entity is transformed into a relation
• The identifier of the entity type becomes the primary key of the corresponding relation
• The primary key must satisfy the following two conditions:
  o The value of the key must uniquely identify every row in the relation
  o The key should be non-redundant

Question 4

Transforming E-R Diagrams into Relations: Represent Relationships

Answer 4

• The procedure depends on both 1) the degree and 2) the cardinalities of the relationship

Binary 1:N relationship
o Add the primary key attribute (or attributes) of the one entity as a foreign key into the relation that is for the other entity
o The one side migrates to the many side

Binary and higher M:N relationships
o Create another relation and include primary keys of all relations as primary key of new relation

Binary or unary 1:1 relationship
o Add the primary key of A as a foreign key of B
o Add the primary key of B as a foreign key of A
o Both of the above

Unary 1:N relationships
o Relationship between instances of a single entity type
o Utilize a recursive foreign key
 Foreign key in a relation that references the primary key values of that same relation

Unary M:N relationships
o Create a separate relation
o Primary key of new relation is a composite of two attributes that both take their values from the same primary key

Question 5

E-R Structure: Regular Entity

Answer 5

Create a relation with primary key and nonkey attributes

Question 6

Weak Entity

Answer 6

Create a relation wit h a composite primary key (which includes the primary key of the entity on which this weak entity depends) and nonkey attributes

Question 7

Binary or unary 1:1 relationship

Answer 7

Place the primary key of either entity in the relation for the other entity or do this for both entities

Question 8

Binary 1:N relationship

Answer 8

Place the primary key of the entity on the one side of the relationships as a foreign key in the relation for the entity on the many side

Question 9

Binary or Unary M:N relationship or associative entity with additional keys

Answer 9

Create a relation with a primary key composed of the primary keys of the related entities, and additional primary key-attributes associated with the relationship or associative entity, plus any nonkey attributes of the relationship or associative entity

Question 10

Binary of unary M:N relationship or associative entity with ITS OWN KEY

Answer 10

Create a relation with the primary key associated with the relationship or associative entity, plus any nonkey attributes of the relationship or associative entity and the primary keys of the related entities (as nonkey attributes)

Question 11

Well-structured relation

Answer 11

• A relation that contains a minimum amount of redundancy and allows users to insert, modify, and delete the rows without errors or inconsistencies

Question 12

Normalization

Answer 12

• Normalization: eliminates redundancy, the process of converting complex data structures into a simple, stable data structure
• The result of normalization is that every non-primary key attribute depends upon the whole primary key

Question 13

Normalization and Functional dependencies

Answer 13

Normalization is based on the analysis of functional dependence

Functional Dependency

• A particular relationship between two attributes, i.e. for every (valid) value of A, that value of A uniquely determines the value of B
• Instances (or sample data) in a relation do not prove the existence of a functional dependency
• Knowledge of problem domain is the most reliable method for identifying functional dependency

Normalization Standards

Second normal form (2NF)
o A relation is in second normal form (2NF) if any of the following conditions apply:
1. The primary key consists of only one attribute
2. No non-primary key attributes exist in the relation
3. Every non-primary key attribute is functionally dependent on the full set of primary key attributes

Conversion to second normal form (2NF)
o Decompose the relation into new relations using the attributes, called determinates, that determine other attributes
o The determinates become the primary key of the new relation

Third Normal Form (3NF)
o A relation is in third normal form (3NF) if second normal form (2NF) and there no functional (transitive) dependencies between two (or more) non-primary key attributes
o Decompose the relation into two relations using the two determinants

Question 14

Functional Dependencies - Foreign Key

Answer 14

o An attribute that appears as a non-primary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation

Question 15

Functional Dependencies - Referential Integrity

Answer 15

o Specifies that the value (or existence) of an attribute in one relation depends in the value (or existence) of the same attribute in another relation
o Each foreign key value must match a primary key value in another relation or the foreign key value must be null

Question 16

Designing Physical Tables - Design goals

Answer 16

o Efficient use of secondary storage (disk space)
o Disks are divided into units that can be read in one machine operation
o Space is used most efficiently when the physical length of a table row divides close to evenly with storage units
o Efficient data processing
o Data are most efficiently processed when stored next to each other in secondary memory

Question 17

Designing Physical Tables - Denormalization

Answer 17

Used to increase read performance at the expense of write performance.
Data tables must be normalized beforehand to be denormalized in the next step.

The process of splitting or combining normalized relations into physical tables by adding redundant data or grouping data.

Three common situations where denormalization may be used:
o Two entities with a one-to-one relationship
o A many-to-many relationship with non-key attributes
o Reference data

Question 18

Defining Fields - Field, Definition

Answer 18

o The smallest unit of named application data recognized by system software
o Each attribute from each relation will be represented as one or more fields

Question 19

Defining Fields -

A Data type

Answer 19

A Data Type Is a coding scheme recognized by system software for representing organizational data

Four objectives when choosing a data type

• Minimize storage space
• Represent all possible values for the field
• Improve data integrity for the field
• Support all data manipulations desired on the field

Calculated fields:
- A field that can be derived from other database fields

Question 20

Controlling data integrity

Answer 20

o Default value: a value a field will assume unless an explicit value is entered for that field
o Input mask: a pattern codes that restricts the width and possible values for each position of a field
o Range control: limits range of values that can be entered into field
o Referential integrity: an integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation
o Null value: a special field value, distinct from 0, blank or any other value, that indicates that the value for the field is missing or otherwise unknown