Database Design and Development Notes

Course Overview

  • Course Name: Database Design and Development (BIT 2104)
  • Lecturer: Mr. Kenga
  • Builds on BIT 1207: Principles of Database Design.
  • Covers advanced database concepts and modeling techniques.
  • Assessment: Two assignments (30%) and a final exam (70%).
  • Objectives:
    • Analyze and design databases.
    • Develop databases using various modeling techniques.
    • Implement databases from different models.
    • Set primary, surrogate, composite, and foreign keys.
    • Normalize databases to the third normal form.
    • Address issues of large database systems.

Lesson 1: Introduction

  • Databases are critical in various fields.
  • Objective: Understand database development life cycle and database classification.
  • Data: Raw facts.
  • Information: Transformed data for decision making.
  • Entity: Real-world object to be stored.
  • Front end: User interface; Client.
  • Back end: Database interaction; Server.
  • Data Dictionary: Database description (tables, fields, etc.).
  • Database: Collection of data for an organization's functions.
  • DBMS: Software to design, enter, maintain data, and perform queries.
  • Database Manager: Responsible for database design, construction, and maintenance.
  • Database Warehouse: Data copy for query, analysis, and reporting.
  • Functional dependency: Field value determines another field's value.
  • Distributed database: Resources stored on multiple computer systems.
  • Domain: Possible values a field can contain.
  • Data management focuses on data generation, storage, and retrieval.
  • A database is a shared, logically related collection of files.
  • Databases can be classified:
    • By number of users (single, multi).
    • By location (centralized, distributed).
    • By usage (operational, data warehouse).
  • DBDLC: Framework defining steps for database application development.

Lesson 2: Database Models

  • Objective: Knowledge of database models, strengths, weaknesses, and application scenarios.
  • Database model: Specifies data structure rules and operations.
  • Components:
    • Data structure types.
    • Operators or inference rules.
    • Integrity rules.
  • Flat file database model:
    • Readable files in text format.
    • Pros: Data migration, easy design.
    • Cons: Difficult to relate data, control duplication; Requires program for management.
  • Hierarchical database model:
    • Records connected through links (pointers).
    • Organized as a rooted tree.
    • Record replication drawbacks: inconsistency, space waste.
    • Pros: Easy retrieval, data integrity management.
    • Cons: User familiarity, redundancy, definite path access.
  • Network Database Model:
    • Allows multiple records linked to the same owner (parent).
    • Many-to-many relationships.
    • Pros: Flexible, easier navigation.
    • Cons: Difficult for new users, alterations impact database, hardware/software overhead.
  • Object oriented database model:
    • Data and relationships in a single structure (object).
    • Similar objects form a class.
    • Pros: High performance, less programming effort, re-use, improved communication.
    • Cons: Complex schema change, lack of standards, language dependence.
  • Relational database model:
    • Based on sets and mappings.
    • Relation modeled as a table with rows and columns.
    • Pros: Structural independence, conceptual simplicity, ad hoc query capability.
    • Cons: Requires design skills, integrity must be ensured.

Lesson 3: Database Architecture

  • Objective: Discuss database architecture levels and data independence.
  • ANSI SPARC three-level approach:
    • External (View) Level: User-relevant part of database.
    • Conceptual (Logical) Level: Data stored and inter-related.
    • Internal (Physical) Level: Physical representation on computer system.
  • Data independence: Keeps data separated from programs.
  • Logical data independence: Change logical schema without changing external schema.
  • Physical data independence: Change physical schema without changing logical schema.

Lesson 4: Conceptual Database Design

  • Objective: Discuss conceptual database design, notations, and model creation.
  • Conceptual design: Abstract model of data.
  • Steps:
    • Identification of entities.
    • Identification of relationships.
    • Construction of conceptual data model.
  • Entity: Thing or object of importance.
  • Relationship:
    • Meaningful association between entity types.
    • Classified by degree, cardinality, and optionality.
  • Notations: Chen’s, Rein85, Crow’s foot, IDEF1X.

