Chapter 3 - RELATIONAL DATABASE DESIGN FUNDAMENTALS

tblCustomer to tblOrders and tblProducts to tblOrders

Based on the tables below, how should the tables be related? 1. tblOrders and tblProducts should be related 2. None of these tables can be related 3. tblCustomer to tblOrders and tblProducts to tblOrders 4. tblCustomer and tblProducts should be related

None

In the following table (artist), which fields have a transitive dependency? artistID artistName... ... countryCode ... ... genreCode ... ... dateAdded ... 1. artistName, dateAdded 2. artistName, genreCode 3. artistName, countryCode 4. None

2NF Explanation Removal of functional dependencies progresses a relation into second normal form.

The normal form in DBMS that does not preserve partial attribute dependencies is: 1. 2NF 2. All normal forms 3. 1NF 4. 3NF

tables which are linked together by a common attribute creating a relationship. Explanation Two tables must have a common attribute to be linked together.

A relational database has 1. tables which relate to each other by data types. 2. tables with relatable uniquely identifiable rows. 3. tables which are linked together by a common attribute creating a relationship. 4. tables which are populated with attributes of all the entities.

it doesn't have transitive dependency among columns

A table is considered to be in third normal form when it is in second normal form and _____. 1. there are no more than ten fields 2. it doesn't have transitive dependency among columns 3. all fields are of the same data type 4. it isn't externally dependent

Yes, if it increases complexity and system resources.

Can the third normal form go too far? 1. Yes, but it's a matter of personal preference. 2. No, it's a perfect solution. 3. Yes, but only in embedded systems. 4. Yes, if it increases complexity and system resources.

string, integer, Boolean Explanation Drink name - Names can consist of letters, numbers or both; a string data type is assigned. Quantity - Quantity indicates a numeric value; an integer data type is assigned. Alcoholic? - The ? indicates a yes/ no answer; a Boolean data type is assigned.

Consider the following Table and its attributes: Drinks Table (Drink Name, Quantity, Alcoholic?) What data types would you assign to the attributes respectively? 1. string integer date/time 2. integer, integer string 3. string, date/time, string 4. string, integer, Boolean

i and ii Explanation Primary keys often are made of characters and numbers

From which class(es) of data are primary keys most often made? i. Character ii. Number iii. Symbol iv. Time 1. i and ii 2. iii and iv 3. ii and iii 4. i, ii, iii, and iv.

Remove the extra Color field Explanation There are two fields for ''color'', and they are very redundant. If we remove one, the table will be in 1NF form.

How can the following table be changed to first normal form? 1. Nothing; it is already 1NF 2. Add a primary key 3. Remove the extra Color field 4. Remove the Part_Name field

Must have an entry in the primary key column for each record Explanation Numbers are only recommended, but there can be no entry with no value as a primary key.

Other than uniqueness, what is another requirement for data to be a primary key? 1. Must be a big number 2. Must have an entry in the primary key column for each record 3. Must be a number 4. There is no other requirement

Normalization Explanation First normal form (1NF) is the first step in normalizing tables. Normalizing tables reduces redundant data and table size. A single table should focus on a single concept and not include mixed information/data, for example, storing Vendor contacts inside a Customer table. Instead, this data should be split and joined.

Reducing redundancy, complexity, and table size is part of the _____ process in database administration. 1. Normalization 2. First normal form 3. Primary key creation 4. Delineation

CustomerZipCode and CustomerCityName

The Customer table has the following fields: CustomerID, CustomerFirstName, CustomerLastName, CustomerZipCode, and CustomerCityName. Which fields have a transitive dependency? 1. CustomerLastName and CustomerFirstName 2. CustomerID and CustomerFirstName 3. CustomerZipCode and CustomerCityName 4. CustomerLastName and CustomerZipCode

Logical

The _____ schema defines the database structure in terms of data, fields, and tables. 1. Physical 2. Logical 3. ODBC 4. Relational

Physical

The _____ schema defines the layout of the database hardware and servers. 1. Physical 2. Relational 3. ODBC 4. Logical

(customer)1-m(invoice)m-m(products) Explanation A customer can place one or more orders. So there can be one or more invoices. An invoice can only belong to one customer but can contain may products. A product can be listed in many invoices.

We are developing a sales database. There are three tables which are shown below. Customer orders are represented in invoices. Invoices can be comprised of one or more products. How would you define the relationship between the customer, orders and products? 1. (customer)m-1(invoice)1-m(products) 2. (customer)1-1(invoice)m-1(products) 3. (customer)1-m(invoice)m-m(products) 4. (customer)1-m(invoice)1-1(products)

Directly identifies another table Explanation Foreign keys link to other tables and records in other parts of the database.

What does a foreign key do? 1. Gives access to another entire database 2. Anything you want it to do 3. Directly identifies another table 4. Directly identifies another column

