COP 3703 CHAPTER #1

Ace your homework & exams now with Quizwiz!

This type of attribute cannot be subdivided

simple

Also known as bridge entities, these entities are used to implement M:N relationships and are composed of primary keys of each of the entities to be connected.

ssociative

is a logically connected set of one or more fields that describes a person, place or thing.

record

characteristics of well-formed relations (tables)

- each table represents a single subject- - no data item will be unnecessarily stored in more than one table (minimum controlled redundancy) - all nonprime attributes in a table are dependent on the primary key, the entire key, and nothing but the key - each table is void of insertion, update, and deletion anomalies

Normalization

process for evaluating and correcting table structures to minimize data redundancies- reduces likelihood of data anomalies

A supertype can ONLY have subtypes.

false

Attribute B is _____________ on A if all rows in the table that agree in value for A also agree in value for B.

functionally dependent

This type of join only returns rows meeting the specified criteria.

inner join

second normal form (2NF)

normal form where tables...- are in 1NF- have no partial dependencies

third normal form (3NF)

normal form where tables...- are in 2NF- have no transitive dependencies

Boyce-Codd normal form (BCNF)

normal form where tables...- are in 3NF - every determinant is a candidate key(special case of 3NF)

first normal form (1NF)

normal form where tables...- are in table format- have no repeating groups- the primary key is identified- all attributes are dependent on the primary key

What type of value is NOT permitted in a primary key field?

null

Strong (identifying) relationship

Primary key of the related entity contains a primary key component of the parent entity

Denormalization

Produces a lower normal form. Results in increased performance and greater data redundancy

Which generation of data model was used mainly on IBM mainframes and managed records, not relationships?

First

Physical independence

Changes in physical model do not affect internal model.

Data quality

Promoting accuracy, validity, and timeliness of data.

Which normal form has no transitive dependencies?

Third

ways to improve DB design after normalization

- evaluate PKs, use of surrogate keys - evaluate naming conventions - refine attribute so they are atomic - identify any new attributes & relationships - maintain historical accuracy - evaluate using derived attributes

steps to convert to 1NF

1) Eliminate the repeating groups 2) Identify the primary key 3) Identify all dependencies

Ernie comes to you for help on an ER Model that he is creating. He is looking at the manufacturing process, and the relationship that exists between products and the bins that they are stored in. What would most likely be the relationship between the products and the bins? Products - Bins

1:M

If Vincent van Gogh hired you to create a database to keep track of his paintings, what would the relationship be between Vincent and his paintings? Vincent -- Painting

1:M

If you were designing a database to record the transaction that takes place between a customer and a "store", what would the likely relationship be between the customer and the store?

1:M

Students in COP3703, and the teacher is an example of this type of relationship.

1:M

Teacher to Students is an example of this type of relationship.

1:M

You have started normalizing an ERD, and notice that one of the relations has attributes that are only partially dependent on part of a composite primary key. This means that the table can only be in ____ normal form.

1st

Normalization is normally done to ___ normal form.

3 rd

Which normal form says that no multiple sets of multivalued dependencies exist.

4th

Using a customer's __________ would be a good choice for a primary key.

customer-id

This is symbolized by a circle over a single line in an entity relationship diagram. It signifies that the supertype does NOT have to be one of the specified subtypes.

Partial Completeness

Business intelligence

Captures and processes business data to generate information that support decision making.

This is a "virtual" entity type used to represent multiple entities and relationships in the ERD.

Cluster Entity

Class

Collection of similar objects with shared structure and behavior organized in a class hierarchy.

This key is comprised of more than one attribute

Composite

When you set up an Entity Relationship Diagram, and have a many-to-many relationship, this entity is created to avoid problems inherent to a many-to-many relationship.

Composite Entity

Entity Integrity

Condition in which each row in the table has its own unique identity.

Object

Contains data and their relationships with operations that are performed on it. The basic building block for autonomous structures. Abstraction of real-world entity.

Entity subtype

Contains unique characteristics of each entity subtype. There must be different, identifiable kinds of the entity in the user's environment. The different kinds of instances should each have one or more attributes that are unique to that kind of instance

This type of attribute may be calculated from other attributes

Derived

Data anomaly

Develops when not all of the required changes in the redundant data are made successfully.

Data inconsistency

Different versions of the same data appear in different places.

Also called nonoverlapping subtypes, this type of subtype contains unique subset of supertype entity set.

Disjoint

steps to convert to 2NF

Eliminate partial dependencies by: 1) Make a new table for each part of the composite key 2) Put dependent attributes into new tables 3) Keep determinants in Orig table as foreign keysOrig table now has only those attributes dependent on entire key.

What is the first step in normalizing a table/entity?

Eliminate the repeating groups

steps to convert to 3NF

Eliminate transitive dependencies by: 1) Make new table(s) with each determinant attribute as a primary key. 2) Put dependent attributes into new tables. 3) Keep determinants in orig table as foreign keysOriginal table attributes are now only dependent on the entire primary key

Full functional dependence

Entire collection of attributes in the determinant is necessary for the relationship.

A supertype entity can contain as many as ____ subtype entities.

No define Limit

inventory would be this type of object in an Entity Relationship Diagram.

