DCIT 24 Reviewer Ex: Student Management Information

System (SMIS)
• Records/basic info
Database
• Records (academics)
• Storage • Medical Records
• Server • Enrollment
• Manage • Capability Enhancement records
• File compilation
Superadmin
• Report Generation
Admin
Goals: Good decision making requires good End users
information derived from raw facts
Data Vs Information Advantages of a DBMS:

• Data are Raw Facts • Improved data sharing

• Data must be formatted for storage • Improved data security
processing and interpretation • Improved data access
• Information requires context to recall • Improved decision making
meaning or processing raw data to • Better data integration
reveal its meaning • Minimized data inconsistency
• Information produced by processing • Increased end-user productivity
data
• Information used to reveal meaning in Types of Databases
data
• Accurate, relevant, timely information Databases can be classified according to:
is the key to good decision making • Number of users
• Database location(s)
Good decision making is the key to
organizational survival • Expected type and extent of use

Data management: focuses on proper Number of users:

generation, storage, and retrieval of data. 1. Single-user database supports only one
Metadata user at a time.
Desktop database: single-user; runs on PC
• Data about data
• Description of data 2. Multiuser database supports multiple users
• Ex: Name, age and date about the data at the same time.
Database Management System (DBMS) Workgroup and enterprise databases

• Collection of programs
• Manages structure and controls access
to data
Database location(s) • Database design focuses on design of
database structure used for end-user
1. Centralized Database
data
• Data located in a single location
– Designer must identify
2. Distributed Database database’s expected use
• Different sites/branches with each • Well-designed database:
other own database
– Facilitates data management
• Cloud database (main database)
– Generates accurate and valuable
3. Relational Database
information
• Rows/columns tables
• Poorly designed database:
• Compare data on database
– Causes difficult-to-trace errors
Two types of distributed database
Evolution of File System Data Processing
1. Operational Database (OLTP)
• Reasons for studying file systems:
• Online Transaction Processing
database – Complexity of database design
• extent of usage/data usage is easier to understand
• supports a company’s day-to-day
– Understanding file system
operations
problems helps to avoid
• Transactional or production database
problems with DBMS systems
2. Data Warehouse (OLAP)
– Knowledge of file system is
• Online Analytic Processing Database useful for converting file system
system to database system
• stores data used for tactical or strategic
• File systems typically composed of
decisions.
collection of file folders, each tagged
Homogenous Database and kept in cabinet
• Same on all databases – Organized by expected use
Heterogenous Database • Contents of each file folder are
logically related
• Different OS, hardware and software
• Manual file systems
MS Access
– Served as a data repository for
• Single and work
small data collections
• Centralized
– Cumbersome for large
Why Database Design Is Important
collections
• Computerized file systems
 – Data processing (DP) specialist tasks for which spreadsheets are
converted computer file not appropriate – database
structure from manual system substitute.
• Wrote software that Problems with File System Data
managed the data Processing
• Designed the application • File systems were an improvement
programs over manual system
• Initially, computer file systems – File systems used for more than
resembled manual systems two decades
• As number of files increased, file – Understanding the shortcomings
systems evolved of file systems aids in
development of modern
– Each file used its own
databases
application program to store,
retrieve, and modify data – Many problems not unique to
file systems
– Each file was owned by
individual or department that • Even simple file system retrieval task
commissioned its creation required extensive programming
– Ad hoc queries impossible
– Changing existing structure
difficult
• Security features difficult to program
– Often omitted in file system
environments
• Summary of file system limitations:
File System Redux: Modern End-User
Productivity Tools – Requires extensive
programming
• Ubiquitous use of personal
productivity tools can introduce the – Cannot perform ad hoc queries
same problems as the old file systems – System administration is
• Microsoft Excel complex and difficult

– Widely used by business users – Difficult to make changes to

