DATABASE DESIGN

Data is raw, unorganized facts or values that have no meaning by themselves.

Example:
 "23", "Blue", "Apple", "₹5000"
These are just facts or symbols without context.

Information is processed or organized data that has meaning and is useful for decision-
making.
Example:
 "John is 23 years old."
 "Apple costs ₹5000."
Now the data is structured and meaningful.
Database
A Database is an organized collection of related data that can be easily accessed, managed,
and updated.
Example:
 A student database may store names, roll numbers, marks, etc., in a structured table
format.
 Database Design
 Database design is the process of creating a structure for storing, managing, and
accessing data efficiently in a database system.

Goals of Good Database Design

 Minimize data redundancy (avoid repetition)

 Ensure data integrity and accuracy
 Improve performance for data retrieval and storage
 Maintain consistency and enforce relationships among data

Traditional File System

A traditional file system is a method of storing and organizing files on a storage medium,
such as a hard disk. It organizes files into directories and subdirectories, allowing users to
perform basic operations like creating, deleting, renaming, and accessing files.
Key Features:
 Simplicity: Easy to use and less complex.
  Cost-Effective: Cheaper to design and implement.
 Minimal Investment: No need for specialized software or hardware
Limitations:
 Data Redundancy: Same data may be stored in multiple files, leading to
inconsistency.
 Limited Data Sharing: Data is distributed across many files, making sharing difficult.
 Security Issues: Less secure compared to DBMS.
 No Data Independence: Changes to data require modifications to all related
programs
Database Approach:
The Database Approach is a method of storing and managing data where a central
database is used by multiple users and applications. This approach is managed by a
Database Management System (DBMS), which provides a structured and consistent way to
store, retrieve, and manipulate data.
Key Features of the Database Approach
1. Centralized Data Management
 All data is stored in a single central database.
 Multiple users and applications can access and share the same data.
2. Reduced Data Redundancy
 The same data is not stored repeatedly in multiple places.
 Data is stored only once and reused wherever required.
3. Data Consistency
 Changes made in one place are reflected throughout the system.
 Ensures uniform and accurate data across the organization.
4. Improved Data Security
 DBMS provides access control mechanisms.
 Users are granted access based on roles and permissions.
5. Data Integrity
 Constraints like primary keys, foreign keys, and unique keys help maintain data
accuracy.
 Prevents invalid or inconsistent data entry.
6. Data Independence
 The structure of the database can be changed without affecting the programs that
use the data.
 Application logic and data are separated.
7. Concurrent Access
 Multiple users can access and modify the database at the same time.
 DBMS handles concurrency to prevent conflicts and ensure consistency.
8. Backup and Recovery
 DBMS provides automated backup and recovery systems.
 Protects data in case of system failure or crash.
9. Efficient Querying and Reporting
 Users can retrieve and analyze data using SQL.
 Allows complex queries and generation of detailed reports easily.
 Database Approach vs File-Based System

Database Approach vs File-Based

System
Aspect File System Database Approach
Data Redundancy High Low
Data Sharing Limited Easy and efficient
Data Security Minimal Strong access control
Data Integrity Hard to maintain Enforced through DBMS
Querying Complex coding SQL simplifies data
required querying
Concurrency Control Not supported Proper concurrency
handling

The range of database application:

Databases range from those for a single user with a desktop computer to those on
mainframe computer with thousands of users. The range of database application can be
divided into four categories from simplest to most complex.
1. Personal Computer Database
2. Workgroup Database
3. Department Database
 4. Enterprise Database

1. Personal Computer Database : Personal computer (PC) databases are designed to

support one user with a standalone personal computer (for example: a desktop or a laptop
computer).
2. Workgroup Databases: A workgroup is a relatively small team of people who collaborate
on the same project or application. A workgroup typically comprise fewer than 25 persons.
Each member of the workgroup has a desktop computer and the computers are linked by
means of Local Area Network (LAN). The database is stored on a central device called the
database server. Thus each member of the workgroup has access to the shared data.