Lesson 5: Logical Database Design

  • Objective: Describe logical design activities, map conceptual model, and identify attributes.
  • Logical design: Constructing a database based on a specific DBMS and model.
  • Steps:
    • Select database model.
    • Map Entity-Relationship Diagrams.
    • Identify attributes.
    • Normalize database.
    • Design the ERD and validate.
    • Create a data dictionary.
  • Attributes:
    • Key attributes: Unique identifiers.
    • Non-key attributes: Descriptors.
    • Required attributes: Must have a value.
    • Optional attributes: May have a value.
    • Composite attribute: Divisible into subparts.
    • Simple attribute: Not divisible.
    • Single-valued attribute: Single value at a time.
    • Multi-valued attribute: Multiple values at a time.
    • Stored attribute: Supplies a value to the related attribute.
    • Derived attribute: Value derived from a stored attribute.
    • Candidate Key: Uniquely identifies entity occurrences.
    • Primary Key: Used to identify an entity type.
    • Composite Key: Two or more attributes.

Lesson 6: Database Normalization

  • Objective: Normalize a table from first to third normal form.
  • Normalization: Removing redundant data, improving storage efficiency, and scalability.
  • Functional dependency: Value of attribute A determines the value of attribute B.
  • Partial Functional Dependency: Attribute B is partially dependent on attribute A if there is an attribute that can be removed from A and yet the dependency still holds.
  • Transitive Functional Dependency: Non-key attribute C depends on another non-key attribute B, which depends on key attribute A.
  • UNF: Contains repeating groups.
  • Reasons for Normalization:
    • Represent any relation in the database.
    • Increase speed and flexibility of queries.
    • Free relations from anomalies.
    • Reduce restructuring needs.
    • Avoid repetitive entries.
  • Data anomalies:
    • Insertion anomalies.
    • Deletion anomalies.
    • Update anomalies.
  • Normalization steps:
    • First Normal Form (1NF): Atomic values, no repeating groups.
    • Second Normal Form (2NF): In 1NF, every non-primary-key attribute fully dependent on the primary key.
    • Third Normal Form (3NF): In 2NF, no non-primary-key attribute transitively dependent on the primary key.

Lesson 7: Physical Database Design

  • Objective: Describe DBMS selection, storage devices, access methods, and database distribution.
  • Steps for physical design:
    • Select DBMS.
    • Select storage devices.
    • Determine access methods.
    • Design files and indexes.
    • Determine database distribution.
    • Produce the physical model and validate with the users
  • Factors for selecting a DBMS:
    • Costs
    • Features and Tools
    • Underlying model
    • Portability
    • Requirements
    • Security
    • Usability
  • DBMS Functions:
    • Database creation, retrieval, update, programming user facilities, database revision and restructuring, integrity control, performance monitoring, concurrency control
  • DBMS architecture:
    • Data definition language, data pre-compiler, file manager, query processor, database administrator
  • Select storage devices:
    • Speed with which data can be accessed, Cost per unit of data, and Reliability
  • Determine access methods:
    • Sequential , Indexed sequential, and Hashing
  • Physical data model:
    • Specification of all tables and columns, use Foreign keys , occur De-normalization ,and physical Considerations .
    • Steps for design are:
      • Convert entities into tables,convert relationships into foreign keys, convert attributes into columns
      • Modify the physical data model based on physical constraints and requirements

Lesson 8: Database Implementation

  • Objective: Describe database implementation steps, coding practices, and testing strategies.
  • Implementation activities:
    • Hardware/Software Acquisition
    • Coding
    • Testing
    • Training
    • Conversion and loading
  • Coding:
    • Translate design into executable statements.
    • Follow best practices for commenting, error correction, and code simplicity.
  • Testing:
    • Validate data loading, interface correctness, and system performance.
    • Use test data to cover various input scenarios.
    • Testing Techniques: black box, white box, grey box, functional, unit, integration, system, regression, acceptance, alpha, beta.
      • Regression Testing: To ensure that a change, such as a bug fix did not result in another fault being uncovered in the application.
      • Acceptance Testing: To gauge whether the application meets the intended specifications and satisfies the client’s requirements.
      • Alpha Testing: First stage of testing .
      • Beta Testing: performed after Alpha testing has been successfully performed where a sample of the intended audience tests the application.
  • Training of users:
    • Documentation and end-user training in classrooms, on the job, and through computers.

