Departments› Computer Science› Module III› Database Management› Ch 6: Database applications
Computer Science  ·  Level 6
Database Management
Chapter 6: Database applications
📚 8 Topics
← Chapters ⬇ Download PDF
⚑ What you will be able to do

By the end of this chapter, you will be able to:

  • Effectively use decision support systems to help design databases.
  • Correctly apply data mining techniques to find useful information in databases.
  • Create distributed databases that meet required standards.
  • Accurately and securely warehouse data following best practices.
  • Use spatial and geographical database features to manage location-based data properly.
  • Handle various media types by using multimedia databases effectively.
  • Apply mobility and personal database techniques to support mobile data access.
  • Design complete and accurate data warehouses based on given requirements.

Mastering these skills will help you build powerful, efficient databases that meet real-world needs in many industries.

6.1 Decision support system

A decision support system (DSS) is a computer-based information system that helps managers make semi-structured and unstructured decisions by combining data, analytical models and an interactive user interface. Unlike a transaction processing system, which records day-to-day operations, a DSS lets the user ask "what if" questions, compare options and see the likely results before acting. It supports the decision maker rather than replacing them.

Example: a maize milling company in Moi's Bridge uses a DSS that combines grain stock levels, maize prices from the National Cereals and Produce Board and past sales to decide how much maize to buy before the harvest season ends.

Components of a DSS

  • Database (data management subsystem): stores internal data such as sales and stock, and external data such as market prices and weather.
  • Model base (model management subsystem): holds statistical, financial, forecasting and optimisation models such as linear programming and regression.
  • User interface (dialogue subsystem): dashboards, forms, charts and reports through which the manager interacts with the system.
  • Knowledge base: in intelligent DSS, rules and expertise that suggest options.
  • Users: managers and analysts who frame the problem and interpret results.

Types of DSS

  • Data-driven DSS: analyses large volumes of data, often from a data warehouse, using queries and OLAP.
  • Model-driven DSS: uses mathematical models, for example a loan pricing or production scheduling model.
  • Knowledge-driven DSS: recommends actions using rules or expert knowledge.
  • Document-driven DSS: retrieves and analyses unstructured documents.
  • Communication-driven (group) DSS: supports several people deciding together.

Characteristics

  • Interactive: the user changes inputs and sees new results quickly.
  • Flexible: handles problems that change over time.
  • Supports what-if and goal-seeking analysis: for example, "what price gives a 20% margin?".
  • Combines models with data: both internal and external.
  • User-controlled: the manager makes the final decision.

Analysis techniques used

  • What-if analysis: change a variable and observe the effect.
  • Sensitivity analysis: find which inputs affect the outcome most.
  • Goal seeking: work backwards from a target to the required input.
  • Optimisation: find the best value subject to constraints.

Role of the database in a DSS

The database supplies clean, integrated and timely data. It is usually read-only for the DSS and is often fed from operational databases through ETL into a data warehouse or data mart. Queries are written in SQL or generated by business intelligence tools such as Power BI.

Advantages and limitations

Advantages Limitations
Faster, better-informed decisions Expensive to develop and maintain
Explores many alternatives quickly Quality depends on data quality
Improves communication with charts and reports Users may rely on it too much
Supports managers at all levels Models may not capture every real-world factor
The rest of this chapter
🔒

Create a free account to open more of this chapter.

Free: practical guides, quick cards, workplace scenarios and more.

Create a free account
🔒6.2 Data mining

Data mining is the process of discovering useful patterns, relationships, trends and anomalies in large data sets using statistical, machine learning and database techniques. It is the analysis step of knowledge discovery in databases (KDD), turning stored dat…

🔒6.3 Distributed Databases

A distributed database is a single logical database whose data is stored across several computers (sites) linked by a network, managed by a distributed database management system (DDBMS) so that users see it as one database. Each site can process local queries…

🔒6.4 Features of Data warehouses

A data warehouse is a large, central store of integrated historical data drawn from many operational systems and organised for querying, reporting and analysis rather than for daily transactions. Bill Inmon defined it as a subject-oriented, integrated, time-va…

🔒6.5 Features of Spatial and geographical databases