3. Department Database: A department is a functional unit within an organization. Typical

examples of departments are personnel, marketing, manufacturing & accounting. A
department is generally larger than a workgroup (typically between 25 & 100 persons).
4. Enterprise Database: An enterprise database is one whose scope is the entire
organization or enterprise. Such databases are extended to support organization-wide
operation and decision making.
Database System Application:
Databases are widely used. Some representative applications are as follows:
1. Banking: For customer information, accounts, loans and banking transaction.
2. Airlines: For reservations schedules information.
3. Universities: For student information, course, registration and results with grades.
4. Credit Card Transaction: For purchases on credit cards and generation of monthly
statements.
5. Telecommunication: For keeping records of calls made, generating monthly bills,
maintaining balances on prepaid calling cards and storing information about the
communication networks.
6. Finance: For storing information about holdings, sales, purchases of financial
instruments such as stocks and bonds; also for storing real time market data to
enable online trading by customers and automated trading by the firm.
7. Sales: For customer, product and purchase information.
8. Online Retailers: For sales data noted above plus online order tracking, generation of
recommendation lists and maintenance of online product evaluations.
9. Manufacturing: For management of the supply chain and tracking production of
items in factories, inventories of items in warehouse and stores and orders for items.
10.Human Resources: For information about employees, salaries, payroll taxes, benefits
and for generation of pay checks.
Costs & Risks of Database Approach: While the database approach offers many
advantages, it also comes with certain costs and risks that need to be considered.

1. New Specialized Personnel: Database systems require Database Administrators

(DBAs), System Analysts, Database Designers, and SQL Programmers.
  These professionals are highly skilled and expensive to hire and retain.
 Small organizations may not afford such skilled staff.
2. Installation & Management Costs and complexity: Setting up a Database System
includes:
 Purchasing hardware and licensed software
 Networking setup
 Configuring databases and user roles
 Ongoing management of the database (e.g., tuning, backup, monitoring) also
adds complexity and recurring costs.
3. Conversion Costs: Converting from a traditional file system or older software to a
new DBMS involves:
Migrating large amounts of data
Rewriting application programs to interact with the DBMS
Training staff on the new system
This process can be time-consuming, risky, and expensive.
4. Organizational Conflicts
A centralized database may change control of data from individual departments to a
central team or DBA.
Departments may resist losing ownership or direct access to "their" data.
Policy changes, security restrictions, and new workflows may lead to internal conflicts
or resistance to change.
Components of Database Environment:
There are five main components of database
1. 📁 Hardware
 Physical devices used in the database system.
 Includes servers, storage devices, processors, and network devices.
 Provides the computing power and storage capacity.

2. 🧠 Software
 Includes the Database Management System (DBMS) software that manages the data.
 Examples: MySQL, Oracle, PostgreSQL, SQL Server.
 Also includes OS, networking software, application programs, etc.

3. 🧾 Data
 The most important component.
  It includes actual information stored in the database (e.g., student records,
transactions).
 Organized in tables, rows, columns, etc.

4. 👩‍💻 Users
 People who interact with the database system.
o Database Administrators (DBA): Manage and maintain the DBMS.
o Application Programmers: Develop programs to access the database.
o End Users: Use the application to retrieve and enter data (e.g., employees,
customers).

5. ⚙️Procedures
 Instructions and rules for using and managing the database.
 Includes backup rules, access control policies, maintenance procedures, etc.
 Ensures proper and secure use of the database.
Types of Database :
1️⃣ Hierarchical DBMS
 Data is organized in a tree-like structure (parent-child relationship).
 Each child has only one parent.
🧠 Example:
Organizing employee records under department →
Department → Manager → Employees
📌 Example DBMS: IBM IMS

2️⃣ Network DBMS

 Data is organized in a graph structure.
 A child can have multiple parents.
