Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

dbs > ch3 > Flashcards

ch3 Flashcards

1
Q

The first step in the database design process is the requirements collection and analysis. Explain this step.

A

During this step, the DB designers interview prospective DB users to understand and document their data requirements. The result of this step is a concisely written set of user’ requirements. These requirements should be specified in as detailed and complete a form as possible.
In parallel with specifying the data requirements, it is useful to specify the known functional requirements of the app. These consist of user-defined ops (or transactions) that will be applied to the DB, including retrievals and updates. In software design, it is common to use DFDs, sequence diagrams, scenarios, and other techniques to specify functional requirements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Discuss the two cases of the unknown category of NULL with the help of an example.

A

There are 2 cases where a value can be NULL:

  • if the attribute is not applicable to a particular entity instance
    • ex: an ApartmentNumber attribute is only applicable to RESIDENCE entities with Type=apartment; the ApartmentNumber attribute could be NULL for a RESIDENCE entity with Type=house
  • if the attribute value is unknown; there are 2 cases:
    • when you know that a value for the attribute for an entity instance exists, but you do not know what that value is
  • ** ex: a PERSON entity that has the DateOfBirth attribute as NULL
    • when you do not know whether a value for the attribute for an entity instance exists or not
  • ** ex: a PERSON entity that has the CHILDREN attribute as NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Define: entity

A

a thing or object in the real world with an independent existence; can be an object with a physical existence or an object with a conceptual existence

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Define: attribute

A

the particular properties that describe an entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Define: attribute value

A

the value of an attribute for a particular entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define: relationship instance

A

each relationship instance in a relationship type is an association of entities, where the association includes exactly one entity from each participating entity type;
each relationship instance represents the fact that the entities participating in it are related in some way in the corresponding miniworld situation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Define: composite attribute

A

a composite attribute is an attribute that’s composed of multiple simpler attributes; useful in cases where you sometimes refer to the entire attribute and sometimes refer to its simpler attributes on their own;
ex: Address: composed of simpler attributes: Number, Street, ApartmentNumber, City, State, Zip;

vs simple/atomic attributes: attributes that cannot be broken down any more
ex: State

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Define: multivalued attribute

A

an attribute that can be composed of multiple values; shown on an ERD as a double-outlined oval
ex: the Color attribute of CAR instance like for a car dealership who has a bunch of cars in stock

vs single-valued attribute: can only contain one value for a particular entity
ex: DOB attribute for a PERSON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Define: derived attribute

A

an attribute whose value is derived from one or more other attributes; shown on an ERD as a dotted-outlined oval
ex: Age attributed derived from DOB attribute

vs stored attribute: attr that is stored on its own explicitly and used directly
ex: DOB attribute

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Define: complex attribute

A

a composite and/or multivalued attribute; can be nested arbitrarily;

  • represent components of a composite attribute between parentheses () and separating the values with commas;
    • ex: Pet(Name, Type, DOB)
  • represent multivalued attributes between braces {}
    • ex: Colors{red, orange, yellow, green, blue, indigo, violet}
  • ex (complex): Car(VIN, Model, Year, Colors{black, silver, white, blue, red, green})
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Define: key attribute

A

one or more attributes whose values are distinct for each individual entity in the entity set; its values can be used to identify each entity uniquely;
ex: the Ssn attribute of a PERSON entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Define: value set (domain)

A 
AKA domain of values;
specifies the set of values that may be assigned to that attribute for each individual entity;
similar to the basic data types available in most programming languages;
not specified in ERDs but can be specified in UML class diagrams;
The value set provides all possible values.

power set, P(V): of a set, V, is the set of all subsets of V

Mathematically, an attribute A of entity set E whose value set is V can be defined as a function from E to the power set P(V) of V:
A: E -> P(V)

We refer to the value of attribute A for entity e as A(e).

NULL: empty set

single-valued attribute: A(e) is restricted to being a singleton set for each entity e in E;
no restriction on multi-valued attributes

For a composite attribute A, the value set V is the power set of the Cartesian product of P(V_1), P(V_2), …P(V_n), where V_1, V_2, …, V_n, are the value sets of the simple component attributes that form A:
V = P(P(V_1) x P(V_2) x … x P(V_n))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is an entity type? What is an entity set? Explain the differences among an entity, an entity type, and an entity set?

