Data Management - Foundations - C175

What are four benefits to utilizing databases?

- They divide structure to the data - They allow enforcement rules to be applied to the data -They protect the data from unauthorized access or changes -They make sure changes only get made if other associated changes are made successfully

What is NULL?

A condition that represents no information or absence of a value (not to be confused with 0). False or 0 as placeholder values could cause problems later on.

What is an attribute and how is it represented in an ER diagram?

A description of an entity's characteristics. Ex.) Employee Name, Date Of Hire, Job Skill Code. In an ER diagram they are the text inside the rectangles and below the entity names.

What is a transaction?

A group of queries that must either be completed or they are rejected as a whole.

What is a relationship and how is it represented in an ER diagram?

A link between entities. In an ER diagram they are represented by lines between the rectangles.

What is an entity and how is it represented in an ER diagram?

A person, place, thing or event about which information is maintained. In an ER diagram it is represented by the name at the top of the rectangles.

What is a synthetic key name a combination of?

A singular identifier that corresponds to the table and the identifier or key. Ex. TablenameID, TablenamePK( for primary key)

When you move data from an unstructured form to a structured form, what benefit do you gain? a) The ability to use data programmatically b) The ability to easily locate data c) The ability to easily sort data d) all of the above

All of the above

What is an ER Diagram?

An Entity Relationship Diagram. A diagram that uses tables, fields and relationships to plan a database

For large, complex databases, what are the three phases of the database design process?

Analysis, Logical design and Physical design

What is DML and DDL?

DML and DDL are subsets of SQL. DML stands for Data Manipulation Language and DDL - Data Definition Language. DML consist of INSERT, UPDATE and DELETE DDL commands CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX. CREATE/ALTER/DROP VIEW

What is structured data?

Data that is stored in a traditional system such as a relational database or spreadsheet. Then input into tables and rows and columns.

What is a DBMS?

Database Management System Software used to interact with and create, process, and administer databases. Ex. Microsoft Access, SQL Server, MYSQL

What is referential integrity?

Databases are aware of relationships and won't allow a user to modify data in a way that violates those relationships. To help maintain the consistency of the database.

Adding a FOREIGN key constraint to a table only affects inserting new rows into the table. True or False?

False

After the 'implement entities' step is completed, table and column specifications are final. True or False?

False

All database systems use identical SQL statements. True or False?

False

All design decisions in the 'implement entities' step are affected by the database system. True or False?

False

Data type depends on attribute cardinality. True or False?

False

Data types are independent of the database system. True or False?

False

Denormalization never results in second normal form tables. Tue or False?

False

Exactly one query execution plan is possible for each SQL query. True or False?

False

In order to sort results based on a field, that field needs to appear in the final output. True or False?

False

The activities of the 'implement entities' step are always executed in sequential order. True or False?

False

The logical design phase ends when attributes have been implemented as columns. True or False?

False

Distinguishing independent and dependent entities is a logical design activity. True or False?

False- Activities that document database requirements without regard to specific database system at part of the analysis phase.

The query processor has direct access to the database data on storage media. True or False?

False- All access to the database must go through the storage manager.

Every database query must be logged by the transaction manager to recover the database in the event of a system failure. True or False?

False- All inserts, updates, and deletes must be logged in order to correctly restore database changes. Reads are not needed to restore database changes, however, and thus are not logged.

Analysis considers implementation issues related to a specific database system. True or False?

False- Analysis documents database requirements, without regard to implementation details for any database system.

"Department-IsManagedBy-Employee" is a correct relationship name. True or False?

False- Is managed by is passive, should be manages which is an active verb.

Query optimizers typically choose an optimal expression based on total number of rows processed. True or False?

False- Most query optimizers use more precise measures, such as bytes read from storage media or CPU processing time.

In MySQL, two different queries that generate the same result table always have the same execution time. True or False?

False- MySQL, like most databases, efficiently compiles SQL queries. However no database always compiles equivalent queries in exactly the same way. As a result, equivalent queries often have different execution times.

Without query optimization, the storage manager cannot retrieve the database data. True or False?

False- Query optimization ensures the most efficient instructions are sent to the storage manager, but the storage manager can retrieve data with efficient or inefficient instructions.

SQL is a general-purpose programming language. True or False?

False- SQL was specifically designed for database queries and lacks many features needed for programming applications.

The catalog allows the storage manager to quickly locate the requested data. True or False?

False- The catalog just describes the database. The index helps locate data quickly by providing information about where on the storage media certain pieces of data can be found.

If a table is set to auto-increment the primary key, you'll need to know the next value and set it manually when you enter a record. True or False?

False- When a database automatically increments a key field, you don't need to worry about setting the value. The database will provide the next value in the sequence automatically.

In any given query, you can only join together a maximum of two tables. True or False?

False- You can join together many tables as long as you tell the database which pairs of values on the table are intended to match.

A database must include one or more relationships. True or False.

False- You can use tables in a database without defining any relationships.

An entity can be in two subtypes of the same partition. True or False?

False- a card cannot be both Visa and Mastercard at the same time.

Oracle database has multiple storage engines. True or False?

False- all databases have built in storage that cannot be changed.

An entity-relationship model is developed for all database design projects. True or False?

False- an ER model is developed in the analysis phase. Analysis is sometimes omitted for simple databases with just a few tables and users.

"License" is a correct attribute name. True or False?

False- attribute names should have a type suffix or prefix. Correct name would be "LicenseNumber".

One partition attribute can correspond to several partitions. True or False?

False- each partition corresponds to one partition attribute.

Denormalization accelerates all SELECT queries. True or False?

False- eliminates join queries only

"Students" is a correct entity name. True or False?

False- entity names should be singular

