Slide/Poll Everywhere Review Questions - Exam 1

Term
1 / 81
What are the four actions of data management?
Click the card to flip 👆
Terms in this set (81)
What are the four actions of data management?
Create
Read
Update
Delete
What is a schema like?
MAP
Decomposing a problem means?
Breaking it down into manageable chunks
What two structural constraints define how entities participate in a relationship.
Cardinality and participation
The maximum number of times an entity instance participates
Cardinality
Describes whether all entity instances are required to participate
Participation
Why is dependent a weak entity?
Because a dependent can not exist unless there is an "employee" they are a dependent of
In the "Employee Supervised_by Employee" relationship, the degree of connectivity is?
Unary
With regard to the "Project Undertaken_by Plant" relationship, which is the parent.
Plant is the parent
Which deletion rule is in effect when the deletion of an instance in the parent entity results in the deletion of instance in the child entity?
Cascade
In a two schema architecture, data may become inconsistent between multiple data sources if it is updated in one location but not another. This is related to:Lack of data integrationA partial keyUniquely identifies an instance when combined with a unique identifier from its base entityWhat is the primary use of a gerund?To decompose M:N relationshipsWhich type of attribute does the dotted circle symbol represent?DerivedWhat does this line of DDL code do CONSTRAINT fk_med FOREIGN KEY (ord_med_code) REFERENCES medication (med_code) ?Creates a relationship between two tablesWhich of the following best describes the characteristics of a primary key?Unique, irreducible, and entity integrity constraintWhat does the following DDL code do: ALTER TABLE student ADD nickname char (12); ?Creates a new attribute called "nickname" in the studentWhich type of key is NOT irreducible?Super keyGerund:Product of decomposing m:n relationships, also called a bridge entity, always child in the parent child relationshipIn the class lecture with the horse model the terms, "red, yellow, and mocha" were what?ValuesIdentify the relationship in the following statement: "Jeffrey is a nationally ranked fencer. He competes in multiple matches throughout the season. "CompetesWhich key is not only unique, but also irreducible and cannot have a null value?Primary keyA set of possible values of an attribute if referred to as:DomainWhat is the difference between a Key attribute versus a Non-key attribute?A key attribute is a proper subset of a candidate keyWhich of the relational algebra operators creates a second relation by extracting a subset of columns that reduces the degree/s of the resulting relation?ProjectionGiven two sets, {A, B, E, F} and {D, E, F, G} what relational algebra operation would result in the set {A, B, D, E, F, G}?UnionWhich relational algebra operation creates a relation with a subset of the tuples from the source relation?SelectWhat is the difference in data and information?Data are raw unorganized facts and information is organized data with contextWhat is meta data?The lens we use to understand what data means. Describes properties of data so we can infer information. The data that describes your data.What is a DMBS?Database Management System- provides a layer between the application/users and the actual data. Managed through Orcale, Microsoft SQL Server, MySQLWhat are the three limitations of file processing systems?Lack of data integrity Lack of standards Lack of flexibility/maintainabilityWhat are the two fundamental problems that lead to these limitations?Lack of integration Lack of program data independenceWhat is a schema?A description of your metadata. Data that describes the data that describes your data. Its a map that shows how things are related. Mapping where data is logically stored to where it is physically storedExplain the difference in internal, external, and conceptual schemaInternal - the physical organization of the stored data (technology dependent) Conceptual- core of the architecture-how your data is logically organized. Global view of the structure. Describes all data items and relationships with integrity constraints (technology independent) External - represents views for individual users and/or applications. Views are generated by logical references to the conceptual elements. (Technology independent)What is the difference in the "database" and the "DBMS"?Database systems were created to overcome the limitations of the old "file systems" way of doing things. (an integrated set of files) The DBMS is a system for managing the files and data contained within. Rather than interacting with the data directly, the DBMS provides users and applications a method for "asking" for the dataWhy is it important that a database is "self-describing"?A database is self-describing in that the metadata is recorded within the database (i.e the schemas) not in application programs. Data consists of recorded facts-when viewed through the lens of metadata the meaning of the recorded data becomes explicitWhat are the types of database systems, and what is the scope of their use?Scope: Desktop database system - single user. Workgroup database system - small departments 50 users Enterprise database system - entire company 1000s users Database systems Distributed database systems * Multiple logically connected database. Spread over a geographic area Data warehouse * Optimized for reading data. Used for analytics, business intelligence, data mining etc.What is a data model?Simplified expression of observed or unobservable reality used to perceive relationships in the outside world. Ex: a blueprintWhat are the differences in conceptual, logical, and physical data models? Who is the intended audience for each?**Conceptual product: conceptual schema **Logical model/design product: logical schema **physical design product: physical/internal schemaWhat is an entity relationship (ER) model?Communication/presentation device used by an analyst to interact with the end-user community. A design tool at the highest level of abstraction to convey a deeper level understanding to the database designer.Who uses an ER and why?Analysts use an ER to interact with the end-user communityEntity typeSet of related attributes. Has relationships with other entity types.Entity instanceAn occurrence of an entity typeEntity classA set of entity types that have shared propertiesAttributeEx: Name, color, spots, gender, etc.ValuesFacts ex: Joan, Brown, Female, 1250.Domain"Or" Example: Spots: {yes, no} Gender : {M, F}RelationshipEntities associating with another entityWhat is the difference in strong and weak entities?A strong entity always exists. A weak entity only exists when based on a strong entity.Describe the characteristics of an attributeAttribute type: numeric, alphabetic, alphanumeric Classification - atomic or composite Category- single or multi-value Source - Stored or derived Domain- implicit or explicit Role - unique or non keyDescribe unary, binary, ternary, quaternary, and "n-ary" degrees of connectivityUnary - recursive relationship. n=1 Binary - simplest relationship n=2 Ternary - n=3 Quaternary - n=4 N-ary = any size as n-ary (n refers to the number of degrees)Describe 1:1, 1:n, n:1 and m:n cardinalitiesm:n - An entity instance in set A is associated with no more than "m" (many) entity instances in set B. An entity instance in set B is associated with no more than "n" (many) entity instances in set A. • 1:n - An entity instance in set A is associated with no more than "n" (many) entity instances in set B. An entity instance in set B is related to no more than 1 entity instance in set A. • n:1 - The reverse of 1:n (these are often combined). • 1:1 - An entity instance in set A is associated with no more than 1 entity instance in set B. An entity instance in set B is related to no more than 1 entity instance in set ADescribe deletion constraints and the restrict (R), cascade (C), set null (N), and set default (D) rulesRestrict: Do not allow coach (Parent) to be deleted • Cascade: Delete all the players (Children) as well • Set Null: Players (Children) exist, but without a coach (Parent) • Set Default: A new "default" coach (Parent) is definedWhy do we care about logical data modeling?completion of conceptual modeling phase results in a picture of data requirements at high level of abstractionWhat are the relational data models?Database -collection of relation Relation - 2D table Tuple - row of related data values in the table Attribute - column in the table Domain - set of possible atomic values of an attributeCharacteristics of a relationA relation is a mathematical term that resembles a 2D table. Heading - a single tuple listing the attributes (relation schema) Body-collection of data tuples Attributes of a relation schema have unique names Values of attributes in relation come from same domainImportance of naming conventionsAttribute names MUST be unique within a relation Attribute names MUST be unique across the entire conceptual schemaThree set operatorsUnion (OR) Intersection (AND) Difference (NOT)What is Relational algebraRelational algebra just lets us abstractly talk about relations and data (like algebra lets us abstractly talk about numbers)Be able to define: Super keys, candidate keys, key attributes, non-key attributes, primary keys, and alternate keysSuper key - uniquely identifies tuples Candidate keys - unique + irreducible key attributes - proper subset of a candidate key non-key attributes - non a subset of a ck primary key - candidate key with entity integrity constraints (not null) alternate keys - a ck not selected as pl• What is the entity integrity constraint?A primary Key is a candidate key with one additional constraint: It must not be missing (NULL)What is the referential integrity constraint?Referential integrity constraints are specified between two relation schemas (i.e., R1 and R2) • Specifically, a referential integrity constraint is specified between two relations in order to maintain consistency across tuples of the two relationsWhat is the foreign key constraint?A special form of referential integrity constraint specification • Establishes an explicit association between two relation schemas and maintains the integrity of such an associationWhat are the eight operations in relational algebra?Two unary operations - selection - projection Six binary operators ▫ Union (U) ▫ Intersection (∩) ▫ Difference (-) ▫ Join ▫ Cartesian product ▫ Division• Describe the difference in select and projectselect - creates a second relation by extracting a subset of tuples project - creates a second relation by extracting a subset of columns• Describe the difference in the Union, Intersection, and Difference operationsUnion - creates a third relation containing tuples from either relation Intersection - creates a third relation containing tuples present in both relations Difference - creates a third relation containing tuples present in one relation but not the other relation• What is a natural join?Combines two relations into a third by matching values that come from the same domain for attributes in the two relationsQuestion: Which award winning plants have a budget over $2,000,000•SELECT * FROM AW_Plant WHERE Aw_pl_Budget > 2000000Question: What is the plant number and budget for each of the award winning plants?SELECT Aw_pl_No, Aw_pl_Budget FROM AW_Plant• What award winning plant is each project assigned to?• SELECT * FROM AW_Plant NATURAL JOIN TX_Plant• What plants are award winning but are not in Texas?• SELECT * FROM AW_Plant MINUS SELECT * FROM TX_Plant• What plants are in Texas AND are award winning plants?• SELECT * FROM AW_Plant INTERSECT SELECT * FROM TX_Plant• What plants are in Texas, but are not award winning?• SELECT * FROM TX_Plant MINUS SELECT * FROM AW_Plant• What is the referential integrity constraint?▫ The referenced tuple must exist• What is the foreign key constraint?The referenced value must exist, and must have the same domain as the FK.• What do views do and why are they useful?different types of joins and projections - these are "views" of the data • Unlike a relation schema, a view does not contain data ▫ Is a logical window to the attributes and tuples from one or a set of relations • Views provide many benefits: ▫ Allows the same data to be seen in different ways by different users (or applications) ▫ Provides security by restricting access to data ▫ Hides complexity by making data from several relations appear as a single object• How do growth and restructuring impact views?By using views, changes to the database structure may be made without affecting users• What is the difference in a normal view and a materialized view?• Normal views are temporary ▫ Only exist while the data is being access ▫ This is what most views are Materialized views (snapshots) are constructed from one or more relations ahead of time ▫ Used to "freeze" data at a certain point in time ▫ Improves performance ▫ Periodically recreated to reflect changes in the data ▫ Often deleted if not used for a while, then recreated when needed againwhat is a relation?A mathematical terms approximated by a two dimensional table: ▫ Heading - a single tuple listing the attributes (Relation Schema) ▫ Body - collection of data tuplesGrowth:Adding new attributes to a relation, or new relations to a data modeRestructuring:Changing the conceptual schema * Should be "Information Equivalent" - which facilitates the restructuring being reversible