Module 1 : Database System Concepts and Data Models

Theory:
► Overview of Database
► Concept of Different Data Models
► DBMS Architecture, and Building a DBMS

Practice:
► Collect data from different data sources and identify the type of data source
► Installation of MySQL Workbench. Import and export the database into MySQL Workbench.
► Create instances of database in MySQL.
► Viewing all databases.
► Viewing all Tables in a Database.
► Creating Tables (With and Without Constraints).
----------------------------------------------------------------------------------------------------------------------------------------

Data:
► It is a collection of information.
► The facts that can be recorded and which have implicit meaning known as 'data'.
► Example: Customer ----- 1. c_name.
2. c_no
3. c_city

Database:
► A database is an organized collection of structured information, or data, typically stored
electronically in a computer system.
► A database is usually controlled by a database management system (DBMS).
► Example: Customer database consists the fields as c_name, c_no, and c_city

c_name c_no c_city

Database Management System (DBMS):

► A database management system (DBMS) is a software tool for creating, managing, and reading a
database. With DBMS, users can access and interact with the underlying data in the database.
► It allows users and applications to easily access, update, and interact with the data while ensuring
security, consistency, and efficiency.
► Ex:
1. MySQL
2. Microsoft SQL Server
3. Oracle Database
4. MongoDB (for NoSQL)
DBMS vs File Systems:
► File-processing systems has major disadvantages
1. Data Redundancy and inconsistency : same data in multiple locations
2. Difficulty in accessing data: Retrieving specific data requires complex and time-
consuming programs.

3. Lack of Data integrity: Hard to maintain accuracy and consistency of data

4. Concurrent access is not possible: Multiple users cannot access or update data at the
same time safely.
5. Security problems: File systems provide limited control over data security
► Advantages of DBMS:
1. Data Independence: Application programs are independent of how data is stored.
2. Concurrent access and Crash recovery: Multiple users can safely access data at the same
time, with recovery from failures.
3. Efficient Data Access: Query optimization ensures fast data retrieval.
4. Data Integrity and security: Enforces rules and provides controlled access to ensure
reliable and secure data.
5. Reduced Application Development Time: Built-in features (like queries, recovery,
security) reduce coding effort.

Database Users:
• Database users are categorized based up on their interaction with the data base.
• These are seven types of data base users in DBMS.
1. Database Administrator (DBA):
► Database Administrator (DBA) is a person/team who defines the schema and also controls the 3
levels(external, conceptual, and internal) of database.
► The DBA will then create a new account id and password for the user if he/she need to access the data
base.
► DBA is also responsible for providing security to the data base and he allows only the authorized users
to access/modify the data base.
a) DBA also monitors the recovery and back up and provide technical support.
b) The DBA has a DBA account in the DBMS which called a system or super-
user account.
c) DBA repairs damage caused due to hardware and/or software failures

2. Naive / Parametric End Users:

► Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they frequently use
the data base applications in their daily life to get the desired results.
► For examples, Railway’s ticket booking users are naive users. Clerks in any bank is a naive user because they
don’t have any DBMS knowledge but they still use the database and perform their given task.
3. System Analyst:
► System Analyst is a user who analyzes the requirements of parametric end users. They check whether all the
requirements of end users are satisfied.
4. Sophisticated Users:
► Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database.
► They can develop their own data base applications according to their requirement.
► They don’t write the program code but they interact the data base by writing SQL queries directly through the
query processor.
5. Data Base Designers:
► Data Base Designers are the users who design the structure of data base which includes tables, indexes, views,
constraints, triggers, stored procedures. He/she controls what data must be stored and how the data items to be
related.
6. Application Program:
► Application Program are the back end programmers who writes the code for the application programs.
► They are the computer professionals.
► These programs could be written in Programming languages such as Visual Basic, Developer, C, C++, Java,
Python etc.
7. Casual Users / Temporary Users:
► Casual Users are the users who occasionally use/access the data base but each time when they access the data
base they require the new information.
► For example, Middle or higher level manager.

DATA-ABSTRACTION (View of Database) :

Database systems are complex, as they involve multiple ways of storing and organizing data. To make databases
easier to use, unnecessary details are hidden from the users. This process is called Data Abstraction.

There are three levels of data abstraction in DBMS:

