Introduction to Database

• Database


  An organised collection of data, held in a computer, to generate knowledge for decision making purposes.
• Database Management System (DBMS)


  Software package designed to enable users to create, store, manipulate data, perform data backup and to develop reports in a convenient and efficient way.
• DBMS Actions

  • acts as an intermediary
  • users are not given direct access to the database
  • users can only access the database via a DBMS
• How does users have access to the database?

  Different users have different level of access
• Flat-File Model


  Simple consecutive list of records
• Disadvantages of Flat-File Model

  • each application has its own set of data files
  • limited data sharing
  • basic data retrieval and querying
  • data redundancy
  • no concurrency
  • limited scalability and performance
  • ad-hoc security
• DBMS Model

  • Centralised data management and standardised processes
  • Advanced data sharing and accessibility
  • Sophisticated data retrieval and querying
  • robust data integrity
  • high scalability and performance
  • integrated security measures
• 4 Types of DBMS Data Models

  • Hierarchical
  • Network
  • Relational DBMS Data Model
  • Object-Oriented
• Relational DBMS Data Model

  • Defines database relationships in forms of tables (relations)
  • Based on relations of mathematical set theory
  • DBMS products - MySQL, Oracle
  • Uses structured query language (SQL) statements to manipulate data
• Advantages of Relational DBMS

  • Eliminates data redundancy
  • Enforces data integrity
  • Data consistency
  • Allows concurrency
  • Improved security
  • Enforcement of standards
  • Economy of scale
• Database Structure

  • Schema
  • DBMS (Database Management System)
  • Query language
  • Database
• Schema


  design of the data and how it is organised

  • identify the data needed
  • Identify how the data are related to each other
  • Identify tables and fields to organise the data
  • Within each table - assign primary key and perform normalisation to eliminate data redundancy
  • think each field of a table - assign a data type to dictate what functions can be performed on the data
• Query Language


  Language used to manipulate the data stored within a DBMS
• What does Enterprise level DBMS has?

  • many tables
  • many databases
  • multiple computers
  • complex in design
  • concurrency -> several users access the database at the same time
  • supports more than one application
• Why would you choose a database system, instead of simply storingdata in operating system files?

  fast data access, efficient data management and systematic data protection and security
• What are some disadvantages of DBMS as compared to Flat-file model?

  • Complexity
  • Size
  • Cost of DBMS
  • Additional hardware costs
  • Cost of conversion
• Components of a Database System

  • Users
  • Hardware
  • Software
  • Procedures
  • Data
• 3 Levels of Data Models in Database Design

  • Conceptual Data Model
  • Logical Data Model
  • Physical Data Model
• Conceptual Data Model

  • Highest level of abstraction
  • High level description
  • useful for requirements understanding
  • Entity-Relationship (ER) diagrams
• Logical Data Model

  • describes how the conceptual schema is translated into a logical schema
  • Entities and relationships are converted into tables, attributes, primary and foreign keys
  • using of Normalisation to optimise the logical structure
  • data are organised into simple tables of related information
• Relational Database


  Primary key with records and fields

  
• what does Database contains?

  • data
  • structure
  • metadata
• Metadata

  data describing the structure of the data in the database

  • number of records
  • data type
  • size, description, default value of each field
  • rules of use
• Data Dictionary


  created to hold the metadata
• Physical Data Model

  logical model is translated into a physical model

  • includes decisions about the type of storage (hard disk, SSD)
  • database system (Oracle, MySQL)
  • Optimisation of performance
• What does physical data model detail in?

  It details how the data model will be implemented in the database

  • description about giving details about record formats, file structures
  • storage paths, indexing strategies, hardware specifications