ISDS Database Midterm 1
NumberOfBoxes ---------> CookieCost The term of the left, NumberOfBoxes, is called the:
Determinant
An attribute of the supertype may be used to indicate which of the subtypes is appropriate for a given instance. What is this attribute called?
Discriminator
What is a subtype entity?
it is a special case of another entity called its supertype. For example, universities may be classified as public and private universities. STUDENT is the supertype, and PUBLIC and PRIVATE are the subtypes
How are identifying relationships drawn in a relationship?
it is shown as as a solid line connecting two entities.
In the database entity-relationship model, the maximum and minimum cardinalities of a relationship can never be the same. (T/F)
False
Null values are never acceptable
False
Small databases have simple structures.
False
Surrogate keys are usually removed during normalization of databases
False
True or False, a Null value is the same as 0.
False
True or False: All weak entities are ID dependent.
False
UML is a data modeling language
False
Many-to-Many are the most common types of binary relationships in entity relationship modeling: T/F
False (its one-to-many)
BCNF is the highest degree of normalization after 5NF
False, BCNF is a more specific 3NF and before 4NF
In relations, the order of the rows is important, because the order of rows carries information. T or F
False, the order of the rows do should not matter in a normalized relational database
A normalized database (1NF) does not contain any Many-to-Many relationships. (T/F)
False, while a 1NF database would not have a table that contains multiple values, it is possible to define an associative relation that links two tables together in a many-to-many relationship.
T or F: A foreign key must be unique to a specific row in a relational table.
False. A foreign key is nonunique.
T or F: The terms Row and Field are interchangeable.
False. Rows are records and columns are fields.
A column or composite of columns that are a primary key in another table is what type of key?
Foreign key
A relation is in _____ form if it is in BCNF and has no multi-valued dependencies.
Fourth Normal Form
What type of dependencies do the normal forms 1NF, 2NF, 3NF, and BCNF eliminate?
Functional dependencies
A _______________ is one of the most important concepts to understand in database design, and describes a relationship between attributes. An example of this could be noted as: (ItemPrice,Qty) --> TotalCost
Functional dependency
What is the meaning of maximum cardinality?
Maximum cardinality specifies the maximum number of instances of one entity that are allowed to participate in a relationship
Which of the following is NOT a characteristic of relations? A. Each column has a unique name B. Columns contain data about attributes of the entities C. No two rows may be identical D. Cells of the table hold multiple values
D
Data about the structure of a database is called _________.
Metadata
All table will only have 1 Candidate key
False
Candidate key is a sub-part of primary key
False
What is metadata?
Metadata is the descriptive data describing a table in database. It is because of metadata a database is referred as self-describing.
For some relations, modification of the data can have undesirable consequences called _____
Modification Anomalies
A dashed line is used to demonstrate what type of relationship?
Non-ID Dependent relationship
A key that potentially identifies more than one row
Non-unique key
______ is the process of breaking a table or relation with more than one theme into a set of tables such that each one has only one theme
Normalization
What is Normalization?
Normalization is used to eliminate or reduce redundancy in database tables.
____________ means that no data was entered
Null value
What is the determinant in this example? NumberOfHoursStudying --> Grade
NumberOfHoursStudying
What is the purpose of referential integrity constraints?
Referential integrity constraints ensure that the values of a column in one table are valid based on the values in another table.
What is Referential Integrity?
Referential integrity states that every value of a foreign key must match a value of an existing primary key.
Data record facts and figure and the information is knowledge obtained from data. T/F
True
Database Application is the part a part of Database System. True or False
True
Entity instances have identifiers. (T/F)
True
Functional dependency involves determinant attributes
True
Identifiers in entity instances may be unique or non-unique. True of False?
True
Non-identifying relationships are connected with dashed lines
True
Null values are a problem because they are ambiguous. (T/F)
True
Referential integrity constraints make sure the values of a column in one table are valid based on the values in another table. a. True b. False
True
Surrogate key values have no meaning to users and are usually hidden on forms, queries, and reports. (True or False)
True
T/F A weak entity can be NON-ID Dependent.
True
What are the 3 degrees of relationship?
Unary Relationship Binary Relationship Ternary Relationship
Problems that exist when normalizing a table are:
Update Anomalies Delete Anomalies Insert Anomalies
A ________ in a table represents a relationship among a set of values. a) Column b) Key c) Row d) Entry
c) Row
Whenever a pure N: M relationship cannot properly hold attributes that are describing aspects of the relationship between two entities. We use .................................... entity.
associative
The term _______ is used to refer to a row. a) Attribute b) Tuple c) Field d) Instance
b) Tuple
When you join multiple tables, you end up with: a. Normalized Table b. Denormalized Table c. Large Table d. Super Table
b. Denormalized Table
A relation needs to go through a conversion process to be put in 1NF format. a. True b. False
b. False
Which of the following is an example of an ID-Dependent Relationship? a. Advisor to a Student b. Supplier to a Product c. Course to a Department d. Manager to an Employee e. All of the Above
c. Course to a Department
The Student to Courses relationship has what type of maximum cardinality? a. 1:N Relationship b. 1:1 Relationship c. N:M Relationship d. M:O Relationship
c. N:M Relationship
A relational database stores information in _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _.
tables or relations
Null values means:
that no data exists.
In the relational modes, cardinality is termed as: (A) Number of tuples. (B) Number of attributes. (C) Number of tables. (D) Number of constraints
(A) Number of tuples.
. E-R model uses this symbol to represent weak entity set? (A) Dotted rectangle. (B) Diamond (C) Doubly outlined rectangle (D) None of these
(C) Doubly outlined rectangle
A ____ is a set of one or more computer programs that serves as an intermediary between the user and the DBMS. a) Database management system b) Database application c) Application program d) None
(b) Database application
Given the functional dependency B → (C, D), B is? a) In determinant b) Determinant c) Both (a) and (c) d) none
(b). Determinant
In general, a ________ exists when the value of one or more attributes determines the value of another attribute. a) Functional Relationship b) Database relation c) Functional dependency d) None
(c) Functional Dependency
When referring to binary relationships, N:M stands for...(a)one-to-one (b)one-to-many (c)many-to-many
(c) many-to-many ; Note: It is notated N:M instead of N:N because, each entity can have different cardinalities.
What 3 constraints are needed to make a database useful and meaningful?
- Domain integrity constraint - Entity integrity constraint - Referential integrity constraint
When would you choose not to use BCNF?
- If data are never modified or - If data inconsistencies will be easily corrected via normal operation of business
what is a row in database terms?
A row represents a single, implicitly structured data item in a table
Sources of anomalies and its normal forms:
-Functional dependencies - 1NF, 2NF, 3NF, BCNF -Multivalued dependences - 4NF -Data constraints and oddities - 5NF, DK/NF
What are the principal patterns that use ID-dependent entities?
-Multi-valued attribute -Archetype/instance (a.k.a. version/instance) -Association
Databases consist of what four components?
-User data -Metadata -Indexes and other overhead data -Application metadata
What are the four components of a database system?
-user, database application, DBMS, the database
Enterprise-Class Database Systems
1. Have multiple applications 2. Have many tables 3. Are complex in design 4. Involve many users and computers
Personal database systems
1. Have one application 2. Have only a few tables 3. Are simple in design 4. Involve only one computer 5. Support one user at a time
List the steps to Normalize your data set in the correct order: Examine the determinants of the functional dependencies Identify all candidate keys of the relation Identify all the functional dependencies in the relation
1. Identify all candidate keys of the relation 2. Identify all the functional dependencies in the relation. 3. Examine the determinants of the functional dependencies.
1:1 (one-to-one )
A single entity in one entity class is related to a single entity instance in another entity class
Entity Instance
A specific occurrence of an entity within an entity class
What are some examples of sources of requirements help with the Requirements Analysis Stage?
1. User interviews 2. Forms 3. Reports 4. Queries 5. Use Cases 6. Business Rules
What are the four components of a database system?
1.Users 2.Database Application 3.DBMS 4.Database
Relationships of degree ___ are most common
2
How many candidate keys do we have in this EMPLOYEE relation? EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)
3 Candidate keys and they are EmployeeNumber, Email, and the composite key which is (FirstName, LastName, DepartmentName)
In which option, both keys can determine all of the other columns in a relation? A. candidate key, primary key B. composite key, primary key C. surrogate key, foreign key D. foreign key, composite key
A
The purpose of a database is to: A) help people keep track of things. B) store data in tables. C) create tables of rows and columns. D) maintain data on different things in different tables. E) All of the above.
A
What is a BCNF?
A BCNF should meet the requirements of 3NF and have no overlapping candidate keys. When there are no composite candidate keys, 1NF is in 2NF, and 3NF is in BCNF. A BCNF is when every determinant is a candidate key. In a read-only database, BCNF may not be desirable.
What is a Recursive Relationship?
A Recursive Relationship is when an entity has a unary relationship with itself.
What is a data model?
A data model is like a blueprint represented by an Entity Relationship ER model. The ER model is made up of entities, attributes and relationships.
What is the role of the Database Management System (DBMS)?
A database management system (DBMS) serves as an intermediary between database applications and the database. It manages and controls database activities. It also creates, processes and administers the databases it controls.
What is a database schema?
A database schema is the design on which a database and its associated applications are built.
What is functional dependency?
A functional dependency exists when value of an attribute determines the value of another attribute.
What is a composite key?
A key that contains one or more attributes. Ex. (LastName, Department)
When a relation is in 2NF?
A relation is in 2NF only if it is in the 1NF and all the non key attributes are determined by the primary key. A relation in the 2NF should not have any partial dependencies.
What is a well-formed relation?
A relation, which every determinant must be a candidate key. ( in case it can't be well-form with current attribute, it should be broken down to one ore more relation.)
What classifies an entity as a weak entity?
A weak entity cannot exist without the existence of another entry.
What is key? a. A unique identifier to detect a particular row b.A combination of row and column to detect a particular record in row c. A &B d. None of the above
A&B
When SQL statements produce duplicate rows Distinct keyword should be used to force uniqueness? T or F
A) True
Meta data means the description of data in a data base. True or False
A)True
If attribute A determines both attributes B and C, then it is also true that: A. A → B. B. B → A. C. C → A. D. (B,C) → A.
A. A → B.
Which of the following is considered alternative terminology for "column" when referring to relational tables? A. Field B. Relation C. Tuple D. Record
A. Field
Other than a relational database, what is another commonly used database model? A. NoSQL database B. Keyless database C. Read-only database D. Referential database
A. NoSQL database
What are modification problems are also called?
Anomalies
All tables are relations but not all relations are tables. T or F
Answer, False
What is the highest form of Normalized Category? A)4NF B)6NF C)DK/NF D)BCNF
Answer. C
A functional dependency is a relationship between or among ______
Attribute
In which situation, normalization the tables may not be a good idea. A. The table has insertion anomalies. B. The data are never modified or data inconsistencies will be easily corrected. C. A non-key attribute can be determined by an attribute. D. The table has overlapping composite candidate keys.
B
When remove a name in a relation, you find that you lose a lot of other information not just a name. Which Modification Anomalies must the relation have? A. Insertion anomaly B. Deletion anomaly C. Update anomaly D. None
B
Which one is NOT the advantages of normalization? (Choose all that apply) a) Eliminate modification anomalies b) Simplify SQL required for subqueries and joins c) Save file space d) Faster applications
B & D
What is not true about a subtype entity? A) Each subtype is ID-independent on the supertype. B) We use a dashed line to present an ID-dependent subtype entity. C) An exclusive subtype is also referred to as disjoint. D) A discriminator is used to determine which subtype is appropriate for a given instance.
B) We use a dashed line to present an ID-dependent subtype entity.
If the following Functional Dependencies for a table exist what Normal Form is the Table In? (F,E)->(A,B,C,D) C->D A. 1NF B. 2NF C. 3NF D. BCNF
B. 2NF
An identifier can be a _________ A. Entity Instance B. Relationship C. Composite D. Data Model
B. Composite
Which item below is not an element of the E-R Model? A. Entity B. Entity Instance C. Attribute D. Identifier
B. Entity Instance
What is NOT a characteristic of a relation? A. Each column has a unique name B. The order of the column is important C. Columns contain data about attributes of the entity D. Cells of the table hold a single value
B. The order of the column is important
Which one of these is not a characteristic of a relation? A. Each column has a unique name B. The order of the columns is important C. The order of the rows is unimportant D. No two rows may hold identical sets of data values
B. The order of the columns is important. The correct characteristic is that the order of the columns is unimportant.
Why is a many-to-many relationship abbreviated "N:M" instead of "N:N"?
Because the many of "N" need not equal the many of "M". Two different variables are important.
In a SQL Server database which of the following statements are *never* TRUE: A. True = True B. 0 = 0 C. NULL = NULL D. False = False E. NULL <> NULL
Both C "NULL = NULL" and D "NULL <> NULL" are never true. You cannot apply an equals or not equals operator to a NULL value. the result is always NULL (neither TRUE nor FALSE)
A relation where every determinant is a candidate key is in what form?
Boyce-Codd Normal Form (BCNF)
When creating a data model, we need to be on the lookout for _______ ____ that constrain data values and the processing of the database.
Business Rule
According to the text, what is the naming convention for a table regarding student enrollment? A. StudentEnrollment B. student_enrollment C. STUDENT_ENROLLMENT
C
Which of the following sentences is right? A. Not all the relation which have a primary key is in 1NF. B. Although the primary key of a relation is a single-attribute primary key, we still cannot ensure that the relation is in 2NF. C. If a table is in 3NF, it must be in 2NF. D. If a table is in BCNF, it no need to be normalized any more.
C
What is not true about ID-dependent entity? A) The minimum cardinality from the ID-dependent entity to the parent is always one. B) Some entities that are weak are not ID-dependent. C) An ID-dependent entity must be created and then linked to the parent entity. D) The relationship between an ID-dependent entity and its parent is called an identifying relationship.
C) An ID-dependent entity can be created before linked to the parent entity.
If attributes A and B determine attribute C, then it is also true that: A. A → C. B. B → C. C. (A,B) is a composite determinant. D. C is a determinant.
C. (A,B) is a composite determinant
Which of the following is false about a table that is a relation? A. Each column has a unique name B. No two rows may be identical C. Cells of the table can hold multiple values D. The order of the column is unimportant.
C. Cells of the table can hold multiple values
Which of the following is NOT a function of a DBMS? A. Create supporting structures B. Enforce rules C. Execute Application logic D. Maintain database structures
C. Execute Application logic
A missing value is often referred to as _____ A. Error Value B. Inconsistent Value C. Null Value D. Data Value
C. Null Value
When looking to define a primary key, you should consider all of the: A. composite keys. B. determinants. C. candidate keys. D. foreign keys.
C. candidate keys.
The primary key is selected from ______
Candidate Key
please list the synonymous for Column
Column - Field - Attribute
A combination of two or more columns used to identify particular rows in a relation is a _______________
Composite candidate key
What is the common difference between 1NF/2NF and 3NF/BCNF?
Compound keys
Carnalities are represented by what symbol?
Crow's Foot Symbols
What is the conventional method for ER (Entity Relation) Diagrams we use in class?
Crows Foot Database Notation
Symbol closest to the entity shows _____ cardinality.
Maximum
What issues can normalization of a database prevent? A) Update anomalies B) Deletion anomalies C) Insert anomalies D) All of the above
D) All of the above
Which of the following is defined as an entity that has a unary relationship to itself? A) Inclusive relationship. B) HAS-A Relationship. C) The For-Use-By Pattern. D) Recursive relationship.
D) Recursive relationship.
An entity that is not weak is called A) Super weak B) Ionic bond C) 24 D) Strong entity
D) Strong entity
What is the first step in normalizing relations? A). Place the columns of the functional dependency in a new relation of their own. B). Identify all the functional dependencies in the relation C). Examine the determinants of the functional dependencies D). Identify all the candidate keys of the relation.
D). Identify all the candidate keys of a relation.
Which is not a part of a Personal Database System? a) User b) Database Application c) Database d) End-user Experience e) Database Mangement System (DBMS)
D, End-User Experience is not a part of the Personal Database System structure.
Which one of the following is NOT a reason for a null value? A. The value is inappropriate B. The value is appropriate but unknown C. The value is appropriate and known, but no one has entered it into the database D. All the above are valid reasons for a null value
D. All the above are valid reasons for a null value
Which of the following is not a modification anomaly?
D. Duplicate Anomaly
A ________ _________ is a representation of the content, relationships, and constraints of the data needed to support the system requirements.
Data model
Data Model
Data model is blue print used to develop a data base. For example, to construct a building it is necessary to have plan first. Without plan construction cannot begin. Same way to develop at database it is necessary to have a data model first which serves as guide to developers.
What is the intermediary between users and Database Management Systems (DBMS).
Database application
Explain the process of database design?
Database design is the creation of proper structure of database tables, the proper relation between tables , appropriate data constraints and other structural components of database
If A -- > (B, C), then A -- > and A -- > C is true. What rule does this functional dependency display?
Decomposition rule
What are three types of data anomalies?
Deletion anomalies, update anomalies, and insertion anomalies.
How should you extract data from an operational database to create a read-only database?
Denormalize the data by joining the data and storing the joined result as a table.
Entity Class
Description of the structure and format of the occurrences of the entity
Determinant
Determinant is attribute which determines the value of another attribute in same table. For example in a table PRODUCT(ProductName, Quantity, Price) in this table price of quantity is dependent on value of Quantity. Hence Quantity is determinant
In Functional Dependency, _________ are the attributes that impact another attribute.
Determinants
The common standard data model
E-R Data Model
What are the three major steps in database development process? A). 1NF B).Requirements stage C). System design stage D). Implementation Stage E). Both A,B and C.
E. A, B, C are the three major stages in database design.
What is an Exclusive Subtype?
Entities are said to be exclusive when a Supertype entity relates to maximum one Subtype entity.
An _______ class is a collection of entities of a given type.
Entity
An _____ ___________ of an entity class is the occurrence of a particular entity.
Entity instance
If a supertype relates to at most one subtype this is known as being?
Exclusive
T or F. A weak entity can exist in the database without another type of entity existing in that database.
F. A weak entity CAN'T exist in the database without another type of entity existing in that database.
The term relation is ALWAYS interchangeable with the term table.
FALSE
A "composite identifier" is defined as a composite attribute that is an identifier. (T/F)
False
A Null value is presented as zero in a field. a. True b. False
False
A One to Many relationship is an example of a ternary relationship. (T/F)
False
A recursive relationship is used to connect subtype entities to their supertypes. (True/False)
False
What are the 3 common variations of the E-R model?
IE Crows Foot, IDEF1X, and UML
If a supertype relates to one or more subtypes this is knows as being?
Inclusive
What does an SQL do?
It creates, processes and queries databases and their tables.
Which of the following is false about Surrogate keys Numeric Often hidden on forms Long Ideal primary keys
Long
The Crow's Foot Symbols are commonly used in E-R diagram, please list the symbol types and their meanings.
Mandatory - one, exactly one Mandatory - many, one or more Optional - one, zero or one Optional - many, zero or more
In general, each relation should have ________. a. one and only one theme. b.one to many theme c.Many to one theme. d.Many to many theme. e. All of the above.
One to only one theme.
What are three types of cardinality?
One-to-One One-to-Many Many-to-Many
What are the 3 types of Maxmium Cardinality?
One-to-one (1:1) One-to-many (1:N) Many-to-many (N:M)
A primary key is combined with a foreign key creates: (A) Parent-Child relationship between the tables that connect them. (B) Many to many relationships between the tables that connect them. (C) Network model between the tables that connect them. (D) None of the above.
Option (A)
In a relation (A) Ordering of rows is immaterial (B) No two rows are identical (C) (A) and (B) both are true (D) None of These
Option (C)
Which of the following is a group of one or more attributes that uniquely identifies a row? 1.Key 2. Determinant 3.Tuple 4.Relation
Option 1 : Key
One solution to the multivalued dependency constraint problem is to: 1. Split the relation into two relations, each with a single theme 2. Change the theme 3. Create a new theme 4. Add a composite key
Option 1: Split the relation into two relations, each with a single theme
When the values in one or more attributes being used as a foreign key must exist in another set of one or more attributes in another table, we have created a(n): 1.Transitive dependency. 2.Insertion Anomaly 3.Referential integrity constraint 4.Normal form
Option 3. Referential Integrity Constraint
A database in which a researcher would be the only user of the application is known as:
Personal Database System
If a relation is in parenthetical format, the column names that is underlined is a ________ key and the column name italicized is a ______ key.
Primary key and Foreign key
Row term is used to understand the term _______
Record
In the original E-R model, what is represented by a diamond?
Relationship
What is a Relationship degree?
Relationship degree defines the number of entity classes participating in the relationship: Degree 2 is a binary relationship. Degree 3 is a ternary relationship
What are the 3 stages of database development?
Requirements Analysis Stage Component Design Stage Implementation of Database Stage
please list the synonymous for Row.
Row - Record - Tuple
Which of the following is not one of Characteristics of a Relation? Rows contain data about attributes Cells of the table hold a single value Each column has a unique name The order of the rows is unimportant
Rows contain data about attributes
All relations are tables, but not all tables are relations.
TRUE
Please list the synonymous for Table.
Table - File -Relation
For this class, what is the appropriate capitalization rules when describing tables and fields?
Table names are all caps with underscores like TABLE_NAME. Field names are camel-case like SomeFieldName.
Which component of a database system is responsible for processing the database?
The database management system
What is the minimum cardinality?
The minimum number of entity instances that must participate in a relationship instance. [0] is optional, [1] is mandatory.
What is the primary key of a subtype?
The primary key of the respective supertype.
Please define the terms of identifying relationship and non-identifying relationship.
The relationship between a strong entity and a weak ID-dependent entity is an identifying relationship. The relationship between a strong entity and a weak non-ID-dependent entity is a non-identifying relationship.
Why are surrogate keys hidden on forms, query results, and reports?
The values of the surrogate primary key will have no inherent meaning to the user.
Please choose the correct statement about anomalies There are more than two anomalies Anomalies are knowns as metadata One to many (1: N) is one of two anomalies Entity instance of an anomaly is the occurrence of a particular entity
There are more than two anomalies
What is the main purpose of a database and the most commonly used type of database?
To help people keep track of things, the relationship database.
(T or F) A Nonrelation table can have more than one value in a cell.
True
A database is often compared as a spreadsheet.
True
A primary key can also be a composite key
True
A referential integrity constraint limits the values of a foreign key. (T/F)
True
A relation in 1NF is automatically in 2NF where there are no composite candidate keys. True or False?
True
The identifiers of a supertype and all of its subtypes are the same attributes. (True or False)
True
To illustrate minimum cardinality, one way is to place a hash mark across the relationship line to indicate that an entity must exist in the relationship and to place an oval across the relationship line to indicate that an entity might or might not be in the relationship
True
True or False: A primary key can be identified with an underline.
True
True or False: All primary keys are composite keys.
True
True or False: Composite keys, like one-column keys can be unique or non-unique.
True
Unary relationship is synonymous to recursive relationship
True
An identifying relationship is reflecting the relationship between a weak entity and strong entity with a solid line. True or False
True.
What is an entity whose identifier includes the identifier of another entity called?
Weak entity
The tuples of the relations can be of ________ order. a) Any b) Same c) Sorted d) Constant
a) Any
A relational database consists of a collection of a) Tables b) Fields c) Records d) Keys
a) Tables
How many themes does a table hold? a) 1 b) 3 c) 10 d) none
a) one
During which of these three stages is the E-R diagram created? a. Requirements Analysis Stage b. Component Design Stage c. Implementation Stage
a. Requirement Analysis Stage
Can a relation be both in 3NF and BCNF at the same time? a. True b. False
a. True
It is a common design practice to use the Primary Key of a table as the Foreign Key of another table. a. True b. False
a. True. Most foreign keys exist as primary keys of another table. A constraint or relationship links the tables together through these keys.
Which of the following is an example of an insertion problem? a. Suppliers and Orders from Suppliers are stored in the same table, an order from a supplier that shows up once in the table is deleted. b. Students and Advisors are stored in the same table, the advisor that previously advised student with the last name starting with A though M, now advises students with a last name starting with N through Z. c. Students and Courses are stored in the same table, a new course is entered into the table, but no student has been allowed to sign up for it yet. d. A new student is inserted into a Students Table e. All of the above
c. Students and Courses are stored in the same table, a new course is entered into the table, but no student has been allowed to sign up for it yet.
When a university needs to record the data of teachers in their course, what is the degree of the relationship? a. Unary Relationship b. Binary Relationship c. Ternary Relationship d. None of the above
c. Ternary Relationship
What is a record (or tuple)? a. Characteristic of type of data for an object b. Unique instance of an object c. A complex design d. None of the above
c. Unique instance of an object
_____________ is a key that contains two or more attributes.
composite key
____________ is a primary key from one table placed into another table.
foreign key
A __________ exists when the value of one or more attributes determines the value of another attribute.
functional dependency
Define SQL
international standard for creating, processing, and querying data and their tables
In almost all cases, normalization of tables in a _____database is a bad idea.
read-only
The purpose of a database is _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _.
to keep track of things
When do you use a solid line in an ER diagram?
to show relationship between a strong and ID dependent weak entity
What kinds of key are unique in the database?
•Candidate Key •Composite Key •Primary Key •Surrogate Key
Why database is a self-describing collection of related records?
•The database itself contains the definition of its structure •Metadata are data describing the structure of the data in the database
What are two other names for a row?
Record and Tuple
Null values do not cause problems and are not ambiguous (T/F)?
False
We should apply the solid line for relationship between strong entities.
False
True or false? A column contains data that represents many attributes of an entity.
False. A row contains data that represents many attributes of an entity.
Another common term that used to describe a column is _____?
Field
What are the other two terms used to describe a column?
Field and Attribute
What are the other two terms used to describe a table?
File and Relation
A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table
Functional Dependancy
A relationship between attributes in which one attribute(or group of attributes) determines the value of another value in the same table.
Functional Dependency For Example: (CookiePrice, BoxQty) --> BoxPrice
What are some variations of the E-R data models?
Information Engineering Crow's Feet (IE Crow's Feet), Integrated Definition 1 Extended (IDEF1X), and Unified Modeling Language (UML). IDEF1X is the most difficult to understand.
What does a null value indicate?
It indicates that the item is unknown or it has not been filled in yet.
What is an identifying relationship?
It is a relationship between an ID Dependent entity and its parent
When was the E-R Data Model created?
It was developed in the 70s
What is SQL used for?
Many database applications use SQL to retrieve, format, report, insert, delete, and/or modify data for users
What is data that describes data in a database?
Metadata (examples:Names of tables, names of columns and the tables to which they belong, properties of the tables and columns)
What is the minimum number of entity instances that MUST participate in a relationship instance?
Minimum cardinality
What are the three types of Cardinality Relationships?
One to One (1:1) One to many(1:N) Many to Many(N:M)
________is a relationship in which a single entity instance of one type is related to a single entity instance of another type.
One-to-one
The one to many relationship is also referred to as a _____-_____ relationship
Parent-Child
A ________ is used to limit the possible values of a(n) foreign key.
Referential integrity constraint
___________ ________ are associations among entity classes, and _________ _______ are associations among entity instances.
Relationship classes, relationship instances.
What are the characteristics of a relation?
Rows contain data about entity cells hold single value columns contain data about attributes of the entities no duplicate rows entries in single column are of same type order of rows and columns are unimportant every column has a unique name.
A Database is a ______ collection of related records
Self-Describing
An entity that is "not" weak is called a _____Entity.
Strong
A candidate key is ALWAYS a unique key.
TRUE
What is done to avoid confusion when columns in different relations have the same name?
The column name is preceded with the relation name followed by a period. Because relation names are unique within a database, and because column names are unique within a relation, the combination of relation name and column name uniquely identifies every column in the database.
What is the difference between the entity class and the entity instance?
The entity class is the description of the entity as a whole while the entity instance is the particular occurrence of that entity.
What are the characteristics of a Relation?
1. Rows contain data about an entity 2. Columns contain data about attributes of the entity 3. Cells of the table hold a single value 4. All entries in a column are of the same kind 5. Each column has a unique name 6. The order of the columns is unimportant 7. The order of the rows is unimportant 8. No two rows may hold identical sets of data values
Name three sources for databases.
-from existing data -from new systems development -from the redesign of existing databases.
What are the 4 elements of the E-R model?
1) Entities: something that users want to track. 2) Attributes: describes the entity's characteristics 3) Identifiers: attributes that name or identify entity instance 4) Relationships: entities that can be associated with one another.
What are the three common variations of the E-R model?
1) Information Engineering (IE) Crow's Foot model. 2) Integrated Definition 1, Extended (IDEF1X) 3) Unified Modeling Language (UML)
What are the three major stages of the database development process?
1) Requirement analysis stage. 2) Component design stage. 3) Implementation stage.
What are the three modification issues?
1. Deletion problems 2. Update problems 3. Insertion problems
What are the two design principles for what we can call a well-formed relation?
1. Every determinant must be a candidate key 2. Any relation that is not well formed should be broken into two or more relations that are well formed.
List the steps for Normalization of a database
1. Identify all the candidate keys 2. Identify all the functional dependencies 3. Examine the determinants of the functional dependencies. 4. Repeat step 3 as many times as needed until every determinant of every relation is a candidate key.
What is referential integrity constraint?
A relationship constraint on foreign key values. Referential integrity constraint specifies that the values of a foreign key must be a subset of the values of the primary key to which it refers. Example: The primary key (Student ID) in one table must match up to another table's foreign key (Student ID) to create a relationship.
What is a recursive relationship?
A relationship that has only one entity that is related to itself. It is also called unary relationship. An example is a User entity containing data about different user levels - Analyst, Supervisor, Manager
What is a surrogate key?
A unique, numeric value that is added to a relation to serve as the primary key. For example, adding a Violation ID to a table when there are no proper candidate keys.
What is a null value?
A value that has not been provided.
What is an ID-Dependent weak entity?
A weak entity that cannot exist without its parent entity. For example: an APARTMENT simply cannot exist unless a BUILDING exists for that APARTMENT to be part of.
1) A database may contain _____________ A) tables B) metadata C) triggers D) stored procedures E) All of the above
All of the above
What is a relation design solution when entities are in a many to many relationship?
An association entity can be formed with primary keys from both the entities to represent the many to many relation.
The concept that states that every value of a foreign key must match a value of an existing primary key is: A. Foreign Key Constraint B. Relational Integrity Rule C. Foreign Key Integrity Rule D. Referential Integrity
D. Referential Integrity
What does DBMS stand for and what are some examples of DBMS?
Database management system. Examples include Microsoft SQL Server, Oracle Corporation's MySQL, Oracle Corporation's Oracle Database, IBM's DB2
What is a degree of a relationship?
Degree is the number of entity classes involve in a relationship.
What's the degree of relationships?
Degree of relationship refers to the number of participating entities in a relationship. There are three types of degrees: unary, binary, ternary.
Given the functional dependency F → (G, H), F is a(n) ________. A) independent variable B) dependent variable C) determinant D) none of the above
Determinant
An IE Crow's Foot can depict a One-and only-One relationship. (T/F)
False
What are two example of identifier types?
Uniqueness and Composite
What is the fundamental layout behind a database?
User Application > Database Management System > Database
When should the associative entity be used?
Whenever a pure N:M relationship cannot properly hold attributes that are describing aspects of the relationship between two entities.
How is a nonidentifying relationship drawn in a relationship?
With a dashed line( no ID-dependent)
Can a determinant of a functional dependency be unique in a relation? (YES/NO)
YES
What is a composite key?
a key that contains two or more attributes. For example, the Name key contains FirstName and LastName
What is a candidate key?
a key that uniquely identify each row in a relation
In an E-R diagram, a hash mark across the relationship line indicates ___________.
a relationship cardinality of one (and therefore also mandatory). In addition/side note: a relational line composed of all hased marks (dotted line) indicates a strong entity/table.
When a primary key is only possible with a large number of attributes, the best thing to do would be to: a) Form a surrogate key b) Keep the "original" primary key c) Combine other relations in the database together in order to increase the possible combination of keys d) Form an alternative key
a) Form a surrogate key
What are the four components of a Database System?
a. Users b. Database Application c. Database Management System (DBMS) d. Database
What is an ID-dependent entity?
an entity that must have a supertype (independent entity) relationship to exist. EX. Must have BUILDING to have APARTMENTS.
Modification problems are also called _ _ _ _ _ _ _ _ _.
anomalies
In order for a table to be normalized under the Boyce-Codd Normal Form, the table must: a) have at least one determinant as the candidate key b) Have no null values c) have a composite key d) Have every determinant as a candidate key
d) Have every determinant as a candidate key
_____________ is a unique, numeric value that is added to a relation to serve as the primary key.
surrogate key
A ________ entity is an entity that cannot exist in the database without the existence of another entity.
weak
