Flashcards
What is an Entity Relationship Model (ERM)?
A data model that describes relationships among entities at the conceptual level.
What is an Entity Relationship Diagram (ERD)?
A diagram that depicts an ERM’s entities, attributes, and relations.
What are the five major parts of a database management system (DBMS)?
Hardware, software, people, procedures, and data.
What is a hierarchal model?
An early database model whose basic concepts and characteristics formed the basis for subsequent database development. This model is based on an upside-down tree structure in which each record is called a segment and has a 1:M relationship to the segment directly below it.
What is a network model?
An early data model that represented data as a collection of record types in 1:M relationships.
What is a relational model?
It represents data as independent relations. Each relation (table) is conceptually represented as a two-dimensional structure of intersecting rows and columns. The relations are related to each other through the sharing of common entity characteristics (values in columns).
What is an object-oriented model?
A data model whose basic modeling structure is an object.
What is an extended relational data model?
A model that includes the object-oriented model’s best features in an inherently simpler relational database structural environment.
What types of relationships does an ERM/ERD show?
One to one (1:1), one to many (1:M), and many to many (M:N)
What is determination?
The state in which knowing the value of one attribute makes it possible to determine the value of another.
What is a functional dependency?
The value of one or more attributes determines the value of one or more other attributes.
What is a determinant?
In a functional dependency, it’s the attribute or group of attributes whose value determines another.
What is a dependent?
In a functional dependency, it’s the attribute whose value is determined by the other attribute(s).
What is a full functional dependency?
A functional dependency in which the entire collection of attributes in the determinant is necessary for the relationship.
What is an existence-dependency?
A property of an entity whose existence depends on one or more other entities.
What is a primary key?
An attribute or combination of attributes that uniquely identifies each row in a table.
What is a composite key?
A key that is composed of more than one attribute.
What is a superkey?
A key that can uniquely identify any row in the table. It functionally determines every attribute in the row.
What is a candidate key?
A superkey without any unnecessary attributes. It’s based on a full functional dependency.
What is a foreign key?
The primary key of one table that has been placed into another table to create a common attribute.
What is a secondary key?
A key that is used strictly for data retrieval purposes and does not require a functional dependency.
What is a surrogate key?
A system defined attribute created and managed via the DBMS. It’s used when the primary key is considered unsuitable.
How does a relational database implement relationships between tables?
By having another table’s primary key as a foreign key.
What are the different notations that an ERD uses to show relationships?
Chen, Crow’s Foot, and UML Class Diagram.
What is unstructured data?
Data that exists in its original, raw state; that is, in the format in which it was collected.
What is structured data?
Data that has been formatted to facilitate storage, use, and information generation in a predefined data model.
What is semistructured data?
Data that has already been processed to some extent.
What is data inconsistency?
A condition in which different versions of the same data yield different (inconsistent) results.
What is a query?
A specific request to a DBMS to read or update data.
What is a business rule?
A brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.
What does a relational set operator do?
It uses relational algebra operators on existing relations (tables) and produces new relations.
Define SELECT (aka RESTRICT)
It’s a unary operator that yields values for all rows found in the table that satisfy a given condition.
Define PROJECT
It’s a unary operator that yields all values for selected attributes.
Define UNION
It combines all rows from two tables, excluding duplicate rows. The tables must have the same attribute characteristics.
Define INTERSECT
It yields only the rows that appear in both tables.
Define DIFFERENCE
It yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other.
Define PRODUCT
It yields all possible pairs of rows from two tables—also known as the Cartesian product.
Define JOIN
It allows information to be intelligently combined from two or more tables.
What does a natural join do?
Links tables by selecting only the rows with common values in their common attribute(s).
What does an equijoin do?
Links tables on the basis of an equality condition that compares specified columns of each table.
What does an inner join do?
Only returns matched records from the tables that are being joined.
What does an outer join do?
The matched pairs are retained, and any unmatched values in the other table are left null.
Define DIVIDE
It answers queries about one set of data being associated with all values of data in another set of data.
What is cardinality?
The maximum and minimum number of entity occurrences associated with one occurrence of the related entity.
What is a schema?
The conceptual organization of the entire database as viewed by the database administrator.
What is a subschema?
It defines the portion of the database “seen” by the application programs that actually produce the desired information from the data within the database.
What is a partial dependency?
A functional dependency where an attribute is dependent only on part of the primary key.
What is a transitive dependency?
A functional dependency where an attribute is dependent on an attribute that isn’t part of the primary key.
What is a repeating group?
It happens when multiple entities of the same type exist for any single key attribute occurence.
What does it mean when data is in first normal form (1NF)?
The data is in table format, there are no repeating groups, and the primary key is identified.
What does it mean when data is in second normal form (2NF)?
The data is in 1NF and there are no partial dependencies.
What does it mean when data is in third normal form (3NF)?
The data is in 2NF and there are no transitive dependencies.
What does it mean when data is in Boyce-Codd normal form?
The data is in 3NF and every determinant is a candidate key (special case of 3NF).
What does it mean when data is in fourth normal form (4NF)?
The data is in BCNF and there are no independent multivalued dependencies.
What does it mean when data is in fifth normal form (5NF)?
The data is in 4NF and cannot have lossless decomposition into smaller tables.
