BUAN620 Exam Study Set

What is a dimensional database?

Dimensional database is designed for efficient data queries and analysis. It is a database structure used for data warehouses. Uses and stores historical and current data.

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

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 are the functions of a DBMS?

• Create database • Create tables • Create supporting structures • Read database data • Modify database data • Maintain database structure • Enforce rules & referential integrity • Control concurrency, no interference between user work • Provide security • Perform backup & recovery

What is a SQL View?

•It is a virtual table created by DMBS-stored SELECT statement that can combine access to data in multiple tables and even in other views. •We can run a query against a view in the same way that you run a query against a table

What is ACID?

♦Atomic: Transactions that are treated by the database as a single logical unit of work. ♦Consistent: no other transactions are permitted on the records until the current statement or transaction finishes. ♦Isolation: within multi-user environments, different transactions may be operating on the same data. ♦Durable: all committed transactions are permanent.

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

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.

Which of the following is an example of a personal database system? 1. Microsoft Access 2. Oracle Corporation's Oracle Database 3. Microsoft SQL Server 4. IBM's DB2

1. Microsoft Access

What are the four main types of NoSQL?

1. key-value - Dynamo/MemcacheDB 2. Document- Couchbase and MongoDB 3. Column Family - Appache Cassandra/Hbase 4. Graph - Neo4J/AllegroGraph

What are the four components of a database system?

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

What are the rules of representing recursive relationships?

1:1 and 1:N relationships are saved using foreign keys. M:N relationships are saved by creating an intersecting relation.

ID-Dependent relationships are always? (1:1, 1:N, N:M)

1:N

What is the abbreviated way to write a one-to-many relationship?

1:N

Relationships of degree ___ are most common

2

What is the normal form that requires no transitive dependencies?

3NF

A relationship drawn with a dashed line is used between strong entities and is called a ____________ because there are _________________ entities in the relationship: 1) identifying relationship - No ID-dependent 2)identifying relationship - ID-dependent 3)Non-identifying relationship - ID-dependent 4) Non-identifying relationship- No ID-dependent

4) Non-identifying relationship- No ID-dependent

When a relation is in 3NF it means: A) It is also in 1NF and 2NF B) It is also in 4NF C) It is also in BCNF D) Each case is different

A

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 SQL operation is used to display data obtained from more than one table? (There are two correct answers) A. Subquery B. JOIN C. GROUP BY D. IN

A and B: Subqueries or JOINs

What is a key?

A column/attribute that is used to identify a row

What is a transitive Dependency?

A non-key attribute Determined by another-non key attribute.

What is the difference between a partial dependency and a transitive dependency?

A partial dependency can occur when there is a composite candidate key. It occurs when a non-key attribute is dependent on only PART of the composite candidate key. A transitive key occurs when a non-key attribute is dependent on another non-key attribute.

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 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 Data Manipulation Language (DML) ?

A sub-language of SQL. Statements used to query, insert, modify, and delete data.

Define inclusive subtypes.

A supertype instance can relate to one or more subtypes.

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.

If attribute A determines both attributes B and C, then it is also true that: A. A → B. B. B → A. C. C → A. D. (B,C) → A.

A. A → B.

When does a unary relationship occur? A. When an entity type has a relationship to itself B. When an entity has multiple relationships C. When there is more than one relationship D. When there isn't a recursive relationship available

A. When an entity type has a relationship to itself Note: A recursive relationship is also called a unary relationship

Which SQL keyword is used to change the structure, properties or constraints of a table? A) CHANGE B) CREATE C) SELECT D) ALTER E) UPDATE

ALTER

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.

A discriminator is

An attribute that determines which subtype is appropriate

What is an entity?

An entity is something of importance to a user that needs to be represented in a database. An entity represents one theme or topic.

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.

In a relational database design, all relationships are expressed by creating a/an ________. A) primary key B) foreign key C) supertype D) alternate key

B

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

An SQL virtual table is called a ____________. a) vtable b) view c) index d) triggers

B View

