dbms

• Data is a collection of information, facts that can be recorded and have implicit meaning
• Database is a collection of interrelated data stored in tables of varying complexity
• Database System is a computerized system that maintains information and makes it available on demand
• Advantages of Database Systems:

  • Redundancy reduction
  • Inconsistency avoidance
  • Data sharing
  • Standards enforcement
  • Security restrictions application
  • Integrity maintenance
  • Data gathering
  • Requirements balancing
• Database is a collection of interrelated data stored in tables of varying complexity
• Database Management System (DBMS) is a collection of programs enabling users to create and maintain a database
• Important landmarks in the history of DBMS:

  • 1960: Charles Bachman designed the first DBMS system
  • 1970: Codd introduced IBM’S Information Management System (IMS)
  • 1976: Peter Chen defined the Entity-relationship model (ER model)
  • 1980: Relational Model becomes widely accepted
  • 1985: Object-oriented DBMS develops
  • 1990s: Incorporation of object-orientation in relational DBMS
  • 1991: Microsoft ships MS Access, displacing other personal DBMS products
  • 1995: First Internet database applications
  • 1997: XML applied to database processing
• Disadvantages in File Processing:

  • Data redundancy and inconsistency
  • Difficulty in accessing data
  • Data isolation
  • Data integrity
  • Lack of concurrent access
  • Security problems
• Advantages of DBMS:

  • Data Independence
  • Efficient Data Access
  • Data Integrity and Security
  • Data Administration
  • Concurrent Access and Crash Recovery
  • Reduced Application Development Time
• Database Applications:

  • Banking: all transactions
  • Airlines: reservations, schedules
  • Universities: registration, grades
  • Sales: customers, products, purchases
  • Online retailers: order tracking, customized recommendations
  • Manufacturing: production, inventory, orders, supply chain
  • Human resources: employee records, salaries, tax deductions
• Actors on the scene:

  • Database Administrators (DBA): responsible for authorizing access, coordinating and monitoring use, acquiring resources
  • Database Designers: responsible for identifying data and choosing appropriate structures
  • End Users: people who query, update, and generate reports from the database
  • System Analysts: determine end user requirements and develop transaction specifications
  • Application Programmers: test, debug, document, and maintain transactions
• Levels of Data Abstraction:

  • Physical Level: describes how data is stored
  • Logical Level: describes data and relationships in the database
  • Conceptual (view) Level: describes the entire database
• Data Models:

  • Data model is a collection of high-level data description constructs
  • Relational data model is widely used
  • Data model schema specifies name, fields, and types
  • Data models can be classified into:
  • Object Based Logical Models
  • Record Based Logical Models
  • Physical Models
• Entity-Relationship Model (ER model):

  • High-level data model defining data elements and relationships
  • Develops a conceptual design for the database
  • Database structure portrayed as an entity-relationship diagram
  • Components include entities, attributes, domains, weak entities, key attributes
• Weak Entity:

  • Entity that depends on another entity
  • Represented by a double rectangle
  • Participation is total
  • Relationship with identifying strong entity is identifying relationship
• Key Attribute:

  • Uniquely identifies each entity
  • Represents the main characteristics of an entity
  • Used as a primary key
• Key Attribute:

  • Represents the main characteristics of an entity
  • Used to represent a primary key
• Composite Attribute:

  • Composed of many other attributes
  • Represented by an ellipse, with ellipses connected to each other
• Multivalued Attribute:

  • An attribute that can have more than one value
  • Represented by a double oval
• Derived Attribute:

  • Can be derived from other attributes
  • Represented by a dashed ellipse
• Relationship:

  • Describes the relation between entities
  • Represented by a diamond or rhombus
• Structure of DBMS:

  • Query Processor components include DDL Interpreter, DML Compiler, and Query Evaluation
  • Storage Manager components include Authorization and Integrity Manager, Transaction Manager, File Manager, and Buffer Manager
• Degree of Relationship:

  • Number of participating entity types
  • Binary relationship has a degree of two, and ternary relationship has a degree of three
• Role Name and Recursive Relationship:

  • Each entity type in a relationship plays a specific role
  • Role names help explain the relationship, especially in recursive relationships
• Cardinality:

  • Number of times an entity participates in a relationship set
  • Types include One-to-One, One-to-Many, Many-to-One, and Many-to-Many
• Cardinality Ratios for Binary Relationship:

  • Specifies the maximum number of relationship instances an entity can participate in
  • Examples include One-to-One (1:1), One-to-Many (1:N), Many-to-One (N:1), and Many-to-Many (N:M)
• Participation of an Entity Set in a Relationship Set:

  • Total participation means every entity participates in at least one relationship
  • Partial participation means some entities may not participate in any relationship
• Keys:

  • Super key uniquely determines each entity
  • Candidate key is a minimal super key, and one is selected as the primary key
• Keys for Relationship Sets:

  • Primary keys of participating entity sets form a super key
  • Consider mapping cardinality and semantics when selecting the primary key
• Design Issues:

  • Use of entity sets vs. attributes depends on enterprise structure and attribute semantics
  • Binary vs. n-ary relationship sets depend on the action between entities
• Binary versus n-ary relationship sets:

  • It is possible to replace any non-binary (n-ary, for n > 2) relationship set by a number of distinct binary relationship sets
  • An n-ary relationship set shows more clearly that several entities participate in a single relationship
• • An attribute set that can uniquely identify a tuple
  • A super key is a superset of a candidate key
• Keys:

  • Keys play an important role in the relational database
  • Used to uniquely identify any record or row of data from the table and establish relationships between tables
  • Primary key:
  • The first key used to identify one and only one instance of an entity uniquely
  • An entity can contain multiple keys, and the most suitable one becomes the primary key
  • Candidate key:
  • An attribute or set of attributes that can uniquely identify a tuple
  • Except for the primary key, the remaining attributes are considered candidate keys
  • Super Key:
• Foreign key:

  • Columns of the table used to point to the primary key of another table
  • Used to link two tables through the primary key of one table
• Specialization:

  • Top-down design process
  • Start with few entity sets having many attributes
  • Identify distinctive subgroupings within an entity set, which become lower-level entity sets
  • Depicted by a triangle component labeled ISA
  • Inheritance: a lower-level entity set inherits all attributes and relationship participation of the higher-level entity set it is linked to
• Generalization:

  • A bottom-up design process
  • Combine entity sets that share the same attributes into a higher-level entity set
  • Specialization and generalization are simple inversions of each other
  • Can have multiple specializations of an entity set based on different features
• Design Constraints on a Specialization/Generalization:

  • Constraint on which entities can be members of a given lower-level entity set
  • Constraint on whether entities may belong to more than one lower-level entity set within a single generalization
  • Completeness constraint:
  • Total: an entity must belong to one of the lower-level entity sets
  • Partial: an entity need not belong to one of the lower-level entity sets
• Aggregation:

  • Consider the ternary relationship works-on
  • Eliminate redundancy via aggregation by treating works-on relationship as an abstract entity
  • Allow relationships between relationships
  • Abstraction of relationship into a new entity
• Use of Entity sets Vs Attributes:

  • The use of an entity set or attribute depends on the structure of the real-world enterprise being modeled and the semantics associated with its attributes
• Use of Entity sets Vs Relationship sets:

  • The design issues regarding the implementation of an object as an entity set or relationship set depend on the scenario being modeled