BUAN620 Exam Study Set

What are the three types of database integrity?

Domain - Every column must have information of the same kind. Entity - The requirement that all rows be unique (each row has a candidate key). Referential - Every value of a foreign key must match a value of an existing primary key in another relation.

What is it called when there is a relationship between attributes of a single table, in which one attribute (or group of attributes) determines the value of another attribute in the same table.

Functional Dependency For Example: (CookiePrice, BoxQty) --> BoxPrice

What is an entity whose identifier includes the identifier of another entity called?

ID-dependent entity

Referential integrity constraints make sure the values of a column in one table are valid based on the values in another table. a. True b. False

True

Surrogate key values have no meaning to users and are usually hidden on forms, queries, and reports. (True or False)

True

T/F A weak entity can be NON-ID Dependent.

True

True or False: The primary keys of supertype and subtype are always identical.

True

Modification problems are also called _ _ _ _ _ _ _ _ _.

anomalies

When you join multiple tables, you end up with: a. Normalized Table b. Denormalized Table c. Large Table d. Super Table

b. Denormalized Table

Which characteristic defines a relation in 2NF? a. No functional dependencies (FDs) exist b. No multivalued dependencies (MVDs) exist c. No partial dependency d. No partial MVDs exist

c. No partial dependency Also note that to be in 2NF it must also be in 1NF

What is a subtype entity?

it is a special case of another entity called its supertype. For example, universities may be classified as public and private universities. UNIVERSITIES is the supertype, and PUBLIC and PRIVATE are the subtypes

How are identifying relationships drawn in a relationship?

it is shown as as a solid line connecting two entities.

List the three modification issues?

o Update: Inconsistent update can cause data consistency issues o Deletion: Remove facts about 2 or more themes leading to data loss o Insertion: Inability to add data to a database due to the absence of other data

When do you use a solid line in an ER diagram?

to show relationship between a strong and ID dependent weak entity

What is a composite key?

• A key that contains two or more attributes. • For a key to be unique, it often becomes a composite key. Example: the Name key contains FirstName and LastName

What is the normal form that requires no transitive dependencies?

3NF

What is a recursive relationship?

A relationship that has only one entity that is related to itself. It is also called unary relationship. An example is a User entity containing data about different user levels - Analyst, Supervisor, Manager etc. All of these roles are part of the universe of users.

What is a relation?

A two-dimensional array containing single-value entries and no duplicate rows. Values for a given entity are in rows, and their attributes are in columns. The meaning of the columns is the same in every row and the order of the rows and columns is immaterial.

What is a surrogate key?

A unique, numeric value that is added to a relation to serve as the primary key. For example, adding a Violation ID to a table when there are no proper candidate keys.

What is an ID-Dependent weak entity?

A weak entity that cannot exist without its parent entity and whose identifier includes the identifier of another entity.

What is an ID-dependent entity?

An ID-Dependent entity is a weak entity (i.e. existence depends on the presence of another entity), whose identifier contains the identifier of the parent.

What is BCNF?

BCNF stands for Boyce-Codd Normal Form. A table in BCNF should meet the requirements of 3NF and have no overlapping candidate keys. BCNF is when every determinant is a candidate key. In a read-only database, BCNF may not be desirable.

A relation where every determinant is a candidate key is in what form?

Boyce-Codd Normal Form (BCNF)

Given the functional dependency F → (G, H), F is a(n) ________. A) independent variable B) dependent variable C) Discriminator D) Determinant

Determinant

NumberOfBoxes ---------> CookieCost The term of the left, NumberOfBoxes, is called the:

Determinant

Surrogate keys are usually removed during normalization of databases

False

T/F All tables will only have 1 candidate key

False

Many-to-Many are the most common types of binary relationships in entity relationship modeling: T/F

False (its one-to-many)

In relations, the order of the rows is important, because the order of rows carries information. T or F

False, the order of the rows do should not matter in a normalized relational database

T or F: A foreign key must be unique to a specific row in a relational table.

False. A foreign key is nonunique.

A One to Many relationship is a type of a ternary relationship. (T/F)

False. A one-to-many (1:N) relationship is a type of binary relationships.

T or F: The terms Row and Field are interchangeable.

False. Rows are records and columns are fields.

What are the other two terms used to describe a table?

File and Relation

In a relation (A) Ordering of rows is immaterial (B) No two rows are identical (C) (A) and (B) both are true (D) None of These

Option (C)

Which of the following is a group of one or more attributes that uniquely identifies a row? 1.Key 2.Determinant 3.Tuple 4.Relation

Option 1 : Key

What is NOT a characteristic of a relation? A. Each column has a unique name B. The order of the column is important C. Columns contain data about attributes of the entity D. Cells of the table hold a single value

B. The order of the column is important

In general, a ________ exists when the value of one or more attributes determines the value of another attribute. a) Functional Relationship b) Database relation c) Functional dependency d) None

(c) Functional Dependency

When referring to binary relationships, N:M stands for... (a) one-to-one (b) one-to-many (c) many-to-many