B—>A means each value of A relates to at most one value of B. Tire or False?

False- flip it and reverse it.

The Workbench is intended primarily for database users. True or False?

False- for admin functions mainly

In the analysis phase, cardinality is always determined after discovery is complete. True or False?

False- generally discovery precedes discovery but analysis is an iterative process. Cardinality can take place parallel with discovery and then additional discovery can take place afterward as well.

'Cardinality' refers to relationships only. True or False?

False- it is a general term for minima and maxima of both relationships and attributes.

An application program is part of the Tools layer of MySQL layers. True or False?

False- it is not part of MySQL at all

Redundancy is the repetition of a value. True or False?

False- it is the repetition of related values

A SELECT query may have different results, depending on how data is assigned to nodes of a distributed database. True or False?

False- it only affects performance not query results.

Using materialized views always improves database performance. True or False?

False- materialized view improves performance of SELECT statements that refer to the view. However, base table INSERTS, UPDATES, and DELETES must refresh materialized views and therefor are slower.

"People" is a correct entity name. Tire or False?

False- refers to multiple individuals, should refer to single person.

Entities, relationships and attributes always map directly to tables, foreign keys and columns, respectively. True or False?

False- sometimes entities split into several tables or several merge into one and relationships and attributes become tables.

Determining cardinality always precedes distinguishing independent and dependent entities. True or False?

False- the phases are iterative and occasionally die out of order.

In ER diagrams, maxima and minima are drawn for relationships only. True or False?

False- they are also for attributes

In the first normal form table, non-key columns depend only on the primary key. True or False?

False- they may depend on other non-key columns

Nodes of a distributed database may share memory or storage. True or False?

False- they run on shared nothing parallel systems.

Why were NoSQL systems developed?

In response to the growth of the internet and to support large values of unstructured data, big data.

For small, simple databases, what is the database design process usually?

Informal and unstructured.

What is the NOT NULL constraint?

It enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are Edgar Codd's three rules for organizing data in a database?

Normalization rules first, second and 3rd normal form. 1NF, 2NF, 3NF. They are considered to be the standard level of optimization for a business database.

Where is the foreign key in a one-many relationship?

On the many side.

What is the difference between synthetic and composite keys?

One is a new column name usually a combo of the name and the table and what they are identifying to make a unique identifier . The other is a combo of table names that when together become a unique identifier.

What do normalization rules help with?

Reducing redundancy and improving integrity of data in a database.

What is CRUD?

The four common queries- Create, Read, Update, Delete

How is a foreign key named in a one-many relationship?

The primary key and an optional prefix derived from the relationship name. Or by combining each of the table names.

A program that helps database administrators configure MySQL is considered a utility program. True or False?

True

An UPDATE query may run faster or slower, depending on how data is assigned to nodes of a distributed database. True or False?

True

An attribute indicates the data type of the corresponding column. True or False?

True

Attribute names always include and attribute type. True or False?

True

Changing the order of operations can alter the result of an expression. True or False?

True

Database administrators can optimize query performance by assigning tables or parts of tables to specific nodes of a distributed database. True or False?

True

In a table that never contains duplicate rows, non-key columns always depend on the primary key. True or False?

True

In principle, all tables are in first normal form.

True

Manny Connector tools are built on top of API tools. True or False?

True

Occasionally, database design skips a formal analysis phase and begins in logical design. True or False?

True

Occasionally, tables are Denormalization in a frequently updated database. True or False?

True

Plural attributes are implemented as new dependent tables. True or False?

True

SQL commands can create databases and tables. True or False?

True

Tables that allow redundancy might contain inconsistent data. True or False?

True

Views can be used to hide rows as well as columns from database users. True or False?

True

Each partition attribute value corresponds to one subtype. True or False?

True- CreditType value "Debit" indicates the card is a DebitCard.

Data in a relational database can violate relational rules. True or False?

True- Depending on how a table is specified, some relational rules can be violated. Ex. A table could be made without a primary key. Rule violations can cause business problems and normally should be avoided or automatically prevented.

Each host language, such as Java or C++, has a different API. True or False?

True- Since the API is written primarily I the host language, different host languages must have different API's.

An entity instance can be two subtypes of different partitions. True or False?

True- a card can be both a credit card and a Visa simultaneously.

A view query can reference another view table. True or False?

True- a view is a table and can appear in any SELECT statement. Since a view query is a SELECT statement, view queries can reference other view tables.

Maxima and minima usually depend on business rules. True or False?

True- although some are universal

"PassengerMilagePlanCode" is a correct attribute name. True or False?

True- it names Passenger-Entity, MileagePlan-two word attribute qualifier, Code- attribute

A FOREIGN KEY constraint must reference a primary key. True or False?

True- primary key it references can be in the same table or another table.

Database designers can look for dependency relationships first, before identifying dependent entities. True or False?

True- since the phases are iterative it is a matter of preference for what is done first.

"Employee-Manages-Employee" is a correct relationship name. True or False?

True- sometimes relationships relate an entity to itself.

The performance of a user query on a view is identical to the performance of corresponding merged query on base tables. True or False?

True- the database converts a user query against a view to a merged query and then executes the merged query. The performance is the same as if the user entered the merged query.

A database designer and database user both use SQL. True or False?

True- the designer uses SQL to create databases and tables, and the user user SQL to insert, retrieve, update and delete data from the tables.

Can virtual machines running on the same computer contain different operating systems? Yes or No?

Yes

Determine attribute maxima and minima is what type of analysis? a) Determine cardinality b) Distinguish independent and dependent entities c) Discover entities, relationships and attributes d) Create super type and subtype entities

a

Each appliances model number is different from all other model numbers is an example of what attribute? a) unique b) simple c) stable d) required