existing structures
– Users have become so adept at
working with spreadsheets, they – Security features are likely to be
tend to use them to complete inadequate
Data Redundancy Database Systems
• File system structure makes it difficult • Database system consists of logically
to combine data from multiple sources related data stored in a single logical
data repository
– Vulnerable to security breaches
– May be physically distributed
• Organizational structure promotes
among multiple storage
storage of same data in different
facilities
locations
– DBMS eliminates most of file
– Islands of information
system’s problems
• Data stored in different locations is
– Current generation stores data
unlikely to be updated consistently
structures, relationships
• Data redundancy: same data stored between structures, and access
unnecessarily in different places. paths
• Data inconsistency: different and • Also defines, stores, and
conflicting versions of same data occur manages all access paths
at different places and components
Data anomalies: abnormalities when all
changes in redundant data are not made
correctly
• Update anomalies
• Insertion anomalies
• Deletion anomalies
Lack of Design and Data-Modeling Skills
• Most users lack the skill to properly
design databases
– Despite multiple personal
productivity tools being The Database System Environment
available • Database system: defines and regulates
• Data-modeling skills the collection, storage, management,
use of data
– Vital in the data design process
• Five major parts of a database system:
• Good data modeling facilitates
communication between the designer, – Hardware
user, and the developer – Software
– People
 – Procedures • Backup and recovery management
– Data – DBMS provides backup and
data recovery to ensure data
DBMS Functions
safety and integrity
• Data storage management
– Recovery management deals
– DBMS creates and manages with recovery of database after a
complex structures required for failure
data storage
• Critical to preserving
– Also stores related data entry database’s integrity
forms, screen definitions, report
• Data integrity management
definitions, etc.
– DBMS promotes and enforces
– Performance tuning: activities
integrity rules
that make the database perform
more efficiently • Minimizes redundancy
– DBMS stores the database in • Maximizes consistency
multiple physical data files
– Data relationships stored in data
• Data transformation and presentation dictionary used to enforce data
integrity
– DBMS transforms data entered
to conform to required data – Integrity is especially important
structures in transaction-oriented database
systems
– DBMS transforms physically
retrieved data to conform to • Database access languages and
user’s logical expectations application programming interfaces
• Security management – DBMS provides access through
a query language
– DBMS creates a security system
that enforces user security and – Query language is a
data privacy nonprocedural language
– Security rules determine which – Structured Query Language
users can access the database, (SQL) is the de facto query
which items can be accessed, language
etc.
• Standard supported by
• Multiuser access control majority of DBMS
vendors
– DBMS uses sophisticated
algorithms to ensure concurrent • Database communication interfaces
access does not affect integrity
 – Current DBMSs accept end-user – Frequent upgrade/replacement
requests via multiple different cycles
network environments
– Communications accomplished
in several ways:
• End users generate
answers to queries by
filling in screen forms
through Web browser
• DBMS automatically
publishes predefined
reports on a Web site Lesson 2

• DBMS connects to third- Data Model

