Chapter8_Databases

Posted on In , Views: Disqus:

Syllabus

Database Concepts

  • Limitations of a file-based approach

    • Data redundancy - the same data is stored many times
    • Program-data dependence - the program that access the data has to be re-written if any changes are made to the structure of the files
    • Data dependency - changes to data means changes to programs accessing the data
    • Data inconsistency - when data is updated in one place, it is not updated everywhere
    • Hard to perform complex queries - a new program has to be written each time
    • Lack of data privacy - user views cannot easily be implemented

  • Features of a relational database

    • Multiple tables are linked together
      • reduces redundancy
      • increases integrity
      • data only needs to be updated once
      • associated data will be automatically updated
    • Program-data independence
      • the structure of the data can change and does not affect program
      • the structure of programs can change and does not affect data
      • the data can be accessed by any appropriate program
    • Allows concurrent access to data
      • by the use of record locking
      • by restricting over-writing changes
    • Different user can be given different access rights
      • which improves security
    • Different users can be given different views of the data
      • so data privacy is maintained
    • More complex searches and queries can be executed

  • Entity

    Anything that can have data stored about it (e.g. a person, a place)

  • Table

    A group of similar data, in a database, with rows for each instance of an entity and columns for each attribute

  • Record

    A row in a table in a database

  • Field

    A column in a table in a database

  • Tuple

    One instance of an entity, which is represented by a row in a table

  • Attribute

    A column in a table = field

  • Primary key

    Unique identifier (an attribute) for a table, a special case of candidate keys

  • Candidate key

    An attribute of smallest set of attributes in a table where no tuple has the same value

  • Secondary key

    A candidate key that is an alternative to the primary key

  • Foreign key

    A set of attributes in one table that refers to the primary key in another table

  • Relationship

    Situation in which one table in a database has a foreign key that refers to a primary key in another table in the database

  • Referential integrity

    Property of a database that does not contain any values of a foreign key that are not matched to the corresponding primary key

  • Indexing

    • Data structure built from one or more columns in a database table
    • Helps to efficiently retrieve records from database files

  • Normalization

    • First Normal Form (1NF)
      • No repeated groups of attributes
      • All attributes should be atomic (single value, non-divisible)
      • No duplicate rows
    • Second Normal Form (2NF)
      • No partial dependencies (some attribute only depends on part of a composite key)
    • Third Normal Form (3NF)
      • No non-key dependencies
      • No transitive dependencies

Syllabus

Database Management System (DBMS)

  • Features of DBMS

    • Data dictionary

      • A set of data that contains metadata for a data base
      • Table/entity names
      • Field/attribute names
      • Datatypes
      • Validation
      • Primary keys
      • Used by managers of the database
      • Maps logical database to physical storage

    • Data modeling

      The analysis and definition of the data structure required in a database to produce a data model

    • Logical schema

      Schema refers to a data model for a specific database that is independent of the DBMS used

      Logical:

      • Describes how the relationships will be implemented in the logical structure
      • Defines all the logical constraints that need to be applied on the data stored
      • Defines tables, views, and integrity constraints

  • Tools in DBMS

    • Developer interface
      • Set up forms for the input of student data
      • Add objects to a form to make data input easier
      • Design a report for the output of student marks
      • Add a menu to select options for different actions
      • To create user friendly features
      • To create interactive features
      • To create outputs
      • Create table
      • Create form
      • Set up query
      • Set up relationships between table
    • Query processor
      • To create SQL queries
      • To search for data that meets the set criteria
      • To perform calculation on extracted data

Syllabus

DDL and DML