Which one of these is incorrect for the acronym ACID? A) Atomic B) Concurrent C) Isolated D) Durable

B) Concurrent

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.

In OLAP, the characteristic of a measure is called a ________. A) level B) dimension C) slice D) member

B) dimension

The SQL functions SUM, AVG, MIN, MAX, and COUNT, are called: a. arithmetic b. aggregate c. compound d. mathematical

B, Aggregate

SQL comments are enclosed in the following symbols: A. $* and *$ B. /* and */ C. ( and ) D. %* and *%

B. /* and */

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

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.

What keyword did SQL provide to allow users to specify a minimum and maximum value on one line?

BETWEEN

How are BI systems different from transaction processing systems?

BI systems do not record and process orders; instead, BI systems analyze, plan, control and help make decisions.

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

Boyce-Codd Normal Form (BCNF)

We can creating a surrogate key for the following reasons, EXCEPT: A) There are no candidate keys, except the whole row B) Facilitate writing queries C) Increase the number of columns is beneficial to the database

C

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 XML? A. XML documents can automatically be generated from database data and vice versa. B. XML is used for describing, representing and materializing database views. C. XML syntax is similar to Java D. Standardized but allows for extension by developers.

C.

If attributes A and B determine attribute C, then it is also true that: A. A → C. B. B → C. C. (A,B) is a composite determinant. D. C is a determinant.

C. (A,B) is a composite determinant

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 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

Which keyword may be added to the SELECT statement to inhibit duplicate rows from displaying?

DISTINCT

Which keyword should be used to remove duplicate rows from displaying?

DISTINCT

To erase a table structure completely with its object and data, what statement would be appropriate?

DROP Statement:

A collection of data that is smaller than that in the data warehouse and that addresses a particular component or functional area of the business is called _____

Data Mart

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.

Why can primary keys never be null?

Each row must have a unique identifier

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.

What does E.R in E.R Model stand for?

Entity relationship

A relation is considered normalized when:

Every determinant is a candidate key

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.

4. T/F: All relations are in 3rd normal form.

F: All relations are in 1st normal form.

Maximum cardinalities determine whether a foreign key is required or not required in the database design. (True/False)

FALSE

In Data Modeling, N:M relationships must always include an intersection entity.

FALSE (in the data modeling stage). But when the model is turned into a real database, all N:M relationships are represented with an intersection table.

If you want to delete the data in one single cell of a table, you can use 'delete' syntax.

False

In the database entity-relationship model, the maximum and minimum cardinalities of a relationship can never be the same. (T/F)

False

Left Join is different from Left Outer Join

False

Null values are never acceptable

False

Recursive relations must be eliminated by creating separate tables. (True/False)

False

SQL DROP TABLE statement will drop the table but not all of its data. True or False

False

SQL wildcard character "%" indicates a single, unspecified character in a specific location in an SQL query. (T/F)

False

Surrogate keys are usually removed during normalization of databases

False

T/F All tables will only have 1 candidate key

False

True of False? If (A,B) -> C then A -> C and B -> C

False

True or False: A maximum of only two subqueries may be used in an SQL statement.

False

True or False: In a 1:N relationship the Primary Key can be place into either table as a Foreign Key.

False

True or False: You can use built-in aggregate function in SQL WHERE.

False

True or false: Fact tables are never normalized.

False

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

False (its one-to-many)

True or False, in a database model, an entity can use the ID of another entity without being a weak entity?

False, All entities that have a identifier of another entity as part of their identifier are ID-dependent, and all ID-dependent entities are weak entities.

True or False, Relational database models are the best method for handling big data (1,000,000,000+ Rows)?

False, NoSQL/NoRelational has started to appear as an alternative to relational models for big data.

T/F: A BI System is used to support primary business activities (i.e. sales, purchasing, and inventory-control system).

False, Operational Systems would be used.

True or False: A relationship with 2 degrees is called a ternary relationship?

False, a relationship with 2 degrees is called a binary relationship.

True or False: In the Where clause, text and integers need quotation marks around them?