a

In a join, what are the first and second tables in the FROM clause called? a) left and right tables, respectively b) right and left tables, respectively c) Table one and table two, respectively d) none of the above

a

Relational databases have several special joins, a ________ join compares columns of two table with an operator other than =, such as < and >. a) non-equijoin b) self-join c) equijoin d) cross-join

a

The _______ contains one row for each database object. a) data dictionary b) log files c) routines d) triggers

a

The _______ table provides various table statistics and is used by the query optimizer to generate efficient execution plans. a) table_stats b) log files c) routines d) triggers

a

What does DTL (Data Transaction Language) do with a database ? a) manages database transactions b) manipulates data stored in a database c) retrieves data from the database d) defines the structure of the database

a

What happens during the physical design phase of database design? a) Indexes are added and specifies how tables are organized on storage media. b) implements database requirements in a specific database system. c) specifies database requirements without regard to a specific database system. d) all of the above

a

What is the difference between table and ER diagrams? a) schema specifies the result of logical design in table diagrams b) schema is the result of analysis in ER diagrams c) nothing d) neither have schema

a

When you need to create a many-many relationship, what do you need to generate? a) a linking table that has a one-many relationship with two or more tables b) You can't create relationships to allow many records to be associated with many other records c) one large table to contain all the data d) a series of one-one relationships between each of the items you need to associate

a

Which component detects an incorrect database server address? a) connection manager b) query parser c) query optimizer

a

Which normal form allows the most redundancy of any normal form. a) first b) fourth c) third d) fifth

a

Which of these tasks can you accomplish using SQL as a DML? a) inserting a record into a table b) deleting the database c) allowing a new user access to read from a table d) removing a column from a table

a

Which storage engine is commonly used for transaction management? a) InnoDB b) ARCHIVE c) MEMORY d) InnoDC

a

Which scenario represents a one-many relationship? a) bank customers linked to their bank accounts b) banks linked to the countries where they do business c) bank customers linked to their drivers license details d) bank branches linked to their banking services

a- A certain customer can have multiple accounts, but a certain account can only belong to one customer.

Which of these is not an example of a time when you would use an aggregate function? a) looking up customers with the name Rafael Montresso b) finding the highest price in a table of products c) counting numbers of rows that match a specific query d) finding the average of the values in the Points column in a table of quiz scores

a- Aggregate functions are used to tell you about certain characteristics of multiple records. Requesting a record by specific field values is not an aggregate operation.

When using an aggregate function, how many results do you expect? a) one b) two c) all of the records in the table d) zero

a- Aggregate functions return one value that describes a set of data.

Discovery is a step in which phase? a) Analysis b) Logical design c) Database design d) None of the above

a- Analysis identifies entities, relationships, and attributes in interviews.

If you reference a key from Table A in Table B, what is that value in Table B? a) a foreign key b) a composite key c) a primary key d) a secondary key

a- Because it refers to a key in another table, in this context, the value is called a foreign key.

What is DDL? a) for defining and designing databases b) for manipulating databases c) for normalization d) for DBMS

a- CREATE TABLE

Which SQL sublanguage inserts a data row into a table product? a) DML b) DQL c) DTL d) DDL

a- DML inserts, updates and deletes data in a table

What is a tuple? a)a infinite sequence of values each drawn from a fixed domain b) a collection of values or elements with no inherent order c) a named set of tuples, all drawn from the same sequence of domains d) a named set of possible database values.

a- Ex. (3, apple, TRUE) is a tuple drawn from domains (Integer, DictionaryWords, LogicalValues) Equivalent relational term database- Row, Files- Record. Not significant in a relational model, since each value is already associated with an attribute.

Which is an example of referential integrity? a) preventing the user from entering a record that refers to nonexistent data b) using an auto-incrementing column as a primary key c) double-checking that everything in the record is spelled correctly d) checking with the administrator before deleting a table

a- If a field in one table refers to a column in another table, you can require that the value you reference exists in that other table before the database will allow you to enter a new record.

What is it called if you delete a record and the database goes on and deletes other records associated with that record? a) a cascading delete b) a recursive remove c) a progressive purge d) a galloping get out of here

a- If configured to do so, a delete can cascade across records linked with a relationship.

When modifying a record, it's a good idea to specify the record ________. a) as precisely as possible, ideally using the primary key b) choosing the most recently added result in a set of matching records c) choosing the last result in a set of matching records d) by choosing the first result in a set of matching records

a- If you've designed your database correctly, each record should have a key that uniquely identifies if, making it safe to use that key to modify a record.

In order to put a database into Third normal form, _______. a) it must also be in first and second normal form b) the database must contain three tables c) it must not be in either first or second normal form d) you can skip right to third normal form

a- Normalization is a progressive process, and higher forms depends on the database being compliant with lower forms as well.

When you store data in a database, what is one advantage you get over a plain spreadsheet? a) Rows of data can be associated with each other across tables b) You can keep information stored in a regular, organized way c) You can insert and delete data d) Data can be sorted by column

a- The ability to create relationships between tables is a core function of relational databases.

When talking about data types, what do you call the group of types that represent text? a) string types b) word types c) sentence types d) Boolean values

a- There are various kinds of string types to accommodate text of different lengths.

What are structural rules? a) regulation rules that govern data in every database b) relational rules specific to a particular database application c) not relational rules d) both a and b

a- They include unique primary keys, unique column names, no duplicate rows. Tables are normalized, no duplicate column names and no duplicate rows are three structural rules tables obey

Which statement is true regarding composite keys? a) A composite key is likely useful when there is no primary key b) A composite key is created by merging together all fields in a row c) Composite keys are required when a table needs to be linked to another table d) Composite keys are created by combining primary and foreign keys

