Maslov Analytics Midterm

A data dictionary is sometimes described as ____________.

"The database designer's database." This name is used because the dictionary records the design decisions about tables and their structures.

If a PROFESSOR can teach up to 4 CLASSes, the cardinality of CLASS should be expressed as which of the following?

(1, 4) The minimum class a professor can teach is 1, but can have a max of 4 classes assigned; (min, max).

A car dealership has a table with cars on sale (uniquely identified by BIN number) and a table with its sales representatives. What kind is the relationship: "representative sold car"?​

1:N A sales representative can have many sold cars.

Select one of the following table types which defines a table in 2NF that contains no transitive dependencies?

3NF The elimination of the transitive dependencies involves creating a new table converted to the third normal form (3NF).

Jaiden is in the translation of business rules to data model components. How should he label the address attribute for an entity named CLIENT?

CLIENT_ADDRESS It represents the best practice for the address attribute of an entity named CLIENT. In order to make the data included in an attribute more clearly identifiable, the name of the attribute should be prefixed with the name of the related entity.

A car wash business has a list of all its clients in a table where each client is uniquely identified by a CLIENT_ID attribute; CLIENT_ID is a primary key. Another table with all the services provided contains a SERVICE_NUM, SERVICE_TYPE, DATE, PRICE, CLIENT_ID. Which of the previous attributes in the table of services should be a foreign key?

CLIENT_ID Since each service must match an existing client CLIENT_ID is a foreign key.

M:N relationship can be changed into two 1:M relationships using a __________.

Composite Entity A composite entity uses at least the primary keys of the related entities that it connects to transform the M:N relationship to two 1:M relationships.

A _____ contains all of the attribute names and characteristics for each table in the system.

Data Dictionary The data dictionary contains the definition and characteristics of the attributes.

To eliminate repeating groups and convert a table into the first normal form (1NF)... What can you change within a table to convert the multivalued attributes into single-valued attributes?

Focus Changing the focus of the table will convert fields with multivalued attributes into single-valued attributes.

When the related table uses a composite primary key, it becomes difficult to create which key?

Foreign Key Creation of a foreign key is more difficult when the related table uses a composite primary key.

When adding new tables and deciding names for the attributes, which of the following should be avoided to lessen confusion?

Homonyms Homonyms should be avoided due to the potential similarities, in sound and spelling, to attribute names in other tables.

What component of a particular business rule can be analyzed in order to infer whether the type of relationship between two entities can be represented in notation by 1:1, 1:M, or M:N?

How many instances are on each side of the relationship. The type of relationship can be easily inferred by knowing the number of instances present in each entity.

A table of products with a foreign key of vend_code references to the table vendor. Which of the following could be an unintended effect of deleting an entry in the vendor table?

It damages the referential integrity. Foreign keys are precisely used to ensure referential integrity. By deleting an entry in the vendor tables this might cause some entries in the product table to have an invalid vend_code.

In an entity relationship diagram with objects EMPLOYEE, DEPENDENT, CUSTOMER and PRODUCT... Which would be most true about the EMPLOYEE object?

It is a strong entity EMPLOYEE can exist apart from all of its related entities; it is existence-independent.

A video game store has a table with its users. What type of data type is best for the attribute of being an adult?

Logical A logical value can only have either true or false as value, thus it is ideal to represent if a person is or not an adult.

When explaining tables such as STUDENT, CLASS, or TEACHER to management as entities, they seem to not grasp the reason for each. What other term would be most acceptable to call these entities so that leadership can understand the reason for their existence in the overall database design?

Object The terms "entity" and "object" are often used interchangeably in an ERM.

Business rules must describe the main and distinguishing features of the data as viewed by _____.

The Company Business rules must describe the main and distinguishing features of the data as viewed by the company. They must be simple to grasp and broadly distributed in order for everyone in the company to have the same understanding of the rules.

Which of the following is true of business rules?

They can serve as a communication tool between the users and designers. Business rules serve as a communication tool between end users and the database designer.

Normalization purity is often difficult to sustain in the modern database environment due to which of the following?

They contain partial and/or transitive dependencies. Normalization purity is often difficult to sustain in the modern database environment because they contain partial and/ or transitive dependencies. These dependencies make working with tables more complicated.

A STUDENT entity is related to a bridge entity called ENROLL, and that bridge entity also included an ENROLL_GRADE attribute that is related to another parent entity called CLASS. What would the STUDENT cardinality be?

(1, 1) The STUDENT can only ENROLL in the CLASS once, and receive only one grade for assigned CLASS.

If one shop manager can only be in charge of a single shop, what kind of relationship do the entities shop manager and shop have?

1:1 Since there is only one shop manager associated with a shop, the relationship is 1:1.

Business rules use simple language to establish definitions of entities, attributes, relationships, and constraints. For instance, a business rule establishing that entities can have multiple attributes, and that each available attribute may be associated with only one entity, might be stated as.... "An employee may be assigned multiple devices" and "Each device may be assigned to only one employee". Classify the relationship between EMPLOYEES and DEVICES in this example using shorthand notation.