False, only text requires quotation marks

True or False, A surrogate key is a primary key from one table placed into another table.

False, that would be a foreign key.

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

Single quote is not used in one of the following data types: 1- Char 2- VarChar 3-Date 4-Int

Int is the correct answer

What does referential integrity constraints do ?

It ensures that values of a column in one table are valid based on the values in another table.

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.

What is the identifier of a subtype entity?

It is always the same as the identifier of the supertype entity.

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

Long

Which of the following is a SQL Aggregate function? MIN GROUP BY ORDER BY SELECT

MIN

________ 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 OLAP?

Online Analytical Processing is a technique for analyzing data values, called measures, against characteristics associated with those data values, called dimensions.

----O-|- What does this mean in crow's foot notation?

Optional - One

When do we use a solid line or a dashed line?

Solid line is used in a relationship between a strong and an ID-Dependent entity Dashed line is used in a relationship between a strong and a Non-ID-Dependent entity. It is also used between strong entities.

A dimensional database uses what type of schema?

Star schema

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

Strong

A result of a query that is fed to another query is called

Subquery

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

TRUE

T/F The result of a SQL SELECT statement is always a table

TRUE

TRUE or FALSE All primary keys could also be considered candidate keys?

TRUE

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

TRUE

Null values are allowed in a relational database (TRUE/FALSE).

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.

How are M:N relationships represented with foreign keys and intersection tables?

The intersection table will receive the primary keys of the two parent table and make these keys (foreign keys) as it primary keys.

How is 1:1 relationship represented with foreign key?

The key from one relation is placed in the other as a foreign key. It does not matter which table receives the foreign key. . the specified foreign key must be constrained as UNIQUE.

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 the trade off between a Normalized Data Warehouse, and a Dimensional Warehouse?

The trade off is the efficiency of storing data versus extracting data. The normalized has an easier time storing data, but will require join functions and complex code to extract it. The dimensional data warehouse will have an easy time extracting data, but will require many types of data to be stored in separate tables.

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.

How many dimensions can a dimensional database have?

Theoretically, infinite. In practice, check the specs for your DBMS.

What are the disadvantages of NoSQL databases?

There is no definite schema, they are more prone to data inconsistencies as they generally do not adhere to ACID standards, they don't have across platform standards, and they are often less secure.

Why are diagrams important when designing a database?

They allow you to visualize the database setup before you actually build it, making it easier to see possible problems. It would be far more difficult and expensive to fix potential problems with the setup after the database is built and in use.

What is the main purpose of a database?

To store information about things

(T/F) In 1:1 relationships either table can have the foreign key

True

(T/F) The primary key and foreign key of a subtype is the primary key of the supertype.

True

(T/F) Varchar should be used when the sizes of the data entries vary considerably.

True

(T/F): You can join multiple tables in the same SQL statement by adding JOIN ON as many times as necessary.

True

(True/False) An intersection table is a child table that has two 1:N relationships that have been manufactured to replace a single N:M relationship in the data model.

True

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 table is considered to be in 1NF if it meets all the requirements of being a relation. (T/F)

True

An intersection table is always ID-dependent on both of its parent tables. T/F

True

Dimensional databases are used to track historical data. (T/F)

True

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

True

T /F If a subtype is exclusive, the supertype relates to at most one subtype.

True

T/F In LEFT and RIGHT JOIN operations, all the rows from at least one table are always included in the result set.

True

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

True

T/F If you do not specify the type of JOIN in your SQL command (i.e. INNER, LEFT, RIGHT, FULL), you will perform an INNER JOIN by default.

True

T/F: A recursive relationship can be N:M.

True

T/F: Recursive relationships adhere to the same rules as binary relationships

True

T/F: The condition in WHERE clauses can refer to a set of values by using the IN operator

True

T/F: When using a DELETE function the table structure remains intact.

True

TRUE or FALSE: A VarChar is a character data type that holds a variable-length amount of characters.

True