1. Physical or Internal level
2. Logical or Conceptual level
3. View or External level
1. Physical or Internal level:
- Describes how the data is actually stored in the database.
- Specifies storage details like file organization, indexing, and data structures.
- Concerned with efficiency, performance, and optimization.
- Used by: Database designers and system programmers.

Example: Data stored in hard drives using B-trees, hashing, or linked lists.

2. Logical or Conceptual level:

- Describes what data will be stored in the database and the relationships between them.
- Defines the structure of the whole database (entities, attributes, and constraints).
- Provides a community view of the database.
- Used by: Database Administrators (DBA).

Example: A student database with entities like Students, Courses, and relationships like enrolled in.

3. View or External level:

- The highest level of abstraction, showing only the required part of the database to each user.
- Hides both physical storage and logical structure details.
- Different users can have different customized views of the same database.
- Ensures security by restricting unauthorized access.
- Used by: End users.

Example: A student sees only their marks and courses, a teacher sees marks of all students in their subject, and
an accountant sees fee records only.

Schema and Instances in DBMS:

Schema:
• Schema is the overall design or structure of the database.
It defines how the data is organized and how the relationships among them are associated.
• Schema is like the blueprint of a database.
• It remains unchanged unless the database design is modified.
Types of Schema:
1. Physical Schema → Describes how data is stored physically (files, indexes).
2. Logical Schema → Describes the logical structure (tables, attributes, relationships).
3. View Schema → Describes how data is presented to different users.
Example (Bank Database):
• Tables: Customer (CustomerID, Name, Address)
Account(AccountNo, Balance, CustomerID).