(c) many-to-many ; Note: It is notated N:M instead of N:N because, each entity can have different cardinalities.

List the steps for Normalization of a database

1. Identify all the candidate keys 2. Identify all the functional dependencies 3. If there is a functional dependency that has a determinant that is not a candidate key: A. Move the columns of that functional dependency into a new relation B. Make the determinant of that functional dependency the primary key of the new relation C. Leave a copy of the determinant as a foreign key in the original relation D. Create a referential integrity constraint between the original relation and the new relation 4. Repeat step 3 as many times as needed until every determinant of every relation is a candidate key.

What are the four components of a database system?

1.Users 2.Database Application 3.DBMS 4.Database

What is the main difference between a candidate key and a primary key?

A Candidate Key is a unique key or a combination of unique keys that identifies a record in a table. While a primary key is a unique key that identifies a record in a table, and can be the only primary key in the table. There will only be one primary key in a table whereas you can have many candidate keys. Candidate keys help you select your primary key.

What is a database schema?

A database schema is the design on which a database and its associated applications are built.

What is a well-formed relation?

A relation, which every determinant must be a candidate key. ( in case it can't be well-form with current attribute, it should be broken down to one ore more relation.)

What is a referential integrity constraint?

A relationship constraint on foreign key values. Referential integrity constraint specifies that the values of a foreign key must be a subset of the values of the primary key to which it refers. For example, suppose there are two tables, Customer and Enrollment. The Enrollment table's Customer ID attribute should be the same as the attribute in the Customer table which is enforced through referential integrity. ADD: for example if you wanted to perform a query to identify a customer and edit any information, referential integrity ensures that the data are updated throughout the database via relationships.

What is a strong entity?

An entity that is complete by itself and is not dependent on any other entity type. It possesses a primary key to uniquely identify each record instance in the entity set. In other words, a strong entity can exist in the database on its own without the existence of another type of entity existing in that database.

Which one is NOT the advantages of normalization? (Choose all that apply) a) Eliminate modification anomalies b) Simplify SQL required for subqueries and joins c) Save file space d) Faster applications

B & D

What is not true about a subtype entity? A) Each subtype is ID-independent on the supertype. B) We use a dashed line to present an ID-dependent subtype entity. C) An exclusive subtype is also referred to as disjoint. D) A discriminator is used to determine which subtype is appropriate for a given instance.

B) We use a dashed line to present an ID-dependent subtype entity.

What is not true about ID-dependent entity? A) The minimum cardinality from the ID-dependent entity to the parent is always one. B) Some entities that are weak are not ID-dependent. C) An ID-dependent entity must be created and then linked to the parent entity. D) The relationship between an ID-dependent entity and its parent is called an identifying relationship.

C) An ID-dependent entity can be created before linked to the parent entity.

Which of the following is false about a table that is a relation? A. Each column has a unique name B. No two rows may be identical C. Cells of the table can hold multiple values D. The order of the column is unimportant.

C. Cells of the table can hold multiple values

An identifier can be a _________ A. Entity Instance B. Relationship C. Composite D. Data Model

C. Composite

A missing value is often referred to as _____ A. Error Value B. Inconsistent Value C. Null Value D. Data Value

C. Null Value

The primary key is selected from ______

Candidate Key

A combination of two or more columns used to identify particular rows in a relation is a _______________

Composite candidate key

What issues can normalization of a database prevent? A) Update anomalies B) Deletion anomalies C) Insert anomalies D) All of the above

D) All of the above

What is metadata?

Data describing the structure of the database, provides information about tables, columns, data types, table relationships, and table constraints.

A ________ _________ is a representation of the content, relationships, and constraints of the data needed to support the system requirements.

Data model

What is a degree of a relationship?

Degree is the number of entity classes involved in a relationship.

What are the four primary elements of an E-R model?

Entities - Objects that can store data about themselves. Attributes - Characteristics of entities. Relationships - How multiple entities are associated with each other, as defined by their cardinality. Cardinality-Number of occurrences or instances of one entity that can be associated with another entity in a relationship.

What is an Exclusive Subtype?

Entities are said to be exclusive when a Supertype entity relates to maximum one Subtype entity. For example, in a relationship where the supertype entity instance is STUDENT and the subtype entities are SENIOR, JUNIOR, SOPHOMORE, and FRESHMAN, the subtypes are said to be exclusive. This is because a student can only be related to one subtype at the time. A student cannot be a senior and junior at the same time.

T or F. A weak entity can exist in the database without another type of entity existing in that database.

F. A weak entity CAN'T exist in the database without another type of entity existing in that database.

The term relation is ALWAYS interchangeable with the term table. (T/F)

FALSE, for example, a table may have duplicate rows, whereas a relation may not

TRUE of FALSE: 1NF encompasses 3NF.

FALSE: 3NF encompasses 1NF- that is, if something is in 3NF it's also in 1NF, but not the other way around.

TRUE or FALSE: A composite key can be made up of only one column

FALSE: Composite keys are keys made of more than one column.

A Null value is presented as zero in a field. a. True b. False