The Parent entity is the entity on the ONE side of the relationship and the Child entity is the entity on the MANY side of the relationship. True or False

True

The order of the COLUMNS in your results follows the order you listed them following your SELECT keyword. True/False?

True

The outputs that an OLAP report calculates are called measures. (T/F)

True

True or False, Minimum Cardinality is usually zero or one.

True

How is a nonidentifying relationship drawn in a relationship?

With a dashed line( no ID-dependent)

What is XML's primary function?

XML's primary function is to create formats for data that is used to encode information for documentation, database records, and transactions.

Can a ID-dependent entity be a weak entity?

Yes, an ID-dependent is a weak entity whose identifier includes the identifier of another entity.

Can ORDER BY clause sort data by multiple columns?

Yes, it can.

Is a table that meets all the conditions for a relation and has a non-composite primary key in 2NF?

Yes. 2NF requires all non-key attributes to be determined by the entire primary key. When the primary key is not a composite, this is automatic.

Transitive dependencies can be found in which of the following normal forms? (Select all that apply) a) 1st normal form b) 2nd normal form c) 3rd normal form d) Boyce-Codd normal form

a) 1st normal form b) 2nd normal form

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

anomalies

The ____________________database uses a star schema which resembles a star with the fact table in the center and dimension tables radiating out from the center. The fact table is always fully normalized, but the dimension table may be non-normalized.

answer. Dimensional

The Database Management System is the intermediary between __________ and ___________

applications; the database

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

SQL SELECT clause specifies which ___ are to be listed in the query results.

columns

Which one is NOT an example of a DBMS? a. Microsoft Access b. Oracle Corporation's MySQL c. Microsoft SQL Server d. Microsoft Visio 2016

d. Microsoft Visio 2016

Which SQL command sorts the results of a query? Select one: a. SORT IN (optionally using ASC or DESC) b. SORT BY (optionally using NUM or TEXT) c. ORDER IN (optionally using NUM or TEXT) d. ORDER BY (optionally using ASC or DESC)

d. ORDER BY (optionally using ASC or DESC)

SELECT * means: a. show all column values for rows that do not match specific criteria b. delete all column values for rows that match specific criteria c. delete all row values for rows that match specific criteria d. show all column values for rows that match specific criteria

d. show all column values for rows that match specific criteria

A data warehouse database differs from an operational database because A data warehouse data are often denormalized B data warehouse databases do not have metadata C data warehouse databases have metadata D None of the above

data warehouse data are often denormalized

____________ 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

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

_______________ are associations among entities. The E-R model explicitly defines these.

relationships

SQL WHERE clause specifies which ___ are to be listed in the query results.

rows

SQL FROM clause specifies which ___ are to be used in the query.

tables

The conditions required for a table to be in third-normal form are...

the table is in second normal form and all of its columns are not transitively dependent on the primary key.

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

to show relationship between a strong and ID dependent weak entity

How is a "one-and-only-one" end of a relationship drawn out?

two hashmarks across the relationship line

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.

What are the Normalization Principles?

- To be a well-formed relation, every determinant must be a candidate key. - any relation that is not well formed should be broken into two or more well-formed relations.

What is Big Data?

The rapidly expanding amount of data being stored and used in enterprise information systems

Example of a One-to-Many relationships?

-A department has many employees -Each employee is assigned to one department

What is the star schema?

It is used for dimensional databases and it is named a star schema because the figure is in the shape of a star.

What is the difference between Char and VarChar?

Char: Fixed length, Saves processing power. Used for fields where we always have text of same length. VarChar: Variable-length character data type, Saves storage space. Used for fields having vast difference of text length.

Define each of these: column, super column, column families, super column families

-Column - smallest unit of storage or a RDMBS table cell. Consists of column name, column value, timestamp. -Super columns - Sets of columns. -Column families - groups of Super columns + Column -Super column families - uses a combination of columns and super column or just super columns.

What are characteristics of operation database?

-used for structured transaction data processing -current data are used - data are inserted, updated and deleted by users

SQL statements are divided into five categories, what are they?