party systems to • is the specific representation or
distribute information via blueprint that results from the data
e-mail modeling process. It is a structured and
Managing the Database System: A Shift in organized description of how data
Focus should be stored, organized, and
accessed within a database or
• Database system provides a information system
framework in which strict procedures • Blueprint/tools/guide to create
and standards enforced database
– Role of human changes from Data Modeling
programming to managing
organization’s resources • is the process of creating a simplified
representation of real-world data
• Database system enables more systems or structures to understand,
sophisticated use of the data analyze, and manage data more
• Data structures created within the effectively. It involves defining the
database and their relationships structure, relationships, constraints,
determine effectiveness and rules that govern data within an
organization or system
• Disadvantages of database systems:
• Process to create a blueprint of data
– Increased costs model
– Management complexity Importance of Data Modeling
– Maintaining currency Data models are essential tools for organizing
– Vendor dependence and understanding complex information. A
data model is a conceptual representation of
data objects, their properties, and the design new systems. They also provide a
relationships between them. framework for integrating data from multiple
sources, which is critical for decision-making
Data models are used in various fields,
and analysis.
including computer science, business, and
science, to design databases, software Data Models Building Blocks
applications, and other information systems.
1. Entity- An entity represents a real-world
Here are some of the reasons why data
object, concept, or thing with a distinct
models are important:
identity.
1. Organizing Data- Data models help
2. Attributes- are the characteristics or
organize data into meaningful structures that
properties of an entity, describing various
make it easier to manage, analyze, and use.
aspects of the entity. For example, the name,
They provide a clear understanding of the
age, and gender of a student are attributes
relationships between data entities, which
of the student entity.
helps users identify the most relevant data
and avoid redundancy. 3. Relationships- define how entities are
related to one another. They describe the
2. Communication- Data models serve as a
associations, dependencies, or connections
common language between different
between entities.
stakeholders, such as developers, analysts,
and business users. They help clarify 4. Keys- are attributes or a combination of
requirements, design specifications, and attributes that uniquely identify each instance
constraints, which reduces (row) of an entity within a table.
misunderstandings and errors in the Primary key: A primary key is a unique
development process. identifier for entity in a database. It
3. Consistency- Data models ensure ensures that each record in a table is
consistency in data elements and their unique and can be easily accessed. For
relationships, which is crucial for accuracy example, in a student table, the student ID
and reliability. They help enforce data can be used as the primary key.
integrity rules, such as uniqueness, referential Foreign key: A foreign key is a field in one
integrity, and domain constraints. table that refers to the primary key of another
4. Scalability-Data models provide a table. It establishes a relationship between the
foundation for scalability and flexibility in two tables.
information systems. They allow data to be 5. Constraints- are rules and restrictions that
easily added, updated, and modified, without enforce data integrity and consistency.
disrupting the existing structure or
functionality of the system. 6. Data Types-define the format and type of
data an attribute can hold. Common data
5. Reusability- Data models can be reused types include text (strings), numbers (integers
across different applications and systems, or floating-point), dates, Boolean values, and
reducing the time and effort required to
more. Data types ensure that data is stored 2. Relational Model (1970’s)- The relational
and processed correctly. data model was developed by E.F. Codd in
1970. There are no physical links as they are
Evolution of Data Models
in the hierarchical data model. Following are
The evolution of data models has been the properties of the relational data model :
closely tied to the development of
• Data is represented in the form of table
information systems and the changing needs
only.
of organizations.
• It deals only with the data not with the
1. Hierarchical Data Models (1950’s –
physical structure.
1960’s)- Hierarchical data model is the oldest
type of the data model. It was developed by • It provides information regarding
IBM in 1968. It organizes data in a tree-like metadata.
structure. Hierarchical model consists of the
• At the intersection of row and column
following :
there will be only one value for the tuple.
• It contains nodes that are connected by
• It provides a way to handle the queries
branches.
with ease.
• The topmost node is called the root node.
3. Entity-Relationship Models (1970’s–
• If there are multiple nodes appear at the top 1980’s)- An Entity Relationship (ER)
level, then these can be called as root Diagram is a type of flowchart that illustrates
segments. how “entities” such as people, objects or
concepts relate to each other within a system.
• Each node has exactly one parent.
ER Diagrams are most often used to design
• One parent may have many child. or debug relational databases in the fields of
software engineering, business information
systems, education and research. Also known
as ERDs or ER Models, they use a defined set
of symbols such as rectangles, diamonds,
ovals and connecting lines to depict the
interconnectedness of entities, relationships
and their attributes. They mirror grammatical
structure, with entities as nouns and
In the above figure, Electronics is the root relationships as verbs.
node which has two children i.e. Televisions
4. Object-Oriented Data Models (1980’s–
and Portable Electronics. These two has
1990’s)- In the 1990s, object-oriented
further children for which they act as parent.
databases were introduced. This was when
For example: Television has children as Tube,
the object-oriented (OO) programming
LCD and Plasma, for these three Television
paradigm became popular and there was a
act as parent. It follows one to many
need to represent data in a system as objects
relationships.
as well. Unlike relational databases, object-
oriented databases work in the framework of with relational database management
real programming languages like Java and systems (RDBMS).
C++, for example.
9. NoSQL Data Models (2000’s – present)-
5. Object-Relational Models (1990’s)- An It’s a model that is not reinforced by a
Object relational model is a combination of a Relational Database Management System
Object oriented database model and a (RDBMS). Therefore, the model isn’t explicit
Relational database model. So, it supports about how the data relates – how it all
objects, classes, inheritance etc. just like connects together.
Object Oriented models and has support for
10. Multi-Model Databases (2010’s–
data types, tabular structures etc. like
present)-Multi-model databases refer to
Relational data model. One of the major goals
databases that combine different types of
of Object relational data model is to close the
database models into one integrated database
gap between relational databases and the
engine. They provide a single back end and
object oriented practices frequently used in
support multiple data models depending on
many programming languages such as C++,
the applications they support.
C#, Java etc.
ArangoDB, OrientDB, Azure Cosmos DB,
6. Document and XML Data Models
FoundationDB, Couchbase, Apache Ignite
(2000’s)- The data model for XML is very
are some of the Top Multi-Model Databases.
simple or very abstract, depending on one's
point of view. XML provides no more than a Data abstraction is the method of hiding the
baseline on which more complex models can unimportant details that are present in the
be built. database from the end users to make the
accessing of data easy and secure.
7. Graph Data Models (2000’s – 2010’s)- A
data model organizes a set of data and Degrees of Data Abstraction
establishes how different data elements relate It refers to the different levels of detail and
to one another. Data models help specificity at which data can be represented,
organizations use their data effectively for depending on the context and the needs of
decision making and other business needs. A various stakeholders
relational model organizes data in terms of
tables. A graph data model, on the other hand, 1. Physical Level (internal)- 15% data is
views data as a graph, taking into account the represented with the highest degree of detail.
relationships within the dataset. For developers/superadmins. This is the layer
of data abstraction where the raw data is
8. Columnar Data Models (2000’s – physically stored as files. This layer contains
2010’s)- The Columnar Data Model of all the complex data structures and the data
NoSQL is important. NoSQL databases are accessing methods defined. The physical
different from SQL databases. This is layer is the lowest level of data abstraction in
because it uses a data model that has a a DBMS. It is the database administrator who
different structure than the previously decides how the data is to be stored in these
followed row-and-column table model used physical hard drives.
2. Logical Level (conceptual)- 50% There are several types of logical data
represents data in a more abstract manner models, with two of the most common
than the physical level. After taking the raw being:
data from the physical or internal level, the
1. Entity-Relationship Model (ER Model)
structure of the data is defined at the logical
or conceptual level. This is like a blueprint of Entities, Relationships, Attributes
the raw data. This layer does not have any 2. Relational Model
information about how the end user will view
the data. Tables, Keys, Relationships