Lesson 9: Database Programming

  • Objective: Creating database front end and back end along with various database connection technologies
  • System flowchart: Shows how users access and use the system at the highest level.
  • User interface:
    • Design involves creating forms and reports like Input Forms and Reports
  • Database connection technologies:
    • Query By Example
    • Data Access Object
    • Open Database Connectivity
    • OLE DB
    • ActiveX Data Objects
    • Microsoft COM
    • Java Database Connectivity
  • Creating the application:
    • Design the forms using a suitable high level programming languages, create the database/ Back end the connect the database
    • Connect the controls(text boxes, labels, controls to
    • Code for operation commands:
      • Update transactions , Retrieval transactions , Mixed transactions, Multiple Document Interface , Coding Menu Items,

Lesson 10: Building Reports

  • Objective: Describe functions and controls of a report, and the procedure for creating a report.
  • Reports:
    • Summarize and present data.
    • Help answer questions, formatted for readability.
    • Can be viewed, printed, exported, or emailed.
  • DataReport Designer Objects:
    • DataReport object like VB Form that will have the visual designer and code module with collection Objects
    • Section objects to show he header and footer sections that are added using controls
  • Data Report Controls used for Designing the layout:
    • TextBox Control (RptTextBox): To format text.
    • Label Control (RptLabel): To place labels.
    • Image Control (RptImage): To place graphics.
    • Line Control (RptLine): To draw rules.
    • Shape Control (RptShape): To place shapes.
    • Function Control (RptFunction): To calculate values.
  • Steps for creating a Report:
    • Start Application and adding a DataReport from menu.
    • connect to the database and add properties, retrieve information by creating a query and then drag the fields form Data Environment
    • Add display by accessing the report from a menu or Command

Lesson 11: Database Maintenance

  • Objective: Discuss database monitoring, tuning, and repair processes.
  • Monitoring:
    • Assess server performance, isolate problem processes, and track performance trends.
    • Dynamic environment leads to changing performance.
    • Database administrator should constantly evaluate and improve traffic to allow maximum efficiency.
  • Tuning:
    • Adjust memory, disk usage, CPU, I/O, database processes, structure, and architecture.
    • Database Repair
    • When a problem presents itself, analyze the problem with various questions like:
    • What are the symptioms of the problem?, Where, When, Under which conditions, and can it be reproduced?
    • Tools: DBCC CHECKDB.

Lesson 12: Database Security and Integrity

  • Objective: Define database security, identify threats, describe countermeasures, and identify database integrity types.
  • Database security: Protect against unauthorized access, modification, denial of service, unauthorized connectivity, and distribution of privileges.
  • Characteristics:
    • Confidentiality, Integrity, Availability.
  • Threats:
    • Browsing, misuse, penetration, system flaws, component failures, tampering, eavesdropping, denial of service.
  • Countermeasures:
    • Enforcing integrity, Authentication, Privileges, Roles, Profiles, Access control, Audits, Views, Backups, Replication, Recovery, Data partitioning.
  • Database integrity: Protection from improper modification.
  • Types:
    • Domain: Restricting field entries.
    • Entity: Ensuring unique records.
    • Referential: Protecting related data in separate tables.
    • User defined: Business rules.

Lesson 13: Advanced Topics in Databases

  • Objective: Describe the use of mobile, distributed, object-oriented, spatial, and temporal databases.
  • Distributed:
    • Storage devices not attached to a common processing unit.
    • Data storage approaches: replication, fragmentation.
    • Database fragmentation schemes and Types.
  • Mobile:
    • Resides on a mobile device, limited in resources.
    • Managed by a limited-functionality DBMS.
    • Single user, updates from user or server.
  • Object oriented:
    • Integrates database and object-oriented programming capabilities.
    • Supports modeling and creating data as objects.
    • Object Oriented Database management Systems extends the object programming language with transparently persistent data
  • Spatial:
    • Deals with geo-spatial data (e.g., census data, satellite imagery), operators, and indexes.
  • Temporal:
    • Manages time-varying data and maintains historical information.