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;
58 Cards in this Set
- Front
- Back
Database
|
logically related collection of data
|
|
Schema
|
a description of a particular collection of data, using the a given data model
|
|
DBMS
|
a software package designed to store and manage databases
|
|
Four reasons to use a relational DBMS
|
Data independence and efficient access.
Reduced application development time. Data integrity and security. Uniform data administration. Concurrent access, recovery from crashes |
|
Advantages of the relational database approach
|
Controlled redundancy
Security / Access Security subsystem - roles, privileges Multiple User Interfaces Match interface to the use/user Data integrity Enforcing integrity constraints Backup and recovery |
|
Disadvantages of the relational database approach
|
Start up costs
Hardware Software Personnel and Training Complexity Performance (in some situations) Many benefits are lost if standards are not enforced |
|
Least specific to most specific (concrete), the three data models
|
Conceptual
Logical Physical |
|
Three schema structure
|
External Level > External View
mapping | Logical Ind. Conceptual Level > Conceptual Schema mapping | Physical Ind. Internal Level > Internal Schema |
|
Physical Independence
|
Protection from changes in physical structure of data
|
|
Logical Independence
|
Protection from changes in logical structure of data
|
|
Three major database activities
|
Define - Specify data types, structure, and contraints
Construct - Storing data on a storage medium that is controlled by DBMS Manipulate - Querying, updating, and reporting |
|
Phases of DB development
|
Enterprise Modeling
Conceptual Data Modeling Logical Database Design Physical Database Design Database Implementation Maintenance |
|
Entity
|
Real-world object distinguishable from other objects; something you want to store data about
|
|
Attribute
|
property or characteristic of an entity type
|
|
Identifier
|
attribute or combination of attributes that uniquely identifies individual instances of an entity type
|
|
Entity Type
|
Collection of entities that have same attributes
|
|
Entity Instance
|
Single occurrence of an entity type
|
|
Candidate Key
|
An attribute or set of attributes that uniquely identifies each instance of an entity
|
|
Degree of a relationship
|
number of entity types that participate in it
|
|
Maximum/Minimum cardinality
|
the number of instances of one entity that can or must be associated with each instance of another entity
|
|
Unary relationship
|
Relationship of an entity with itself
|
|
Ternary relationship
|
A shared relationship between three entities
|
|
Derived attribute
|
Attribute value created by performing a function with other attributes as inputs
|
|
Multivalued attribute
|
An attribute that contains more than one value for its entry
|
|
Composite attribute
|
An attribute created by the combination of multiple attributes
|
|
Composite key
|
A unique identifier created by combining multiple attributes of an entity
|
|
Completeness constraint
|
Whether an instance of a superclass entity type must also be a member of at least one subclass entity type
|
|
Disjoint contraint
|
Whether an instance of a superclass entity type can be a member of two or more subclass entity types
|
|
Domain
|
Set of values that may be assigned to an attribute for each individual entity
|
|
Strong entity
|
Independent entity - Would exist without strong entity type
|
|
Weak entity
|
Dependent entity - Would not exist without strong entity type
|
|
Superclass
|
An entity type that has offspring entity types that inherit the parent’s attributes and relationships
|
|
Subclass
|
An offspring entity type of a superclass entity type. The subclass entity type inherits all of the attributes and relationships of the parent entity type
|
|
6 Phases of DB development
|
Enterprise Modeling
Conceptual Data Modeling Logical Database Design Physical Database Design Database Implementation Maintenance |
|
Criteria for selecting good identifiers
|
Value never changes
Dataless (no underlying meaning) Never null Substitute new, simple keys for long, composite keys |
|
Name and describe four degrees of relationships
|
Unary (or Recursive) Relationship: 1 entity
Binary Relationship: 2 entities Ternary Relationship: 3 entities n-ary: n entities |
|
Generalization
|
The process of defining a more general entity type from a set of more specialized entity types
|
|
Specialization
|
The process of defining one or more subclasses of the superclass
|
|
Total specialization
|
Superclass must be an instance of a subclass
Double line notation |
|
Partial specialization
|
Superclass need not have an instance of a subclass
Single line notation |
|
Disjoint rule
|
An instance of a superclass cannot be an instance of two or more subclasses simultaneously
Place a “d” in the circle |
|
Overlap rule
|
An instance of a superclass can be an instance of two or more subclasses simultaneously
Place an “o” in the circle |
|
Relation
|
Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity.
Schema : specifies name of relation, plus name and type of each column |
|
Integrity constraint
|
Condition that must be true for any instance within the database
|
|
Foreign key
|
Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer’.
|
|
Referential integrity
|
No dangling references are left when a change is made to an instance of an entity
|
|
Discriminating attribute
|
An attribute added to a superclass to distinguish each subclass from one another
|
|
Selection
|
Selects a subset of rows from relation
|
|
Projection
|
Deletes unwanted columns from relation
|
|
Cross-product join
|
Allows us to combine two relations
|
|
Steps to mapping ERD to logical model
|
1. Convert Multivalued Attributes to Relations
2. Convert Regular Entities to Relations 3. Convert Weak Entities to Relations 4. Convert Superclasses/Subclasses to Relations 5. Map Binary Relationships 6. Map Unary Relationships 7. Map Ternary (n-ary) Relationships |
|
Rules for mapping entities
|
Each entity becomes a relation.
Decompose all composite attributes to simple, component attributes |
|
Rules for mapping attributes
|
The multivalued attribute becomes a separate relation.
The new relation has a composite key of the attribute name and the key of the entity it used to describe |
|
Rules for mapping super/subtypes
|
One relation for each superclass and one for each subclass.
The primary key of the superclass also appears in the subclass relation. Disjointness Constraints If Overlap, add a discriminating attribute to the superclass to represent each subclass. If Disjoint, add one discriminating attribute to the superclass to specify the applicable subclass |
|
Rules for mapping unary relationships
|
Create a new relation. Similar to binary M:N
Rename to reflect relationship. Make sure PK is unique |
|
Rules for mapping binary relationships
|
One-to-Many(1:N): Primary key on the one side becomes a foreign key on the many side.
Many-to-Many(M:N): Create a new relation from the relationship. If the relationship does not have a unique identifier, then use the primary keys of the two entities as the primary key. One-to-One(1:1): Primary key on the mandatory side becomes a foreign key on the optional side |
|
Rules for mapping ternary relationships
|
Create a relation for the relationship.
If there isn’t already an identifier, create a composite key from the primary keys of the three (n) entities. Ensure that the composite key is unique |
|
3NF
|
requires that there are no non-trivial functional dependencies of non-key attributes on something other than a superset of a candidate key. A table is in 3NF if none of the non-primary key attributes is a fact about any other non-primary key attribute. In summary, all non-key attributes are mutually independent
|