Database Design and Systems Development Principles

Database Modeling and Relationships

• Multi-valued: Derived
• Cardinality: 1(0,3), M(1,1)
• Optionality: ()
• Weak Entities: [[]]
• Formula: TS = PS - DC + OC

Data Integrity Constraints

• Entity Integrity: Different Primary Key (PK) values.
• Cascading Referential Integrity: Foreign Keys (FK) must exist in the referral table.
• Domain Integrity: Predefined data types, maximum and minimum values.

Database Normalization

• 1st Normal Form: Multi-valued attributes removed.
• 2nd Normal Form: Partial dependency (PD) removed.
• 3rd Normal Form: Transitive dependency (TD) removed.

Objectives: Eliminate redundancy, prevent data inconsistencies (anomalies), and ensure consistent database design.

Database Anomalies

• Addition/Insertion anomaly
• Deletion anomaly
• Update anomaly

Logic and Decomposition

• Lossless Decomposition: Lossless join property.
• Deduction: Makes an absolute argument.
• Induction: Likely true, possesses some degree of probability.
• Logic Rules: Modus Ponens (P implies T), Modus Tollens (Q implies not T).

SQL Operations

• DDL: CREATE, DROP
• DML: DELETE, INSERT, SELECT
• DCL: GRANT, DENY
• Set Operations: UNION, INTERSECT, DIFFERENCE
• Joins: Cross join (times), Inner join (equal), Outer join (non-match).

Limitations of Relational Database Design

• Inadequate support for complex data.
• Performance overhead due to many joins.
• Semantic overloading.
• Limited types of operations.
• Difficulty handling recursive queries.
• Schema changes are complex.

Object-Oriented Design Concepts

• Encapsulation: Localizes activity.
• Abstract Class: No direct instances; descendants may have instances.
• Concrete Class: Has direct instances.
• Aggregation: Child object can exist independently of the parent.
• Composition: Child object cannot exist independently of the parent.
• Polymorphism: Ability of an operation to be applied to many classes.

Limitations of Object-Oriented Design

• Steep learning curve.
• Poor code reusability.
• Difficult class hierarchy and relationship definition.
• Queries often require 3GL (e.g., C++, Java).
• Weak SQL tool support.
• Lack of support for views and security.
• High implementation costs.

System Development Life Cycle (SDLC)

1. Planning

General overview of the company, objectives, initial assessment, and feasibility of a new system.

2. Analysis

User requirements and existing system evaluation.

3. Design

Logical system design and detailed system design (screens, menus, reports).

4. Implementation

Coding, testing, debugging, installation, and fine-tuning.

5. Maintenance

Evaluation, maintenance, and enhancement.

Database Development Stages

1. Initial Study: Problem, objectives, scope.
2. Design: Conceptual, logical, physical, software, hardware.
3. Implementation: Create database, tables, and access.
4. Testing
5. Operation
6. Maintenance