Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/58

Click to flip

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