Chapter 2 - Slides Flashcards
The ____ is very popular because of its
simplicity and mathematical foundation
Relational Model
True or False? Relational model shows data to the user in a very simple, logical view as a two-dimensional table.
True
What is the strongest characteristic of the Relational Model?
the ability to establish relationships among tables, which helps to reduce redundancy
What was the first commercial implementation of the Relational Model?
SQL/DS on MVS OS by IBM and the Oracle DBMS in 1980s
What is a model?
a simplified version of real-life
complex objects.
What is a data model?
a simplified graphical
representation of a database structure and is a
tool to represent the various entity types and
relationships among the entity types.
What does the Entity-Relationship (ER) model provide?
- An excellent communication tool.
2. A simple graphical representation of data.
The E-R model uses _____ for
graphical representation of the database components
E-R diagrams (ERD)
What is an Entity?
it is represented by a
rectangle. The name of the entity (set) is written within
the rectangle. Some tools prefer to use uppercase
letters only for entities.
The name of an entity set is a _____
single noun
EMPLOYEE, CUSTOMER, and DEPARTMENT are examples of what?
Singular entity set names
A ___ represents relationship between the two
entities
line
The name of the relationship is an ____ in lowercase letters.
active verb
works, manages, and employs are examples of what?
relationships
How is 1:1 represented on the ER Model?
With 2 vertical lines on each side. (-||——||–) ie. manages
How is 1:M represented on the ER Model?
With 1 set of vertical lines on one side and angled lines on the right hand side.
(-||–||-)
How is M:N represented on the ER Model?
With 2 angled lines on each side.
(->—
The types of relationships (1:1, 1:M, and M:N) between entities are called:
connectivity or
multiplicity
How would you illustrate the following:
An EMPLOYEE supervises a DEPARTMENT, and a
DEPARTMENT has one EMPLOYEE supervisor
How would you illustrate the following:
A DIVISION contains many FACULTY members, but
a FACULTY works for one DIVISION.
D -||–(employs)–
How would you illustrate the following:
An INVOICE contains many ITEMS and an ITEM
can be in more than one INVOICE.
INV ->–(contains)–
The relationship between two entities can be given
using the lower and upper limits. This information is
called the ____.
cardinality
The ____ is written next to each entity in the
form (n, m)
cardinality
a (1, 1) next to
EMPLOYEE means that an employee can supervise a
___ of one and ___ of one department.
minimum; maximum
The value (1, N) next to an entity means
a minimum of one and a
maximum equal to any number
True or False? Some modern tools do not show cardinality in an E-R diagram.
True
True or False? In reality, corporations do not set rules for the minimum and maximum values for cardinality.
False, they do.
A corporation may decide that a department must
have a minimum of 10 employees and a maximum of
25 employees, which results in cardinality of:
(10,25)
An employee can be part of zero or more than one
department, and an item may not be in any invoice!
These types of decisions are known as ___.
business rules
In real life, it is possible to have an entity that is not
related to another entity at all times. The relationship
is known as:
an optional relationship.
What is the symbol of an optional relationship?
Angled lines with a circle
->o–o
The decomposition from M:N to 1:M involves a third
entity, known as a
composite/associative entity
The ____ is created with the primary key
from both tables with M:N relationships. The new
entity has a ___, which is a combination of
primary keys from the original two entities.
composite entity; composite key
In a database, there are entities that cannot exist by
themselves. Such entities are known as
weak entities
The following example, is an example of what type of entity?
In the employee database, there is an entity called EMPLOYEE with employees’
demographic information and another entity called
DEPENDENT with information about each employee’s
dependents.
Weak Entities
The _____ entity cannot exist by itself. In other
words, you need the existence of an employee for his or her dependent to exist in the database.
DEPENDENT
Weak entities are symbolically represented as
Double-lined rectangles
What are Simple attributes?
attributes that cannot be subdivided; for
example, last name, city, or gender.
What are Composite attributes?
attributes that can be subdivided,
into atomic form; for example, a full name can be subdivided
into the last name, first name, and middle initial.
What are Single-valued attributes?
attributes with a single value; for example, Employee ID, Social Security number, or date of birth.
What are Multivalued attributes?
attributes with multiple values; for example, degree codes or course registration. The multivalued attributes have to be given special consideration.
Every table in the database should have a ___, which uniquely identifies an entity
primary key
For integrity of data, a ____ should be defined for each table in the database
primary key
Each table has other columns that do not make up
the primary key for the table, such columns are
called the _____.
nonkey columns
The nonkey columns
are _____ on the primary key
column
functionally dependent
What are the three types of Dependency?
- Total or full dependency
- Partial dependency
- Transitive dependency
What is total dependency?
When a nonkey column is
dependent on all primary key columns and shows total
dependency
What is Partial dependency?
When a nonkey column is dependent on part of the primary key
What is Transitive dependency?
When a nonkey column is dependent on another nonkey
column
When would you need to make a composite key?
When no primary key is possible, you need to combine 2 columns with unique data to make a composite.
Based on the following column dependencies, would it be partially/transitive/total dependency?
Columns A & B are nonkey columns that are dependent only on a part of the primary key column C.
Partially Dependent
Based on the following column dependencies, would it be partially/transitive/total dependency?
The nonkey column A is not dependent on
any primary key column but is dependent on another
nonkey column, B.
Transitive Dependency
Based on the following column dependencies, would it be partially/transitive/total dependency?
The nonkey column A is dependent on both B
and C.
Total Dependency
The first and foremost step in database creation is
_____, which involves a certain degree of
common sense.
database design
_______ involves an attempt to
synthesize the database structure to get the “first draft”.
Relational database design
The initial draft goes through an _____ to
improve the structure
analysis phase
In the ______, entities and their relationships
are identified. The characteristics or the columns of all entities are also identified, and the designer defines the domains for each column.
synthesize phase
A process called _____ is a process of analyzing
the tables created by the synthesis process.
normalization
____ keys will be used to establish relationships
Foreign
There is a large amount of ____ data in a table
with just eight rows and can pose problems.
redundant
The redundancy may also lead to ____
anomalies
A _____ results when the deletion of
information about one entity leads to the deletion of
information about another entity
deletion anomaly
An ______ occurs when the information
about an entity cannot be inserted unless the
information about another entity is known.
insertion anomaly
An _____ can occur, if the item price
changes to a new price. The price change is
valid after the change date, but not before the
change date
update anomaly
Unnecessary and unwanted redundancy and
anomalies are not appropriate in databases.
Such tables are in _____
lower normal form
______ is a technique to reduce redundancy and It is a decomposition process to split tables.
Normalization
The ____ in Normalization is performed carefully so that no
information is lost.
splitting
The higher the normal form is, the ____ the
redundancy.
lower
A table is said to be in first normal form, or can be
labeled 1NF, if the following conditions exist:
- The primary key is defined. This includes a composite
key if a single column cannot be used as a primary key. - All nonkey columns show functional dependency on the
primary key components - The table contains no multivalued columns. In a singlevalued column, the intersection of a row and a column returns only one value. In a normalized table, the intersection of a row and a column is a single value.
What are some disadvantages of 1NF?
A table that is 1NF:
- may have redundant data.
- does not show data consistency and integrity in the long run
A table is said to be in second normal form, or can be labeled 2NF, if the following requirements are satisfied:
- All 1NF requirements are fulfilled
2. There is no partial dependency
____ exists in a table in which
nonkey columns are partially dependent on part
of a composite key
Partial dependency
Partial dependency only exists in a table with a
_____.
composite key
If a table is in 1NF and does not have a
composite key, it is in 2NF, because there is
______.
no partial dependency
A table is said to be in third normal form, or can
be labeled 3NF, if the following requirements
are satisfied:
- All 2NF requirements are fulfilled
2. There is no transitive dependency
A table that has _____ is not
in 3NF, but it needs to be decomposed further
to achieve 3NF
transitive dependency
A table in 2NF that does not contain any ______ does not need any further decomposition and is automatically in 3NF
transitive dependency
Other higher normal forms are also possible:
- Boyce-Codd normal form (BCNF)
- Fourth normal form (4NF)
- Fifth normal form (5NF)
- Domain key normal form (DKNF)
A ___ is used to show total
(full), partial, and transitive dependencies in a
table
dependency diagram
In a dependency diagram:
The _____ components are highlighted. They
are in bold letters and in boxes with a darker
border, and are connected
to each other using a bracket.
primary key
In a dependency diagram:
The _____ are shown
with arrows drawn above the boxes.
total and functional dependencies
In a dependency diagram:
The ____ are shown
with arrows at the bottom of the diagram
partial and transitive dependencies
In the 1NF-to-2NF conversion, all _____ are removed
partial dependencies
The ____ process splits tables into smaller tables. These tables are joined through common
columns to retrieve information from different tables
normalization
The more tables you have in a database, the more
___ are needed to get the desired information. In a
multi-user environment, it is a costly overhead, and
system performance is affected
joins
____ is the reverse process of normalization. It reduces
the normal form, and it increases data redundancy.
Denormalization
With _____ , the information is stored with
duplicate data, more storage is required, and
anomalies and inconsistent data exist. The designer
has to weigh this against performance to come up with a good design and performance
denormalization
A table is supposed to describe one ____
entity
If a table describes multiple entities, it needs to be
_____.
decomposed
When tables are _____, there should be enough foreign keys to establish relationships among
tables.
decomposed
_____ should not end up with a table that is not related to any other table in the database.
Database design process