1- Data Definition Language (DDL) : statements used for creating tables, relationships and other structures. 2- Data Manipulation Language (DML) : statements used for querying, inserting modifying and deleting. 3- SQL/ Presistent Stored Modules (SQL/PSM): statements, which extend SQL by adding procedural programming capabilities. 4- Transaction Control Language (TCL) : statements used to mark transaction boundaries. 5- Data Control Language (DCL): statements used to grant database permissions to users and groups.

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

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

In 1 : N relationship, the entity that is on the many side of the relationship is called ________ entity.

Child

In a 1:N relationship, the foreign key is always set up such that the primary key of the _________ entity becomes the foreign key in the _________ entity. A) parent, child B) child, parent C) primary, secondary D) secondary, primary

A

What is the difference between a dashed and a solid line in an E-R Diagram?

A dashed line is used to identify a non-identifying relationship and a solid line is used to represent the relationship between ID-dependent entity and its parent.

What is the main difference between data marts and data warehouses?

A data mart is smaller than a data warehouse and it addresses a particular component or functional area of the business

What is a database schema?

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

What are some advantages and disadvantages of Normalization?

Advantages: Eliminate modification anomalies, Reduce duplicated data (Eliminate data integrity problems, Save file space), Single table queries will run faster Disadvantages: More complicated SQL required for multi-table sub-queries and joins, Extra work for DBMS can mean slower applications

What is an OLAP report?

An OLAP report is something OLAP systems produce, also called OLAP cube. This is in reference to the dimensional data model and some OLAP products show OLAP displays using three axes, like a geometric cube. OLAP reports are dynamic, the format of these reports can be changed by the viewer, hence the term online in the name Online Analytical processing

What is the difference between inner and outer joins?

An inner join only displays data from the rows that match based on join conditions. An outer join shows all rows of a table in the relation whether or not they match the join condition.

The SQL keyword(s) ________ is used with wildcards. A. LIKE only B. IN only C. NOT IN only D. IN and NOT IN

Ans. Option A

Which of the following functions can be used on both numeric as well as non-numeric data? A - COUNT B - AVG C - STDDEV D - VARIANCE

Ans. Option A

The SQL WHERE clause: A. limits the column data that are returned. B. limits the row data are returned. C. Both A and B are correct. D. Neither A nor B are correct.

Ans. option B

When inserting data, you can omit the column list on the situation that: A. Providing data for all of the columns B. Data is in the same order as the columns in the table C. Have no surrogate keys D. All of them

Answer: D

(T/F) Char should be used when the sizes of the data entries vary considerably.

Answer: False, Char should be used when fields always have text of same length.

Data is read from operational databases using the ETL system? True or False?

Answer: True

A discriminator is used for which of the following relationships? a. association b. super/subtypes c. archetype/instance d. multi-valued

Answer: b, super/subtypes

While similar, what are key differences between an Associative Entity and an Intersection Table ? (check all that apply): a) An Intersection Table is created in the data model stage b) An Intersection Table is made out of necessity when transition from a model to a DBMS c) An Associative Entity is created from N:M strong relationships d) An Associative Table is ID-Dependent e) Intersection Tables cannot have non-key data, where Associative Entities can

Answer: correct selections include: b); d); e)

When an intersection table has columns beyond those of the primary key, what is this relationship called?

Association Relationship

What are characteristics of entities?

Attributes

How is 1:N relationships represented with foreign keys between strong entities?

By Placing the primary key of the parent in the child as a foreign key.

Which keyword is not used to modify data in SQL? A. UPDATE B. INSERT C. JOIN D. DELETE

C. JOIN

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

C. Null Value

Microsoft Access is an example of a ----------------- A. Enterprise DBMS B. Cloud Based DBMS C. Personal DBMS D. Excel Formatted DBMS

C. Personal DBMS