a- When a primary key does not exist, a composite key can be used to uniquely identify and relate a record to other data.

In a database, what is a relation? a) A set of attributes (columns) that describe information about specific instances (rows) of an entity b) The way in which information in one table connects to the information in another table c) alone column of information d) Two pieces of data on one row that are connected in meaning

a- You may also see the rows called "tuples"

Determine the maxima for the relationship "City-IsCapitolOf-State". a) one-one b) one-many c) many-one d) many-many

a- a city is a capitol of at most one state and each state has one capitol.

Determine the minima for the relationship "Person-Marries-Person". a) zero-zero b) one-zero c) zero-one d) one-one

a- a person can be unmarried.

In "PersonIDNumber M-1(1)", the M indicates: a) Maxima-Many b) Maxima-One c) Minima-Zero d) Minima- One

a- a relationship has two maxima, one for each related entity and are specified as one "1" or many "M".

In "EmployeeNumber 1-1(1)", the 1-1 indicates: a) Unique and singular b) Not unique and singular c) Not unique and plural d) Unique and plural

a- aka one-one

Determine the maxima for the Entity-Has-Attribute relationship Entity Employee , attribute LanguageCode. a) many-many b) one-many c) many-one d) one-one

a- and employee can speak many languages and each language can be spoken by many people.

All exams belong to a course. A course must exist before course exams are created. When a course is deleted, course exams are also deleted. Courses are always part of an academic department. We set up a cede mic departments in the database before we add courses. "AcedemicDepartment" is an example of __________. a) Independent entity b) Master entity c) Dependent entity d) Dependent relationship

a- does not depend on anything to exist

In "EmployeeNumber 1-1(1)", the (1) indicates: a) Required b) Optional c) Unique d) Static

a- each employee must have a number

"Students take exams" is an example of what type of entity-relationship model? a) relationship type b) relationship instance c) attribute type d) attribute instance

a- it is a statement about two entity types, students and exams.

The foreign key in the subtype table implements the _______ relationship between subtype and supertype entities. a) IsA b) supertype c) cascade d) restrict

a- or dependency. Subtype entities always have dependency to the supertype.

The primary key of a subtype table is also a _______. a) foreign key b) supertype c) cascade d) subtype

a- subtype table are dependent on supertype tables

What would be a good name for a table containing customer details and contact information? a) Customers b) CustomersDetailsAndContactInformation c) Customers Contact Information d) customercontactinfo

a- the name is short, descriptive and uses a pluralized name based on the type of records it contains.

Which WHERE condition can you use to find all records containing a first name starting with the letter "A"? a) WHERE FirstName LIKE "A%"; b) WHERE FirstName = "A"; c) WHERE FirstName LIKE "A*"; d) WHERE FirstName = "A%";

a- the percent symbol is used as a wildcard for anything coming after the letter A.

A _________ attribute becomes a column that is never NULL. a) unique b) required c) simple d) stable

b

A _________ is a descriptive property of an entity. a) relationship b) attribute c) entity d) reservation

b

A _________ is a statement about two entities. a) relationship b) attribute c) entity d) reservation

b

A model number is a combination of five letters and numbers, is an example of what attribute? a) unique b) simple c) stable d) required

b

A one-one relationship _______. a) doesn't exist in the real world b) allows only one record to be connected to only one other record c) is how you connect one table to one other table d) allows one record to be Otto more than one other records

b

A table with a simple primary key must be in what normal form? a) First normal form only, and no higher b) Second normal form or higher c) Third normal form

b

Determine the dependency relationship for each dependent entity is what type of analysis? a) Determine cardinality b) Distinguish independent and dependent entities c) Discover entities, relationships and attributes d) Create super type and subtype entities

b

How often is a master entity also an independent entity? a) Never b) Sometimes c) Always d) Once

b

In some models, dependent entities are called ______ entities. a) strong b) weak c) puny d) mega

b

In the acronym CRUD, what does the "R" stand for? a) remove b) read c) revise d) retrieve

b

Relational databases have several special joins, a ________ join, joins a table to itself. a) non-equijoin b) self-join c) equijoin d) cross-join

b

The ____________ ______________ translates the query processor instructions into low level file system commands that modify or retrieve data. a) transaction manager b) storage manager c) query processor d) architecture index

b

What are the requirements of first normal form? a) No value in a table should depend on only part of a key that can be used to uniquely identify a row b) Values in each cell should be atomic and tables should have no repaying groups. c) Values should not be stored if they can be calculated from another non-key field. d) all of the above

b

What does DCL (Data Control Language) do with a database ? a) manages database transactions b) controls database user access c) retrieves data from the database d) defines the structure of the database

b

What does DML (Data Manipulation Language) do with a database ? a) manages database transactions b) manipulates data stored in a database c) retrieves data from the database d) defines the structure of the database

b

What happens during the logical design phase of database design? a) Indexes are added and specifies how tables are organized on storage media. b) implements database requirements in a specific database system. c) specifies database requirements without regard to a specific database system. d) all of the above

b

What is a database schema? a)The overall process of determining and implementing database requirements b) The logical design as specified in SQL and depicted in a table diagram c)the principle that physical design never effects query results d) a library of procedures or classes that links a host programming language to a database.

b

Which component detects a missing semi colon at the end of an SQL statement? a) connection manager b) query parser c) query optimizer

b

Which normal form eliminates all redundancy arising from functional dependency. a) first b) Boyce-Codd normal form c) third d) fifth

b

Which normal form eliminates multi values dependencies and associated redundancy. a) first b) fourth c) third d) fifth

b

_______ support database recovery in the event of a system failure. a) indexes b) log files c) routines d) triggers

b

