Ch.5 Design
3rd
1st 2nd or 3rd form? One important piece to note by looking at this is that the City and State are determined by the Zip in the US which falls under the pattern that we discussed before as the Zip is dependent on the UserID but the City and State are dependent on the Zip.
1st
1st, 2nd or 3rd Normal Form? In this table, we have a few repeating groups including the Actor as there can be many actors in a movie. In the Genre column, this is also a repeating group as there are many genres that a movie can be in. Let us first start by removing the repeating groups for the Actor and Genre and creating a new table for each. We will also need to create a primary key on the Rating table. Movie (MovieId, User, MovieTitle, NumericRating, TextualRating, ReleaseDate) Actor (MovieId, ActorId, ActorName) Genre (MovieId, GenreId, GenreName)
Strong
A ____ entity can exist without any dependency on any other relationship. Typically, these entities are ones that do not have any foreign keys
transitive
A _____ dependency exists when there are functional dependencies when you may have a column X dependent on the primary key of a table but then you have a dependency of column Y being dependent on column.
Reference
A ______ table is frequently also called a lookup table.
Strong Entity
Can exist without any dependency on any other relationship. Typically, these entities are ones that do not have any foreign keys. These tables are the ones that you would typically create first since they don't depend on other entities. Other entities will typically depend on them. There are times where the foreign key is also the primary key component in the related entity which creates a ____ relationship.
Chasm
Fan or Chasm? For example, if we have a situation where you have a group that has students and the group works on projects.The relationship, in this case, would set it such that not all projects are worked on by students as they may be handled externally. By having this type of model, if any project is not associated with a student, it cannot be associated with the group. So, if we have a situation where a group without students is assigned to a project (perhaps the students have not been assigned to the group yet), we could not actually create that link. To work around that scenario, we would have to create a link between the group and the project directly rather than depend on the middle relationship to include the student.
Fan trap
Fan or Chasm? If we attempted to aggregate the sum using the total from the invoice as well as the sum of the quantity * the unit_price at the same time. Let us see what would happen: SELECT customer.customer_id, sum(total) as "SUM of Total", sum(quantity * unit_price) as "SUM of quantity times unit_price" FROM customer, invoice, invoice_line WHERE customer.customer_id = invoice.customer_id AND invoice.invoice_id = invoice_line.invoice_id GROUP BY customer.customer_id;
3rd
For the ____ normal form, the tables should have no transitive dependencies. A transitive dependency exists when there are functional dependencies when you may have a column X dependent on the primary key of a table but then you have a dependency of column Y being dependent on column X.
1st
For the ____ normal form, we will ensure that each column in our tables will be single-valued meaning that they should not contain multiple values. By doing so, we will be able to eliminate any repeating groups of columns. We want to also ensure that we have a unique name for each of the attributes or columns in a table
2nd
For the ______ normal form, there should be no partial dependency. As a reminder, functional dependency means that each column in a table that is not a primary key should be determined by that primary key.
Underline
How do we signify a primary key in chen's notation?
1NF
Identify the normal form in which all of the attributes are set up to be dependent on the primary key. 1NF None of the first 3 normal forms 3NF 2NF
D.
Identify the normal form in which we eliminate repeating groups. a.)3NF b.)2NF c.)None of the first 3 normal forms d.)1NF
B
Identify the normal form in which we identify the primary key. a.)3NF b.)1NF c.)None of the first 3 normal forms d.)2NF
D
Identify the normal form that we have no partial dependencies. a.)3NF b.)None of the first 3 normal forms c.)1NF d.)2NF
weak
In Chen's notation, the ___ entity is defined by a double wall on the entity.
conceptual and logical
Normalization is typically performed when we are first designing the database and takes place between the _______ and ______ data model creation.
Boyce-Codd normal form
Only when a table contains more than one candidate key that we would need to address it. Also known as 3.5F
Diamond
Relationships like one to many are drawn with a ____ shape and listing the cardinality between each entity.
T
T or F The accepted difference between a lookup table and a reference table is that the reference table is meant to have a long value that's stored outside of the main table like a customerID
T
T or F There is no standard difference between the lookup and the reference table
Fan
The ___ trap occurs when you have two one-to-many joins that follow a parent-child form. If you try to aggregate both measures simultaneously, you will probably get incorrect results
1st
The ____ normal form is when we identify the primary keys of each table. A common-sense rule with the _____ normal form is to ensure that each of the column values should be of the same data type and purpose
1st
The ____ normal form will ensure that we will be able to uniquely identify each row in each table as well as remove all repeating groups from our tables.
Chasm
The ____ trap can occur when we have two one-to-many joins that converge on a single table and the query includes aggregate data from both leaf tables. As a result, you can run into a situation where the result from multiple rows are returned from the tables when the query is being processed. This type of issue can occur due to the join between the leaf tables as those valued would be multiplied potentially resulting in incorrect results. In an entity-relationship model, this can occur when the existence of a relationship is seen in the ERD but in reality, there isn't actually any connection
2nd
The business requirements do not define this so we do not have to add in a RoleFirstName or RoleLastName so our many-to-many relationship will be defined by a bridge table named Role to join the two tables of Actor and Movie together. Movie (MovieId, MovieTitle, ReleaseDate)Role (ActorId, MovieId)Actor (ActorId, ActorFirstName, ActorLastName) The ActorId and MovieId can be set up as a composite primary key. Which form would this be in?
weak
The first criterion that would define a ____ entity is if it is existence-dependent. This means that it cannot exists without the entity that it has a relationship with.
weak
The other criteria of a ___ entity are if the entity has a primary key that is partially or entirely derived from the parent entity in the relationship
Reference
The primary key of the _____table is either an auto-increment value or an abbreviation while the value column will have the full value
Weak Entity
There are instances where the primary key of one entity only appears as the foreign key in the related entity, and in those cases, we have a ____ relationship. Ex. The Rating entity will have dependencies on both the user and the movie. A rating cannot exist without having the user that submitted the rating. A rating cannot exist without being connected to a movie either
Strong
These tables are the ones that you would typically create first since they don't depend on other entities. Other entities will typically depend on them
Reference
These tables generally will have at least two columns set up as key-value pairs where the first column is the key while the second column (or more columns) would have the value. These tables are used when there is no need to store the history for the data.
Transitive Functional dependency
This means that every attribute that is not the primary key in a table must depend on the primary key and only the primary key. Say that we have column A determines column B and column B determines column C. We would have transitive functional dependency as column A would determine column C and column C should be removed and placed in a separate table
Chasm
This type of issue can occur due to the join between the leaf tables as those valued would be multiplied potentially resulting in incorrect results.
C.
What does an associative entity model? a.) Associative entities model the real-world scenarios of ways to lookup data. b.) Associative entities model attributes and how they are linked to entities. c.)Associative entities model pure relationships rather than entities. d.)Associative entities model entities that are independent.
D
What is a benefit of reducing the complexity of data models? a.) Adaptable in case business rules change b.) Increased data validation c.) More room for scalability d.) Simpler application code
D.
What is a benefit of reducing the complexity of data models? a.) More room for scalability b.) Decreased redundancy c.) Adaptable in case business rules change d.) Faster application development
D. Lookup tables hold data in the form of key-value pairs. The keys themselves usually are integers or short strings. These keys are used in other tables as foreign keys. There is the benefit of storage savings, performance enhancement for some queries, data centralization, data modification flexibility, and provide quick answers for some questions related to the key-value pairs.
What is a benefit of using a lookup table? a.)They model pure relationships rather than entities. b.) They guarantee that there will be no data redundancies due to functional dependencies. c.)They resolve weak relationships. d.)They contribute to storage savings.
C The Boyce-Codd normal form has every determinant in the table to be a candidate key. This means that the candidate keys have the same characteristics as a primary key but may not be chosen as the primary key. If a table contains only a single candidate key, the 3NF and Boyce-Codd normal forms are the same.
What is a difference between the BCNF and 3NF? a.)BCNF has no multivalued dependencies. b.)BCNF has the tables broken down into as many tables as possible to avoid redundancy. c.)BCNF normal form guarantees that there will be no data redundancies due to functional dependencies. d.)There is no difference between the two.
C
What is a driving factor to reduce the complexity of data models? a.)Optimization b.)Performance c.)Business rules d.)Redundancy
C.
What is the key difference between BCNF and 3NF? a.)This normal form has the tables broken down into as many tables as possible to avoid redundancy. b.)This normal form removes repeating groups. c.)The BCNF has every determinant in a table to be a candidate key. d.)This normal form has the multivalued dependencies resolved.
B. The Boyce-Codd normal form has every determinant in the table to be a candidate key. This means that the candidate keys have the same characteristics as a primary key but may not be chosen as the primary key. If a table contains only a single candidate key, the 3NF and Boyce-Codd normal forms are the same.
What is true about BCNF? a.)This normal form has the multivalued dependencies resolved. b.)BCNF is the simplest and yet strongest among the definitions of normal forms associated with functional dependencies. c.)This normal form removes repeating groups. d.)This normal form has the tables broken down into as many tables as possible to avoid redundancy.
A.
What is true about the data in a lookup table? a.)The keys are usually integers or short string codes. b.)They transform an M:N relationship into two 1:M relationships. c.)They resolve weak relationships. d.) They guarantee that there will be no data redundancies due to functional dependencies.
A
What is true about the data in a lookup table? a.)The table holds data in the form of key-value pairs. b.)They model pure relationships rather than entities. c.)They transform an M:N relationship into two 1:M relationships. d.)They resolve weak entities.
Oval with a line connecting to the entity
What shape is an attribute in chen's notation?
D. A fan trap occurs when you have two one-to-many joins that follow a parent-child form that can create an association among entities that are not expressed in the module. A chasm trap occurs when you have two one-to-many joins that converge on a single table that is meant to show a relationship that doesn't exist in the database design.
What type of connections would exist given a fan trap? a.)One many-to-many relationship that follows a parent-child form. b.)Two one-to-many relationships that converge on a single table. c.)One many-to-many relationship that converges on a single table. d.) Two one-to-many relationships that follow a parent-child form.
D.
What type of trap can occur when you have one entity in two 1:M relationships to other entities which can create an association among other entities that is not expressed in the model? a.) Design trap b.) System trap c.) Chasm trap d.) Fan trap
A
What type of trap can occur when you have two one-to-many relationships that converge on a single table that doesn't show a relationship that is meant to exist? a.) Chasm trap b.) System trap c.) Design trap d.) Fan trap
A.
What type of trap can occur when you have two one-to-many relationships that converge on a single table that doesn't show a relationship that is meant to exist? a.) Chasm trap b.) System trap c.) Design trap d.) Fan trap
C.
What would be a reason to normalize a database? a.) To have simplified queries with less joins b.) To store pre-aggregated or derived data c.) To reduce the storage space of the data d.) To reduce the joining of tables as that takes additional input/output operation
A
What would be a reason why we may want to denormalize our database? a.) Our database may not need to have data be inserted, updated, or deleted. b.)We want to increase the number of joins between tables to optimize performance. c.) We want to increase performance for insert, updates, and deletes. d.)We want to reduce redundancy.
B.
What would be a reason why we may want to denormalize our database? a.)We want to eliminate repeating groups. b.)We may want to reduce the joining of tables as that takes additional input/output operations. c.)We want to remove multivalued dependencies. d.) We want to increase performance for insert, updates, and deletes.
C.
What would be introduced as criteria of a table being in first normal form? a.)There should be no functional dependencies. b.)Every functional dependency where X is functionally dependent on Y, X should be the super key of the table. c.)Each cell of a table should have a single value. d.)The primary key of the table should be composed of one column.
B
What would be introduced as criteria of a table being in first normal form? a.)This normal form guarantees that there will be no data redundancies due to functional dependencies. b.)There should be no repeating groups. c.)All transitive functional dependencies of a non-prime attribute of a super key should be removed. d.) No non-prime attributes dependent on the candidate key should be included.
D. The second normal form indicates that the table should already be in first normal form and the primary key of a table should be composed of a single column. No non-prime attribute is dependent on the proper subset of any candidate key of a table.
What would be introduced as criteria of a table being in second normal form? a.)All transitive functional dependencies of a non-prime attribute of a super key should be removed. b.)There should be no repeating groups. c.)This normal form guarantees that there will be no data redundancies due to functional dependencies. d.) No non-prime attributes dependent on the candidate key should be included.
A. he second normal form indicates that the table should already be in first normal form and the primary key of a table should be composed of a single column. No non-prime attribute is dependent on the proper subset of any candidate key of a table.
What would be introduced as criteria of a table being in second normal form? a.)The primary key of the table should be composed of one column. b.)Every functional dependency where X is functionally dependent on Y, X should be the super key of the table. c.)There should be no functional dependencies. d.)Each cell of a table should have a single value.
B
What would be introduced as criteria of a table being in third normal form? a.) No non-prime attributes dependent on the candidate key should be included. b.)All transitive functional dependencies of a non-prime attribute of a super key should be removed. c.)This normal form guarantees that there will be no data redundancies due to functional dependencies. d.)There should be no repeating groups.
D.
What would be introduced as criteria of a table being in third normal form? a.)Each cell of a table should have a single value. b.)Every functional dependency where X is functionally dependent on Y, X should be the super key of the table. c.)The primary key of the table should be composed of one column. d.)There should be no functional dependencies.
B.
Which is true about the primary key in an associative entity? a.)An associative entity has an artificial primary key. b.) An associative entity has its primary key columns being all foreign keys. c.) An associative entity has a natural primary key. d.) An associative entity has a synthetic key.
5th
With the ____ normal form, this is not implemented at all but conceptually, a relation between tables is in 5NF if it is in fourth normal form and does not contain any join dependencies
4th
With the _____ normal form there should not be any multivalued dependencies. This is when a single primary key can determine multiple values of two other attributes and those attributes are independent of one another.
Associative
______ entities can have many different names such as composite entity, bridge entity, or linking tables.
Entities
______ will be drawn using rectangles in the Chen notation with a line between them to signify a relationship between the ________.
functional dependency
_______ ________ means that each column in a table that is not a primary key should be determined by that primary key. This is done during the 2nd normal form.
Normalization
________ helps database designers to focus on evaluating and correcting issues with table structures to help minimize the amount of data redundancy and anomalies in our tables.
Associative entity
a solution for a many-to-many (M:N) relationship between two (or more in some cases) entities in creating multiple one-to-many (1:M) relationships
associative relationship attribute
an attribute that only exists because of the many-to-many relationship
Denormalize
here may be situations where we may even need to ________ our database to help improve performance while adding data redundancy.
2nd
this form ensure that each non-key attribute has to be functionally dependent on the primary key