1:M This signifies a one-to-many relationship, as described in the example. This relationship can also be described according to database convention as EMPLOYEE is assigned DEVICES.

The database of a theater has a list of plays in the PLAY table and a customer's list in the CUSTOMER table. The relationship, CUSTOMER attended a PLAY, is M:N. Which of the following relations with the BRIDGE entity should be used instead?

1:M CUSTOMER, BRIDGE and 1:M PLAY, BRIDGE The bridge entity uses the primary keys of CUSTOMER, and PLAY to compose its primary key.

In a relational table, an intersection of a row and a column represents ____________.

A single data value The row would be a single entity and the column the data value of an attribute of such an entity.

The marketing team isn't able to distinguish customers in a specific city because the CUSTOMER table is too compact. The table's attributes must be broken down to make the customers' city a groupable query. Which of the following attributes should be simplified to improve queries for the CUSTOMER table?

ADDRESS It can be further subdivided to yield additional attributes like ADDRESS_STREET, ADDRESS_CITY, and ADDRESS_STATE.

In a M:N relationship between STUDENT and CLASS at a university, it is possible that a class may start with no students and a student may start with no classes. If a database designer is using the O symbol using the Chen model, what is the designer hoping to establish between these entities?

An Optional Relationship STUDENT and CLASS do not require each other to exist, but they can be related once a student is assigned a class, eventually.

What type of data is stored at the lowest level of granularity.

Atomic Data

The movement to find new and better ways to manage large amounts of web- and sensor-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost is referred to as "_____."

Big Data It's the term that refers to a movement to innovate in the space of managing large amounts of web- and sensor-generated data.

In the early development stage, if a company suggests entities CUSTOMER and MAINTENANCE to a database designer to build a database that will track car repairs... What other entities would a designer want to add to improve the overall design based on operational needs?

CAR and PART They are the most appropriate entities that can be added to CUSTOMER and MAINTENANCE in the overall design.

If a table is in the third normal form (3NF) and is also considered to be in the Boyce-Codd normal form, what functional dependencies are taken into account as determinants?

Candidate Keys Tables are in Boyce-Codd normal form when it is in the 3NF and every determinant is a candidate key.

If processing speed was an important requirement for a company when building out a database.... What changes can a designer revise in the next database iteration if the current design has more than 95% of tables with a 1:1 relationship?

Combine some tables together Combining groups of tables that have 1:1 relationship emphasizes higher transaction speeds when there are fewer relationships to traverse when making a query. Including derived attributes in the design can also help with speed.

A database stores the data of the sales using the primary key RECEIVE_NUM in the SALE table and the products in the PRODUCT table with primary key PROD_NUM. To create the relation that represents what products were sold in a SALE a M:N relationship is necessary. What should be used to represent this in a relational model?

Composite Entity with RECEIVE_NUM + PROD_NUM The composite entity that uses RECEIVE_NUM + PROD_NUM as primary key can be used to represent the relation.

Your manager has requested a report that gives them a global view of the entire database being used as the basis for the identification and high-level description of main data objects without model-specific details. Which model or schema would you provide them?

Conceptual Model The conceptual model represents a global view of the entire database.

When configuring a relational database to show that a PROFESSOR can teach multiple CLASSes, and that a CLASS can have only one PROFESSOR, a simple line is not enough for design reviewers to understand the full relationship on the diagram. What else would make it easier for the reviewer to understand their relationship?

Connectivity Classification Connectivity classification include 1:1, 1:M, and M:N. This will help better express the relationship between entities or tables rather than just a line on a diagram.

When creating a student roster, a school cannot enroll more than 20 students per course. This is an example of what data modeling building block?

Constraint Constraints are rules or restrictions placed on data. Constraints help to ensure the integrity of data.

In an entity relationship diagram with objects EMPLOYEE, DEPENDENT, CUSTOMER and PRODUCT, which of the following would be the most appropriate example of a derived entity?

DEPENDENT Usually, this would have been listed as an attribute in EMPLOYEE but not every EMPLOYEE will have a DEPENDENT and each DEPENDENT could be linked to other EMPLOYEEs if married.

Dale's boss has tasked him with creating a simple representation of the entire employee database including items such a hire date, office contact number, and resume. What type of model should Dale construct?

Data Model A data model is a relatively simple, abstract representation of more complex real-world data models.

When using a system functionality point of view, which attribute values can be calculated when they are needed to write reports or invoices?

Derived Attribute Derived attribute values can be calculated when they are needed to write reports or invoices.

Attribute A _____ attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B.

Determines An attribute is determined by another if all of the rows in the table that agree in value for the first attribute also agree in value for the second attribute.

Recognizing the entities CLASS and STUDENT in a college ERD.... What bridged entity can a database designer add to help ensure each student is assigned their classes for specific courses?

ENROLL It can serve as an associative entity. The ENROLL table is descriptive enough in its name to only link attributes like STUDENT_CODE or COURSE_CODE as soon as a student officially enrolls.

