CH8

• Database


  A structured collection of items of data that can be accessed by different application programs
• File based approach


  • Storage space is wasted when data items are duplicated by the separate applications and some data is redundant
  • Data can be altered by one application and not by another - inconsistent
  • Enquirers available can depend on the structure of data and software used so data is not independent
• Relational database


  Database in which the data items are linked by internal pointers
• Relational database


  • Storage space is not wasted as data items are only stored once - no little or no data redundant
  • Data entered is one application is available in another application - data is consistent
  • Enquirers available are not dependent on the structure of data and software used - data is independent
• Table


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


  A row in a table in a database
• Field


  Column in a table in a database
• Keys


  • Candidate Key
  • Primary Key
  • Secondary Key
  • Foreign Key
• Candidate Key


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


  A unique identifier for a table
• 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


  Formed when one table in a database has a foreign key that refers to a primary key in another table in the database
• Entity Relationship


  • One-to-One
  • One-to-Many
  • Many-to-One
  • Many-to-Many
• Normalisation


  Used to construct a relational database that has integrity and in which data redundancy is reduced
• First Normal Form (1NF)


  • Entities do not contain repeated groups of attributes
• Second Normal Form (2NF)


  • Entities are in 1NF and non-key attributes depend upon the primary key
• Third Normal Form (3NF)

  • Entities are in 2NF and all non-key attributes are independent
  • Table contains no non-key dependencies
• Database Management Systems (DBMS)


  System software for the definition, creation, and manipulation of a database
• How a DBMS addresses the limitation of a file based approach

  1. Data Redundancy Issue

  2. Data Inconsistency Issue

  3. Data Dependency Issue

  4. The DBMS Approach
• How a DBMS addresses data redundancy

  • Solved by storing data in separate linked tables, which reduces the duplication of data as most items of data are only stored once
  • Items of data used to link tables by the use of foreign keys are stored more than once
  • DBMS will flag any possible errors when any attempt is made to accidentally delete this type of item
• How a DBMS addresses data inconsistency

  • Solved by storing most items of data only once, allowing updated items to be seen by all applications
  • As data is not inconsistent, the integrity of the data stored is improved
  • Consistent data is easier to maintain as an item of data will only be changed once, not multiple times, by different applications
• How a DBMS addresses data dependency

  • Data is independent of the applications using the database, so changes made to the structure of the data will be managed by DBMS and have little or no effect on the application using the database
• The DBMS Approach


  • Uses a more structured approach to the management, organization, and maintenance of data in a database
  • An already defined data structured can be used to set up and create the database
  • Uses a data dictionary to store the metadata, including the definition of tables, attributes, relationship between tables and any indexing
  • Can also define the physical storage of the data
  • Improve the integrity of data stored, helping to ensure that it is accurate, complete and consistent
• Data Modeling


  • To show the data structure of a database
  • Ex: E-R diagram

  
• Logical Scheme


  A data model for a specific database that is independent of the DBMS used to build the database
• DBMS data security


  • Prevent unwanted alteration, corruption, deletion, sharing data with others that has no access
  • Using usernames, passwords to prevent unauthorized access
  • Encryption of the data stored
  • Using access rights — different level of access
• Usage of DBMS software tools

  • Developer Interface
  • Query Processor
• Developer Interface

  • Allows a developer to write queries in Structured Query Language (SQL)
  • These queries are then processed and executed by the Query Processor
  • Allow the construction of more complex queries to interrogate the database
• Query Processor


  • Takes a query written in SQL and processes it
  • Includes a DDL interpreter, DML compiler, and a Query Evaluation Engine
  • DDL Statements = interpreted and recorded in the database's data dictionary
  • DML Statements = compiled into low level instructions that are executed by the query evaluation engine
  • DML compiler will optimize the query
• Data Definition Language (DDL)

  • A language to create, modify, and remove the data structures that form a database
  • Written as scripts that uses syntax similar to a computer program
  • To work on relational databases structure
• Data Manipulation Language (DML)


  • A language used to add, modify, delete, and retrieve the data stored in a relational database
  • Written in a script that is similar to a computer program
  • To work with the data stored in the relational database
• Structured Query Language (SQL)


  A list of SQL commands that perform a given task, often stored in a File for reuse