Title: Theory / Important / CS / Algo / Database / Database System Concept 6 - McGraw Hill+27{ / 2 Design
Tags: examplesSwitchExample1 hasSeenConjDef récupéré representation toBinaryRelation

Name: The phases of designing a database
Definition: characterize fully the data needs of the prospective database users
Page: 260
Chapter: 7
Section: 1.1
Definition2: conceptual-design phase
Conjdef: In this order
Definition3: specification of functional requirements
Definition4: logical-design phase
Definition5: physical-design phase

Name: Redundancy
Definition: Repeating information
Page: 261
Chapter: 7
Section: 1.1

Question: Problem with redundancy ?
Answer: Data may become inconsistent.
Page: 261
Chapter: 7
Section: 1.1
Context: SQL
Answer2: An update which affect a copy may not affect the other one

Name: Incompleteness
Definition: The fact that some data may not be represented because of the schema
Page: 261
Chapter: 7
Section: 1.1
Context: DB
Example: If we want to represent a set S and a value for some elements of S, we could naively store only the table assigning values to elements of S. But this would forbid to add elements to S without values.

Name: An entity set
Definition: A set of entities of the same type that share the same properties, or attributes.
Page: 262, 275
Chapter: 7
Section: 1.1, 5
Context: Entity-Relationship diagram
Notation: Divided rectangle
Extra: Entity was defined in the introduction. Definition not repeated here.

Name: The extension of \(e\)
Definition: The actual collection of entities belonging to the entity set.
Page: 262
Chapter: 7
Section: 1.1
Variables: \(e\) an entity set
Context: DB

Question: An entity is represented by
Answer: a value for each attribute of its entity set.
Page: 262
Chapter: 7
Section: 1.1

Name: Attributes
Definition: Descriptive properties possessed by each member of an entity set.
Page: 262
Chapter: 7
Section: 1.1

Name: A relationship set
Definition: A set of relationships of the same type.
Page: 264, 275
Chapter: 7
Section: 2.2
Extra: Relationship was defined in the introduction. Definition not repeated here.

Name: \(E\) participates in \(R\)
Definition: \(E\) is one column of \(R\)'s relationship set
Page: 264
Chapter: 7
Section: 2.2
Variables: \(R\) a relationship,
\(E\) an entity

Name: The role of \(E\)
Definition: The function that \(E\) plays in \(R\)
Page: 264
Chapter: 7
Section: 2.2
Variables: \(R\) a relationship,
\(E\) an entity participating in \(R\)

Name: A recursive relationship
Definition: A relationship with an entity participating multiple times
Page: 265
Chapter: 7
Section: 2.2
Example: Prerequisite in a course database

Name: A descriptive attribute
Definition: An attribute which does not have to be used to identify a relation
Page: 265
Chapter: 7
Section: 2.2

Name: A binary relationship
Definition: A relationship involving two entities.
Page: 266
Chapter: 7
Section: 2.2

Name: The degree of \(R\)
Definition: The number of entity sets that participate in \(R\)
Page: 267
Chapter: 7
Section: 2.2
Variables: \(R\) a relationship set

Name: The domain of \(a\)
Definition: The set of permitted values of \(a\)
Page: 267
Chapter: 7
Section: 2.3
Variables: \(a\) an attribute
Name2: The value set of \(a\)

Name: A simple attribute
Definition: An attribute which is not divided into subparts
Page: 267
Chapter: 7
Section: 2.3

Name: A composite attribute
Definition: An attribute which is divided into subparts
Page: 267
Chapter: 7
Section: 2.3
Example: A student table may contain the composite attribute address, which is composed of street, city, state, zip_code

Name: A single-valued attribute
Definition: An attribute which contains only one value in each entity
Page: 267
Chapter: 7
Section: 2.3
Example: The id of a student

Name: A multivalued attribute
Definition: An attribute which possibly contains multiples value for some entity
Page: 267, 268
Chapter: 7
Section: 2.3
Context: Database
Example: The phone number of a student
Denoted by: {attr}

Name: A derived attribute
Definition: An attribute which can be computed from other attributes
Page: 268
Chapter: 7
Section: 2.3
Example: The number of students is derived from the set of students

Name: A not applicable value of attr
Definition: The fact that, in a tuple, some attribute may have no value
Page: 268
Chapter: 7
Section: 2.3
Variables: attr an attribute
Example: Middle name column, for someone without middle name

Name: A missing value of attr
Definition: The fact that, in a tuple, some attribute is applicable, but is not in the DB
Page: 268
Chapter: 7
Section: 2.3
Variables: attr an attribute
Example: Middle name column, for someone with a middle name, which is not in the DB

Name: A not known value of attr
Definition: The fact that, in a tuple, we do not know whether the value is missing or not applicable
Page: 268
Chapter: 7
Section: 2.3
Variables: attr an attribute

Name: An unknown value of attr
Definition: Either a not known or a missing value
Page: 268
Chapter: 7
Section: 2.3
Variables: attr an attribute

Name: Mapping cardinalities
Definition: The number of entities to which another entity can be associated via a relationship set.
Page: 269
Chapter: 7
Section: 3.1
Name2: Cardinality ratio

Name: A one-to-one mapping cardinalities
Definition: An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.
Page: 269
Chapter: 7
Section: 3.1

Name: A one-to-many mapping cardinalities
Definition: An entity in A is associated with any number (zero or more) of entities in B.
Page: 269
Chapter: 7
Section: 3.1
Definition2: An entity in B, however, can be associated with at most one entity in A.
Conjdef: An association of entities such that

Name: A many-to-one mapping cardinalities
Definition: An entity in A is associated with at moste one entity in B.
Page: 270
Chapter: 7
Section: 3.1
Definition2: An entity in B, however, can be associated with any number (zero or more) entities in A.
Conjdef: An association of entities such that

Name: A many-to-many mapping cardinalities
Definition: An entity in A is associated with any number (zero or more) entities in B.
Page: 270
Chapter: 7
Section: 3.1
Definition2: An entity in B, however, can be associated with any number (zero or more) entities in A.
Conjdef: An association of entities such that

Name: The participation of \(E\) is total in \(R\)
Definition: Every entity in \(E\) participates in at least one relationship in \(R\)
Page: 270
Chapter: 7
Section: 3.1
Variables: \(R\) a relationship
\(E\) an entity set

Name: The participation of \(E\) is partial in \(R\)
Definition: Only some entities in \(E\) participates in at least one relationship in \(R\)
Page: 270
Chapter: 7
Section: 3.2
Variables: \(R\) a relationship
\(E\) an entity set
Definition2: The participation of \(E\) is not total

Name: The set of attributes that forms the primary key for the entity set \(E_i\)
Page: 271
Chapter: 7
Section: 3.3
Variables: \(E_1,\dots,E_n\) entity set
\(R\) a relationship set involving the \(E_i\)'s
Notation: \(primary-key(E_i)\)

Question: An example of candidate key for \(R\)
Answer: \(\bigcup_{i}primary-key(E_i)\), up to renaming attributes' name occuring multiple times/having the same name
Page: 271
Chapter: 7
Section: 3.3
Variables: \(E_1,\dots,E_n\) entity set
\(R\) a relationship set involving the \(E_i\)'s

Smaller0: one-to-many relationship
Greater0: the primary key of \(E_1\)
Page: 272
Chapter: 7
Section: 3.3
Variables: \(E_1,\dots,E_n\) entity set
\(R\) a relationship set involving the \(E_i\)'s
Greater1: a candidate key for \(R\)
Connect Greater0: is
Prefix Smaller: \(R\) is a

Smaller0: one-to-one relationship
Greater0: both the primary key of \(E_1\) and the one of \(E_2\)
Page: 272
Chapter: 7
Section: 3.3
Variables: \(E_1,\dots,E_n\) entity set
\(R\) a relationship set involving the \(E_i\)'s
Greater1: a candidate key for \(R\)
Connect Greater0: is
Prefix Smaller: \(R\) is a

Name: Attribute of a relationship set
Page: 274
Chapter: 7
Section: 5
Notation: Undivided rectangle as in

Definition: link entity sets to relationship sets.
Page: 274
Chapter: 7
Section: 5
Context: Entity-Relationship diagram
Notation: Line

Definition: link attributes of a relationship set to the relationship set.
Page: 274
Chapter: 7
Section: 5
Context: Entity-Relationship diagram
Notation: Dashed line

Definition: link entity sets to relationship sets, such that the entity is «one» (one-to-one or one-to-many)
Page: 274
Chapter: 7
Section: 5
Context: Entity-Relationship diagram
Notation: A directed line from the relationship to the entity set

Name: An aggregation operation
Definition: Some \(_{G_1,\dots,G_n}\mathcal G_{F_1(A_1),\dots,F_m(A_m)}(E)\)
Page: 237
Chapter: 6
Section: 1
Denoted by: \(\mathcal G\)

Question: Why do we avoid writting key of an entity as a column of another entity ?
Answer: Because this limit the first entity to be present at most once. Adding multiple key would require a change to the database, and not only to the assertions
Page: 273
Chapter: 7
Section: 4

Definition: The attributes of a relationship set. Attributes that are part of the primary key are underlined.
Page: 275
Chapter: 7
Section: 4
Context: Database
Representation: undivided rectangles

Definition: total participation of an entity in a relationship set.
Page: 275
Chapter: 7
Section: 4
Context: Entity-Relationship Diagrams
Notation: Double lines

Definition: stating that the relationship is «many-to» or «to-many»
Page: 275
Chapter: 7
Section: 4
Context: Entity-Relationship Diagrams
Notation: No arrow from the relation to the entity

Definition: The number of occurence an entity have in a relation is between l and r
Page: 275
Chapter: 7
Section: 4
Context: Entity-Relationship Diagrams
Notation: l..r

Name: Nonbinary relationship
Definition: A relationship between at least three entities set
Page: 278
Chapter: 7
Section: 5.5

Name: Weak entity set
Definition: An entity set that does not have sufficient attributes to form a primary key
Page: 280
Chapter: 7
Section: 5.6

Name: Strong entity set
Definition: An entity set that have sufficient attributes to form a primary key
Page: 280
Chapter: 7
Section: 5.6

Name: The identifying entity set of w
Definition: The entity set with which w is associated
Page: 280
Chapter: 7
Section: 5.6
Variables: w a weak entity set
Name2: The owner entity set of w
Name3: the entity set who owns w

Name: The identifying relationship of w
Definition: The relationship between w and its owner
Page: 280
Chapter: 7
Section: 5.6
Variables: w a weak entity set

Name: An existence dependent set of s
Definition: A weak entity set w such that s is the owner entity of s
Page: 280
Chapter: 7
Section: 5.6
Variables: s an entity set

Name: The discriminator of w
Definition: A set of attributes that allow the distinction between entities of w
Page: 280
Chapter: 7
Section: 5.6
Variables: w a weak entity set
Representation: underlined with a dashed line

Name: The primary key of w
Definition: Both the primary keys of the identifying entities set of w, and the discriminator of w
Page: 280
Chapter: 7
Section: 5.6
Variables: w a weak entity set

Name: conceptual-design phase
Definition: designer chooses a data model and, by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database
Page: 260
Chapter: 7
Section: 1.1
Context: database

Name: logical-design phase
Definition: designer maps the high-level conceptual schema onto the implementation data model of the database system that will be used
Page: 260
Chapter: 7
Section: 1.1
Context: database

Name: physical-design phase
Definition: the physical features of the database are specified
Page: 260
Chapter: 7
Section: 1.1
Context: database

Question: Difference between fine-granularity and coarse-granularity parallelism
Answer: coarse is for up to four processors sharing main memories, while fine is for bigger processors
Page: 771
Chapter: 17
Section: 1.1
Answer2: One processor by query for coarse, multiple processor for fine

Question: How to transform strong entity set into relation schema
Answer: Replace each strong entity set of name foo and descriptive attributes (a_1,...,a_n) with a schema foo(a_1,...,a_n) and same primary key.
Page: 283
Chapter: 7
Section: 6.1
Answer2: In case of single valued composite attribute, transform it into simple attribute
Answer3: For multivariate attribute bar, create a schema foo_bar(primary key of foo, bar)
Extra: strong entity set:
An entity set that have sufficient attributes to form a primary key

Question: How to transform weak entity set A into relation schema
Answer: Transform the strong entity set {a_1,...,a_n,b_1,...,b_m}
Page: 283
Chapter: 7
Section: 6.3
Variables: B a strong entity set with primary key b_1,...,b_mA a weak entity set with attributes a_1,...,a_n depending on B

Question: How to transform R into relation schema
Answer: take as primary key: if its one-to-one or many-to-one, the primary key of the side one. If its many-to-many, the primary key is the union of all primary keys
the primary keys have foreign key constraint
Page: 286
Chapter: 7
Section: 6.4
Variables: R a relationship set
take a1,...,an the union of primary keys
b1,...,bn the descriptive keys

Question: Why is there no relation schema for weak entity to its strong entity
Answer: it would be redundant and add no more information
Page: 288
Chapter: 7
Section: 6.4.1

Question: Thumb rule to decide whether some thing x is an attribute or an entity set of a schema S
Answer: if there could be zero or many, use entity set
Page: 291
Chapter: 7
Answer2: if x can has related information not directly related to S, entity set
Answer3: if x is a foreign key, use a relation schema

Question: How to encode n-ary relationship R (A_1,...,A_n)into binary relationships
Answer: create a new entity set R' with an entry by line r. Then n binary relationship R_A_i, relating n to its element in A_i.
Page: 293
Chapter: 7
Section: 7.2
Index: 7.19

Name: ISA relationship
Definition: The process of designating subgroupings within an entity set
Page: 295
Chapter: 7
Section: 8.1
Context: entity-relationship
Example: The entity set students can be specialized into graduate and undergraduate
Name2: superclass-subclass relationship
Etymology: «is a» relationship
Name3: Specialization
Representation: hollow arrow-head
pointing from the specialized entity to the other entity.

Name: overlapping specialization/generalization
Definition: Specialization where a an entity may belong to multiple specialized entity sets
Page: , 300
Chapter: 7
Section: 8.4
Representation: one arrow from each specialization

Name: disjoint specialization
Definition: Specialization where a an entity may belong to at most one specialized entity sets
Page: , 300
Chapter: 7
Section: 8.4
Context: Database
Example: employee can be instructor OR secretary, not both
Name2: Disjointness constraint
Representation: A single arrow from multiple specialization

Name: top-down design process
Definition: refinement from an initial entity set into successive levels of entity subgroupings
Page: 297
Chapter: 7
Section: 8.2

Name: bottom-up design process
Definition: multiple entity sets are synthesized into a higher-level entity set on the basis of common features
Page: 297
Chapter: 7
Section: 8.2
Context: DB

Page: 297
Definition: a containment relationship that exists between a higher-level entity set and one or more lower-level entity set
Chapter: 7
Section: 8.2
Name: generalization
Context: DB
Example: employee is the higher-level entity set and instructor and secretary are lower-level entity sets

Name: attribute inheritance
Definition: The fact that attributes of the higher-level entity sets are inherited by the lower level
Page: 298
Chapter: 7
Section: 8.3

Name: An inherited attribute of F from E
Definition: An attribute of F coming from E
Page: 298
Chapter: 7
Section: 8.3
Variables: E an entity set
F a specialization of E
Context: database

Name: A hierarchy of entity-set
Definition: A set of entity-set with some specialization relation
Page: 299
Chapter: 7
Section: 8.3
Context: database
Name2: A lattice of entity-set (when there is multiple inheritance)
Extra: A lattice is a specific kind of ordered mathematical structure

Name: An entity set with multiple inheritance
Definition: An entity set which is a specialization of multiple entity set
Page: 299
Chapter: 7
Section: 8.3
Context: database

Name: An entity set with single inheritance
Definition: An entity set which is a specialization a single entity set
Page: 299
Chapter: 7
Section: 8.3
Context: database

Name: condition-defined lower-level entity sets
Definition: Specialization where the membership of an entity e in the specialized entity-set R is determined by the value of an attribute of e
Page: 299
Chapter: 7
Section: 8.4
Context: database

Name: user-defined lower-level entity sets
Definition: Specialization where the membership of an entity e is decided on a case by case basis
Page: 299
Chapter: 7
Section: 8.4
Context: database

Name: Completeness constraint
Definition: Either total or partial generalization/specialization
Page: 300
Chapter: 7
Section: 8.4
Context: database, generalization

Name: Total generalization/specialization
Definition: Each entity of the higher-level entity-set must belong to a lower-level entity set
Page: 300
Chapter: 7
Section: 8.4
Context: database, generalization
Example: students must be graduate or undergraduate
Representation: «total» added to the arrow

Name: Partial generalization/specialization
Definition: Some entity of the higher-level entity-set may belong to no lower-level entity set
Page: 300
Chapter: 7
Section: 8.4
Context: database, generalization
Example: An employee may belong to a team or to no team

Question: In a hierarchy of entity-relationship, what happens when an entity is removed from a high-level entity-set ?
Answer: It is removed from all entity-set below
Page: 301
Chapter: 7
Section: 8.4
Context: database, generalization

Question: Limit of the E-R model
Answer: no relation between relation
Page: 301
Chapter: 7
Section: 8.5
Context: database

Name: Aggregation
Definition: abstraction through which relationships are treated as higher-level entities
Page: 301
Chapter: 7
Section: 8.5
Context: database
Example: The 3-tuple instructor-student-project is treated as a single entity, in a relation linking this entity to reports.

Question: How to represent hierarchy as relation schemas
Answer: Create a standard schema for the top level
Page: 302
Chapter: 7
Section: 8.6
Context: database
Answer2: for each lower-lever relation, create a schema with the attribute of this relationship not belonging to its parent(s) and the primary key(s) of its parent(s). Those primary keys have foreign constraint and serve as its primary key
Answer3: if a higher-level entety-set as a specialization disjoint and complete, the higher-level may be omitted. In this case, make a full schema for all specialization. (Can't be done if its primary key is used as a foreign key constraint somewhere else)

Question: How to create schema for aggregation
Answer: Create a schema for the aggregation as a relation.
Then consider this schema as any entity schema
Page: 304
Chapter: 7
Section: 8.6.2

Definition: The name of the entity set
Page: 275
Chapter: 7
Section: 5
Context: Entity-Relationship diagram
Notation: The blue part of this Entity

Definition: the names of all the attributes of the entity set
Page: 275
Chapter: 7
Section: 5
Context: Entity-Relationship diagram
Notation: The white part of this Entity-Relationship diagram