weaknesses from midterm 1 Flashcards
The _______ is a constraint on the number of instances of one entity that can (or must) be associated with each instance of another entity.
The correct answer is: cardinality
Explanation:
Cardinality refers to the number of instances of one entity that can (or must) be associated with each instance of another entity in a relationship. It’s a key concept in database design and entity-relationship modeling.
What are three (3) rules that a database designer has to consider which choosing a primary key?
- Uniqueness
The primary key must uniquely identify each record in the table.
No two rows can have the same value for the primary key.
- Non-nullability
A primary key cannot contain NULL values.
Every record must have a valid value for the primary key.
- Immutability (Stability)
The value of a primary key should not change over time.
It should be stable and not based on attributes that might be updated (like a phone number or email address).
Fill in the blanks
Conceptual Design
{Entity, [x], [x]}
Logical Design
{[x], Rows, [x]}
Implemented SQL
{[x], [x], Fields}
Conceptual Design
{Entity, Attribute, Identifier (Key)}
Logical Design
{Table, Rows, Primary Key}
Implemented SQL
{CREATE TABLE, Column, Fields}
This table maps how data concepts transition from high-level design to actual implementation in SQL:
Entity → Table → CREATE TABLE: Represents real-world objects stored as tables.
Attribute → Rows → Column: Characteristics of entities become data entries and columns.
Identifier → Primary Key → Fields: Unique identifiers ensure each row is distinct.
It shows how abstract ideas become structured, working SQL code.
What does a foreign key always refer to?
A foreign key always refers to a primary key (or a unique key) in another table.
What are TWO differences between the SQL HAVING clause and the WHERE clause in a SELECT statement?
WHERE filters rows before grouping; HAVING filters after grouping.
WHERE cannot use aggregate functions; HAVING can.
What are business rules in a database?
Business rules are guidelines that control how data is entered, stored, and used in a database.
How do business rules affect database design?
They help decide how tables are set up and what data can be stored.
Example of a business rule?
“Each customer must have a unique email address.”
What are integrity constraints?
They enforce business rules to make sure data is correct (e.g., unique customer IDs).
Types of business rules?
Data integrity (e.g., unique IDs)
Referential integrity (e.g., matching foreign keys)
Security (e.g., limited access to data)
Operational (e.g., max order size)
Why define business rules first?
To make sure the database works properly and data is accurate.
How do business rules relate to normalization?
Business rules guide how tables should be split and related to keep data consistent.
