13-Mar-19
Data processing
• Data: raw facts and figures
• An important component of a computer system
Databases • A computer processes data (input) and
transforms it into required information (output)
Behjat Zuhaira
• Do we require storing data in computer?
b_zuhaira@comsats.edu.pk
3 4
Definition DB helper documents
• Organized collection of data Forms Reports
▫ Store large collections of data • To fill a record • Produce printed results from
▫ Organize the data • Often used to change or view the database
▫ Becomes a data storage system data • Includes tools to summarize
• Stores a collection of related items data
• Collection is arranged in a structure • Easy to read/understand
▫ Organizes and describes the data
Organized collection of related data, stored in a
computer
OR
Collection of related items/facts arranged in a specific
structure
1
13-Mar-19
5 6
A form A report
7 8
Another report Database models
• A type of data model that determines logical
structure of a DB
• Determines the manner in which data can be stored,
organized, and manipulated
• Most popular examples
▫ Flat-file models
▫ Relational models (SQL: structured query language)
▫ Non-relational (NoSQL) models
▫ Object models
▫ Document models
2
13-Mar-19
9 10
Flat-file Databases
• A flat-file is a file having no internal hierarchy
▫ containing records that have no structured interrelationship; e.g. a text
document without any word processing capabilities, markups (tags)
• Flat-File DB: stores data in plain text files
• Typically has only one table; If multiple, each has a separate file
▫ Each record is on a separate line
• Useful for simple data storage needs
• Hard to manage large data needs; difficult to maintain
• Limited capabilities
• Easy to learn and use
• More chances of error
• Can waste disk space
▫ A lot of redundant data may exist in large flat-file DB systems
▫ Adding, deleting, changing any field requires same changes in every file
that contains the same field
11 12
Relational Databases Entity-relationship diagram (ERD)
• Made of two or more tables or relations
• Tables are related by a common field
▫ Called a relationship or join (student and teacher
table)
▫ Can help organize data
• Most common form of database
• Maintaining data is easier than flat-file
• No wasted disk space
• More capabilities
3
13-Mar-19
13 14
A school’s database A hospital’s database
15 16
Comparison Relational DB structure
Field Name
Field
Record
4
13-Mar-19
17 18
Relational DB structure Relational DB structure
Fields/attributes Records/tuples Tables/relations
• Hold an individual piece of • One full set of fields • One complete collection of
data • Often called a row records
• Are named descriptively • Phone book example • Databases may have
• Often called a column ▫ Smith, Joe, 123 Some thousands of tables
• Phone book examples Street, 412-555-7777
▫ Name, address, e-mail, • Databases may have unlimited
phone number rows
• Fields may contain no data
19 20
The database management system
(DBMS)
• Programs/software that control the database
• Interacts with end users, applications, and the DB to capture
and analyze the data
• Allows
▫ Storing or Entering data
▫ Accessing or Querying data
▫ Processing data
▫ Finding data: very important; as DB size grows tremendously,
finding a record in less time is not an easy task
▫ Printing reports
• Supports thousands of users; includes tools to protect the data
• Relational DBMS (RDBMS) for relational DB
• Examples: Oracle, MS SQL Server, IBM DB2, SAP, MySQL
5
13-Mar-19
21 22
Working with a Database Working with a Database
Creating tables Field types Entering data into a table Sorting records
• List the necessary fields • Describes the type of data stored • Users type data into a field • Order records based on a field
• Steps to define a field • Most DBMS use the same types • Data must be entered accurately • Multiple sub sorts resolve „ties‟
▫ Text fields store letters and ▫ Constraints help to verify
▫ Descriptively name the field • Several types of sorts
numbers data
▫ Specify the field type ▫ Numeric field store numbers • Forms are typically used for data ▫ Alphabetic
▫ Determine the field size ▫ Date and time field entry ▫ Numeric
▫ Logical field stores yes or no Viewing records ▫ Chronological
▫ Binary field stores images or • Datasheet view shows all records ▫ Ascending
sounds
• Filters can limit the records shown ▫ Descending
▫ Counter field generates
sequential numbers ▫ Display only records matching a
criteria
▫ Memo fields store large
amounts of data • Forms allow viewing one record
23 24
Working with a Database Query languages
Querying a database Generating reports • Query languages • SQL
▫ All DBMS use a query Select FirstName, LastName,
• Statement that describes • Printed information extracted language Phone
desired data from Most DBMS modify the From tblPhoneNumbers
• List of fields can be modified a database language Where LastName=“Norton”;
• Uses of querying • Can calculate data ▫ Structured Query Language
(SQL) • xBase
▫ Find data ▫ Calculate data per row
Most common query
▫ Calculate values per record ▫ Calculate for entire table language
Use tblPhoneNumbers
• Pictures and formatting can be ▫ xBase List FirstName, LastName,
▫ Delete records Phone
• Most important DBMS skill included Query language for dBase
systems For LastName=“Norton”
▫ Query by example (QBE)
Interface to SQL or xBase
Interactive query design
6
13-Mar-19
25 26
Components of a DB system
• Users
• Database application
• DBMS
• Database
27 28
DBMS: four main functional groups New advancements in databases
• Data definition • Knowledge graph ??
▫ Defines organization of data
• Unstructured databases ??
▫ Creation, modification, removal of definitions
• Update • Business analytics / business intelligence ??
▫ Insertion, modification, deletion of actual data • Big data ??
• Retrieval
▫ Accessing the actual data
• Administration
▫ Registering, monitoring users
▫ Enforcing data security
▫ Monitoring performance