What would the following query do: SELECT S.Major, AVG(S.GPA) AS AvgGPA FROM Student S WHERE S.Major LIKE 'B%' GROUP BY S.Major HAVING AVG(S.GPA)>3.5; A. Show Majors with an average GPA above 3.5 B. Show Majors with an average GPA above 3.5 and the Major name ends in a B C. Show Majors with an average GPA above 3.5 and the Major name starts with a B D. Show Majors that start and end with a B and with an average GPA above 3.5 E. None of the above

C. Show Majors with an average GPA above 3.5 and the Major name starts with a B

Structured Data

Can be displayed in rows, columns, and relational databases, includes numbers, dates and strings, requires less storage, and easier to manage and protect with legacy solutions.

The primary key is selected from ______

Candidate Key

Unstructured Data

Cannot be displayed in rows, columns and relational databases, includes images, audio, video, word processing files, emails, and spreadsheets, requires more storage, and is more difficult to manager and protect with legacy solutions.

What is cardinality?

Cardinality represents the count of entity instances allowed in a relationship between two tables.

What is the difference between char and varchar? And Which One is Faster?

Char is a fixed-length data type. The storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column. Char runs faster than varchar.

Weak ID dependent entity always have ______________ primary key.

Composite

How is a dimensional database structured?

Dimensional databases uses a star schema. It resembles a star, has a fact table at the center of the star and dimension tables radiating out from the center.

What is a conformed dimension?

Dimensions are conformed when they are either exactly the same (including keys) or one is a perfect subset of the other. In other words, the dimension table links to two or more fact tables.

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.

Which of the following is NOT a DML Command? A. UPDATE B. DELETE C. MERGE D. INSERT E. All of the Above are DML

E. All of the Above are DML

The ALTER DDL Command can NOT do which of the following? A. Add a Foreign Key Constraint to a Table B. Add a Primary Key Constraint to a Table C. Add another Attribute to a Table D. Change an Attributes Data Type E. Delete a table

E. Delete a table

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.

What is at the center of the Star Schema?

Fact Table

What is placed at the center of star or snowflake schema?

Fact Table

(T/F) DISTINCT keyword is used if user wishes to see duplicate columns.

False

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

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.

(T/F) The result of a SELECT query is a relation

False. The result of a SELECT query is always a table, but it is not necessarily a relation because there could be duplicate rows in the results.

T or F? The value of a surrogate key may change.

False. The value of surrogate keys never change

Entities cannot be related to themselves (T/F)

False. This is called a recursive relationship.

T/F: Subqueries can display data from more than 1 table

False: They can only display data from the top level table.

True or False: The foreign key must have the same column name as the primary key.

False: only requirement is that they have the same set of values

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

File and Relation

Where does a foreign key in a 1:N relationship go?

Foreign Key always goes on the many side.

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

When using calculations in combination of regular attributes you must use the _______ clause or you will get an error

Group By

WHERE is to individual rows as HAVING is to _____________

Groups/aggregated data

What's the fun phrase to remember the difference between normal forms?

I swear to construct my tables so that all non-key columns are dependent on - the key, [1NF] - the whole key, [2NF] - and nothing but the key [This is 3NF and BCNF] so help me Codd!

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

ID-dependent entity

what is an equivalent SQL keyword used to replace multiple OR conditions in a SQL Statement? 1. IN 2. NOT IN 3. BETWEEN

IN

What is the ultimate goal of normalization in a relation?

If a relation is normalized, rows can be inserted, deleted, or modified without creating update anomalies.

What is a Full Outer Join?

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

What is a transitive dependency?

In a relation with at least three attributes, for example R(A,B,C), the situation in which A-->B, B-->C, and as a result, A--> C is a transitive dependency. In this case a non-prime attribute (C) is dependent on another non-prime attribute (B), which is dependent on the candidate key.

XML or Extensible Markup Language provides

It provides a standardized yet customizable way to describe the content of documents. XML documents can automatically be generated from database data and vice versa.

What are the three types of outer join?

LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN

_____ can be paired with wildcards to find rows matching a string value

LIKE

•How are 1:1/1:N/M:N relationships represented with foreign keys?

