Outlin

Introduction es
Chapter 7: Traditional File Processing System
 Limitations of Traditional File
Processing Systems
Databases
Database Management System
 Advantages of DBMS

Database Design Concepts

Database Design
 Conceptual Database Design
 Logical Database Design
 Physical Database Design

Types of Database
MS Access
Database Design in MS Access
 Introduction
• A collection of logically related data organized to be accessed, managed and
updated easily.
• Small data collection or a large scale database.
• Used for various purposes including:
 Storing internal data
 Analyzing the market
 Analyzing users’ interest
 Discovering patterns in data and more.

• Information is organized in rows and columns

(forming tables).
• Indexed information:
 Easier search.
 Easier modification.
 Easy to add or remove data items.

Introduction To Computer & ICT 2

 Introduction
 Data
• Facts concerning objects and events.
• A salesperson’s data:
 Names
 Addresses
 Phone numbers
 Items
 Prices and more.

• Numeric data
• Textual data
• Graphic data (Pictures and videos)
• Qualitative data
• Quantitative data.

Introduction To Computer & ICT 3

 Introduction
 Information
• Structured / processed data.
• Meaningful in some context.
• Well organized data on the basis of which an organization can take some
decision and can discover useful patterns in it.

Data Information

Introduction To Computer & ICT 4

 Introduction
 Metadata
• Data about data.
• Describes the characteristics and context of data.
• The primary mechanism to provide the context for data.
• Examples:
 Data type
 Length
 Maximum/minimum values

Introduction To Computer & ICT 5

 Traditional File Processing System
• Spreadsheets, word processing applications.
• A group of files that store data of an organization.
• Manual/computer based filling systems.
• All the departments have to maintain their own sets of files.

 Limitations
• Duplicate data; complex and hard to manage.
• Separate sets of files for each department or organization.
• Limited data sharing.
• Hard to develop and maintain.
• Excessive program maintenance.
• Less security.

Introduction To Computer & ICT 6

 Database Management System
• A computer software for creating and maintaining databases.
• A systematic way to create, retrieve, update and manage data.
• An interface between the database and the user.

• DBMS manages three things:

 The data.
 Database Engine, used to access and modify the data.
 Database Schema i.e. the logical structure of the database.

 Advantages

• Data Sharing
 A centralized database, visible to every user and department.

• Concurrency
 Multiple users can work with the database to add, update and update data.

Introduction To Computer & ICT 7

 Database Management System
 Advantages (continued…)
• Data Integrity
 Consistent, accurate and trustworthy data.

• Data Quality
 Improved data quality by specifying integrity constraints i.e., rules that can’t be
violated by database administrators.

• No Redundancy
 Data is recorded in a centralized database accessible from everywhere.

• Backup and Recovery

 Backup and recovery mechanism recovers the database in case of accidental loss.

• Security and Privacy

 Limited access to end users.
 Data can be accessed and modified only by authorized person.
 Limited privileges for each user

Introduction To Computer & ICT 8

 Database Design Concepts
 Entity
• A person, place, object or event.
• Must be of some interest to the organization.
• E.g. a college database may contain Student,
Teacher, Admission and Courses etc.
• Represented by a Rectangle.
Product

Course Customer

Room Entities Order

Sales
Building man
Exam

Introduction To Computer & ICT 9

 Database Design Concepts
 Attributes

• A property of an entity that is of interest to the organization.

• E.g. the Student entity may have an id, name, address, admission number etc.
• Represented by circles, or alternatively in form of a table.

Introduction To Computer & ICT 10

 Database Design Concepts
 Relationships

• An association that represents interaction among in a database.

• For example, a student in a college may have many courses and a course can be
completed by several students.

• A relationship may be:

 One to one

 One to Many

 Many to many
Introduction To Computer & ICT 11
 Database Design Concepts
 Entity Relationship Diagram
• A detailed logical representation of the data in an organization.
 Entities
 Relationships among entities
 Attributes of the entities and relationships.

Introduction To Computer & ICT 12

 Database Design Concepts
 Relation
• A named, two dimensional table of data with:
 A set of named columns (representing attributes)
 Arbitrary number of unnamed rows (representing data values for a single entity)

• In its simplest form, a relation is denoted as:

 Student (ID, Name, Address, Phone_no, Major)
• Or

