DATABASES

• Data management and modelling

  Key aspects of organizing data and information
• Well-designed and well-managed database

  • Central to almost all information systems
  • Extremely valuable tool in supporting decision-making
• The number and type of database applications will continue to evolve and yield real business benefits
• Database

  The centre of almost every information system
• Relational database

  A series of related tables, stored together with a minimum of duplication to achieve consistent and controlled pool of data
• Table

  Made up of a number of records
• Record

  Made up of a number of fields
• Entity

  A person, place, or thing for which data is collected, stored, and maintained
• Attribute

  A characteristic of an entity
• Data item

  The specific value of an attribute
• Primary key

  A field or set of fields that uniquely identifies the record
• Primary keys are used to join tables
• Referential integrity means you cannot have an instance of a foreign key before it exists as an instance of a primary key
• Creating relationships prevents the data from being stored in both tables (i.e. data redundancy), thereby reducing costs
• Advantages of the database approach

  • Improved strategic use of corporate data
  • Reduced data redundancy
  • Improved data integrity
  • Easier modification and updating
  • Data and program independence
  • Better access to data and information
  • Standardization of data
  • A framework for program development
  • Better overall protection of the data
  • Shared data and information resources
• Disadvantages of the database approach

  • More complexity - DBMS can be difficult to set up and operate
  • More difficult to recover from a failure - With a DBMS, a failure can shut down the entire database
  • More expensive - DBMS can be more expensive to purchase and operate
• Database design

  Also known as a data model or a database schema, a list of all the tables in the database, along with all the fields, with any primary and foreign keys identified
• Designing relational databases

  1. Identify all entities

  2. Identify all relationships between entities

  3. Identify all attributes

  4. Resolve all relationships
• Degree

  The number of entities involved in a relationship
• Cardinality


  In a relationship, cardinality is the number of one entity that can be related to another entity
• Optionality


  If a binary relationship is optional for an entity, that entity doesn't have to be related to the other
• Enterprise rules govern the relationships between entities
• Types of binary relationships

  • One-to-one relationship, obligatory on both sides
  • One-to-one relationship, optional on one side
  • One-to-one relationship, optional on both sides
  • One-to-many relationship, many side optional to one side
  • One-to-many relationship, many side optional on both sides
  • Many-to-many relationship
• DBMS


  A group of programs used as an interface between a database and application programs or between a database and the user
• Capabilities and types of database systems


  • Creating and Modifying the Database
  • Storing and Retrieving Data
  • Manipulating Data and Generating Reports
• Data definition language (DDL)


  A collection of instructions and commands used to define and describe data and relationships in a specific database
• Structured Query Language (SQL) is a DDL
• SQL to define a table


  • CREATE DATABASE Lettings;
  • USE Lettings;
  • CREATE TABLE landlords(
  • Firstname CHAR(10),
  • Surname CHAR(10),
  • Telephone CHAR(10);
  • )
  • INSERT INTO landlords(
  • 'John', 'Smith', '123456'
  • )
• Data dictionary


  Stores meta data, a detailed description of all the data used in the database, to achieve reduced data redundancy, increased data reliability, assist program development, and easier modification
• A field is a specific type of information that can be contained within a record, such as a person's first name, last name, street address, city, state, zip code, email address, etc.
• A record is an individual piece of information about something or someone, such as a person's name, address, phone number, etc.
• The database is the collection of all data stored on a computer system.
• Relational databases


  Lists of information are related to one another
• Microsoft Access


  • Provides tools that allow sorting, grouping, analyzing and reporting data in many different ways
• Access objects


  • Table - contains all the raw data within a database; tables are linked with a common field to create a relational database, which minimizes redundant data
  • Query - allows you to select a subset of fields or records from one or more tables; queries are created when you have a question about the data
  • Form - Provides an easy-to-use data entry screen
  • Report - Provides a professional printout of data that can contain enhancements such as headers, footers, graphics, and calculations on groups of records

  
• Create a database


  1. Start by using an Access template or by opening a blank database

  2. Table Design View provides the most options for defining fields

  3. Datasheet View is a spreadsheet-like view of the data in a table
• Data types


  • Numbers
  • Text
  • Dates
• Create a table


  1. Defining the fields

  2. Selecting data type for each field

  3. Naming the table

  4. Determining how the table will participate in the relational database

  
• Important database terminology


  • Database
  • Theory
  • ERD
  • Table
  • Flat file
  • Entity
  • Column
  • Field
  • Attribute
  • Row
  • Record
  • Instance of an entity

  
• Primary key field


  Contains data that uniquely identifies each record; no 2 records can have the same entry in this field