M:N: The intersection table will receive the primary keys of the two parent table and make these key (foreign keys) as it primary keys. 1:N: By Placing the primary key of the parent in the child as a foreign key. For the representation with foreign keys, in 1:1 relationship, if either of the entities has an optional side, then the entity having optional side contains the foreign key.

----|-|- What does this mean in crow's foot notation?

Mandatory - One

In a binary relationship, what does N:M mean?

Many-to-many: Many entity instances in one entity class is related to many entity instances in another entity class.

Symbol closest to the entity shows _____ cardinality.

Maximum

Which Microsoft application is helpful for diagramming database models and flowcharts?

Microsoft Visio

What do minimum and maximum cardinality mean?

Minimum cardinality is the minimum number of entity instances that MUST participate in a relationship instance (0: optional; 1: mandatory) Maximum cardinality is the maximum number of entity instances that MAY participate in a relationship instance (one, many).

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

NULL value

What elements do the "columns" in a "Column Family Database" consist of?

Name Value Timestap

Can a primary key have duplicate values?

No

What does NoSQL not stand for?

No SQL

Can a candidate key contain NULL values?

No, candidate keys cannot contain NULL values

Does the SQL view table have its own data?

No, it obtains data from tables or other views

"Column family" architecture Belongs to which type of database? a. MySQL Database b. Oracle Database c. NoSQL Database d. None of the above e. All the above

NoSQL Database

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 does NoSQL stand for?

Not Only SQL

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

NumberOfHoursStudying

In a 1:N relationship, the term "parent" refers to the table on which side of the relationship.

ONE

What does OLAP stand for and name one software that can run it.

On Line Analytical Programming. Microsoft's Excel Power pivot is one application for OLAP. Online Analytical Processing - a technique for dynamically examining database data which uses arithmetic functions such as Sum, Average, etc..

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

What is OLTP?

Online Transaction Processing is an operational system such as sales, purchasing, and inventory-control that support primary business activities. OLTP Uses a DBMS to read and store data in the operational database. i.e. records the ongoing stream of business transactions.

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

Name at least 3 widely used RDBMS systems.

Oracle Database, MySQL, Microsoft SQL Server, IBM Db2, Microsoft Access, PostgreSQL

What is another name for 1:N relationship?

Parent & Child relationship

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

The SQL keyword BETWEEN is used in ____

Ranges

What are two other names for a row?

Record and Tuple

How are recursive relationships represented with foreign keys and intersection tables?

Recursive relationships adhere to the same rules as binary relationships. -1:1 and 1:M relationships are saved using foreign keys. -M:N relationships are saved by creating an intersecting relation.

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

If a table has a composite primary key composed of columns "column1" and "column2" how would you start calling it in SQL?

SELECT * FROM your_table WHERE column1 = 'value1' AND column2 = 'value2';

Which statement is an SQL DDL statement that is used to change the structure of an existing table. a. SQL CREATE b. SQL ALTER c. SQL MODIFY

SQL ALTER

The SQL statement that reads data from a table is ________

Select

The requirement that all of the values in a column are of the same kind is known as________

The Domain Integrity Constraint

What is the ETL process?

The ETL process is how data is transferred from an operational database to a data warehouse. You first Extract the data, then Transform it to clean up any dirty data and to fit other business requirements, then Load it into the data warehouse.

What is the main purpose of a dimensional database?

The Main purpose of a dimensional database is to analyze data processing using current and historical data systematically.

Which of the following functions is NOT related to OLAP report? a. The OLAP report calculates outputs called measure b. The OLAP report is a form of statistical data c. Excel PivotTables can be used to create OLAP report d. The OLAP report is known as OLAP cube

The OLAP report is a form of statistical data

Find what is wrong with the SQL Query SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. Instead, the HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

In a Crow's Foot Version relationship how do we identify the max and min cardinality?

The outer oval or hash-mark next to the entity represents the maximum cardinality, and the inner oval or hash-mark is the minimum cardinality.

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

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

True or False, Minimum cardinality is the minimum number of entity instances that must participate in a relationship instance.

