Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
81 Cards in this Set
- Front
- Back
|
what is a database schema and what does it include?
|
it defines a database's structure, tables, relationships, domains, and business rules. it's a design on which the database is built
|
|
|
What is a domain?
|
the parameters of values that a column in a table may have.
|
|
|
define foreign key
|
a key to one table that is foreign to another table in which it resides.
|
|
|
examples of database applications
|
forms, queries, reports, menus
|
|
|
what is a surrogate key
|
a key such as id number that helps organize relationships but is meaningless to the user (so is not often shown on user interface).
|
|
|
what are some ways to make a query?
|
query by example
query by form directly programming in SQL |
|
|
what does it mean to parameterize a query
|
save a basic query but you input the relevant values at the time you run it.
|
|
|
discuss top-down vs. bottom-up development
|
top-down - you analyze the organization, break it down into component parts, and keep breaking down until you have a single problem that you choose a product to fit. Good approach but time-consuming.
bottom-up - you pick a product to fit a problem, then you work to integrate it into the rest of the organization. Fast, but more prone to errors in the long run. |
|
|
which works better for entity relationship modelling - top-down or bottom-up
|
top-down is better
|
|
|
which works better for semantic object modelling - top-down or bottom-up?
|
bottom-up
|
|
|
what are the four main elements of a database?
|
user data
metadata indexes application metadata |
|
|
how do most products store metadata?
|
in the form of tables usually called system tables, that can be manipulated in the same way as the data tables.
|
|
|
what is overhead data
|
organizational data, usually indexes, that make a database more efficient and responsive.
|
|
|
what are the three subsystems of a database management system?
|
the design tools subsystem, the run-time subsystem, and the DBMS engine.
|
|
|
what is a design tools subsystem?
|
a set of tools to facilitate the design and creation of the database and its applications. tools for creating tables, forms, queries, and reports.
|
|
|
what is a run-time subsystem
|
part of the dbms that processes the application components that are developed using the design tools.
|
|
|
what does the DBMS engine do?
|
it's the intermediary between the design tools and run-time subsystems and the data.
|
|
|
Describe 4 major types of databases, give typical numbers of users and typical size for each.
|
1. personal - 1 user, <10 Mb
2. Workgroup - < 25 users, <100 Mb 3. Organizational - hundreds or thousands of users, > 1 trillion bytes 4. Internet/multimedia - hundreds to thousands of users, any size |
|
|
What does DBMS stand for
|
database management system
|
|
|
What were major disadvantages of old-style file processing systems?
|
1. data are separated and isolated
2. duplicated data 3. application programs are dependent on file formats 4. files often incompatible with one another 5. no flexibility in user's perspective |
|
|
define and discuss data integrity
|
a collection of data has integrity if the data are logically consistent. Database designs that minimize duplication are more likely to have data integrity.
|
|
|
define metadata
|
aka data dictionary, it is the self-describing portion of a database, which encodes the description of its own structure
|
|
|
define database
|
a self-describing collection of integrated records.
|
|
|
what are indexes
|
files used to represent relationships among the data and also to improve the performance of database applications.
|
|
|
define application metadata
|
information encoded in the DB that describes the structure of the applications that use the DB.
|
|
|
define transactions
|
representations of events, as recorded in a db.
|
|
|
what is the main advantage of the relational database model?
|
data are stored in a way that minimizes duplicated data.
|
|
|
define normalization
|
a process of evaluating a relational db to minimize data duplication
|
|
|
what are two approaches to handling LAN-based multi-user dbs?
|
1. client-server database architecture
2. file-sharing architecture. |
|
|
What is a distributed database?
|
one in which the data is distributed across multiple sites. THe idea is that each user has a locally managed database and the distributed system integrates these into a central DB.
|
|
|
What are the key elements of the Entitity-Relationship model?
|
entities - the thing you are trying to track
attributes - properties; describe the entity's characteristics identifiers - attributes that name or identify entity instances relationships - ways that entities are associated with one another. |
|
|
What is UML?
|
Unified modeling language - a design tool that is used to set up entity-relationship models. Is designed for object-oriented databases but can be used for relational.
|
|
|
Discuss unique, non-unique, and composite identifiers in ER models
|
unique identifiers identify only one entity instance, for example ssn. Non-unique may identify multiple entity instances, for example name. Composite identifiers derive a unique identity from two or more attributes in combination.
|
|
|
Define "degree of relationship" in the ER model.
|
the degree refers to the number of entity classes involved in a relationship. For example, "Parent" may be a third degree relationship, involving father, mother, and child. relationships of degree 2 are called binary relationships.
|
|
|
describe the three types of binary relationship in the ER model
|
1:1
1:N (one to many) N:M (many to many) |
|
|
What is the maximum cardinality for an ER relationship?
|
the maximum number of entities that can occur on one side of a relationship. Does not indicate a minimum.
|
|
|
How can you indicate minimum cardinality on an ER diagram?
|
place a hash mark across the relationship line to indicate an entity must exist, and an oval to indicate an entity may or may not exist.
|
|
|
What is a recursive relationship and how would you show it in an ER diagram?
|
a relationship between two entities in the same entity class. shown by a relationship line that loops back onto the same entity it started with.
|
|
|
In ER models, what is a weak entity?
|
an entity which cannot exist unless another type of entity exists. for example, you can't have an apartment number unless you have a building to put it in.
|
|
|
In the ER model, what is an ID dependent entity?
|
a weak identity in which the identifier of this entity includes the identifier of another entity. For example, if an apartment is defined by a composite id that includes building code.
|
|
|
how might you handle multi-value attributes in the ER model?
|
by creating a weak entity associated with the primary entity in a 1:N ordinality.
|
|
|
whatis a subtype entity?
|
when an entity is broken into two or more subtypes each with a different set of attributes. For example, the entity "client" may play one role in a model, but have two subtypes for individual or corporate clients.
|
|
|
Define inheritance in relation to the ER model
|
a special characteristic of generalization hierarchies; the entities in subtypes inherit attributes of the supertype entity class.
|
|
|
what is a generalization hierarchy?
|
in the ER model, it's a hierarchy in which an entity type is divided up into two or more subtypes.
|
|
|
Explain why a semantic object is a "sufficient description of a distinct identity"
|
the object is a collection of attributes that are supposed to represent a unique identity; the attributes listed should be sufficient to identify it and distinguish it from other objects.
|
|
|
name and describe three types of semantic object attributes.
|
simple attributes - a single element
group attributes - composites of other attributes semantic object attributes - establish a relationship between one semantic object and another. Sometimes called object links. |
|
|
explain why semantic object attributes are always paired.
|
if you have a relationship in one direction, the relationship must also exist in the other direction.
|
|
|
In semantic object modeling, what is an object identifier?
|
the attribute used to identify the object, may be unique or not, may be a group identifier (a composite of two attributes).
|
|
|
define a semantic object view
|
the portion of an object that is visible to a particular application.
|
|
|
In semantic object modeling, what is a domain?
|
a description of an attribute's possible values. Includes both a physical and a semantic description, may include an enumerated list. May also be a formula domain - an attribute that is computed from other values.
|
|
|
define: simple attribute, multi-value attribute, and nonobject attribute.
|
simple attribute is one whose maximum cardinality is 1. multivalue attribute has maximum cardinality greater than 1. And nonobject attribute is a simple or group attribute.
|
|
|
What are the seven types of object found in semantic object modeling?
|
1. simple objects - contains only single-value, nonobject attributes.
2. composite objects - contain one or more multi-value, nonobject attributes. 3. compound objects - contain at least one object attribute 4. hybrid objects - combinations of composite and compound objects - at least one multivalue group attribute that contains a semantic object attribute. 5. association objects - relate two or more objects and store data peculiar to that relationship. 6. parent/subtype objects - objects that represent a subtype attribute in another object. 7. archetype/version object - objects that represent versions, releases, or editions of the archetype object. |
|
|
how is a subtype attribute represented in semantic object modeling?
|
in the parent object, the subtype object is denoted with the subscript 0.ST or 1.ST. In the subtype object, the parent object is listed as the first attribute and denoted with a subscript P.
|
|
|
What is a multi-value dependency?
|
One in which two (or more) table attributes may have multiple values in relation to the key, and no relationship to each other.
|
|
|
What is fourth normal form
|
a relation is in fourth normal form if it is in BCNR and has no multi-value dependencies.
|
|
|
What is fifth normal form?
|
it concerns the (rather obscure) relations that can be divided unti subrelations but then cannot be reconstructed. We don't know if this has consequences.
|
|
|
Define constraint in the context of domain key normal form.
|
a constraint is any rule governing static values of attributes that is precise enough that we can determine if it is true. Does not include rules pertaining to changes in values, or time-dependent constraints.
|
|
|
What is a key, in the context of domain /key normal form?
|
a unique identifier of a tuple
|
|
|
what is a domain, in domain/key normal form?
|
a description of an attributes allowed values.
|
|
|
what is domain/key normal form?
|
a relation is in DKNF if enforcing key and domain restrictions causes all of the constraints to be met. proven to eliminate modification anomolies.
|
|
|
Discuss controlled redundancy
|
while fully normalized relations eliminate redundancy, there are times when it's worth choosing to introduce some non-normalized redundancy to decrease the work of the DBMS. One example is when the normalized table includes some data this is not always essential, and is very large (such as big graphics files).
|
|
|
What is normalization?
|
The process for converting a relationship that has certain problems to two or more relationships that don't have problems.
|
|
|
What is a relation?
|
A two dimensional table used for storing data in the relationship model.
|
|
|
What is the row in a relation called?
|
a Tuple. also may be called a record.
|
|
|
What is the column in a relation called?
|
an attribute, or a field.
|
|
|
What are the restrictions for ta table to be a relation?
|
1. cells must be a single value (no groups or arrays)
2. all entries in a column must be of the same kind 3. no two rows may be identical |
|
|
define Functional dependency
|
attribute Y is functionally dependent on attribute X if the value of C determines the value of Y. if we know X, we can obtain Y.
|
|
|
Define determinant
|
if B is functionally dependent on A, (A-->B), then A is a determinant of B.
|
|
|
What is a key?
|
one or more attributes that uniquely defines a row.
|
|
|
Why must every relation have a key?
|
because no relation can have duplicate rows and hence, at the extreme, the key consists of all the attributes of the relation.
|
|
|
Define modification anomalies
|
defects in a table where changing the data leads to undesirable conseqences such as data loss.
|
|
|
what is a deletion anomaly?
|
a situation where deleting the facts about one entity inadvertently leads to deleting facts about another entity.
|
|
|
What is an insertion anomaly?
|
A situation where you can't insert data about one entity without some additional fact about another entity.
|
|
|
what are referential integrity constraints?
|
AKA inter-relational constraints, situations where entering data in one table should also require data inserted into another table.
|
|
|
What are the normal forms, in order?
|
first, second, third, Boyce-Codd, fourth, fifth, domain/key normal
|
|
|
Describe first normal form:
|
Any table of data that meets the definition of a relation - cells of a single value, each column uniquely named, each row unique, and order of columns and rows doesn't matter.
|
|
|
Describe second normal form
|
a relation is in second normal form if all its nonkey attributes are dependent on all of the key.
|
|
|
what is a transitive dependency?
|
A-->B and B-->C
|
|
|
What is third normal form?
|
a relation is in third normal form if it is in second normal form and has no transitive dependencies.
|
|
|
What are candidate keys?
|
two or more attributes in a relation which could be the key for that relation. WHichever is chosen becomes the primary key.
|
|
|
What is Boyce-Codd normal form?
|
A relation is in boyce codd normal form if every determinant is a candidate key.
|
