Lesson 4 Database Design Flashcards
binary relationship
An ER term for an association (relationship) between two entities.
For example, PROFESSOR teaches CLASS.
cardinality
- A property that assigns a specific value to connectivity
- expresses the range of allowed entity occurrences associated with a single occurrence of the related entity.
composite attribute
For example, a phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368).
An attribute that can be further subdivided to yield additional attributes.
composite identifier
also known as a composite key
In ER modeling, a key composed of more than one attribute.
derived attribute
- An attribute that does not physically exist within the entity and is derived via an algorithm.
- For example, the YEARS_EMPLOYED attribute might be derived by subtracting the start date from the current date.
existence-dependent
- A property of an entity whose existence depends on one or more other entities.
- In such an environment, the existence-independent table must be created and loaded first, because the existence-dependent key cannot reference a table that does not yet exist.
existence-independent
- A property of an entity that can exist apart from one or more related entities.
- Such a table must be created first when referencing an existence-dependent table.
identifiers
One or more attributes (columns) that uniquely identify each entity instance.
also known as keys
iterative process
A process based on repetition of steps and procedures.
mandatory participation
A relationship in which one entity occurrence must have a corresponding occurrence in another entity.
- For example, an
EMPLOYEEworks in aDIVISION. - A person cannot be an employee without being assigned to a company’s division.
multivalued attributes
An attribute that can have many values for a single entity occurrence.
- For example, an
EMP_DEGREEattribute might store the string"BBA, MBA, PHD"to indicate three different degrees held.
non-identifying relationship
A relationship in which the the child table’s primary key is independent of the parent table’s primary key.
optional attribute
In ER modeling, an attribute that does not require a value; therefore, it can be left empty.
optional participation
In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
participants
- An ER term for entities that participate in a relationship.
- For example, in the relationship “PROFESSOR teaches CLASS,” the teaches relationship is based on the participants
PROFESSORandCLASS.
recursive relationship
A relationship found within a single entity type.
- For example, an
EMPLOYEEis married to anEMPLOYEEor - A
PARTis a component of anotherPART.
relational schema
The organization of a relational database as described by the database administrator.
relationship degree
- The number of entities or participants associated with a relationship.
- A relationship degree can be unary, binary, ternary, or higher.
required attribute
In ER modeling, an attribute that must have a value.
In other words, it cannot be left empty.
simple attribute
An attribute that cannot be subdivided into meaningful components.
Compared to composite attribute.
single-valued attribute
An attribute that can have only one value.
strong (identifying) relationship
- This relationship exists whenever the primary key of the related entity contains the primary key components of the parent entity.
- A relationship that occurs when two entities are existence-dependent.
strong entity
- An entity that is existence-independent.
- It can exist apart from all of its related entities.
Also called a regular entity.
ternary relationship
- An ER term used to describe an association (relationship) between three entities.
- For example, a DOCTOR prescribes a DRUG for a PATIENT.
unary relationship
- An ER term used to describe an association within an entity.
- For example, an EMPLOYEE might manage another EMPLOYEE.
weak entity
- An entity that displays existence dependence and inherits the primary key of its parent entity.
- For example, a DEPENDENT requires the existence of an EMPLOYEE.
weak relationship
- A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.
- A weak relationship links two strong entities that can exist independently.
Is the entity relationship model (ERM); dependent on the database type?
No, the ERM is not dependent on the database type; it serves as a conceptual framework for organizing data regardless of the specific database implementation
Why is The Crow’s Foot notation more implemented than the Chen notation.
- CF Notation symbols directly translate to database concepts like relationships (one-to-many, etc.)
- Offers simplicity visualization of data structures.
- This makes it easier to map diagrams to database tables compared to the more abstract Chen notation.
An entity in the entity relationship model corresponds with $ ?
Entities in ER models corresponds to a table in the relational environment.
What does a table row represent in the entity relationship model?
A table row represents a single entity instance, containing all attributes for that specific occurrence of the entity.
In the Chen and Crow’s Foot notations, an entity is represented with ?
Entity: a rectangle containing the entity’s name
In the original Chen notation how are attributes & attribute names represented?
- Each attribute is represented by an oval
- The attribute name connected to an entity rectangle with a line.
Why do Software vendors oppose the Chen representation?
- Software vendors may not favor Chen notation because it can be visually complex and use many symbols, potentially making it harder to use and less appealing to a wider audience.
- This complexity can lead to larger diagrams and a steeper learning curve
What characterizes a composite identifier in database design?
A composite identifier is a primary key that consists of more than one attribute used to uniquely identify an entity instance.
Why is Crow’s Foot notation useless for multi variable attributes?
- CF notation focuses on relationships between entities.
- It uses symbols to represent cardinality and optionality (mandatory or optional) in relationships.
- Multi valued attributes are about a single entity.
cardinality (one-to-one, one-to-many, etc.)
How does Composite attributes make it easier to facilitate detailed queries?
- They group related information together.
- Instead of searching through separate fields for each part of an address,you can use the composite “address” attribute to find exactly what you need with a single query.
- This makes queries more efficient and easier to formulate.
Do relationships between entities operate in only one direction?
No, relationships between entities can operate in both directions, allowing for interactions that can be navigated from either entity involved.
What establishes connectivities and cardinalities in database relationships?
Connectivities and cardinalities are established by concise statements known as business rules that define how entities relate to each other.
Is there any way to represent cardinality in Chen notation?
Chen notation includes methods to represent cardinality through specific symbols that indicate relationships between entities.
What does it mean for an entity to have a mandatory primary key in implementation terms?
- A mandatory primary key means that every record in a database table must have a unique identifier.
- This ensures that each entry can be easily distinguished from others, preventing confusion and duplication.
Under what conditions does a weak relationship exist in database design?
A weak relationship exists if the primary key of the related entity does not contain any primary key components of the parent entity.
In a 1:M relationship, what must be done to avoid referential integrity errors?
To avoid referential integrity errors in a 1:M relationship, the data of the “1” side must be loaded first, ensuring that reference entities are in place.
Are unary relationships common in manufacturing industries?
Yes, unary relationships, where an entity relates to itself, are common in manufacturing industries and can represent scenarios such as employee management hierarchies.
Define both participation & referential integrity; are they bidirectional in a relationship?
- Participation relates to whether an entity requires a relationship with another & does not necessarily imply a bidirectional requirement
- Referential integrity ensures that such references are valid and also do not necessarily imply a bidirectional requirement.
When does a weak relationship, also known as a non-identifying relationship, exist?
A weak relationship exists if the primary key of the related entity does not incorporate any primary key components from the parent entity, indicating a lack of identity dependency.
What does the existence of a mandatory relationship suggest about minimum cardinality?
A mandatory relationship indicates:
That its minimum cardinality must be at least 1 for the mandatory entity, not 0
Why must a database designer understand the “1” and “M” sides of each relationship when implementing a small database?
Understanding the “1” and “M” sides is crucial for proper relational design, as it determines how entities reference each other and ensures integrity within the database.
The _____ notation of entity-relationship modelling can be used for both conceptual and implementation modelling.
UML
The entity relationship diagram (ERD) represents the _____ database as viewed by the end user.
conceptual
A(n) _____ is the set of possible values for a given attribute.
domain
Ideally, an entity identifier is composed of _____ attribute(s)
one
A _____ attribute can be further subdivided to yield additional attributes.
composite
A _____ attribute is one that cannot be subdivided.
simple
The conceptual model can handle _____ relationships and multivalued attributes.
M:N
A derived attribute is indicated in the Chen notation by a _____ that connects the attribute and an entity.
dashed line
The __________ attribute need not be physically stored within the database.
derived
A relationship is an association between _____.
Entities
What does cardinality express?
Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity.
When the specific cardinalities are not included on the diagram in Crow’s Foot notation, cardinality is implied by the use of _____.
symbols
Knowing the minimum and maximum number of _____ occurrences is very helpful at the application software level.
entity
An entity is said to be _____ -dependent if it can exist in the database only when it is associated with another related entity occurrence.
existance
If an entity can exist apart from all of its related entities, then it is existence-independent, and it is referred to as a(n) _____ entity.
strong
A weak entity does not have $ attributes to form a $.
- sufficient
- Primary key
The existence of a(n) _____ entity indicates that its minimum cardinality is zero.
optional
The Crow’s foot symbol with two vertical parallel lines indicates _____ cardinality.
(1:1)
A _____ relationship exists when an association is maintained within a single entity.
unary
A _____ relationship exists when three entities are associated
ternary
If an employee within an EMPLOYEE entity has a relationship with itself, that relationship is known as a _____ relationship.
recursive
To simplify the conceptual design, most higher-order relationships are decomposed into appropriate equivalent _____ relationships whenever possible.
binary
The entity relationship model uses the associative entity to represent a(n) _____ relationship between two or more entities.
M:N
When using the Crow’s Foot notation, the associative entity is indicated by _____ relationship lines between the parents and the associative entity.
solid
______ are charcteristics of entities
Attribute
A _____ attribute is an attribute that must have a value
required
______ are underlined in an ER diagram
Identifiers
A person’s Social Security number would be an example of a ______ ______ attribute.
Single valued
A ______ attribute need not be physically stored within the database.
derived
A _____ attribute is an attribute that can be further subdivided to yield additional attributes.
composite
When indicating cardinality, the first value represents the _____ number of associated entities.
minimum
The concept of relationship strength is based on how the ______ ______ of a related entity is defined.
primary key
A ______ relationship is also known as an identifying relationship.
strong
• A strong relationship links a strong entity to a weak entity (the weak entity depends on the strong one).
The Crow’s Foot notation depicts the strong relationship with a(n) _____ line between the entities.
solid
A weak entity must be ______ dependent.
existence
The Chen notation identifies a weak entity by using a double-walled entity ________.
rectangle
Participation is _______ ; if one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
optional
In Crow’s Foot notation, an optional relationship between entities is shown by drawing a small _____ _____ on the side of the optional entity.
small circle/circle (o)
________ expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity.
Cardinality
A relationship _______ indicates the number of entities or participants associated with a relationship.
degree/cardinality
Is the process of database design considered sequential?
No, the process of database design is often iterative, involving repetitions and revisions rather than a strictly linear sequence of steps.
The first step in building an entity-relationship diagram (ERD) is _____ .
Analysis: creating a detailed narrative of the organization’s description of operations.
- If
Tiny Collegehas some departments that are classified as “research only” and do not offer courses. - The
COURSEentity of the college database would be _____ to; theDEPARTMENTentity.
Entity does mean table
An ______ process is based on repetition of processes and procedures.
iterative
Identifying the attributes of entities helps in the better understanding of ______ among entities.
relationships
In organizations that generate large number of transactions, _____ are often a top priority in database design.
high processing speeds
Complex information requirements may $ ?
- Dictate data transformations OR
- Expand the number of entities and attributes within the designs
________ not only helps database designers to stay on track during the design process, it also enables them to pick up the design thread when the time comes to modify the design.
Documentation
What is an entity type in entity-relationship modeling?
A set of unique individuals
Example: The entity type ‘Passenger’ includes all passengers in the system.
What does the relationship type represent in entity-relationship modeling?
A set of related pairs
Example: The relationship type ‘Passenger-Holds-Booking’ represents pairs of passengers and their associated bookings.
What is an attribute type?
A set of values related to an entity
Example: The attribute type ‘BookingCode’ encompasses all possible booking codes.
Define entity instance
An individual member of the entity type
Example: ‘Muhammed Ali’ is a specific entity instance representing a passenger.
What is a relationship instance?
A specific assertion about entities
What is an attribute instance?
An individual value related to an attribute
Example: ‘39240’ is a specific instance of the ‘BookingCode’ attribute. Learn more
In entity-relationship modeling, what do entity types, relationship types, and attribute types correlate to?
Tables, foreign keys, and columns
Their instances correspond to rows, foreign key values, and column values, respectively.
What is an entity in the context of data management?
A distinct object or thing in the domain that can be identified and has relevance to the data being managed.
What are attributes in relation to entities?
Properties or characteristics of an entity that provide more detailed information about it.
Define relationships in data management.
Define how entities interact with or relate to one another.
How do entities and attributes correlate?
Each entity is defined by its attributes, which describe its state or properties.
What is the role of relationships between entities?
Relationships connect two or more entities, illustrating how they are interrelated within the data model.
In addition to entities, what other things can possess attributes?
In database design, a relationship between two entities can sometimes have its own attributes that describe the relationship itself.
What is a weak entity?
An entity that cannot be uniquely identified by its own attributes alone and relies on a foreign key from an identifying table.
What is an ‘IsA’ relationship?
A type of relationship that signifies inheritance or a hierarchical connection between entities.
What is a primary key?
A column (or set of columns) in a table that uniquely identifies each row in that table.
What are composite keys?
Keys that consist of two or more columns used together to create a unique identifier for a row in a table.
What is an artificial key?
A single-column primary key created by the database designer when no suitable natural primary key exists.
Define a foreign key.
A column (or set of columns) in one table that refers to the primary key in another table, establishing a link.
In a one-to-one relationship, how is the primary key used?
Table A’s primary key can serve as a foreign key in Table B.
What is the relationship between one-to-many and foreign keys?
Table A has the primary key, which Table B references as a foreign key.
What is a many-to-many relationship?
A relationship where multiple records in Table A can relate to multiple records in Table B, often requiring a junction table.
What is the purpose of implementing strong entities as tables?
To create a table for each strong entity identified in the data model.
What happens if no suitable primary key exists?
An artificial key is created to serve as the primary key.
What is the purpose of implementing subtype entities as tables?
To create tables for subtype entities that inherit from a main entity.
How is a weak entity implemented as a table?
By using a composite primary key that includes a foreign key referencing the identifying entity.
What does specifying cascade and restrict actions do?
Defines referential integrity actions, for relationships between tables.
Fill in the blank: In the first step of the logical design phase, each entity becomes a _______ and each attribute becomes a _______.
[table], [column]
What is the first step in the logical design phase of database design?
Each entity becomes a table and each attribute becomes a column.
What must primary keys be in a database?
Unique and not NULL.
What are the three desirable characteristics of primary keys?
- Stable
- Simple
- Meaningless
What do solid bullets (●) denote in table diagrams?
Primary key columns.
What defines a strong entity in database design?
Can be uniquely identified by its own attributes.
What is a weak entity?
Cannot be uniquely identified by its own attributes and relies on a strong entity.
What is the primary key of a weak entity typically composed of?
A weak entity uses a composite key including a foreign key from the identifying strong entity to make a PK.
What types of relationships can entities have?
- One-to-One
- One-to-Many
- Many-to-Many
What is a foreign key?
- A field in a database table refers to a single piece of information within a row; representing a specific attribute or detail about the data entry within that row.
- A FK is a field in one table that uniquely identifies a row of another table.
What happens in a many-to-many relationship?
A new dependent table (junction) is created containing two foreign keys.
What does cascade on primary key update and delete mean?
Foreign keys that reference a primary key will also update or delete automatically.
Can NULLs be allowed in the foreign key column?
Yes, unless the foreign key constraint specifies NOT NULL.
Are many-one and one-one relationships always implemented before many-many relationships?
- Many-to-many relationships often rely on underlying one-to-many relationships to be implemented first.
- Think of it like building a house – you need the foundation (one-to-many) before you can add the complex roof structure (many-to-many).
What kind of relationship is implemented where a primary key of a table implementing is always composite?
- Many-to-many relationships in databases require a special table to link two other tables.
- This “junction table” uses a composite primary key (made up of foreign keys from the two main tables) to ensure each relationship is unique.
What is the rule regarding unique and required attributes in attribute cardinality?
Each unique attribute instance describes at most one entity instance.
What SQL keyword is used for unique attributes?
UNIQUE.
What SQL keyword is used for required attributes?
NOT NULL
What happens to optional attributes in a database?
They can have zero instances and may allow NULL values.
What does the PRIMARY KEY keyword enforce?
Unique and required constraints.
What does the FOREIGN KEY column require in terms of constraints?
UNIQUE and NOT NULL if derived from unique and required relationships.
What SQL command is used to create a table with a primary key?
CREATE TABLE
What is normalization in database design?
The process of eliminating redundancy from tables created during the implementation of entities, relationships, and attributes.
What is the highest normal form typically aimed for in practical database design?
Boyce-Codd Normal Form (BCNF)
Define dependency in the context of database normalization.
Column A depends on column B; if each B value relates to at most one A value.
What are the three steps involved in normalizing to BCNF?
- List all unique columns
- Identify dependencies on non-unique columns
- Eliminate dependencies on non-unique columns
What is denormalization?
Intentionally introducing redundancy by merging tables to eliminate join queries and improve query performance.
When is denormalization typically considered in database design?
For tables that experience infrequent data modifications; to enhance SELECT queries.
What should designers do as tables and keys are specified in the database design process?
- Review for BCNF
- Identify dependencies and unique columns.
What should be done if dependencies are identified; that do not meet the uniqueness criteria?
Decompose tables into smaller tables that meet BCNF requirements.
Originally, normalization referred to achieving _______.
first normal form
What is the purpose of identifying dependencies on non-unique columns during normalization?
To eliminate redundancy by decomposing tables.
Why is Denormalization less preferred in database design.
- Denormalization introduces redundancy to speed up reads.
- The best choice depends on whether your priority is data consistency or read performance.
What does Crow’s Foot notation primarily represent?
Entity-relationship models in database design.
How is Chen notation less detailed than Crow’s Foot notation.
- Chen notation , it tends to be less detailed regarding the nature of relationships, such as cardinality or specific constraints, which can lead to ambiguity in larger or more complex designs.
- Crow’s Foot Notation, on the other hand, provides for designers needing a comprehensive view of relationships and constraints in their data models.
In Crow’s Foot notation, what symbol represents a one-to-many relationship?
A line ending with a crow’s foot symbol (three prongs).
What shape is used to represent entities in Chen notation?
Rectangles.
In Chen notation, attributes are represented by _______.
Ovals/Ellipses.
What does a diamond shape represent in Chen notation?
A relationship.
What common visual feature is absent in Crow’s Foot notation?
Colors
What is the primary purpose of using entity-relationship diagrams?
To visually represent the data structure and relationships within a database.
In Crow’s Foot notation, a single line represents?
a one-to-one relationship
What does the ‘0..1’ notation indicate in Crow’s Foot?
An optional relationship where an entity can exist without a corresponding entity.
Short Answer: Name one advantage of using Chen notation over Crow’s Foot notation.
Chen notation provides a more detailed representation of attributes and relationships.
In Crow’s Foot notation, a relationship with a maximum of five occurrences is represented as _______.
Which notation is more widely used in software engineering?
Crow’s Foot
Chen notation can represent more than just weak entities. It’s a visual way to design databases, showing:
- Entities and Attributes: Rectangles for entities, with attributes listed inside.
- Relationships: Diamonds show how entities connect.
- Cardinality/Participation: Symbols indicate how many entities participate in a relationship.
- Weak Entities: Double-lined rectangles for weak entities.
- Inheritance: Models entities inheriting attributes from a parent entity.
What is the significance of a dashed line in Crow’s Foot notation?
It indicates a non-identifying relationship.
Short Answer: How are multi-valued attributes represented in Chen notation?
By using a double ellipse.
In Crow’s Foot notation, what does a double line indicate?
mandatory relationship is indicated by a solid vertical line (perpendicular to the relationship line) next to the entity.
Which notation is generally considered simpler for beginners?
Crow’s Foot
In Chen notation, a weak entity is represented by a _______.
Double rectangle.
True or False: Crow’s Foot notation uses colors to differentiate between types of relationships.
False
What does ‘1..*’ signify in Crow’s Foot notation?
A one-to-many relationship where one entity can relate to many others.
What is a primary key in the context of entity-relationship diagrams?
An attribute or set of attributes that uniquely identifies an entity.
Relationships in Chen notation are represented by _______.
Diamonds.
What is the main difference between cardinality and participation in Crow’s Foot notation?
- Cardinality specifies the number of instances.
- Participation indicates whether an entity’s existence is dependent on another.
In which notation are relationships explicitly labeled with verbs?
In CFN Verbs are often used to label the relationships, making them explicit.
Crows Foot uses symbols to label relationships, which in turn represent?
Crow’s Foot notation uses symbols to represent cardinality and optionality.
- Determine the Maxima and Minimum
- 1(1)
singular - required
- Determine the Maxima and Minimum
- 1(0)
singular - optional
- Determine the Maxima and Minimum
- M(1)
plural - required
- Determine the Maxima and Minimum; is it Unique?
- 1-1(1)
Unique singular - required
In ER diagrams, 1 indicates a unique attribute and M indicates a non-unique attribute.
- Determine the Maxima and Minimum; is it Unique?
- M-M(0)
Not Unique plural - optional
In ER diagrams, 1 indicates a unique attribute and M indicates a non-unique attribute.