A spatial database stores and queries data that describes objects in space, such as points, lines and polygons, together with their attributes. A geographical database is a spatial database whose objects are located on the Earth's surface using a coordinate re…

🔒6.6 Features of Multi-media databases

A multimedia database stores, manages and retrieves media data such as images, audio, video, animations and documents together with ordinary text and numbers. Media objects are large, have time and space properties, and are often searched by their content, so…

🔒6.7 Mobility and personal databases

Mobile databases are databases that run on, or are accessed from, portable devices such as smartphones, tablets and laptops that move and may lose their network connection. Personal databases are small databases designed for and controlled by a single user or…

🔒6.8 Design and implementation of data warehouses

Designing and implementing a data warehouse means turning business questions into a dimensional data model, building the processes that extract and load data, and providing tools for users to analyse it. The work follows a planned life cycle so that the wareho…

Chapter Practical Activities

Practical 1: Create a Simple Student Database with Two Tables

Computer Science · Level 6
Database Management
PRACTICAL ASSESSMENT
TIME: 4 HOURS
⬇ PDFCandidate Instructions (Candidate Tool)

Type: Individual

INSTRUCTIONS TO CANDIDATE:
1.  You are required to perform the following task:
i.  Create a student database named 'StudentDB' with two tables: 'StudentDetails' and 'StudentMarks', where StudentDetails has fields StudentID (Primary Key), FullName, Gender, and DateOfBirth, and StudentMarks has fields StudentID (Foreign Key), Subject, and Marks.
2.  You have been provided with the following resources for the practical task:
Tools & EquipmentMaterials
Functional computer installed with MS Access or equivalent DBMSPlain A4 paper
⬇ PDFResources Required (Cutting List)
S/NItemQuantity
1Functional computer installed with MS Access or equivalent DBMS1 Pc per Candidate
2Plain A4 paper2 Sheets per Candidate
⬇ PDFAssessor Guide
Items to be EvaluatedMarks AvailableMarks ObtainedComments
TASK 1: Database Creation and Table Setup
Launched the database management system successfully
(Award 2 marks for correct launch or zero)		2
Created a new database named 'StudentDB'
(Award 2 marks for correct database name or zero)		2
Created table 'StudentDetails' with fields StudentID (Primary Key), FullName, Gender, DateOfBirth
(Award 1 mark per correctly created field or zero)		3
Created table 'StudentMarks' with fields StudentID (Foreign Key), Subject, Marks
(Award 1 mark per correctly created field or zero)		3
Sub-Total10
TASK 2: Data Entry
Inserted at least five records into 'StudentDetails' table
(Award 3 marks for correct insertion of 5 records or proportional)		3
Inserted at least ten records into 'StudentMarks' table linked to StudentDetails
(Award 4 marks for correct insertion of 10 records or proportional)		4
Sub-Total7
TASK 3: Query Design and Execution
Created a query to extract all male students
(Award 2 marks for correct query or zero)		2
Created a query to extract students scoring above 70 marks
(Award 2 marks for correct query or zero)		2
Created a query to calculate total marks per student and stored in a new field
(Award 3 marks for correct calculation or zero)		3
Created a query to calculate average marks per student and stored in a new field
(Award 3 marks for correct calculation or zero)		3
Sub-Total10
PRODUCT CHECKLIST
Database 'StudentDB' contains two correctly structured tables with proper field names and data types
(Award 5 marks for complete and accurate table design or zero)		5
Tables contain the required data records with correct linking via StudentID
(Award 5 marks for correct data insertion and relational integrity or zero)		5
Queries run successfully and return correct results as per task requirements
(Award 5 marks for queries returning correct results or zero)		5
Sub-Total15
GRAND TOTAL42
ASSESSMENT OUTCOME:   ☐ Competent    ☐ Not Yet Competent (competent if at least 50%)

Practical 2: Design and Create Student Enrollment Database Tables

Computer Science · Level 6
Database Management
PRACTICAL ASSESSMENT
TIME: 4 HOURS
⬇ PDFCandidate Instructions (Candidate Tool)

Type: Individual

