Quiz 3 Flashcards
During which phase of the SDLC is the E-R model developed?
- E-R model developed during Analysis phase
During which phase of the SDLC is the logical data model model developed?
- Logical data model (Relations) developed during Design phase
What are the steps to transform E-R Diagram into Relations?
- Represent entities
- Represent relationships
- Normalize if necessary
How do you build the logical data structure to represent a Unary 1:1 relationship?
- Add a foreign key that references the primary key values of the entity
How do you build the logical data structure to represent a Unary 1:N relationship?
- Add a foreign key that references the primary key values of the entity on the “one” side
How do you build the logical data structure to represent a Unary M:N relationship?
- Modeled as a separate relation
- Primary key of new relation is a composite key of two attributes that both take their values from the same primary key of the entity
How do you build the logical data structure to represent a Binary 1:1 relationship?
- Add the primary key of A as a foreign key of B
- Add the primary key of B as a foreign key of A
How do you build the logical data structure to represent a Binary 1:N relationship?
- Add the primary key of the entity on the “one’ side of the relationship as a foreign key in the relation on the many side of the relationship
How do you build the logical data structure to represent a Binary M:N relationship?
- Create an associative entity (intersection table)
- Create another relation and include primary keys of all relations as primary key of new relation as well as attributes of the relationship
How do you build the logical data structure to represent a Ternary relationship?
- Create an associative entity (intersection table)
- Create another relation and include primary keys of all relations as primary key of new relation as well as attributes of the relationship
What are the requirements to be normalized to 1NF?
- All column values must be atomic (i.e., indivisible)
- NO multivalued or composite attributes
What are the requirements to be normalized to 2NF?
- 1NF +
- every non-key column is fully dependent on the entire (composite) primary key
What are the requirements to be normalized to 3NF?
- 2NF +
- all non-key columns are mutually independent
AKA:
- no transitive dependency is allowed
What are the concerns of the physical data model?
- Databases
- Data types
- Integrity constraints
- Optimization
Name some database software
- Oracle
- MS SQL Server
- MS Access
- MySQL (now owned by Oracle)
What is a data type?
- a classification identifying one of various types of data, such as
- real
- integer
- Boolean
- determines the possible values for that type
- determines operations that can be done on values of that type
- meaning of the data
- way values of that type can be stored
What are integrity constraints?
- Referential Integrity
- Default value
- Range Control
- Null value control
What are the major methods of database optimization?
- Denormalization
- Partitioning
- Read/Write Splitting
Why is data sometimes denormalized?
- Speed of query vs. storage efficiency
- Normalization can slow down queries
What are the major types of partitioning?
- Horizontal Partitioning
- Vertical Partitioning
What is Horizontal Partitioning?
- Split a table horizontally
- each partition contains only certain rows
- Each partition is also called a shard
What is Vertical Partitioning?
- Split a table vertically
- each partition contains only certain columns
What is Read/Write Splitting?
- Master for writing to
- Replicated Slaves for reading
What are the first three Normal Forms?
- No repeating elements or groups of elements
- No partial dependencies on a concatenated key
- No dependencies on non-key attributes
