Electronic Data Storage and Databases

• The chapter explores methods to store and manage data electronically
• Examines database concepts

Introduction to Electronic Data Storage

• Schools maintain student data, attendance, and guardian details
• Class teachers record daily attendance as present ('P') or absent ('A')
• Manual record-keeping for a class of 50 students with 26 working days requires 50x26 entries per month
• Manual data entry becomes tedious as data volume increases

Limitations of Manual Record Keeping

• Student details must be re-entered in the new attendance register when promoted
• Inconsistency arises when writing student details each month due to incorrectly written names or skipped records
• Data loss occurs if the attendance register is lost or damaged
• Erroneous calculations happen when consolidating attendance records manually
• Office staff maintains student details: Roll Number, Name, Date of Birth, Guardian Name, Contact Number, Address
• This information is needed for correspondence regarding student attendance and results
• Finding information or modifying entries in a large volume of papers is difficult
• Computerized systems overcome these issues by storing separate data files
• Computerized systems allow easy copying of student details, finding data about students/guardians, adding details for new students, modifying stored data, and removing data when students leave

File Systems

• A file is a container for storing data on a computer's storage device
• Files can store texts, computer program code, comma-separated values (CSV), pictures, audios/videos, and web pages
• Files on a computer can be directly accessed and searched for data
• Displaying data like monthly attendance reports on a school website requires computer programs

Storing Student and Attendance Data in Separate Files

• Student and attendance data are stored in two separate files
• The STUDENT file has six columns:
• RollNumber: Student roll number
• SName: Student name
• SDateofBirth: Student date of birth
• GName: Guardian name
• GPhone: Guardian phone number
• GAddress: Guardian address
• The ATTENDANCE file has four columns:
• AttendanceDate: Date of attendance marking
• RollNumber: Student roll number
• SName: Student name
• AttendanceStatus: Marked as Present (P) or Absent (A)

Limitations of File Systems

• File systems become difficult to manage as the number of files and data volume increase

Difficulty in Access

• Files lack mechanisms to retrieve data directly
• Data is accessed through application programs
• Retrieving specific data formats can be difficult
• It requires writing programs to access data

Data Redundancy

• Same data are duplicated in different files
• Student names appear in both student and attendance files
• Redundancy can lead to excessive storage use

Data Inconsistency

• Occurs when the same data in different places doesn't match
• Changes, like name spelling corrections or student departures, need updates in multiple files
• If changes aren't applied consistently, data becomes inconsistent

Data Isolation

• Files at Table 8.1 (STUDENT) and Table 8.2 (ATTENDANCE) are related to students
• There is no direct link or mapping between these files
• Separate programs are needed to access each file

Data Dependence

• Data are stored with a specified structure in a file. If file structure or format changes, existing application programs must be changed
• Updates to a file's structure needs modifications in all programs that has access to that file

Controlled Data Sharing

• Different users have different access needs (teachers, office staff, parents)
• Not all users should access all data
• Limited access (read-only) is difficult to enforce in file systems

Overcoming Limitations with Database Management Systems(DBMS)

• Logical relationship for data in databases overcomes file system limitations
• Related data can be organized and managed efficiently in a database
• DBMS is software for creating and managing databases and it stores, manages, updates/modifies, and retrieves data
• Open-source and commercial DBMS examples: MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB

DBMS Functionality Summary

• Hides storage and maintenance details, providing an abstract data view
• Interfaces between databases and end-users/applications
• Allows data retrieval through querying
• Enables modification of database structure

Real-Life Applications of Databases

• Banking: Customer information, account details, and loan transactions
• Crop Loan: Farmer data, land details, loan histories, and repayment data
• Inventory Management: Product details, order information, and delivery data
• Organization Resource Management: Employee records, salary details, and branch locations
• Online Shopping: Item descriptions, user login details, and user preferences

Transitioning from File System to DBMS

• Revisit the school example: two files, office and teacher maintained
• Tables in a database are linked through columns or fields
• STUDENT and ATTENDANCE files share common rollNumber and sName
• To convert these files into a database there are changes to incorporate

SName Elimination in DBMS

• SName is already present in student information, hence, is unneeded in ATTENDANCE
• Student details can be accessed through the common RollNumber

Data Redundancy Reduction

• Eliminating redundancy of guardian details where siblings are in the same class by splitting files (STUDENT file and GUARDIAN)

Unique Identification of Guardians

• One or more guardians with the same name need unique GUID (Guardian ID)

Relating Data Files in DBMS

• Related data files in student, guardian, and attendance include these attributes:
• student; rollNumber, sname, sDateofBirth, GUID
• guardian; GUID, gName, gPhone, gAddress
• attendance; attendanceDate, rollNumber, attendanceStatus
• This shows the record structure of three files in STUDENTATTENDANCE database but excludes any relationship among tables

High Costs of DBMS

• High costs may be incurred shifting from file systems to DBMS

Key Points:

• Purchasing sophisticated hardware and software
• Training users for querying.
• Recurrent cost to take regular backup and perform recovery operations

Key Concepts in DBMS

• Efficient Data Management: Understanding Key Terms

Database Schema: Blueprint

• The design/skeleton of a database describing:
• Table names
• Fields/columns
• Data types for each column
• Constraints
• Relationships among tables
• Database schema is also referred to as the visual or logical architecture

Data Constraint: The Limitations

• Puts restrictions on the type of data that inserts into one or more columns
• It specifies one or more data contraints, while creating tables
• Can be defined to have mobile number column as non-negative integer values with 10 digits only
• Can have one unique roll number and put the NOT NULL and UNIQUE constraints on the RollNumber column to make sure of accuracy and reliability of data

Meta-data or Data Dictionary: Data Synopsis

• The database schemas alongside constraint data are stored by DBMS in the database catalog
• Meta data is the data stored in the dictionary

Database Instance: A Snapshot

• No data entry when defining database structure or schema
• snapshot /state of database after loading is the database instance
• Data can be retrieved after manipulating

Queries of a Database

• A query is a request to a database for obtaining the information we want
• We write it using a query language that is discussed in the later chapter
• Example: find all names of students present on Attendance Date 2000-01-02
• The state of database can change through deletion, modification, or updation, and thus a database schema can have many instances
• A data base schema can change, and thus database schema can have many instances at different times

Data Manipulation in a Database

• Modification includes insertion, deletion, and update
• If a new students arrives, the student and guardian file must be updated which makes this insertion
• If they leave, it must be deleted which is deletion operation
• Suppose students change phone, then must update guardian file which is an update operation

Database Engine

• The underlying component or set of programs used by a DBMS to create a database and handle various queries for data retrieval and manipulation

Relational Data Model

• Different types of DBMS are available and the classification is done based on the underlying data model
• A data model describes data structure for the database
• The most used database models include Relational Data Model, object-oriented data model, entity-relationship data model, document model and hierarchical data model

Relational Model

• Tables are relations that store data for different columns with unique names
• Each row represents a group of values
• Has particular guardian and related values that include phone, names, and address
• A table is regarded as collection of relationships

Relations in Database

• Relations associated with each other but are not independent
• Rollnumber links with Student and attribute GUID links to student
• Without linking attributes database cannot be in a correct state