"Eleanor Rigby takes the final exam in calculus" is an example of what type of entity-relationship model? a) relationship type b) relationship instance c) attribute type d) attribute instance

b- "Eleanor Rigby" and the exam are entity instances and this is a statement about them.

"Eleanor Rigby, a student at San Antonio community college" is an example of what type of entity-relationship model? a) entity type b) entity instance c) attribute type d) attribute instance

b- "Eleanor Rigby" refers as a name to a student.

"Students at San Antonio Community College" is an example of what type of entity-relationship model? a) entity instance b) entity type c) relationship type d) attribute type

b- "Students" are a set of people. A set of thins is an entity type.

A SQL statement that returns requested records from the database is called: a) a SQL keyword b) a SQL query c) a SQL request d) a SQL question

b- All statements return a status when executed, but a query is a special case of statements that return's information you asked for.

Which is a good example of a candidate key? a) a customers first name b) an employees ID number c) an employees birthday d) a customers favorite movie

b- Any piece of data that represents a row is a candidate key, and if you have a value that occurs in the data naturally, that's a natural key.

Which SQL sublanguage selects all rows from table Product? a) DML b) DQL c) DTL d) DDL

b- DQL selects data from a table

I'm a database where you keep track of records for a school, what tables should you expect to find? a) STU001, CLA002, and GRA003 b) Students, Classes and Grades c) Students, Class and Grade d) Student, Class and Grade

b- Each of the tables holds a different kind of information and because the tables can store more than one record, the table names are pluralized.

What is DML? a) for defining and designing databases b) for manipulating databases c) for normalization d) for DBMS

b- INSERT, UPDATE, DELETE

First normal form tells you to do what? a) check that data in fields matches the data type specified for them b) remove repeating groups c) ensure that no non-key field is dependent on only part of a composite key d) ensure that no non-key field is dependent on another non-key field

b- If you find yourself adding lists of things in individual fields or adding columns to represent additional fields of the same type, you're probably creating repeating groups. And 1NF tells you that you need to refactor your tables if that happens.

What is a set? a)a infinite sequence of values each drawn from a fixed domain b) a collection of values or elements with no inherent order c) a named set of tuples, all drawn from the same sequence of domains d) a named set of possible database values.

b- Sets are denoted with braces ex. {apple, orange, banana} is a set of fruits

SQL is ________. a) a database manager system b) the language you use to communicate with a database c) only used by professional database administrators d) outdated and obsolete

b- Structured Query Language is the most common language interacting with relational databases.

The definition of how data in a database will be organized is called the ________? a) setting b) schema c) tables d) layout

b- The databases schema includes the information about the layout of tables and other information about the database itself.

In a one-many relationship, the value representing the 'many' side is what? a) number b) foreign key c) string type d) primary key

b- The foreign key points to the primary key for the 'one' side of the relationship.

Where are duplicate collections man names allowed? What are structural rules? a) within a single table b) in different tables c) never

b- They can be distinguished by calling them by "TableName.ColumnName"

What are business rules? a) regulation rules that govern data in every database b) relational rules specific to a particular database application c) not relational rules d) both a and b

b- They include unique column values, no missing values, delete cascade

Must entity, relationship and attribute synonyms follow naming conventions? a) Yes b) No c) Only attribute synonyms d) Only entity synonyms

b- They should reflect common usage only.

When planning a database, what do you start with? a) a smile on your face and a song in your heart b) an Entity Relationship (ER) Diagram c) Table Definition (TD) diagram d) a rough idea of what data will be stored

b- Using an Ear diagram, you can plan out what fields will appear on which tables, and how they're related

Determine the maxima for the relationship "Person-Has-Passport". a) one-one b) one-many c) many-one d) many-many

b- a person can have many passports from different countries. But each passport belongs to only one person.

In "LicensePlateNumber 1-1(1)", the first 1 indicates: a) Maxima-Many b) Maxima-One c) Minima-Zero d) Minima- One

b- a relationship has two maxima, one for each related entity and are specified as one "1" or many "M".

In "FullName M-1(1)", the 1-1 indicates: a) Unique and singular b) Not unique and singular c) Not unique and plural d) Unique and plural

b- aka many-one

In "PersonIDNumber M-1(1)", the M-1 indicates: a) Unique and singular b) Not unique and singular c) Not unique and plural d) Unique and plural

b- aka many-one

Second normal form tells you to _________ in addition to being compliant with first normal form. a) remove repeating groups b) ensure no non-key field is dependent on only part of a composite key c) ensure that no non-key field is dependent on the entire composite key d) ensure that no non-key field is dependent on another non-key field

b- before you get to 2NF, you need to make sure your tables have no repeating groups.

All exams belong to a course. A course must exist before course exams are created. When a course is deleted, course exams are also deleted. Courses are always part of an academic department. We set up a cede mic departments in the database before we add courses. "Course and AcedemicDepartment" are an example of __________. a) Independent entity b) Master entity c) Dependent entity d) Dependent relationship

b- course is the master entity for exams, without course exams would not exist. Course depends on AcedemicDepartment, so it is the master entity for course.

Determine the minima for the relationship "Person-Has-Passport". a) zero-zero b) one-zero c) zero-one d) one-one

b- every passport must belong to an person, but not all people have passports.

What is denormalization? a) No value in a table should depend on only part of a key that can be used to uniquely identify a row b) The process of intentionally duplicating information in a table, in violation of normalization rules. c) specifies database requirements without regard to a specific database system. d) Values should not be stored if they can be calculated from another non-key field.

b- it is done after normalizing a database, it does not skip normalization all together. It is about trade offs, while it may make a gain in speed it may reduce consistency.

Which is not a MySQL storage engine? a) MEMORY b) MongoDB c) CSV

