Chapter 5 ILS

• 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
• Database components

  • FIELDS
  • RECORDS
  • PK
  • FK
• Relational database


  • Contains numerous tables
  • A table is made up of a number of records
  • A record in a table is a row
  • Each record is made up of a number of fields
  • A field in a table is a column e.g. name, date of birth, address etc. for a student
  • Each table stores the data about someone or something of interest to the firm, known as an entity
• Primary key


  • A field that is defined to be unique for each entity e.g. Student number, Exam ID and Library ID, ID number
  • Used to join tables
• Foreign key


  The primary key of one table found in another table
• Primary Key Rules


  • Values in this column must be unique i.e. different
  • There must be NO empty cells in this column i.e. no NULL values
• Foreign Key Rules


  • All values in this column must be found in the PK of the linked table
  • There can be EMPTY cells in this column
• 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
• Database design


  1. Identify all entities/tables

  2. Identify all relationships between entities

  3. Identify all attributes/characteristics

  4. Resolve all relationships (normalization)
• Database design


  1. Interviewing staff (managers and users)

  2. Observing staff at work

  3. Reviewing existing documentation
• Relationships between entities


  Governed by business rules
• Data definition language (DDL)

  A collection of instructions and commands used to define and describe data and relationships in a specific database
• SQL to define a table

  • CREATE DATABASE Lettings;
  • CREATE TABLE Table_name (column1 datatype, column2 datatype, column3 datatype ..., PRIMARY KEY (one or more columns));
  • CREATE TABLE landlords (Firstname CHAR(100), Surname CHAR(10),Telephone CHAR(11), PRIMARY KEY (LandLord_num));
• Data manipulation language (DML)


  The commands that are used to manipulate the database
• SQL to manipulate data

  • SELECT surname, telephone FROM lettings WHERE surname = "M*"
  • SELECT * FROM EMPLOYEE WHERE JOB_CLASSIFICATION = "C2"
  • INSERT INTO landlords('John', 'Smith', '011-4217401');
  • ROLLBACK;
  • UPDATE landlords SET SURNAME = "Minty" WHERE SURNAME = "Rajcoomar"
  • SELECT * FROM ORDER WHERE Customer_Number = '10'
  • SELECT Description, Price, Colour FROM ORDER WHERE Customer_Number = '10'
• 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 of data and information
• Database Output


  • Can produce any desired reports, documents
  • Appears in screen displays or hard-copy printouts
  • Output-control features allow selecting the records and fields to appear in reports
  • Can make calculations specifically for the report by manipulating database fields
  • Formatting controls and organization options help to customise reports and create flexible, convenient, and powerful information-handling tools
• Database Management System (DBMS)


  A group of programs used as an interface between a database and application programs or a database and the user e.g. MS ACCESS, ORACLE
• Important characteristics of databases to consider

  • Database size
  • Database cost
  • The number of concurrent users
  • Performance
  • Integration
  • Vendor
• Database administrator (DBA)

  • Plans, designs, creates, operates, secures, monitors, and maintains databases
  • Works with both users and programmers
• Linking databases to the Internet

  Allows people to access and manipulate a number of traditional databases at the same time through the Internet
• Semantic Web


  Developing a seamless integration of traditional databases with the Internet
• Data warehouse


  Database that collects business information from many sources in the enterprise, covering all aspects of the company's processes, products, and customers
• Data mining


  Information-analysis tool that involves the automated discovery of patterns and relationships in a data warehouse
• Data mining


  1. Analysing data to try to discover patterns and relationships within the data

  2. Association rules algorithms are used to find associations between items in the data
• Data mining uses


  • Improve customer retention
  • Identify cross-selling opportunities
  • Manage marketing campaigns
  • Market, channel and pricing analysis
  • Customer segmentation analysis (especially one-to-one marketing)
• Business intelligence (BI)


  • Process of gathering enough of the right information in a timely manner and usable form and analyzing it to have a positive impact on business strategy, tactics, or operations
  • Turns data into useful information that is then distributed throughout an enterprise
• Distributed database


  • A set of databases stored on multiple computers, but that present as a single database to users
  • Gives corporations more flexibility in how databases are organized and used
• Advantages of distributed databases


  • Nodes can easily share data with other nodes
  • It can be scaled as required – more nodes added when necessary
  • Failure of one node does not lead to the failure of the entire distributed system
  • Other nodes can still communicate with each other
• Replicated database


  • Database that holds a duplicate set of frequently used data
  • Database replication is the frequent electronic copying of data from a database in one computer or server to a database in another
• Advantages of database replication


  • Better performance
  • Better data recovery