Instance:
• Instance is the actual data stored in the database at a particular moment in time.
• While schema is fixed, instances change frequently (with insert, update, delete).
Example (Bank Database):
• At 10 AM:
• Customer Table → {(101, "User1", "Delhi"), (102, "User2", "Mumbai")}
• Account Table → {(AC001, 5000, 101), (AC002, 10000, 102)}
• At 5 PM (after transactions):
• Customer Table → {(101, "User1", "Delhi"), (102, "User2", "Mumbai")
• Account Table → {(AC001, 3000, 101), (AC002, 10000, 102)

Data Independence in DBMS:

• Data Independence means the ability to change the schema at one level of the database without
affecting the schema at the next higher level.
• It ensures that users and applications are not affected by changes in the way data is stored and organized.

Types of Data Independence:

1. Physical Data Independence-
• Definition: Ability to change the physical storage of data without affecting the logical schema.
• Example:
A bank decides to move its customer data from magnetic disks to SSDs or add indexes for faster
access.

2. Logical Data Independence-

• Definition: Ability to change the logical schema (conceptual level) without affecting the external views
or application programs.
• Example:
In a university database, if we add a new column PhoneNumber in the Student table, old
applications (that only fetch Name and RollNo) are not affected.
•

Data Models in DBMS:

• A Data Model gives us an idea that how the final system will look like after its complete
implementation.
• It defines the data elements and the relationships between the data elements.
• Data Models are used to show how data is stored, connected, accessed and updated in the database
management system.
• Though there are many data models being used nowadays but the Relational model is the most widely
used model.
• Apart from the Relational Model, there are many other types of data models in DBMS.
• Some common Data Models in DBMS are:
1. Hierarchical Data Model
2. Network Data Model
3. Entity-Relationship (E-R) Model
4. Relational Data Model
5. Object-Oriented Data Model
1. Hierarchical Data Model:
• The Hierarchical Data Model organizes data into a tree-like structure.
• Data is stored as records (nodes), and each record has a parent–child relationship.
• Each child record can have only one parent, but a parent can have multiple children.
• Example: CEO → Manager → Executive, where the CEO is the root, managers are children of the
CEO, and Employees are children of managers.

2. Network Data Model:

• This model is an extension of the hierarchical model. It
was the most popular model before the relational model.
• This model is the same as the hierarchical model, the only
difference is that a record can have more than one
parent. It replaces the hierarchical tree with a graph.
• Example: In the example below we can see that node
student has two parents i.e. CSE Department and
Library. This was earlier not possible in the hierarchical model.
3. Entity-Relationship (E-R) Model:
• Entity–Relationship (ER) Model is a high-level conceptual data model used to describe data and
their relationships.
• It represents the real-world problem in a pictorial form (ER diagram), making it easier for
stakeholders to understand.
• Developers can also easily understand the system structure just by looking at the ER diagram.
• Components: Entities (objects)

Attributes (properties)

Relationships (connections).

4. Relational Data Model:

• Relational Model organises the data in the form of relations (tables).
• A relational database is made up of a set of tables, and each table has a unique name.
Features of Relational Model:
Tuples (Rows):
Each row in a table is called a tuple. A tuple represents a single record or instance of the object.
Attributes (Columns/Fields):
Attributes are the properties or characteristics of the table. Each attribute stores values from the same
domain (data type).

5. Object-Oriented Data Model:

• The real-world problems are more closely represented through the object-oriented data model. In this
model, both the data and relationship are present in a single structure known as an object.
• We can store audio, video, images, etc in the database which was not possible in the relational
model(although you can store audio and video in relational database, it is advised not to store in the
relational database). In this model, two are more objects are connected through links.

• In the above example, we have two objects Employee and Department.

• All the data and relationships of each object are contained as a single unit.
• The attributes like Name, Job_title of the employee and the methods which will be performed by that
object are stored as a single object.
• The two objects are connected through a common attribute i.e the Department_id and the communication
between these two will be done with the help of this common id.

DBMS Architecture:
A DBMS architecture defines how users interact with the database to read, write, or update information. A
well-designed architecture and schema (a blueprint detailing tables, fields and relationships) ensure data
consistency, improve performance and keep data secure.
Types of DBMS Architecture:
There are several types of DBMS Architecture that we use according to the usage requirements.
• 1-Tier Architecture
• 2-Tier Architecture
• 3-Tier Architecture

1-Tier Architecture:
• In 1-Tier Architecture, the user works directly with the database on the same system. The client, server,
and database are all part of a single application.
• The user can open the application, interact with the data, and perform tasks without needing a separate
server or network connection.
Example:
Microsoft Excel: The user enters data, performs calculations, and saves files directly on the computer.

Advantages of 1-Tier Architecture:

• Simple Architecture: Only a single machine is required, making it easy to set up.
• Cost-Effective: No extra hardware or network setup is needed.
• Easy to Implement: Quick deployment, ideal for small projects or personal use.
Disadvantages of 1-Tier Architecture:
• Limited to Single User: Supports only one user at a time; not suitable for teamwork.
• Poor Security: If the system is compromised, both application and data are at risk.
• No Centralized Control: Data is stored locally, making backup and management harder.
• Difficult to Share Data: Sharing data across devices is challenging since it’s stored on a single machine.

2-Tier Architecture:
• A 2-tier architecture is a basic client-server model.
• The client application communicates directly with the database server using APIs like ODBC or
JDBC.
• Server Side: Handles query processing, transaction management, and data storage.
• Client Side: Runs the user interface and application programs, sending requests to the server and
receiving responses.
Example:
Library Management System in schools or small organizations.
• Client Layer (Tier 1): Desktop application used by staff to search books, issue them, or check due dates.
• Database Layer (Tier 2): Stores book details, user information, and transaction logs.

The client sends requests (like searching for a book), and the server processes them and returns results.

Advantages of 2-Tier Architecture:

• Easy Access: Direct access to the database enables fast data retrieval.
• Low Cost: Cheaper than 3-tier or multi-tier architectures.
• Easy Deployment: Simple to deploy for small systems.
• Simple Structure: Only two layers, easy to understand and manage.
Disadvantages of 2-Tier Architecture:
• Limited Scalability: Server may slow down as the number of clients increases.
• Security Issues: Direct client-server connection can be not secure.
• Tight Coupling: Changes in the database often require client updates.
• Maintenance Challenges: Harder to manage and update as user base grows.

3-Tier Architecture:
• In 3-Tier Architecture, there is an intermediate layer (application server) between the client and the
database server.
• The client does not communicate directly with the database; it sends requests to the
application server, which processes them and interacts with the database.
• This architecture is commonly used for large web applications.
Example: E-commerce Store
• User (Client): Visits the online store, searches for a product, and adds it to the cart.
• Processing (Application Server): Checks stock, calculates price, applies discounts.
• Database: Stores product details, cart, and order history for future reference.

Advantages of 3-Tier Architecture:

• Enhanced Scalability: Application servers can be distributed, reducing direct connections
to the database.
• Data Integrity: Middle layer helps prevent data corruption and ensures consistency.
• Better Security: Clients don’t interact directly with the database, reducing unauthorized
access.
Disadvantages of 3-Tier Architecture:
• More Complex: Extra layer increases system complexity and points of communication.
• Harder to Manage: Managing requests through the middle layer can be challenging.
• Slower Response Time: Extra processing in the application server may delay responses.
• Higher Cost: Requires more hardware, software, and skilled personnel.

Building a DBMS:
Steps to Build a DBMS
1. Requirement Analysis – Identify what data needs to be stored (e.g., student records, employee data).
2. Design Database Schema – Create a blueprint with tables, fields, and relationships.
3. Choose DBMS Software – Example: MySQL, Oracle, PostgreSQL.
4. Implement the Database – Create tables and define constraints.
5. Load Data – Insert data into the tables.
6. Access & Manage Data – Use queries (SQL) for inserting, updating, deleting, or retrieving.
Installation:
Install MySQL Community Server, Shell and Workbench from from MySQL official site.

- Set Environment Variables (if required):

Add MySQL bin folder path to system environment variables (Windows).
- Start MySQL Service:

“ mysql -u root -p ”

Basic SQL Queries with Examples:

1. Create Database
CREATE DATABASE database_name;

Ex:
CREATE DATABASE SchoolDB;

2. Use Database
USE database_name;

Ex:
USE SchoolDB;

3. Create Table
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
Ex:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Grade VARCHAR(10)
);

4. Insert Data
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Ex:
INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES (1, 'Rahul Sharma', 20, 'A');
5. Select All Data
SELECT * FROM table_name;

Ex:
SELECT * FROM Students;

6. Delete Data
DELETE FROM table_name
WHERE condition;
Ex:
DELETE FROM Students
WHERE StudentID = 1;

7. Drop Table
DROP TABLE table_name;

Ex:
DROP TABLE Students;

8. Drop Database
DROP DATABASE database_name;

Ex:
DROP DATABASE SchoolDB;

Keys in DBMS:
Keys in DBMS are an attribute or set of attributes that help to uniquely identify a row (tuple) in a relation
(table).
• They allow us to find the relation between two tables.
• Keys uniquely identify a row using one or more columns.
• Keys are useful for fetching unique records from a table.
• They help to establish relationships and enforce identity and integrity in the database.
Why we need a key?
1. Keys uniquely identify any row in a table, even with thousands of records.

2. Prevent duplication of rows.

3. Establish relationships between tables.

4. Enforce identity and integrity of data.

1. Candidate Key:
• A candidate key is a minimal set of attributes that uniquely identify each row in a table.
• The Primary key should be selected from the Candidate Key.
• A table can have multiple candidate keys, but only one becomes the primary key.

Properties of Candidate Key:

• Uniqueness → Must contain unique values; no two rows can have the same candidate key value.

• Not-Null → Candidate key attributes must not contain NULL.

• Single/Composite → Can consist of a single attribute or a combination of attributes.
• Multiplicity → A table can have multiple candidate keys.
• Source for Primary Key → Out of all candidate keys, one is chosen as the Primary Key, and the others
become Alternate Keys.

• Subset of Super Keys → All candidate keys are super keys, but not all super keys are candidate keys

Candidate Keys = {StudID}, {Roll No.}, {Email}. Each of these columns

individually can uniquely identify a student.

2. Super Key:
• A Super Key is a set of one or more attributes (columns) that can uniquely identify a tuple (row) in a
relation (table).
• Unlike Candidate Keys, a Super Key may contain extra/redundant attributes that are not necessary for
uniqueness.
• Every table must have at least one super key.
• All Candidate Keys are Super Keys, but not all Super Keys are Candidate Keys.
Properties of Super Key:
• Uniqueness → Ensures that each tuple (row) can be uniquely identified.
• Redundancy Allowed → May contain extra attributes that are not necessary for uniqueness.
• Multiplicity → A table can have many super keys.
• At least One Must Exist → Every relation must have at least one super key.
• Superset Property → Any superset of a candidate key is a super key.
• Can Support NULLs → A super key attribute can allow NULL unless it is also a primary key.

Example: {Roll No.}, {Phone}, {Roll No., Name}, {Roll No., Age}, {Roll No.,
Phone}, {Phone, Name}, {Phone, Age}, {Roll No., Name, Age}, {Roll No., Name,
Phone}, {Roll No., Name, Age, Phone}

3. Primary Key:
• A Primary Key is a special column (or set of columns) in a table that is used to uniquely identify each
row.
• Each table can have only one primary key, which may be a single column or a combination of columns
(called a composite key) that together uniquely identify each row.
• Out of several candidate keys, one is selected as the primary key.

Rules of Primary Key:

1. Unique → No two rows can have the same primary key value.
2. Not NULL → A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every
record.
3. Only One Primary Key per Table → A table can have many candidate keys, but only one is chosen as
the primary key.
4. Cannot Change if Linked → If another table uses it as a foreign key, it should not be changed.
5. Can be Single or Multiple Columns → Sometimes, a combination of two or more columns can be used
as a primary key (called a composite primary key).
Ex: Stu_Id in a STUDENT table can be a primary key because each student has a unique ID.

4. Alternate Key:
• An alternate key is a candidate key that is not selected as the primary key.
• It is sometimes called a secondary key because it can also uniquely identify records in a table.
• An alternate key can consist of a single column or multiple columns (composite), as long as it uniquely
identifies a record.
• Essentially, all candidate keys not chosen as the primary key become alternate keys.

5 . Foreign Key:
A foreign key (FK) is an attribute (or a set of attributes) in one table that refers to the primary key in
another table. It is used to establish and enforce a link between the data in two tables.
• The table that contains the foreign key is called the referencing table (or child table).
• The table whose primary key is referenced is called the referenced table (or parent table).
Purpose of Foreign Key:
• Establishes Relationships:
A foreign key creates a relationship between two tables.
• Maintains Data Integrity:
It ensures that the value in the foreign key must exist in the referenced primary key. This prevents
invalid or orphaned records.
• Prevents Data Redundancy:
Instead of repeating the same data in multiple tables, a foreign key allows you to link tables efficiently,
reducing duplication.

👉
• dept_id is Primary Key in department_info table.
It uniquely identifies each department (no two departments can have the same dept_id).

👉
• dept_id is Foreign Key in employee_info table.
It means that the dept_id field in employee_info can only have values that already exist in the
dept_id field of department_info.

• In department_info, dept_id is used for unique identification of department records.

• In employee_info, dept_id is used for knowing which employee belongs to which department.

Referential Integrity in Foreign Keys:

• Referential Integrity (RI) is a rule that ensures the relationship between two tables (Parent and
Child) remains consistent.
• It guarantees that every foreign key value in the child table matches an existing primary key value in
the parent table.
1. INSERT Operation
• Parent Table (Student Details):
You can insert new students freely. No issue.
• Child Table (Student Marks):
- Allowed if the ID exists in Student Details.
- If you insert a marks record for a non-existing student → RI violation.
Ex:
→ If i add a new data in Child Table “ID- 2099 and Marks- 44”, so 2099 does not exist in Student
Details, so this insert fails (RI violation).
2. UPDATE Operation
• Parent Table (Student Details):
If you update a student’s ID (PK) without updating it in Student Marks, the child table loses its
reference.
Example:
→ Tom’s ID becomes 3000, but Student Marks still has 2041.
→ Orphan record created → Violation.
• Child Table (Student Marks):
You can update marks freely.
f you update ID to a non-existing student → Violation.
Example:
→ 4000 not in Student Details → Violation.

3. DELETE Operation
• Parent Table (Student Details):
- If you delete a student whose ID is still used in Student Marks, Violation occurs.
Example:

✅
→ John (ID 2204) still exists in Student Marks → Violation.
To handle this, DBMS provides actions:
• CASCADE: Delete related rows in Student Marks automatically.
• SET NULL: Set child’s FK as NULL.
• SET DEFAULT: Assign a default value to FK.

✅
• Child Table (Student Marks):
You can delete rows freely because removing marks does not affect student details.
6. Composite Key:
A Composite Key is a type of primary key that consists of two or more attributes (columns) of a table.
These attributes together uniquely identify a record (row) in a table, but individually they cannot uniquely
identify a record.
• A composite key is used when a single column is not enough to ensure uniqueness.
• The combination of all columns in the composite key must be unique for every row.

Composite Key: {Customer ID, Product ID}

Why?
• Neither Customer ID nor Product ID alone can uniquely identify a record.

📌
• But the combination of Customer ID and Product ID is unique in every row.
For example:
• Customer ID 121 appears twice, but with different Product IDs.
• Product ID 9446 appears twice, but with different Customer IDs.
• The pair (121, 9034) only appears once — this ensures uniqueness.