3. External Level (Visual)- 60% even more The relational model is widely used in
abstract than the logical level. For end users relational database management systems
with lowest degree of detail. This is what an (RDBMS) and forms the basis for SQL
end-user gets to see. He/she does not get the databases.
entire database, but depending on the queries Referential Integrity Rule in Relational
made from the front-end the user gets to see Database Model
the data. It may be a single data from the
entire database or a collection of data in Referential Integrity Rule in DBMS is based
tabular format. Multiple views of the same on Primary and Foreign Key. The Rule
data are available to the user, the defines that a foreign key have a matching
representation can be a table, a graph, or a pie primary key. Reference from a table to
chart. View Level is the highest level of data another table should be valid.
abstraction in DBMS. Referential Integrity Rule examples

Lesson 3
Relational Database Model
& Entity Relationship Diagram
The rule states that the DEPT_ID in the
Employee table has a matching
Logical View of The Data valid DEPT_ID in the Department table.
A logical view of data refers to the To allow join, the referential integrity rule
representation of data without necessarily states that the Primary Key and Foreign Key
considering the physical implementation or have same data types.
storage details. It focuses on how data is
organized, structured, and related to each
other in a way that makes sense for the users
and the applications interacting with the data.
The logical view is often expressed through
data models, which define the relationships,
constraints, and rules governing the data.
Entity Integrity Rule • Note: Secondary Key is not a Foreign
Key.
• Make sure that each tuple in a table is
unique. Let’s say you selected Student_ID as
• Every table must have a primary key, primary key,
for example, Student_ID for a Student therefore Student_Enroll and Student_Em
table. ail will be Secondary Key (candidates of
• Every entity is unique. primary key).
• The relations Primary Key must have
unique values for each row.
• Primary Key cannot have NULL value
and must be unique.
• Example can be
an Employee_ID cannot be null in an C. SUPER KEY
Employee table. Super Key is an attribute (or a set of
Key attributes) uniquely identifying a tuple, i.e.,
an entity in an entity set.
A. COMPOSITE KEY
It is a superset of Candidate Key, since
• A primary key having two or more
Candidate Keys are selected from super key.
attributes is called composite key. It is
a combination of two or more columns. Let us see an example −
• An example can be −