Conceptual. Explanation At the conceptual design level, which is commonly the first level performed, an ERD typically includes only the entities and known attributes of a database.

What is the lowest design level (the least detail) for an ERD? 1. Logical 2. Physical 3. Metadata 4. Conceptual

Primary key Explanation Primary keys act as absolute identifiers, like an ID number.

What is the main way to absolutely recognize a record within a database? 1. Secondary key 2. Tertiary key 3. Foreign key 4. Primary key

An instance in one table can have linkage to multiple instances in another table.

What is the meaning of a one-to-many (1:m) cardinality? 1. One instance in a table is logically linked to many other instances in the same table. 2. One database table is linked to multiple other database tables. 3. An instance in one table can have linkage to multiple instances in another table. 4. Multiple instances in one table all connect to a single instance in another table.

Parent-child Explanation Tables that are subordinate to others are called child tables, while the main tables are parent tables.

What is the name of the relationship between tables that often happens when using a foreign key? 1. Aunt-niece 2. Sibling 3. Friends 4. Parent-child

Attributes Explanation Databases, at a technical level, consist of entities (tables), attributes (fields), and the relationships between entities.

What is the technical term used to reference data fields in a database table? 1. Rows 2. Attributes 3. Columns 4. Entity

1NF Explanation Keeping field values single satisfies one of the 1NF rules.

When it comes to DBMS, a table in which every field contains only single values, and no identical rows, is said to be: 1. 3NF 2. 1NF 3. unnormalized 4. 2NF

All of the answers are correct Explanation The First Normal Form, Second Normal Form, and Third Normal Form rules must be obeyed in order to avoid database anomalies.

Which of the following comprises database anomalies in DBMS? 1. Same piece of data held in multiple places 2. Columns with the same names 3. Sets of data saved in one field 4. All of the answers are correct

first_name Explanation Proper naming convention deems that attributes consist of lower case letters and words separated by an underscore

Which of the following is an example of proper naming convention of an attribute? 1. FirstName 2. first_name 3. First_Name 4. firstname

A primary key Explanation The primary key is a unique identifier for each record and is required when normalizing tables.

Which of the following is required for first normal form (1NF) tables? 1. A secondary key 2. Similar field names 3. Similar column names 4. A primary key

It is not unique Explanation A ZIP code is shared by many people.

Why would a ZIP code make a bad primary key for an individual in SQL? 1. ZIP codes are actually perfect primary keys 2. It is too short 3. It is too long 4. It is not unique

List out sample data to determine the final layout

You are designing a database schema and you are unsure how some of the tables will relate. What should you do? 1. List out sample data to determine the final layout 2. Keep one table into which you can store all the extra data 3. Join tables together no matter the field types or data 4. Leave as-is and begin adding data to the database

3NF Explanation A table is said to be in the 3NF if it is in 2NF and all non-primary fields are dependent on the primary field.

You have a table in a DBMS that satisfies 2NF. It can be considered in _____ if an attribute is related to another attribute through a second attribute. 1. 0NF 2. 2NF 3. 3NF 4. 1NF

Normal forms Explanation Normal forms is the process of formatting database structural relations to remove anomalies.

_____ specify the actions needed to remove the drawbacks in a DBMS design. 1. Normal forms 2. Anomalies 3. Transitive dependencies 4. Data modifications

Information Explanation A database is a place to store information. Databases can store something as simple as a list of items or as complicated as all the data needed by a large company. Databases contain structured collections of information.

A database is a place to store _____. 1. Paper 2. Spare parts 3. Information 4. Family heirlooms

Blueprint Explanation A database schema is the blueprint that defines the database structure. The schema tells the database engine, the program that runs the database, the labels, structure, interrelationship, and rules to use when storing and retrieving information from a database. The schema provides the framework for the database operations and contents.

A database schema is the _____ that defines the database structure. 1. Mural 2. Blueprint 3. Circuit board 4. Circle

Sorted Explanation A database table can be sorted by different fields depending on what you are looking for. Sorts simplify the process of locating specific information.

A database table can be _____ by different fields depending on what you are looking for. 1. Linked 2. Timestamped 3. Sorted 4. Tagged

The table has 205 rows. Explanation A row in a table is a representation of one single record.

A database table has 205 records. What does this mean 1. The table has 205 rows. 2. The database has 205 columns. 3. The table has 205 fields. 4. The database has 205 data types.

1. Create a new table called CustomerLocation. 2. Add CustomerZipCode and CustomerCity. 3. In the original Customer table, CustomerZipCode is a foreign key to the new table.