Introduction To Computer & ICT 13

 Database Design Concepts
 Table
• A collection of data held in a structured format within
a database.
• Vertical columns, identified by names.
• Horizontal rows, holding data for a single entity.

Introduction To Computer & ICT 14

 Database Design Concepts
 Record
• A complete set of information, relevant to a specific entity.
• Also called row; comprised of a single row in a table.

• Example:
 The information (values against each attribute) for a specific student.

Introduction To Computer & ICT 15

 Database Design Concepts
 File
• A related collection of records.
• Example:
 A collection of the records of all the students enrolled in a course.

 Field
• A data structure for a single piece of data.
• Example:
 Customer_ID, Name, Address, Phone_no are some of the fields in the table
Customer in an organization’s database.

• Fields are organized into records.

Introduction To Computer & ICT 16

 Database Design
 Conceptual Database Design

• First step of database design.

• Entities and their relationships are recognized by studying the organization.

• Output:
 Entity relationship model, comprised of entities and their relationships

Introduction To Computer & ICT 17

 Database Design
 Logical Database Design
• An extended version of the conceptual design:
 Entities, their attributes and the relationships.
 Primary keys (that uniquely define entities of an entity type).
 Foreign keys (used to relate two tables).
 Redundancy and other anomalies removed by Normalization.

Introduction To Computer & ICT 18

 Database Design
 Physical Database Design
• The logical database design is converted into a detailed design.
• Data types and lengths etc. of the attributes.
• The output is a design ready to be implemented.

Introduction To Computer & ICT 19

 Types of Database
 Flat File Database
• Files with no structured relationships among the records.
• Data is stored in a single table.
• Useful for storing small amount of records.
• Example: Spreadsheets.

 Document Oriented Database

• Data is stored in form of text records rather than tables.
• Useful for document based application.

 Embedded Database
• Runs within an application rather than a separate database application.
• Integrated with program that needs to store data.
• Hidden from the user, requiring little or no maintenance.

Introduction To Computer & ICT 20

 Types of Database
 Distributed Database
• A set of interconnected databases located at different locations.
• Logically a single database.
• Data is accessed and modified with the help of a network.

Introduction To Computer & ICT 21

 Types of Database
 Centralized Database
• Located and maintained on a single location.
• Accessible from different locations via a network.

 Knowledge Base
• Stores complex information used by computer systems.
• Structured or unstructured data.
• Examples:
 Expert system, which uses knowledge base containing facts about a specific field.

 Hypertext Database
• Used to organize large, dissimilar information.
• No regular structure, different from commonly used databases.
• Example:
 Databases for maintaining encyclopedias.
Introduction To Computer & ICT 22
 Types of Database
 Operational Databases

• Store data related to the operations in an organization.

• Used to modify (add, update or delete) data in real time.
• Example:
 A database storing daily transactions data of an organization.

 Online Database

• Database made accessible form the internet.

• Hosted on websites, to be accessed using a web browser.
• May be free or may require subscription.

Introduction To Computer & ICT 23

 MS Access
• A DBMS, part of the MS Office suite.
• Lightweight and easy to use tool for designing database.
• Not suitable for designing large scale databases.
• Used to design and maintain small scale databases.
• Key features:
 Creating databases
 Creating tables
 Creating relations
 Inserting data into tables
 Data validation
 Creating forms
 Creating Queries
 Creating reports and more.

Introduction To Computer & ICT 24

 Database Design in MS Access
 Creating Database

• File  New  Blank desktop database.

• Choose a destination folder and click “Create”.

Introduction To Computer & ICT 25

 Database Design in MS Access
 Creating Tables
• Navigate to Create tab on the ribbon and click
Table.
• A table “Table 1” will be created. Right click on it
and click “Save”.

• Name the table, e.g. Student.

Introduction To Computer & ICT 26

 Database Design in MS Access
 Creating Tables (continued…)
• Each table is created with one field, the ID.
• Right click it and rename to something relevant to the table, e.g. Student_ID.
• ID field should be “Auto Number” and “Unique” as it is the primary key.
• This is done by selecting the field and navigating to the FIELDS tab on the
ribbon.

Introduction To Computer & ICT 27

 Database Design in MS Access
 Creating Tables (continued…)
• To add a new field, click on “Click to Add” and select a suitable datatype, e.g.
“Short Text” for the field Name.

