SCS 307
SLIDE 5
Business Rule
• A business rule is statement that imposes some
form of constraint on a specific aspect of the
database, such as the elements within a field
specification for a particular field or the
characteristics of a given relationship.
• Business rules are based on the way an
organization perceives and uses its data, which is
determined from the manner in which the
organization functions or conducts its business
ERM
• ERM is a tool for Modeling database
• Data modeling is considered as the initial step
in database design
• It helps to creating a conceptual model of how
entities relate to each other
• ERM, has become a widely accepted standard
for data modeling
Entity Relationship Diagram
• In software engineering, an entity-relationship
model (ERM) is an abstract and conceptual
representation of data.
• Entity-relationship modeling is a database
modeling method, used to produce a type of
conceptual schema.
• Diagrams created by this process are called
entity-relationship diagrams, ER diagrams, or
ERDs.
ERM – Basic Concepts
Terms
• Entities
• Attributes
• Relationship
• Cardinality
ERD Notations
ERD – Various notations:
• The Chen notation: It favours conceptual
modeling.
• The Crow’s Foot notation: It favours a more
implementation-oriented approach.
• The UML notation: Used for both conceptual
and implementation modeling.
ERD – Major Parts
There are 5 major parts of ERDs
• Entities
– Represents a person, place or thing we want to
track using a DB
– These entities are grouped in tables of a DB
– Each occurrence of these entities is an entity
instance
– The instance becomes a row or record in a table
ERD – Major Parts
• Attributes
– Describes various characteristics about individual
entities
– These are known as table columns
• PKs
– An attribute or group of attributes that uniquely
identifies an instance of an entity
• Relationships
– Describes how one or more entities interact with each
other
– Usually a verb is used to describe the relationship
ERD – Major Parts
• Cardinalities
– The count of instances that are allowed or are
necessary between entity relationships
• 4 different ways to represent cardinality in crow's
foot notation
– One-Mandatory: At least one and only one instance
– Many-Mandatory: At least one but several instances
– One-Optional: You don't have to have an instance but
if you do you, you can only have one
– Many-Optional: You don't have to have an instance
but if you do you, no limit on instances
(1,1) (1,3)
Cardinality: The count of instances that are allowed or are
necessary between entity relationships
Professor
ProfID FName LName
If cardinality is (1,3), then a
Professor cannot teach more than 3 classes
PP1 Samuel Tree
PP2 Lizy Laing
PP3 Abu John
Class
ClassID ClassName Professor
CC1 Computer Science-L100 PP3
CC2 Computer Science-L300 PP3
RS1 Religious Studies-L400 PP1
ACC1 Accounting-L300 PP3
RS2 Religious Studies-1400 PP3
Case Study
Systems Development Life Cycle
Planning
Analysis
Logical Design
Physical Design
Implementation
Maintenance
Systems Development Life Cycle
(cont.)
Planning
Planning Purpose–preliminary understanding
Deliverable–request for study
Analysis
Logical Design
Physical Design
Database activity– Implementation
enterprise modeling and
early conceptual data
Maintenance
modeling
Systems Development Life Cycle
(cont.)
Purpose–thorough requirements analysis and
Planning structuring
Deliverable–functional system specifications
Analysis
Analysis
Logical Design
Physical Design
Database activity–thorough Implementation
and integrated conceptual
data modeling
Maintenance
Analysis
• After planning comes a more in-depth study called
analysis. The data models developed here are more
detailed (project-level), including more entities,
attributes and relationships than the enterprise
models.
• Analysis includes detailed study, interviews,
requirements elicitation, and document review, as well
as studying the current system. The output from this
involves a detailed set of specifications for what the
desired system should do.
Systems Development Life Cycle
(cont.)
Purpose–information requirements elicitation
Planning and structure
Deliverable–detailed design specifications
Analysis
Logical Design
Logical Design
Physical Design
Database activity– Implementation
logical database design
(transactions, forms,
Maintenance
displays, views, data
integrity and security)
Logical Design
• Whereas analysis involves identifying what
the system should do, the logical design is
concerned with specifying how the system is
going to do it.
• From a database perspective, analysis involves
drawing the various entity-relationship data
models, whereas logical design involves
defining the tables, screenshots, metadata,
etc. of the finalized system.
Systems Development Life Cycle
(cont.)
Purpose–develop technology and
Planning organizational specifications
Deliverable–program/data
Analysis
structures, technology purchases,
organization redesigns
Logical Design
Physical Design
Physical Design
Database activity–
Implementation
physical database design (define
database to DBMS, physical
data organization, database Maintenance
processing programs)
Physical Design
• After logical design comes physical design.
This can include the development or
acquisition of application programs.
• From a database perspective, we have
decided on the physical database platform
(e.g. Oracle, SQL Server, MySQL) and written
much of the SQL for actually creating and
manipulating our data structure.
Systems Development Life Cycle
(cont.)
Purpose–programming, testing,
Planning training, installation, documenting
Analysis Deliverable–operational programs,
documentation, training materials
Logical Design
Physical Design
Database activity–
database implementation, Implementation
Implementation
including coded programs,
documentation, Maintenance
installation and conversion
Impementation
• Often “physical design” and “implementation”
overlap. There’s still programming and more SQL
that takes place during implementation.
• But other activities include installing the finished
product on the production environment
(previously it was only in test), and also preparing
users through documentation and training.
• At the end of implementation is when the system
is actually “up and running”.
Systems Development Life Cycle
(cont.)
Planning Purpose–monitor, repair, enhance
Deliverable–periodic audits
Analysis
Logical Design
Physical Design
Database activity–
database maintenance, Implementation
performance analysis
and tuning, error Maintenance
Maintenance
corrections
Maintenance
• After implementation comes maintenance.
• This is typically by far the longest phase, because
maintenance lasts throughout the life time of the
operational system. During this time there will be
needs for enhancements, bug fixes, and problem-
solving of various sorts.
• Sometimes you can think of enhancements as
little mini-SDLCs that produce the needed
improvements to the system.
Case Study - Solution steps
Step-1
• Planning: Enterprise modelling (conceptual)
Step-2
• Analysis: Draw the various entity-relationship
data models
Step-3
• Logical Design: Define the tables
Steps (contd.)
Step-4
• Physical Design: DBMS to use (MySQL and Ms
Access)
Step-5
• Implementation: Often “physical design” and
“implementation” overlap.
• Deploy finished product.
• System must be up and running
Steps (contd.)
Step-6
• Maintenance: Maintenance lasts throughout
the life time of the operational system.
• Provide support to users. You are the
seller/Marketer/vendor
Identify Objects & Attributes
Products
• (PID,ProductName, UnitPrice)
Customer
• (CustomerID, FullName, Address, City,
Country, EmailAddress, Comments)
Identify Objects & Attributes
Oders
• (OrderID, CustID, OrderDate, ProductID, Qty,
UnitPrice)
Payments
• (PaymentID,CustomerID, PaymentDate,
AmtPaid)
Security Management
• The DBMS creates a security system that
enforces user security and data privacy.
• Security rules determine which users can
access the database, which data items each
user can access, and which data operations
(read, add, delete, or modify) the user can
perform.
• This is especially important in multi-user
database systems
Security Management
• Database Administration and Security,
examines data security and privacy issues in
greater detail.
• All database users may be authenticated to
the DBMS through a user-name and password
or through biometric authentication such as a
fingerprint scan.
• The DBMS uses this information to assign
access privileges to various database
components such as queries and reports
Backup and Recovery Management
• The DBMS provides backup and data recovery
to ensure data safety and integrity.
• Current DBMS systems provide special utilities
that allow the DBA to perform routine and
special backup and restore procedures.
• Recovery management deals with the
recovery of the database after a failure, such
as a bad sector in the disk or a power failure.
• Such capability is critical to preserving the
database’s integrity.
END