A

entity type: defines a collection of entities that have the same attributes; each entity type in the DB is described by its name and attrs;
* ex: entity types: DEPARTMENT, EMPLOYEE, PROJECT, DEPENDENT

entity set: AKA entity collection; the collection of all entities of a particular entity type in the DB at any point in time
* ex: EMPLOYEE entity set = the set of all EMPLOYEE instances in the DB at a given point in time

entity: one specific instance of an entity type(?)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Explain the difference between a single-valued attribute and a multi-valued attribute.

A

multi-valued attribute: an attribute that can be composed of multiple values; shown on an ERD as a double-outlined oval
ex: the Color attribute of CAR instance like for a car dealership who has a bunch of cars in stock

vs single-valued attribute: can only contain one value for a particular entity
ex: DOB attribute for a PERSON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a relationship type? Explain the differences among a relationship instance, a relationship type, and a relationship set.

A

relationship type: a relationship type, R, among n entity types, E_1, E_2, …, E_n defines a set of associations among entities from these entity types

relationship instance: each relationship instance in a relationship type is an association of entities, where the association includes exactly one entity from each participating entity type;
each relationship instance represents the fact that the entities participating in it are related in some way in the corresponding miniworld situation

relationship set: a relationship set, R, is a set of relationship instances, r_i, where each r_i, associates n individual entities (e_1, e_2, …, e_n) and each entity e_j in r_i is a member of entity set E_j, 1<=k<=n.
[HM: the set of all relationship instances of a given relationship type(?)]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What do you understand by the term “degree of a relationship type”? Explain with example.

A

degree of a relationship type: the number of participating entity types in each relationship instance

  • binary relationship type: degree=2; ex: EMPLOYEE is related to DEPARTMENT via binary relationship type WORKS_FOR; each instance of WORKS_FOR associates 2 entities: an EMPLOYEE and a DEPARTMENT
  • ternary relationship type: degree=3; ex: SUPPLIER, PROJECT, PART are related via ternary relationship type SUPPLY; each instance of SUPPLY associates 3 entities: a SUPPLIER, a PROJECT, and a PART

the higher the degree, the more complex the relationship can be to represent

17
Q

What does participation constraint specify? What are the two types of the participation constraints?

A

participation constraint:

  • AKA minimum cardinality constraint
  • specifies whether the existence of an entity depends on its being related to another entity via the relationship type;

2 types of participation constraints:

  • total participation:
    • AKA existence dependency
    • every entity in the total set of EMPLOYEE entities must be related to a DEPARTMENT entity via WORKS_FOR;
  • ** ex: company policy states that an EMPLOYEE must work for a particular DEPARTMENT so EMPLOYEE’s participation in the relationship is total; and since a DEPARTMENT cannot exist without EMPLOYEEs, DEPARTMENT’s participation in the relationship is also total
  • partial participation:
    • some or part of the set of EMPLOYEE entities are related to some DEPARTMENT entity via MANAGES, but not necessarily all
  • ** ex: an EMPLOYEE must manage a DEPARTMENT, but not every EMPLOYEE is a manager; so EMPLOYEE’s participation in the relationship is partial
18
Q

Under what conditions can an attribute of a binary relationship type be migrated to become the attribute of one of the participating entity types?

A

1: 1 relationship types: the relationship attribute can be migrated to either entity type;
* ex: relationship MANAGES between entity types EMPLOYEE and DEPARTMENT, relationship attribute StartDate that indicates when the employee started managing the department: StartDate could be migrated to EMPLOYEE, or to DEPARTMENT

1: N relationship types: the relationship attribute can be migrated ONLY to the N-side of the relationship;
* ex: relationship WORKS_FOR between entity types EMPLOYEE and DEPARTMENT, relationship attribute StartDate that indicates when the employee started working for that department: StartDate can ONLY be migrated to EMPLOYEE because each EMPLOYEE only works for one DEPARTMENT but each DEPARTMENT is made up of multiple EMPLOYEES who can each have a different start date