According to the data-modeling checklist, _____ should be nouns that are familiar to business, should be short and meaningful, and should document abbreviations, synonyms, and aliases for each entity.

Entity Names Entity names are represented as nouns.

A new database designer is currently struggling with the process of identifying and documenting business rules. They are considering skipping this part of the process and moving forward. If you were their mentor, which statement would show the importance of this step to the new designer?

It improves the ability to understand the nature, role, and scope of data. Identifying and documenting business rules helps designers understand the nature, role, and scope of data used by the company, as it is essential to their ability to design a useful database model.

Comel, a student, takes four classes with her group of best friends. She sits in the front row with two friends and the others sit behind them. What type of relationship would Comel's situation be described as?

M:N Because a student can take many classes, and each class can accommodate many students. This expression describes a many-to-many (M:N) relationship.

If a college had professors who are not always assigned a class, but created classes would require one professor, then what is the relationship of the PROFESSOR table to the CLASS table?

Mandatory A mandatory participation means that one entity occurrence requires a corresponding entity occurrence in a particular relationship.

When a student's grade point average (GPA) attribute has a domain of (0,4).... The GPA can hold ____ possible values.

Many GPA values such as a 2.5, 3.5, or 3.9 are valid.

Which type of dependency illustrates how one key determines multiple values of two other attributes and those attributes are independent of each other?

Multivalued Dependency

By examining the _____ in a business rule, a database designer can identify the database as a collection of records in 1:1, 1:M, or M:N relationships

Nouns By determining whether the nouns representing the entities are plural or singular, it is possible to categorize the relationship.

When relating SEMESTER and COURSE to an associative entity such as CLASS where a SEMESTER can have many CLASSes and a COURSE can have many CLASSes, then COURSE would have what type of relationship to CLASS?

One to many A single course is made up of many classes.

As a database, _____ relates to activities that make them operate more efficiently in terms of storage and access speed.

Performance Tuning

Because a partial dependency can exist only when a table's primary key is composed of several attributes, a table whose _____________ key consists of only a single attribute is automatically in 2NF once it is in 1NF.

Primary Key

The one-to-many (1:M) relationship is easily implemented in the relational model by putting the _________of the "1" side in the table of the "many" side as a ________.

Primary Key; Foreign Key In this way one entity identified by the primary key of the "1" side is related to all the entities of the "many" side.

Recognizing the entities PROFESSOR, SCHOOL, and DEPARTMENT, what else can a database designer add that is most appropriate for professors to know which rooms they are assigned to teach in?

ROOM_NUM Entity ROOM entity which can contain attributes ROOM_NUM and even ROOM_CODE, if required.

What kind of integrity is violated in the previous database?

Referential Integrity The row with attribute1 = 2 in TABLE1 references an entity with attribute3 = 1 in TABLE2. But there is no such entity. That violates referential integrity.

A(n) _____ is bidirectional.

Relationship A relationship between entities flows in both directions.

A _______________ derives its name from the fact that a collection of multiple entries of the same type can exist for any single key attribute occurrence.

Repeating Group

A relational table must not contain a(n) _____.

Repeating Group

Which of the following is a reason to use indices (index plural) in a database?

Retrieve data ordered by a specific attribute. Aside from retrieving data faster, indices can be used for other purposes like retrieving data in an ordered way by a specific attribute.

After looking at the database design, another designer found that the primary keys were not established with a simple underline. Knowing that single-valued attribute can almost always be labeled as primary identities, which of the following attributes would be a good candidate as a primary key for a table?

SS_NUMBER A single-valued attribute is an attribute that can have only a single value. SS_NUMBER or social security number is a unique number that can be assigned to a STUDENT table, for example.

In an ERD that says DOCTOR writes PRESCRIPTION, a PATIENT receives a PRESCRIPTION, and a DRUG appears in a PRESCRIPTION, it is mostly portraying what type of relationship degree?

Ternary A ternary exists when three entities are associated.

Sally's Dog Grooming Salon is creating a database of every dog she grooms. She wants to collect specific information about the breed, height, weight, and age of each dog. What building block does "dog" represent in her data model?

The Entity An entity is a basic building block of a data model that represents a real-world person, place or thing from which data will be collected and stored in the database.

According to a standard data-modeling checklist, the non-key entities in an entity-relationship (ER) data model must be fully dependent on __________.

The Primary Key When following data-modeling checklists, non-key attributes must be fully dependent on the primary key attribute.

Normalization works through a series of stages called normal forms. For most purposes in business database design, _____ stages are as high as you need to go in the normalization process.

Three Three stages are as high as you need to go in the normalization process. Optimal relational database implementation requires that all tables be at least in the third normal form.

A database contains how many types of data?

Two A database contains two types of data: end user data (raw facts) and metadata.

When examining an associative entity with a primary key and two foreign keys, what can a database designer review as well to better understand the overall design?

Two Parent Entities Each of the two foreign keys are derived from its associated parent entity.