• Create all the fields in the table the same way

and save the table.

• Right click on the table and select “Design View”

to view the table in design view.

Introduction To Computer & ICT 28

 Database Design in MS Access
 Inserting Data into Tables

• Switch to datasheet view and insert records.

• The ID will be generated automatically for each record being inserted.

Introduction To Computer & ICT 29

 Database Design in MS Access
 Data Validation
• To restrict the user not to enter invalid data.
• E.g. currency and price can’t be negative, a field can accept only letters etc.
• Achieved by the use of validation rules.

• Add validation rules to a table:

 Right click on the table and select
“Design View”.

 Highlight the field to which a

validation rule is to be added.

 Add the validation rule against

“Validation Rule” in the field
properties.

Introduction To Computer & ICT 30

 Database Design in MS Access
 Validation Rules
• Example, validation rule for email field:
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))
Where “Is Null” means the field can be empty.
• Commonly used validation rules:
To do….. Validation Rule
Accept letters only Is Null OR Not Like "*[!a-z]*"
Accept digits only Is Null OR Not Like "*[!0-9]*"
Accept digits and letters only Is Null OR Not Like "*[!((a-z) or (0-9))]*"
Accept a string of exactly 5 characters Is Null OR Like "?????"
Accept a value of exactly 4 digits Is Null OR Like "####"
Accept positive numbers only Is Null OR >=0
Accept date that is not future date Is Null OR <= Date()
Accept only “Male” and “Female” Is Null OR "Male" OR "Female"

Introduction To Computer & ICT 31

 Database Design in MS Access
 Creating Query
• To manipulate data in the database, e.g. to update a field or delete a record.
• Example: Update student name in the first record.
 Create  Query Design
 Select the table (Students) and click Add.

 Next, click Update in Design tab

Introduction To Computer & ICT 32

 Database Design in MS Access
 Creating Query (continued…)

 Double click the field (Name) to be updated.

 Enter the new value in the Update To box.

 Also enter the old value in the Criteria box.

 Finally click Run in the Design tab to run the

query.

Introduction To Computer & ICT 33

 Database Design in MS Access
 Creating Forms
• An object used to create user interface for the database.
• Used to access and view data from one or more tables in a database.

• Procedure:
 Create  Form Wizard

 Select a table from the

dropdown list and double click
the fields which are to be added
to the form.

Introduction To Computer & ICT 34

 Database Design in MS Access
 Creating Forms (continued…)
 On the next screen, select a layout for the form.
 Give the query a name before finishing.
 In Form View, the output would be something like this.

Introduction To Computer & ICT 35

 Database Design in MS Access
 Creating Reports
• An object used to display data from the database.
• Pick data from one or more tables using a query and then create a report.
• Example:
 A report containing names, addresses and phone number of students.

 Create a query that selects

names, addresses and
phone number of all the
students in the table.

 Select the query and click

on Create  Report

Introduction To Computer & ICT 36

 Database Design in MS Access
 Creating Reports (continued…)

 The report opens in Layout mode where its layout can be changed.

 Switch to report view and you will see an output like this.

Introduction To Computer & ICT 37

 Database Design in MS Access
 Importing Tables to Database
• Import wizard is used to import tables etc.
from a .mdb or .accdb database.

• EXTERNAL DATA  Access

• Locate the existing database file in the wizard.

• Select “Import tables, queries, forms, reports, macros and modules”.

Introduction To Computer & ICT 38

 Database Design in MS Access
 Importing Tables to Database (continued…)

• Select the tables, queries and forms etc. that are to be imported into the
database.
• Click OK to finish import. The selected items should appear in your database.

Introduction To Computer & ICT 39

 Database Design in MS Access
 Linking Tables with Database
• To have one or more tables in multiple databases.
• The tables are stored in a single database and are linked to as many
databases as needed.
• Eliminates the need to enter or update the same data multiple times.

• Procedure:
 Similar to importing tables to database.

 Repeat the process, selecting “Link to data

sources by creating a linked table”.

 The linked tables appear in the database with

a star.

Introduction To Computer & ICT 40

 References

 Ali, R. & Ali, A. (2018). Chapter 7: Databases. Introduction to Computer

and ICT – 1st Edition (pp. 117-139). Muhalla Jangi, Qissa Khawani,
Peshawar, Pakistan: Al-ilum Publications.