Entity

It is possible to make good decisions without having good information?

False

SQL is a proprietary language that is used with Microsoft only produtcs.

False

This occurs when you have one entity in two 1:M relationships to other entities, thus producing an association among the other entities that is not expressed in the model.

Fan Trap

Structural independence

File structure is changed without affecting the application's ability to access the data

Customer number is the primary key in the customer table. Customer number is also used in the Order table. What is the key called in the order table?

Foreign Key

You are designing an ERD for a local hospital to redo their database. You have identified a number of entities that you think could be subtypes. From these subtypes you need to create a supertype that will connect these subtypes. What is this type of process called?

Generalization

Entity supertype

Generic entity type related to one or more entity subtypes. Contains common characteristics.

Which type of join operation returns not only the rows matching the join condition, but also the rows with unmatched values?

Outer Join

When you can change the internal model without affecting the conceptual model, you have

Logical independence

Which relation cannot be implemented in the relational model, properly?

M:N

You are designing a new database and need to create an Entity Relationship Diagram. Two of the entities are employees and benefits. What is this type of relationship

M:N

You are designing a new database and need to create an Entity Relationship Diagram. Two of the entities are employees and benefits. What is this type of relationship?

M:N

When would you use a composite primary key?

M:N Relationship

Which ONE of the following DBMS systems does not permit distributed data?

MS Access

The M:N relationship specifies that there is a ______to ______ relationship between entities.

Many, Many

Outer join

Matched pairs are retained and unmatched values in the other table are left null

The length of a specific data field or its format (char, vchar, num, etc.) is an example of this type of data.

Metadata

__________ is a real-world identifier used to uniquely identify real-world objects.

Natural Key

What is wrong with the following SQL code? Select Customer_Name, Customer_Address From CustomerWhere Customer_Zip = 32765 Order By Customer_Name;

Nothing, It is fine.

What value is NOT permitted as a primary key?

Null value

Which "specialization" type specifies that the supertype can have multiple subtypes?

O

Inheritence

Objects inherits methods and attributes of parent class.

Design trap

Occurs when a relationship is improperly or incompletely identified. When it is represented in a way not consistent with the real world.`

You have been hired by XYZ Corp to develop a data model for their human resources department. You are working on the model and determining what the relationship would be between employee and employee dependents. What would the relationship be?

One to Many (optional)

Inner join

Only returns matched records from the tables that are being joined

Which type of database supports a company's day-to-day functions?

Operational database

Jennifer is updating the database for her company. She wants to delete customer records that have not made a purchase from more than 5 years ago but when she tries the database tells her that she can't because she must first delete the customer sales transactions records first. What is this referred to?

Referential Integrity

________________ defines theoretical way of manipulating table contents using relational operators

Relation Algebra

Which data model type was developed in the mid 1970's and is still used to this day?

Relational

Class hierarchy

Resembles an upside-down tree in which each class has only one parent

This type of join only returns rows meeting the specified criteria.

Rollback

The ________ is the conceptual organization of the entire database as viewed by the database administrator.

Schema

If you wanted to find all instances of a particular product that has the characters nuts in it, which command would find all instances of the word Nuts in the Product_Name field in the Products table and list all of the other fields in the table Product_Name?

Select * From Products Where Product_Name = 'nuts';

Which ONE of the following commands will pick all data from the table Sales and sort it by Sales_Date, from low to high?

Select * From Sales Order by Sales_Date ASC;

Joe needs to add an additional field to the organization's database. However, if he does, this will require that the applications that interact with the database will not longer function. This is an example of what?

Structural dependence

You would this as a primary key when you don't have an attribute that makes sense or is a good choice for a primary key.

Surrogate

When one attribute is dependent upon another non-key attribute it is called this type of dependency?

Transitive

Which type of dependency exists when there are functional dependencies such that X -> Y, Y -> Z, and X is the primary key?

Transitive

This occurs when there is an indirect relationship that causes a functional dependency. For example, "A -> C" is a transitive dependency when it is true only because both "A -> B" and "B -> C" are true.

Transitive Dependency

A DBMS promotes and enforces integrity rules. These include minimizing redundancy and maximizing consistency.

True

The _______ statement combines rows from two or more queries without including duplicate rows.

Union

This is data that exist in their original (raw) state, ie. the format in which they were collected.

Unstructure

Functional dependence

Value of one or more attributes determines the value of one or more other attributes.

A _______ relationship between entities exists if the primary key of the related entity does not contain the primary key component of the parent entity.

Weak

represents data elements in textual format.

XML

What is a "tuple"?

a roe in a relation

Which ONE of the following attribute types can be sub-divided?

composite

The entity "cars" would be this type of entity if another entity called "vehicles" existed in an ER-Diagram.

subtype?

A table is in 3rd normal form when it contains no transitive dependencies.

true

The order of the rows and columns is immaterial to the DBMS.

true


Related study sets

CHAPTER 28 - FLUID AND ELECTROLYTES - PrepU

View Set

Life Insurance Underwriting and Policy Issue

View Set

Session 16 - Pay Structure Design

View Set

Nur 210 Cultural Diversity Quiz Practice

View Set

327 Chapter 7: Pain Management Q's

View Set