🧠 Example:
A student enrolled in multiple courses.
So:
Student ↔ Course (many-to-many relation)
📌 Example DBMS: Integrated Data Store (IDS)
3️⃣ Relational DBMS (RDBMS)
 Data is stored in tables (rows and columns).
 Uses SQL for querying and managing data.
 Most popular and widely used.
🧠 Example:
Students table, Courses table → linked using keys.
📌 Examples: MySQL, Oracle, PostgreSQL, MS SQL Server

4️⃣ Object-Oriented DBMS (OODBMS)

 Data is stored as objects, like in object-oriented programming.
 Supports complex data types, inheritance, etc.
🧠 Example:
A multimedia file (image + sound + text) can be stored as an object.
📌 Examples: db4o, ObjectDB, Versant

Three Schema Architecture

The Three-Schema Architecture is a
framework that separates a database into
three levels of abstraction to simplify
management and ensure data
independence. It was proposed by the
ANSI/SPARC committee.
The three levels are :
External level
Conceptual level
Internal level
👤 1. External Schema (View Level)
 Highest level of abstraction.
 Describes how users view the data (customized views for each user/application).
 Each user can have a different external schema.
 Focus: Security, simplification, and role-based access.
 Handled by: Application developers.
🧠 Example: A student only sees their own marks; an admin sees all student data.
📐 2. Conceptual Schema (Logical Level)
 Middle level of abstraction.
 Describes what data is stored in the database and the relationships among them.
 Independent of physical storage.
 Focus: Data types, entities, relationships, constraints.
 Handled by: Database designers.
🧠 Example: Student table with fields like Roll_No, Name, Age, Course.
📚 3. Internal Schema (Physical Level)
 Lowest level of abstraction.
 Describes how data is physically stored in the system (e.g., files, indexing,
compression).
 Focus: Storage structure, file organization, data access paths.
 Handled by: DBMS and DBA.
🧠 Example: Data is stored on disk using B-trees or hash indexing.
✅ Advantages:
 Hides data complexity from users.
 Provides physical and logical data independence.
 Enhances data security through controlled user views.
 Allows multiple user-specific views of data.
 Simplifies application development and maintenance.

❌ Disadvantages:
  Increases overall system complexity.
 Requires more development time and skilled personnel.
 May reduce performance due to schema mapping.
 Costly for small or simple applications.
 Not fully implemented in some DBMS systems.

Database Development Process

1. Planning
o Identify the need for a database system.
o Define goals, scope, and feasibility (technical, financial).
2. Requirement Analysis
o Collect requirements from users.
o Study the current system and determine data needs.
o Identify entities, attributes, and relationships.
3. Design
o Design the conceptual schema (ER model).
o Convert to logical schema (tables, keys, relationships).
o Plan the physical schema (indexing, storage format).
4. Implementation
o Create the database using a DBMS (e.g., MySQL, Oracle).
o Write SQL scripts to create tables, relationships, constraints.
5. Testing
o Verify the database works correctly with test data.
o Check for data integrity, validation, and performance.
6. Deployment
o Install the database system for actual use.
o Train users and provide documentation.
7. Maintenance
o Monitor performance and apply updates.
o Backup, recovery, tuning, and handling user feedback.
Information System
An Information System (IS) is a combination of people, hardware, software, data, and
procedures that work together to collect, process, store, and distribute information to
support decision-making and control in an organization.
System Development Life Cycle (SDLC)
SDLC is a structured process used to develop information systems, including the database
design. It consists of phases that guide the planning, creation, and maintenance of the
system.

🔄 Phases of Information System Development:

1. Planning
 Identify problems or opportunities.
 Set objectives and assess feasibility (technical, economic).
2. System Analysis
 Gather and analyze user requirements.
 Understand existing system and define what the new system should do.
3. System Design
 Create the system architecture.
 Design database structure, user interface, and data flow.

4. Development (Implementation)
 Write the code and build the system components.
  Develop database and application programs.
5. Testing
 Test the system for errors, bugs, and performance.
 Ensure it meets user requirements.
6. Deployment
 Install the system in a real environment.
 Train users and provide documentation.