INSTRUCTIONS TO CANDIDATE:
1.  You are required to perform the following task:
i.  Design and create two database tables named StudentDetails and EnrollmentRecords with specified fields and data types as per the provided schema.
2.  You have been provided with the following resources for the practical task:
Tools & EquipmentMaterials
Functional computer installed with MS Access or equivalentPlain A4 paper
⬇ PDFResources Required (Cutting List)
S/NItemQuantity
1Functional computer installed with MS Access or equivalent1 Pc per Candidate
2Plain A4 paper2 Pcs per Candidate
⬇ PDFAssessor Guide
Items to be EvaluatedMarks AvailableMarks ObtainedComments
TASK 1: Create Database and Tables
Observed computer laboratory rules
(Award 2 marks or zero)		2
Launched MS Access or equivalent database software correctly
(Award 2 marks or zero)		2
Created a new database named StudentEnrollment
(Award 3 marks or zero)		3
Created StudentDetails table with fields: StudentID (AutoNumber), FullName (Short Text), DateOfBirth (Date/Time), Gender (Short Text), PhoneNumber (Short Text)
(Award 1 mark per correct field name and data type, max 5 marks)		5
Created EnrollmentRecords table with fields: EnrollmentID (AutoNumber), StudentID (Number), CourseCode (Short Text), EnrollmentDate (Date/Time), Status (Short Text)
(Award 1 mark per correct field name and data type, max 5 marks)		5
Sub-Total17
TASK 2: Populate Tables with Records
Inserted at least five valid records into StudentDetails table
(Award 1 mark per correctly inserted record, max 4 marks)		4
Inserted at least five valid records into EnrollmentRecords table linking to StudentDetails
(Award 1 mark per correctly inserted record, max 4 marks)		4
Sub-Total8
TASK 3: Modify Tables and Calculate Fields
Added a field TotalCourses (Number) to StudentDetails table
(Award 2 marks or zero)		2
Added a field AverageStatus (Short Text) to StudentDetails table
(Award 2 marks or zero)		2
Calculated and updated TotalCourses field with the count of courses each student is enrolled in
(Award 4 marks for correct calculation and update)		4
Determined and updated AverageStatus field with the most frequent enrollment status per student
(Award 4 marks for correct calculation and update)		4
Sub-Total12
PRODUCT CHECKLIST
StudentDetails and EnrollmentRecords tables exist with correct structure and fields
(Award 5 marks if both tables have all specified fields with correct data types)		5
Tables contain at least five valid records each with correct data
(Award 5 marks if records are accurate and linked correctly)		5
TotalCourses and AverageStatus fields correctly added and populated in StudentDetails table
(Award 6 marks if fields exist and contain correct calculated values)		6
Sub-Total16
GRAND TOTAL53
ASSESSMENT OUTCOME:   ☐ Competent    ☐ Not Yet Competent (competent if at least 50%)
🔒

Free: practical guides, quick cards, workplace scenarios and more.

Create a free account
🔒Establishing Relationships Between Employee and Department TablesPractical 3
🔒Data Entry and Validation in Student DatabasePractical 4
🔒Query Creation and Execution on Student DatabasePractical 5
🔒Design and implement a data input form for a student databasePractical 6
🔒Generate structured database reports using MS AccessPractical 7
🔒Database Backup and Restoration for SalesDBPractical 8
🔒Implementing User Permissions and Security Measures in a Student DatabasePractical 9
🔒Import and Export Data in a Student Records DatabasePractical 10
Am I competent?

At the start of this chapter we promised you would be able to:

  • Effectively use decision support systems to help design databases.
  • Correctly apply data mining techniques to find useful information in databases.
  • Create distributed databases that meet required standards.
  • Accurately and securely warehouse data following best practices.
  • Use spatial and geographical database features to manage location-based data properly.
  • Handle various media types by using multimedia databases effectively.
  • Apply mobility and personal database techniques to support mobile data access.
  • Design complete and accurate data warehouses based on given requirements.

Tick each one you can genuinely do.

So, are you there yet?

You're competent when you can confidently do 50% or more of what this chapter promised.

Sign in to record how you're doing.

← Ch 5: Indexing and hashing