Lecture 1, 2 and 3 Flashcards
(40 cards)
Reasons for DBMS
- Data redundancy and inconsistency
• Multiple file formats, duplication in different files - Difficulty in accessing data
• Need to write a new program to carry out each new task - Data isolation
• Multiple files and formats - Integrity problems
• Integrity constraints (e.g., account balance > 0) become “buried” in program code rather than being stated explicitly
• Hard to add new constraints or change existing ones - Atomicity of updates
• Example: Transfer of funds from one account to another should either complete or not happen at all
• Failures may leave data in an inconsistent state with partial updates carried out
6. Concurrent access by multiple users
• Needed for performance
• Example: Two people reading a balance (e.g., 100) and then withdrawing money (e.g., 50 each) at the same time
• Uncontrolled concurrent accesses can lead to inconsistencies
- Security problems
• Hard to provide user access to some, but not all, data
DBMS architecture:
Database (DB)
- Collection of data with same structure
- Including correlations and relationships
- Common purpose
- Shared by several users
DBMS
- Collections of programs over DB
- Define; i.e. specify data types, structure, organisation
- Build & manipulate
- Administrate
Applications / Users
- Access for performing queries
- android app etc.
Ultimate DBMS goal:
Separate data from application
- Provide an interface
- Allow system administrator to make modifications without having an impact
on the user
- Users can change their view of the data without having to worry about how it’s
stored
Three layers
Internal
- Storage
- Storing data
- Software for constructing data
- Efficient access methods (keys, indices etc)
Logical
- Control
- Optimization of queries
- Resolving conflicting accesses (multiple users)
- Guarantees constant availability
External
- Dialog
- communication with user
- Access control
- answer presentation
- Analysis of user requests
Development process of DBMS:
- Planning
- Analysis
- Logical design
- Physical design
- Implementation
- Maintainance
Four different types of DBMS:
Traditional - Text and numerical data Multimedia DBMS - Multimedia (movies, music etc) Spatial DBM - Geographic and geometric data Data warehouses
Waaruit bestaat een Entity-relationship-model
Entities
- Instance: person, place, object, event, concept (often corresponds to row)
- Type: collection of entities (often corresponds to table)
Relationship
- Instance: link between entities (corresponds to primary-foreign key
equivalence in related tables)
- Type: Category of a relationship (corresponds to links between entity types)
Attributes
- Properties or characteristics of an entity type or relationship type
Wat zijn business rules?
- statements that define or constrain some aspect of the business
- Derived from policies, procedures, events, functions
- Assert business structure
- Control or influence business behavior
- Expressed in terms familiar to end users
- Automated through DBMS software
Wat maakt een goede business rule?
- Declarative - what, not how
- Precise, clear, agreed-upon meaning
- Atomic - one statement
- Consistent - internally and externally
- Expressible - structured, natural language
- Distinct - non-redundant
- Business-oriented - understood by business people
Wat is belangrijk bij data naming?
- Related to business and not technical
- Meaningful and self-documenting
- Unique
- Readable
- Composed of words from an approved list
- Repeatable
- Written in standard syntax
Wat is een entity type?
a collection of entities that share common properties or characteristics
Wat is een entitiy instance?
- A single occurrence of an entity type
Voorwaarden van een entity
Should be:
- object with many instances in db
- object that is composed of multiple attributes
- object that you’re trying to model
Should not be:
- user of database
- output of database
What are strong and weak entities?
Strong
- exist independently of other types of entities
- has its own unique identifier
- Identifier underlined with single line
Weak
- dependents on a strong identity
- without unique identifier
- entity box and partial identifier have double lines
What is an attribute?
Property or characteristic of an entity type or of a relationship type
Types of attributes
Required vs optinal
Derived vs Stored
Singlevalue vs Multivalue
Simple vs composite
Identifier
Is a key = can be a single attribute or combination of
attributesAn identifier has to be:
- Unique
- Not be Null
- Not change in value
Defining attributes:
- State what it is and why it is important
- Make it clear
- Include aliases in documentation
- State source of values
- State whether it can change
- Specify whether required or optional
- State min and max number of occurrences allowed
- Indicate relationships with other attributes
Wat is an relationship instance?
ink between specific entity instances
What are three types of relationship degrees?
Number of entity types that participate in the relationship
- Three types:
Unary
- entities of the same entity type related to each other
- employee is supervisor of another employee; in same table displayed
Binary
- entities of one type related to entities of another
Ternary
- entities of three different types involved in the same relationship
- e.g. product shipped by supplier to a warehouse
three relationship cardinalities?
- One-to-one
- Each entity in the relationship will have exactly one related entity
- One-to-many: An entity on one side can have many related entities on the other side, but an
entity on the other side, will have a maximum of one related entity - many-to-many
- entities on both side can have multiple related entities
What are cardinalities contraints?
number of instances of one entity that can, or must be, associated with each instance of another entity Minimum cardinality - If zero, then optional - if one or more, than mandatory Maximum cardinality - the maximum number
What is an associative entity?
one or more attributes on a relationship suggests that the relationship should perhaps instead be represented as an associative entity
Four conditions for associative entities:
- all the relationships for the participating entities are “many” relationships
- The resulting associative entity has an independent meaning and (preferrably) can
be identified with a single-attribute identifier (key) - Associative entity has one or more attributes in addition to the identifier (must add
something) - Associative entity participates in one or more relationships independent of the entities
related in the associated relationship