b- it's a database, not a storage engine

__________ columns contain no descriptive information and make good primary keys. a) unique b) meaningless c) simple d) stable

b- meaningless columns contain no additional information and are unlikely to change.

Which columns can be compared in a join? a) only primary and foreign key columns b) only columns with compatible data types c) any columns

b- most often the foreign key is compared for the primary key

What is an attribute? a)a infinite sequence of values each drawn from a fixed domain b) the position of a tuple with a unique name within the relation c) a named set of tuples, all drawn from the same sequence of domains d) a named set of possible database values.

b- since each tuple position has a domain, so does each attribute. Equivalent relational term database- Column, Files- Field.

The subtype table primary key is identical to the _______ table primary key. a) foreign key b) supertype c) cascade d) subtype

b- since subtype instance are always superior instances, subtype tables have the same primary key as the supertype table.

In "PassportNumber 1-M(0)", the (0) indicates: a) Required b) Optional c) Unique d) Static

b- some people don't have passports.

A _________ is a person, place, product, concept, or activity. a) relationship b) attribute c) entity d) reservation

c

A _________ primary key is easy to specify in a WHERE clause. a) unique b) required c) simple d) stable

c

A _________ primary key reduces cascading updates in the database. a) unique b) required c) simple d) stable

c

A group of entities is often called a/an ________. a) substance b) joint c) subject area d) convention

c

An appliances model number does not change is an example of what attribute? a) unique b) simple c) stable d) required

c

An entity-relationship model is completely described by: a) An ER diagram b) A glossary c) both ER diagram and glossary d) none of the above

c

ER diagrams drawing is a step in which phase? a) Analysis b) Logical design c) Database design d) None of the above

c

List standard attribute types in glossary is what type of analysis? a) Determine cardinality b) Distinguish independent and dependent entities c) Discover entities, relationships and attributes d) Create super type and subtype entities

c

Relational databases have several special joins, a ________ join compares columns of two table with the = operator. a) non-equijoin b) self-join c) equijoin d) cross-join

c

What block does InnoDB discard when more space is needed in the buffer? a) the block that was loaded to buffer first b) the block that was loaded to buffer last c) the block that has not been accessed for the longest time

c

What does DQL (Data Query Language) do with a database ? a) manages database transactions b) manipulates data stored in a database c) retrieves data from the database d) defines the structure of the database

c

What does logical design change? a) specifies database requirements b) adds indexes and specifies how tables are organized c) converts entities, relationships and attributes into tables, keys and columns d) all of the above

c

What happens during the analysis phase of database design? a) Indexes are added and specifies how tables are organized on storage media. b) implements database requirements in a specific database system. c) specifies database requirements without regard to a specific database system. d) all of the above

c

What is data independence? a)The overall process of determining and implementing database requirements b) The logical design as specified in SQL and depicted in a table diagram c)the principle that physical design never effects query results d) a library of procedures or classes that links a host programming language to a database.

c

What is included included in a glossary? a) Descriptions only b) Names and synonyms only c) Names, Synonyms and descriptions only d) Names, synonyms, descriptions and ER diagrams

c

Which SQL command will you use to create a new database called"mydb"? a) use mydb; b) DATABASE mydb; c) CREATE DATABASE mydb; d) CREATE mydb;

c

When you are using SQL statements to create or modify the structure of a database, what is SQL being used as? a) DML b) DBMS c) DDL d) DCL

c- As a Data Definition Language, SQL can be used to create or modify the structure of database tables.

Which SQL sublanguage grants permission to user 'tester'? a) DML b) DQL c) DCL d) DDL

c- DCL grants and revokes permissions to and from users.

Which SQL sublanguage rollbacks database changes? a) DML b) DQL c) DTL d) DDL

c- DTL commits data to the database, rolls back data from a database and creates savepoints.

Determine the maxima for the Entity-Has-Attribute relationship Entity Passenger, attribute PassengerName. a) many-many b) one-many c) many-one d) one-one

c- Each passenger has one name and each name can describe many passengers.

What is a relation? a)a infinite sequence of values each drawn from a fixed domain b) a collection of values or elements with no inherent order c) a named set of tuples, all drawn from the same sequence of domains d) a named set of possible database values.

c- Equivalent relational term database- Table, Files- File.

Which other table in your database will likely have a one-to-one relationship with the employees table? a) the departments table b) the permissions table c) the badges table d) the titles table

c- Every single badge should be associated with a single employee.

When telling the database that a certain field must not contain an empty value, you say that it is: a) not empty b) populated c) not null d) required

c- For some fields, you might want to prohibit entering records with empty values. By telling the database that the field can't be null, the database will handle the restriction for you.

When might you choose to denormalize a table? a) You don't have time to follow the normalization process b) There is never a new fit to denormalizing a table c) Retrieving the data upon request would be slow or burdensome, and you are able to pre-calculate or store a copy of the data somewhere it can be retrieved faster d) Our tables are small, and normalization is not a concern

c- If you need to prioritize the speed of a particular operation, you might choose to denormalize, as long as you remain aware of the threat to database integrity.

What is a the principle of data independence state? a) The performance of a query is not related to the physical organization of data b) Data in each row is independent of data in all other rows c) The result of a database query is not affected by the physical organization of data on storage devices d) all of the above

c- In relational databases, query results at not dependent on physical storage. This allows database administrators to tune storage for optimal performance at any time.

A table has two rows with the same values in all columns. Which step can you take to have this table meet the first normal form requirements? a) Consolidate all table columns into a single columns b) Delete one of the rows c) Add a primary key to the table d) Move both rows to the end of the table

c- The primary key will add a unique value for each row, thus eliminating the repeating duplicate rows issue.