False

A recursive relationship is used to connect subtype entities to their supertypes. (True/False)

False

All tables are relations but not all relations are tables. True or False

False

What is a candidate key?

It is a minimal key (one or more columns) that uniquely identifies each row in a relation. It can become the primary key of the relation and cannot be null.

Which of the following is false about Surrogate keys Numeric Often hidden on forms Long Ideal primary keys

Long

What is the term for when a value has not been provided?

NULL value

A key that potentially identifies more than one row

Non-unique key

______ is the process of breaking a table or relation with more than one theme into a set of tables such that each one has only one theme

Normalization

What is the purpose of the Normalization process?

Normalization is used to eliminate or reduce redundancy in database tables. Data is separated into different tables and primary/candidate keys help avoid modification problems and anomalies.

What is the determinant in this example? NumberOfHoursStudying --> Grade

NumberOfHoursStudying

In general, each relation should have ________. a. one and only one theme. b.one to many theme c.Many to one theme. d.Many to many theme. e. All of the above.

One and only one theme.

The parent to child relationship is also referred to as a _____ to _____ relationship.

One, Many

________ is a relationship in which a single entity instance of one type is related to a single entity instance of another type.

One-to-one

What is the difference between personal and enterprise database systems?

Personal: - Simple in design, have only 1 application, supports only 1 user, involves only 1 computer, only few tables. Example: Microsoft Access Enterprise Database System: - Have multiple applications, have many tables, complete design, Involves many users and computers. Ex: Oracle, MySQL, Microsoft SQL Server

What are two other names for a row?

Record and Tuple

What is a Relationship degree?

Relationship degree defines the number of entity classes participating in the relationship: Degree 2 is a binary relationship. Degree 3 is a ternary relationship

An entity that is "not" weak is called a _____Entity.

Strong

A candidate key is ALWAYS a unique key. (T/F)

TRUE

TRUE or FALSE: All relations are tables, but not all tables are relations.

TRUE

True or False? A column contains data that represent an attribute of an entity.

TRUE. For example, in an EMPLOYEE relation each row would contain data about a particular employee. Each column would contain data that represented an attribute of that employee, such as, LastName, Phone, or EmailAddress.

What is the key difference between a table and a relation?

Table - Logical representation, Foreign keys needs to mention. Relation - Conceptual - foreign keys are not required. In a relation, no two rows may be identical. Rows must contain data about an entity. Columns contain data about attributes of the entities. All entries in a column are of the same kind. Each column is unique. Cells of the table hold a single value. The order of the columns and rows are unimportant.

What is the minimum cardinality?

The minimum number of entity instances that must participate in a relationship instance. [0] is optional, [1] is mandatory. Ex: A customer has 0-Many sales orders meanwhile, sales orders MUST have a minimum of 1 customer

What is the primary key of a subtype?

The primary key of the respective supertype. said another way: Foreign key that references its supertype relation

What is an identifying relationship?

The relationship between a strong and weak entity is termed an identifying relationship if the weak entity is ID-dependent

Why are surrogate keys hidden on forms, query results, and reports?

The values of the surrogate primary key will have no inherent meaning to the user.

A referential integrity constraint limits the values of a foreign key. (T/F)

True

A relation in 1NF is automatically in 2NF where there are no composite candidate keys. True or False?

True

A ________ entity is an entity that cannot exist in the database without the existence of another entity.

Weak. For example a ROOM can only exist in a BUILDING.

When should the associative entity be used?

Whenever a pure N:M relationship cannot properly hold attributes that are describing aspects of the relationship between two entities.

How is a nonidentifying relationship drawn in a relationship?

With a dashed line( no ID-dependent)

When a primary key is only possible with a large number of attributes, the best thing to do would be to: a) Form a surrogate key b) Keep the "original" primary key c) Combine other relations in the database together in order to increase the possible combination of keys d) Form an alternative key

a) Form a surrogate key

A relational database consists of a collection of a) Tables b) Fields c) Records d) Keys

a) Tables

Which of the following is an example of an insertion problem? a. Suppliers and Orders from Suppliers are stored in the same table, an order from a supplier that shows up once in the table is deleted. b. Students and Advisors are stored in the same table, the advisor that previously advised student with the last name starting with A though M, now advises students with a last name starting with N through Z. c. Students and Courses are stored in the same table, a new course is entered into the table, but no student has been allowed to sign up for it yet. d. A new student is inserted into a Students Table e. All of the above

c. Students and Courses are stored in the same table, a new course is entered into the table, but no student has been allowed to sign up for it yet.

Key to represent relationship between tables is called a. primary key b. secondary key c. foreign key d. none of the above

c. foreign key

When the values in one or more attributes being used as a foreign key must exist in another set of one or more attributes in another table, we have created a(n) ........ a. transitive dependency b. insertion anomaly c. referential integrity constraint d. normal form

c. referential integrity constraint

____________ is a primary key from one table placed into another table.

foreign key

A __________ exists when the value of one or more attributes determines the value of another attribute.

functional dependency