C. SURROGATE KEY
A Surrogate Key’s only purpose is to be a
unique identifier in a database, for example,
B. SECONDARY KEY
incremental key, GUID, etc. It has
• Secondary Key is the key that has not
Surrogate Key has no actual meaning and is
been selected to be the primary key.
used to represent existence. It has an
However, it is considered a candidate
existence only for data analysis.
key for the primary key.
Let us see an example −
• Therefore, a candidate key not selected
as a primary key is called secondary
key. Candidate key is an attribute or set
of attributes that you can consider as a
Primary key.
D. FOREIGN KEY Language). Relational algebra operations are
used to perform various tasks on sets of
• A Foreign Key creates a link between
relations (tables) in a database.
tables. It references the primary key in
another table and links it. Fundamental Operators
• For example, the DeptID in the 1. Selection(σ)- The selection operation is
Employee table is a foreign key − used to retrieve rows from a table that satisfy
a given condition.
Let us see an example −
Notation − σp(r)
Where σ stands for selection predicate and r
stands for relation. p is prepositional logic
formula which may use connectors like and,
or, and not. These terms may use relational
E. UNIQUE KEY operators like − =, ≠, ≥, < , >, ≤.
• Many users consider Primary Key as
Unique Key, since both uniquely
identify a table, but Unique Key is
different from Primary Key. Unique
Key accepts null values and Primary
Key cannot have null.
• Let us compare Primary Key and
Unique Key and understand its concept
−
2. Projection(π)
The projection operation is used to retrieve
specific columns from a table.

3. Union Operation (∪)

Relational Algebra
The union operation combines two relations
Relational algebra is a theoretical framework
and returns a relation containing all unique
and a set of operations used to manipulate
rows from both relations.
and query relational databases. It provides a
mathematical foundation for the relational
database model and serves as the basis for
query languages like SQL (Structured Query
4. Set Difference(-) a. Theta Join - If we join R1 and R2 other
than the equal to condition then it is called
The difference operation returns a relation
theta join/ non-equi join.
containing rows that are in the first relation
but not in the second.

5. Intersection (∩)
The intersection operation returns a relation b. Natural Join - If we join R1 and R2 on
containing only the rows that are common to equal condition then it is called natural join
both input relations. It displays the common or equi join. Generally, join is referred to as
values in R1 & R2. It is denoted by ∩. natural join.

Example: Find all the customers whose Natural join of R1 and R2 is −

account is in the bank and who have taken out {we select those tuples from cartesian
a loan. product where R1.regno=R2.regno}
The expression is as follows −
∏Name(Depositor)∩ ∏Name(Borrower)

c. Outer Join - It is an extension of natural

join to deal with missing values of relation.
6. Cartesian Product (Χ)
The Cartesian product combines every row
from the first relation with every row from
the second relation.

6. Join (⨝)
The join operation combines rows from two Relationships within the Relational
relations based on a common attribute. Database
Different types of joins include Theta Join, In a relational database, relationships are
Natural Join, Outer Join (Left Outer Join, established between tables to organize and
Right Outer Join, Full Outer Join) link related data. The relationships are
defined using keys, which are columns or sets
of columns that uniquely identify records A junction (or associative) table is used to
within a table. There are several types of manage this relationship.
relationships in a relational database:
Example: A "Student" table and a "Course"
Several types of relationships in a relational table connected by a "StudentCourse"
database: junction table.
1. Primary Key - Foreign Key These relationships help maintain data
Relationship: integrity, avoid data redundancy, and provide
a way to navigate and retrieve related
•This is the most common type of
information across multiple tables in a
relationship.
relational database.
The primary key in one table is referenced as
a foreign key in another table.
Lesson 4
It establishes a link between the two tables.
ENTITY RELATIONSHIP DIAGRAM
Example: Connecting an "Employee" table
with a "Department" table using the An Entity Relationship Diagram is made up
DepartmentID as a foreign key in the of many different components:
Employee table.
• Entity
2. One-to-One Relationship: • Relationship
Each record in the first table is related to one • Attribute
and only one record in the second table, Symbols and notations
and vice versa.
Chen
Example: Storing detailed information about
employees in a separate table from their basic Example of the symbols used with the Chen
information. notation style.

3. One-to-Many Relationship:
Each record in the first table can be related to
multiple records in the second table, but each
record in the second table is related to only
one record in the first table.
Example: One department has many
employees, but each employee belongs to
only one department.
4. Many-to-Many Relationship:
Each record in the first table can be related to Crow’s Foot
multiple records in the second table, and vice
versa.
 Barker

Conceptual, Logical, Physical

Conceptual

Logical

Bachman

Physical