To store the value 4:32 PM, December 27, 2019, which data type would you use? a) VARCHAR b) DATE c) DATETIME d) CHAR

c- This data type includes both date and time components. The other options do not.

What would you use a relationship to connect? a) a customers first name and last name b) a customers email and phone number c) a customer with their favorite table in a restaurant d) the price of a dish and the name of that dish

c- This would be a one to many relationship. For every table, there would be many customers who prefer to sit at it. But one customer cannot have many favorite tables.

What is the name of a key that consists of different fields taken together to act as a unique identifier? a) foreign key b) surrogate key c) composite key d) primary key

c- a composite key combines two or more fields to act as a unique identifier

In "DealerInvoiceNumber 1-0(0)", 0 indicates: a) Maxima-Zero b) Maxima-One c) Minima-Zero d) Minima- One

c- a relationship has two minima, one for each related entity and are specified as one "1" or zero "0".

In "SkillCode M-M(1)", the 1-1 indicates: a) Unique and singular b) Not unique and singular c) Not unique and plural d) Unique and plural

c- aka many-many

Determine the maxima for the Entity-Has-Attribute relationship Entity Booking, attribute TotalCost. a) many-many b) one-many c) many-one d) one-one

c- each booking has one cost and each cost can describe many bookings.

How many dependency relationships can each dependent entity have? a) Zero or One b) Exactly One c) One or Many d) Zero, One or Many

c- each dependent entity must have at least one dependency.

All exams belong to a course. A course must exist before course exams are created. When a course is deleted, course exams are also deleted. Courses are always part of an academic department. We set up a cede mic departments in the database before we add courses. "Exam" is an example of __________. a) Independent entity b) Master entity c) Dependent entity d) Dependent relationship

c- exam depends on course.

"Student record number" is an example of what type of entity-relationship model? a) entity type b) entity instance c) attribute type d) attribute instance

c- it is a set of numbers describing the student entity. A set of descriptive properties is an attribute type.

Determine the maxima for the relationship "Person-Has-MailingAddress". a) one-one b) one-many c) many-one d) many-many

c- several people can share one address, but the property can only have one address.

The foreign key in the subtype table usually has the referential integrity action _______ on primary key delete. a) foreign key b) supertype c) cascade d) restrict

c- since the subtype cannot exist without the supertype, delete automatically cascades and deletes any corresponding tables.

A unique value________. a) almost never repeats b) occurs only once in a given table c) occurs only once in a given column d) occurs only once in a given row

c- unique values are useful because they never appear twice. If a value appears more than once in a given column, it's not unique in that column.

A model number is randomly generated code, is an example of what attribute? a) unique b) simple c) stable d) meaningless

d

All exams belong to a course. A course must exist before course exams are created. When a course is deleted, course exams are also deleted. Courses are always part of an academic department. We set up a cede mic departments in the database before we add courses. "Exam-BelongsTo-Course" is an example of __________. a) Independent entity b) Master entity c) Dependent entity d) Dependent relationship

d

Defining relationships helps you to do what? a) reduce the repetition of data across tables b) model real-world scenarios and requirements c) understand how your data should be stored d) all of the above

d

Each appliance must have a model number is an example of what attribute? a) unique b) simple c) stable d) required

d

How many dependent entities can depend on one independent entity? a) Zero or One b) Exactly One c) One or Many d) Zero, One or Many

d

Identify partitions and partition attributes is what type of analysis? a) Determine cardinality b) Distinguish independent and dependent entities c) Discover entities, relationships and attributes d) Create super type and subtype entities

d

Relational databases have several special joins, a ________ join combines two tables without comparing columns. a) non-equijoin b) self-join c) equijoin d) cross-join

d

What are relational rules? a) the same as integrity rules b) logical constraints that ensure data is valid and conforms to business policy. c) include Business rules and structural rules d) all of the above

d

What does DDL (Data Definition Language) do with a database ? a) manages database transactions b) manipulates data stored in a database c) retrieves data from the database d) defines the structure of the database

d

What does referential integrity require ? a) That all foreign keys must be partially NULL or match some primary key value b) That all foreign keys must match all primary keys c) That all foreign key values must be NOT NULL or match some primary key value d) That all foreign key values must either be fully NULL or match some primary key value

d

What is a API? a)The overall process of determining and implementing database requirements b) The logical design as specified in SQL and depicted in a table diagram c)the principle that physical design never effects query results d) a library of procedures or classes that links a host programming language to a database.

d

What's types of languages can SQL be used as? a) HTML b) DDL c) DML d) both b and c

d

When first defining a table, what should you specify? A. The tables name. B. The tables padding. C. The fields and type of data they contain. D. The data per cell. E. The primary key and any referential constraints a) B, C, E b) A, B, D c) C, D, E d) A, C, E

d

Which component determines that a query was recently executed? a) connection manager b) query parser c) query optimizer d) cache manager

d

Which normal form eliminates join dependencies and associated redundancy. a) first b) fourth c) third d) fifth

d

Which normal form has a simple primary key and a non-key column cannot depend on part of a composite primary key. a) first b) fourth c) third d) second

d

You can write SQL: a) in database management software b) in an apps source code c) at a command line console d) all of the above

d

In some diagrams, 'many' is depicted with _________ notation. a) A circle b) A rectangle c) A bar across d) Crows feet

d- 3 lines converging at one end often depict max cardinality of many.

"324A21" is an example of what type of entity-relationship model? a) entity type b) entity instance c) attribute type d) attribute instance

d- 324A21 is an individual student record number. Individual attributes are are attribute instances.

If you can figure out the value of one non-key field in a row by looking at another non-key field in that same row, what do you violate? a) Second normal form b) the rules of the space time continuum c) first normal form d) third normal form