M:N relationship types: the relationship attribute canNOT be migrated to either entity type; must remain a relationship attribute;
* ex: relationship WORKS_ON between entity types EMPLOYEE and PROJECT, relationship attribute Hours that indicates how many hours the employee spends working on the project: Hours cannot be an attribute of EMPLOYEE because an employee can work on many projects; Hours cannot be an attribute of PROJECT because many employees can work on the project; Hours MUST remain a relationship attribute of WORKS_ON

19
Q

When we think of relationships as attributes, what are the value sets of these attributes? What class of data models is based on this concept?

A

when we think of a binary relationship as an attribute we always have two options or two points of view;
ex:
* a Department attr of the EMPLOYEE entity type, where the value of Department for each EMPLOYEE entity is a reference to the DEPARTMENT entity for which that employee works
** the value set for this Department attr is the set of all DEPARTMENT entities, which is the DEPARTMENT entity set
* a multivalued Employees attr of the DEPARTMENT entity type whose value for each DEPARTMENT entity is the set of EMPLOYEE entities who work for that department
** the value set of this Employees attr is the power set of the EMPLOYEE entity set

functional data models are based on this concept

20
Q

What is the significance of role names in the relationship? In what situations, role names are essential?

A

role names help specify what role each entity instance plays in the relationship;
role names are not strictly necessary in relationships between two different entity types;
role names ARE essential though if the relationship is between two instances of the same entity type [i.e. a recursive relationship, AKA self-referencing relationship];
ex: a recursive relationship between two instances of EMPLOYEE type: one with role name=Boss, the other with role name=Worker

21
Q

What is the difference between weak entity types and strong entity types? How is weak entity type identified?

A

strong entity types have their own key attribute that can be used to define them uniquely;

weak entity types don’t have key attributes of their own;

entities belonging to a weak entity type are identified by being related to specific entities from another entity type [i.e. the identifying entity type AKA owner entity type AKA parent entity type AKA dominant entity type] in combination with one of their attribute values;
identifying relationship [of the weak entity type]: the relationship that relates a weak entity type to its owner;
a weak entity type always has a total participation constraint (existence dependency) wrt its identifying relationship bc a weak entity cannot be identified without an owner entity;

a weak entity type normally has a partial key (AKA discriminator), which is the attribute that can uniquely identify weak entities that are related to the same owner entity; in the worst case a composite attr of all the weak entity’s attributes will be the partial key

22
Q

Can an identifying relationship of a weak entity type be of a degree greater than 2? Give examples to illustrate your answer.

A

yes: (some DB design tools are based on variations of the ER model that permit only binary relationships, so ternary relationships have to be represented as a weak entity type with no partial key and with 3 identifying relationships)
ex:
* 3 participating entity types SUPPLIER, PART, and PROJECT are together the owner entity types; hence an entity in the weak entity type SUPPLY is identified by the combo of its 3 owner entities from SUPPLIER, PART, and PROJECT

23
Q

Discuss the conventions for displaying an ER schema as an ER diagram.

A
  • regular (strong) entity types are shown in rectangular boxes
  • relationships are shown in diamond-shaped boxes attached to the participating entity types with straight lines
  • attributes are shown in ovals, and each attribute is attached by a straight line to its entity type or relationship type
    • component attrs of a composite attr are attached to the oval representing the composite attr
    • multivalued attrs are shown in double attrs
    • key attrs have their names underlined
    • derived attrs are shown in dotted ovals
  • weak entity types are identified by being placed in double rectangles
    • and a weak entity type’s identifying relationship is placed in double diamonds
    • the partial key of a weak entity type is underlined with a dotted line
  • the cardinality ratio of each binary relationship type is specified by attaching a 1, M, or N on each participating edge
  • participation constraint:
    • partial participation: single line
    • total participation: double line
  • role names for relationship types
24
Q

Discuss the naming conventions used for ER schema diagrams.

A
  • choose names that convey as much as possible the meanings attached to the different constructs in the schema
  • entity type names: singular, capital letters
    • usu. nouns
  • relationship type names: capital letters
    • usu. verbs
    • choose binary relationship names to make the ER diagram of the schema readable from L->R and T->B
  • attribute names: first letter capitalized
    • usu. add’l nouns that describe the nouns corresponding to entity types
  • role names: lowercase letters

dbs (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions