COP 3703 CHAPTER #1
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