7. Maintenance
 Regularly update the system.
 Fix bugs, improve features, and adapt to new requirements.

Database Analysis
Data analysis is the systematic process of inspecting, organizing, transforming, and
interpreting data to discover useful information, draw conclusions, and support decision-
making.
Data analysis is a structured process involving multiple phases that transform raw data into
meaningful insights. These phases help ensure the data is accurate, understandable, and
useful for decision-making.
1️⃣ Data Requirement Specification
This is the initial phase where the objective of the analysis is defined. Analysts work with
stakeholders to understand what questions need to be answered, what decisions the data
will support, and what kind of data is required. A clear understanding at this stage ensures
the right data is collected for meaningful analysis.

2️⃣ Data Collection

Once the requirements are clear, relevant data is gathered from various sources such as
databases, spreadsheets, APIs, or online forms. This step is crucial because the quality of
the analysis depends directly on the quality and relevance of the data collected.

3️⃣ Data Processing

In this phase, the raw data collected is transformed into a structured format suitable for
analysis. This involves organizing the data into tables, converting data types, merging
datasets, and formatting values so they can be efficiently analyzed in the next stages.

4️⃣ Data Cleaning

This step focuses on improving data quality by handling missing values, removing duplicates,
correcting errors, and standardizing entries. Clean data ensures accuracy and reliability in
the final analysis, preventing misleading results or incorrect conclusions.

5️⃣ Data Analysis

The cleaned data is now explored to identify patterns, relationships, and insights. Analysts
may use statistical methods, visualizations, or machine learning techniques depending on
the goals. This phase helps uncover trends and supports data-driven decision-making.

6️⃣ Communication
After analysis, the findings are communicated through reports, charts, dashboards, or
presentations. The goal is to present insights in a clear and actionable way so stakeholders
can make informed decisions based on the data.

E-R (Entity Relationship) Model

The Entity-Relationship Model (ER Model) is a conceptual model for designing a databases.
This model represents the logical structure of a database, including entities, their attributes
and relationships between them.

Entity: An Entity represents a real-world object, concept or thing about which data is stored
in a database. It act as a building block of a database. Tables in relational database
represent these entities. They are represented as rectangles with the entity name inside the
rectangle.

Types of Entity
There are two main types of entities:
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute that can uniquely identify each
instance of the entity.
2. Weak Entity
A Weak Entity cannot be uniquely identified by its own attributes alone. It depends on a
strong entity to be identified.

Attribute: Properties that describes an entity such as StudentID, CourseName,

or EmployeeEmail. In ER diagram, the attribute is represented by an oval.

Types of Attributes
1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key
attribute. For example, id will be unique for each student.
Composite attribute
It can be divided into smaller sub parts, each sub part can form an independent attribute.

Multi-valued Attribute
Attribute having more than one value for a single entity is called a multi-valued attribute.

Derived Attributes
When one attribute value is derived from the other is called a derived attribute.
Age can be derived from date of birth.

Relationship : a relationship represents how two or more entities are connected or interact
with each other in a database.
A diamond-shaped box represents relationships. All the entities (rectangle-shaped)
participating in a relationship get connected using a line.

Types of Relationship:
1. One-to-One Relationship
One entity instance in Entity A is related to only one instance in Entity B

2. One-to-Many
One entity instance in Entity A can be associated with many instances in Entity B, but each
instance in Entity B is related to only one in Entity A.

3. Many to One:
Many instances in Entity A are associated with one instance in Entity B.
This is logically the reverse of One-to-Many.

4.Many-to-Many:
Many instances in Entity A can be related to many instances in Entity B.
Degree and Cardinality:
The degree of a table refers to the number of columns (attributes) in a relation (table).
The cardinality of a table refers to the number of rows (tuples) in a relation (table).
StudentID Name Age Course
101 Anil 20 B.Sc
102 Priya 21 B.Com
 This table has 4 columns → So, the degree is 4
 This table has 2 rows → So, the cardinality is 2

College management system (ER diagram) :