BIS II - Relational Databases Flashcards
Relational Database Model
- 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
Primary Key
o An attribute whose value is unique across all occurrences of a relation
Transforming E-R Diagrams into Relations: Represent Entities
- 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
Transforming E-R Diagrams into Relations: Represent Relationships
- 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
E-R Structure: Regular Entity
Create a relation with primary key and nonkey attributes
Weak Entity
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
Binary or unary 1:1 relationship
Place the primary key of either entity in the relation for the other entity or do this for both entities
Binary 1:N relationship
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
Binary or Unary M:N relationship or associative entity with additional keys
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
Binary of unary M:N relationship or associative entity with ITS OWN KEY
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)
Well-structured relation
- A relation that contains a minimum amount of redundancy and allows users to insert, modify, and delete the rows without errors or inconsistencies
Normalization
- 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
Normalization and Functional dependencies
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
Functional Dependencies - Foreign Key
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
Functional Dependencies - Referential Integrity
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
Designing Physical Tables - Design goals
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
Designing Physical Tables - Denormalization
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
Defining Fields - Field, Definition
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
Defining Fields -
A Data type
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
Controlling data integrity
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