How would you remove the following transitive dependency for the table Customer, with fields CustomerID, CustomerCity, and CustomerZipCode? 1. - Create a new table called CustomerLocation. - Add CustomerZipCode and CustomerCity. - In the original Customer table, CustomerZipCode is a foreign key to the new table. -------------------------- 2. - Create a new table called CustomerLocation. - Add CustomerID as the primary key. - Add CustomerCity as a foreign key in the original Customer table. --------------------------- 3. - Create a new table called Customer2 and add all fields. - Join on Customer ID. ---------------------------- 4. - Create a new table called CustomerZip and add only the zip code.

Table Explanation The database schema contains the list of attributes and instructions that tells the database engine how the data is organized and how the components are related. The basic database element is the table. A table is consists of rows of related information. Each row consists of fields of information where data is stored. Field attributes include information and rules that govern the data stored in the field. The field attributes and rules may limit the type of data stored in the field. A field may be defined as a key or may be limited by rules to a set number of characters or numbers.

The basic database element is the _____. 1. Row 2. Key 3. Table 4. Field

Relationships Explanation The cardinality of a database includes the nature and frequency of the relationships between the database's tables.

The term 'cardinality' refers to what aspect of an ER model? 1. Redundancy 2. Duplication 3. Relationships 4. Identification

Units Explanation Under schema theory, knowledge is organized into units. Units of knowledge, or schemata, contain or store information. As such, a schema is a conceptual system for understanding knowledge or a generalized description of the knowledge. A schema contains a description of how knowledge is represented and how it is used.

Under schema theory, knowledge is organized into _____. 1. Databases 2. Tables 3. Units 4. Rows

A unique identifier Explanation The primary key is considered a unique identifier in that it indicates a field that is intended to contain a value that cannot be duplicated.

What is the basic definition of a primary key? 1. A tuple that contains exact matches 2. A unique identifier 3. A naming convention that uses all lowercase letters 4. A key secondary to the first

Primary key Explanation The primary key of a database table is unique to a single instance within that table.

What is the entity attribute that uniquely identifies an instance in a table called? 1. Compound key 2. Secondary key 3. Primary key 4. Foreign key

Social Security Number Explanation Everyone has a specific and unique social security number.

What is the primary key most often used by health insurance companies and banks? 1. Phone number 2. Social Security Number 3. ZIP Code 4. Date of birth

First name Explanation The essential components of a database table include the scheme, attributes, and zero or more tuples. A first name may not be a description of an attribute, in particular, if the table scheme has nothing to do with individuals.

Which of the following is not an essential component of the database table? 1. Tuple 2. Attribute 3. Scheme 4. First name

It can contain zero tuples Explanation A database table can contain zero or more rows, or tuples

Which of the following is true about a database table? 1. It can contain zero tuples 2. It must contain at least one row, or tuple 3. All rows must be in alphabetical order to be considered a table 4. Each field in a tuple must always contain data

They have to be unique Explanation Foreign keys can be unique or they can be the same - primary keys must always be unique.

Which of the following is true about primary keys but not always about foreign keys? 1. Primary and foreign keys have no differences 2. They are used to retrieve data 3. They have to be unique 4. They are used to identify data

data collected about an entity Explanation An entity is anything about which data is collected. An attribute is the collected data used to populate columns in a table.

Which of the following statements is true about an attribute? 1. primary key of the table 2. always has a one to one relationship 3. related rows in a table 4. data collected about an entity

Customer's bank account number Explanation A primary key is defined as a unique identifier. Customers in a database table could have the same first or last names, however no customers will have the exact same bank account number.

Which of the following would be a good choice for data found in a primary key? 1. Customer's home city 2. Customer's bank account number 3. Customer's first name 4. Customer's favorite food

Driver's license number Explanation Each record is assigned a specific and unique number.

Which of these is likely a primary key for the DMV in SQL? 1. Driver's license number 2. Last name 3. Birthday 4. ZIP code

Student_name Explanation Student_name would be the common column between the two tables.

Which of these would likely be used as a foreign key between a table on student enrollment and student grades? 1. Grades 2. Student_name 3. Tuition 4. Student_hometown

ID ... Customer ... State... Website 1025 ... Jane Doe... AZ... Shop.com Explanation Two of the tables do not have primary keys. The other table has redundant data: Even though the State and Location are different, AZ and Arizona repeat the same information.

Which table is in first normal form (1NF)?

It is difficult to modify the database structure once data is entered into it.

Why is it important to design a database schema before adding data into a database? 1. Database schemas are difficult to change before data is entered. 2. It is difficult to modify the database structure once data is entered into it. 3. Databases allow only one change after the schema is designed. 4. Schemas are translated directly into the physical database structure.

Redundant data Explanation The field Part_Name_Number just repeats the data already in the table. This is redundant; as you can get these two fields in a query, don't combine them in the table.

Why is the following table NOT in first normal form (1NF)? 1. Redundant data 2. No foreign key(s) 3. No primary key 4. Repeated column names