True

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

True

True or False: candidate keys can be single-column keys, or they can be composite keys

True

True or False? Metadata is data that describes other data

True

True or False? Normalization reduces data integrity problems.

True

True or False? Recursive relationships can be 1:1, 1:N, and N:M.

True

True or false: Subtypes have the same identifier as their related supertypes?

True

True/False: nchar and nvarchar use UNICODE

True

True or False: Each database design must be created for a particular DBMS product?

True (Data models are not DBMS-specific, but implementable database designs are)

True or False: A relation in 3NF is by definition also in 2NF.

True, a relation in 3NF is by definition also in 2NF.

True or False: A primary key must be a candidate key.

True, primary key is a human choice, chosen by the database designer.

4. True or False: When a table is in 1NF and does not have any composite candidate keys, it is automatically in 2NF.

True.

T/F: An ID-dependent WEAK entity must have a composite identifier

True. It must have a composite identifier, one part of which is the identifier for the strong entity.

To change the data values in an existing row (or set of rows) use the ______ statement.

UPDATE

Name a way to deal with the leading zero problem.

Use short text instead of number for data type if appropriate.

What are Operational Databases?

Used for structured transaction data processing. Current data are used. Data are inserted, updated, and deleted by users.

What is Data Definition Language (DDL)?

Used to change the schema of a database by creating tables, relationships, and other structures.

How do you sort in descending order in SQL?

Using ORDER BY ColumnName DESC (DESC comes after the column name)

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.

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

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

a) a one-to-one relationship

What is the purpose of a foreign key? Select one: a. By corresponding with the primary key in another table, it creates a relationship b. It is a unique identifier in a table that is not designated as the primary key c. It is an auto-generated number assigned by the DBMS d. It is a key that opens doors in foreign countries

a. By corresponding with the primary key in another table, it creates a relationship

What data type should ZIPCODE use? Assume that you are only storing 5 digit zipcode (92645). a. Char(5) b. VarChar(5) c. Int(5) d. Double(5)

a. Char(5)

Which of the following is not one of the four main types of NoSQL databases? a. Row b. Document c. Graph d. Key-value

a. Row

What are some synonyms for an intersection table?

association table, bridge table, cross-reference table, crosswalk, intermediary table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, or transition table

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

associative

It is compulsory to remove the transitive dependencies in order to the table convert into a __________ Form. a) First Normal Form (1NF) b) Third Normal Form (3NF) c) Boyce-Codd (BCNF) d) Second Normal Form (2NF)

b) Third Normal Form (3NF).

Statements which are used for creating tables, relationships, and other structures. a. DML (Data Manipulation Language) b. DDL (Data Definition Language) c. DCL (Data Control Language) d. TCL (Transaction Control Language)

b. DDL (Data Definition Language)

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

b. Denormalized Table

A relation needs to go through a conversion process to be put in 1NF format. a. True b. False

b. False

When using aggregate functions (SUM, AVG, etc.) in a SELECT statement, if you have non-aggregated columns as well, then you must also include a _______ clause as well. a. HAVING b. GROUP BY c. ORDER BY d. WHERE

b. GROUP BY

Relationships of two degree are called ____________ relationships while three are called _____________ relationships.

binary; ternary

Which of the following is not a valid SQL keyword? a) BETWEEN b) ORDER BY c) ENABLE d) DISTINCT

c) ENABLE

Which one of these Database Keys are not unique? a) Candidate Key b) Surrogate Key c) Foreign Key D) Primary Key

c) Foreign Key

Which is a database system that has data, programs, and personnel that specialize in the preparation of data for BI processing? a. Data mart b. Database c. Data warehouse

c. Data warehouse

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 are the four components of a database system? What function do they each have?

o Entities - Objects that can store data about themselves. o Attributes - Characteristics of entities. o Identifiers - Attributes that indicate a specific instance in the entity class. Identifier types can be unique or non-unique o Relationships - How multiple entities are associated with each other, as defined by their cardinality.