d- 3NF tells you that each field in a row should represent something unique about a record.

Which condition represents a NULL value? a) a textual cell holding an empty string b) a numeric cell holding the value 0. c) a textural cell holding the string "NULL" d) a date cell containing no data

d- A cell, regardless of its data type, is NULL when it has no value.

Which SQL sublanguage creates table Product? a) DML b) DQL c) DTL d) DDL

d- DDL creates, alters, and drops tables.

What does denormalization refer to? a) storing different types of data in a particular column b) only complying with their normal form and skipping the first two c) skipping the normalization process d) consciously choosing to violate the rules of normality in order to improve speed or for some other business reason

d- Denormalization is usually a trade off between speed and integrity.

How many SQL clauses are in this query? SELECT Width,Height FROM Shapes; a) 3 b) 1 c) 5 d) 2

d- Each keyword, SELECT and FROM, defines a single clause.

A relationship connects two pieces of data in different ______ in the same ________. a) rows; databases b) columns; table c) fields; row d) tables; databases

d- Even though a relationship is concerned with individual rows, a relationship is defined as being between tables in the same database.

You can narrow down the results that a query returns by only asking for results where a _______ matches a given value. a) type b) row c) database d) field

d- In order to reduce the number of records you get back from a query, you can filter the results based on the contents of a field. For example, you might ask the database to show you records for customers who have their 'State' field set to 'California'.

In order to use records from more than one table query, you need to _______ the tables based on some matching criteria. a) match b) zip c) blend d) join

d- Joining table allows you to match rows from one table with rows on another table.

If you don't use a number type to store numeric data, _______. a) the database will compensate and do what you intend rather than what you said b) the precision of the number will be lost c) you can't store numbers in any type other than numeric d) you need to take additional steps to process the data as a number whenever you use it.

d- Storing numeric data in numeric data types gives you the ability to work with numbers directly, as you might do when you use mathematical operations in queries.

What does normalization help you do? a) reduce redundancy b) organize tables c) improve integrity d) all of the above

d- The normalization process provides a framework to think about how data is organized.

Which role uses an application to query a database and generate a report? a) database designer b) database administrator c) database programmer d) database user

d- They typically access the database via an application. Advanced users may use a query language instead.

________ is a modeling standard intended for software development. a) DCL b) DMV c) SQL d) UML

d- Universal Modeling Language is commonly used for depicting software requirements. Since software data structures are similar to database structures.

When modeling a many-to-many relationship, how should you name the linking table? a) A linking table has no name b) LinkingTable c) using capital letters d) with a combination of the tables it's linking

d- While it's not enforced by the DBMS, using table names like this helps to keep queries readable and remind you what tables are for.

Before you create a database, what do you need to know? a) the names and data types of fields you will use. b) how entries in different tables will be used together c) what tables you'll need for you're data d) all of these answers

d- You can't create your database without knowing the basics of the tables, their columns, and how data will be related.

Determine the maxima for the relationship "Person-Owns-Vehicle". a) one-one b) one-many c) many-one d) many-many

d- a person can own many vehicles and the vehicle can have more than one registered owner.

In "DealerInvoiceNumber 1-1(0)", the second 1 indicates: a) Maxima-Many b) Maxima-One c) Minima-Zero d) Minima- One

d- a relationship has two minima, one for each related entity and are specified as one "1" or zero "0".

In "Passport 1-M(1)", the 1-M indicates: a) Unique and singular b) Not unique and singular c) Not unique and plural d) Unique and plural

d- aka one-many

Determine the minima for the relationship "Flight-ArrivesAt-Airport". a) zero-zero b) one-zero c) zero-one d) one-one

d- all flights must arrive at an airport and all airports have arriving flights.

Which table name follows the best practice? a) Song Lists b) CarModel c) geoLocations d) CheckDeposits

d- at his table name correctly uses upper camel case and a plural form.

Determine the maxima for the Entity-Has-Attribute relationship Entity Airport, attribute AirportCode. a) many-many b) one-many c) many-one d) one-one

d- each airport has at most one airport code and each airport code describes at most one airport.

Determine the maxima for the relationship "Student-Takes-Course". a) one-one b) one-many c) many-one d) many-many

d- each student can take many courses and each course can have many students.

What is a domain? a)a infinite sequence of values each drawn from a fixed domain b) a collection of values or elements with no inherent order c) a named set of tuples, all drawn from the same sequence of domains d) a named set of possible database values.

d- such as integers, dictionary words, or logical values TRUE and FALSE. Equivalent relational term database- Data type, Files- Data type.

When storing the text Mozambique in a column with a data type of VARCHAR(8), what would be saved in the database? a) MOZAMBIQUE b) Mozambique c) Mozambiqu d) Mozambiq

d- the data type can hold at most 8 letters, so the remaining two are not stored.

What is one advantage of using a spreadsheet to store data? a) It protects data from becoming inconsistent b) It ensures that the data is correct c) It protects data from unauthorized changes d) It can help you see gaps in the data

d- when pieces of information are arranged in a consistent way, it becomes easier to see what information you have.

What are the requirements of third normal form? a) No value in a table should depend on only part of a key that can be used to uniquely identify a row b) Values in each cell should be atomic and tables should have no repaying groups. c) Values should not be stored if they can be calculated from another non-key field. d) all of the above

d- while the definition is 'c', third normal form must meet all previous forms as well.

What are the requirements of second normal form? a) No value in a table should depend on only part of a key that can be used to uniquely identify a row b) Values in each cell should be atomic and tables should have no repaying groups. c) Values should not be stored if they can be calculated from another non-key field. d) a and b

d- while the definition is a, second normal form must also meet first normal form as well.