Introduction to Databases and DBMS

• Databases are collections of related data stored electronically in a computer system.
• They are designed, built, and populated for specific purposes with intended users and applications.
• Data can be recorded in an indexed address book or stored on a hard drive using a personal computer and software.

Database vs. Spreadsheet

• Databases use structured tables and support relationships, while spreadsheets use a flat grid structure.
• Databases use SQL for queries and automation, whereas spreadsheets use formulas and manual input.
• Databases allow multi-user, role-based permissions, and spreadsheets have basic sharing with limited access control.
• Databases scale to millions/billions of records, while spreadsheets are limited to thousands/millions, with performance issues on large data.

Database Management Systems (DBMS)

• DBMS is software that allows creating, updating, and retrieving data in an organized and secure way.
• A general-purpose software system that facilitates defining, constructing, manipulating, and sharing databases among various users and applications.
• A software utility for storing and retrieving data gives the end user the impression that the data is well-integrated, even if stored without redundancy.
• An essential tool for managing, organizing, and retrieving large volumes of data across various industries.

Key Features of DBMS

• Data modeling is for designing and modifying database structures and relationships.
• Data Storage and Retrieval provides efficient tools for fast storage and retrieval.
• Concurrency Control ensures conflict-free multi-user access.
• Data Integrity and Security enforces data accuracy, security, and access control.
• Backup and Recovery protects data with regular backups.

Users

• Administrators maintain the DBMS and are responsible for administration, usage, and user permissions.
• Designers: A group who design databases.
• End Users reap the benefits of having a DBMS, ranging from simple viewers to sophisticated users like business analysts.

Types of Databases

Hierarchical Database

• Hierarchical databases organize data in a tree-like structure with one parent record and multiple child records.
• Records are linked together in parent-child relationships, with each child record having only one parent.
• Information Management System (IMS) by IBM and NOMAD by NCSS are examples of hierarchical databases.

Network Database

• The Network Model represents data as collections of records and sets with relationships defined between records.
• Records can have multiple parent and child records, forming a network structure.
• Integrated Database Management System is an example of a network database.

Relational Databases

• Repesents data and relationships through a collection of tables.
• Each table, also known as a relation, consists of rows and columns.
• Primary keys uniquely identify rows, and foreign keys establish relationships between tables.
• SQL allows efficient data manipulation and retrieval.
• Examples include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL.

Object-Oriented Databases

• Stores data in the form of objects, similar to object-oriented programming (OOP).
• They use complex data structures to represent data instead of rows and columns.
• db4o and Object DB are examples of object-oriented databases.

Advantages of DBMS

• Controlling of Redundancy enables easy retrieval and querying of data.
• Improved Data Sharing provides mechanisms for controlling concurrent access without conflicts.
• Data Integrity helps in permitting the administrator to define integrity constraints to the data in the database.
• Security is provided as the DBA controls access to operational data, enforcing authorization checks for sensitive data.
• Data Consistency eliminates redudancy which reduces inconsistency, simplifies updates, and saves storage.
• Backup and recovery: Provides mechanisms for backing up and recovering the data in the event of a system failure.

Disadvantages of DBMS

• Complexity requires specialized knowledge and skills to set up and maintain.
• Performance overhead can add overhead to the performance of an application.
• Scalability, requires the of locking and other synchronization mechansims to ensure data consistency.
• Can be costly to purchase, maintain, and upgrade, especially for large or complex systems.
• Not all use cases are suitable for a DBMS.

Two Parts of Database Management

Data Administration

• Responsible for setting data policy and standards, and for promoting the company's data as a competitive resource.
• Focuses on data governance, policies, and quality.
• It is more strategic and business-oriented.

Database Administration

• Responsible for the day-to-day monitoring and management of the company's databases.
• Carries out many of the policies set by data administration.
• More technical focuses on managing and maintaining databases to ensure performance, availability, and security.
• Data is treated as a valuable enterprise-wide asset.
• Data Administration (DA) and Database Administration (DBA) specialize in different aspects of data management
• Operational management of data covers data consistency, accuracy, and availability for business operations.

Managing Externally Acquired Databases

• DA and DBA ensure external databases are integrated, validated, and secured properly.

Managing Data in Decentralized Environment

• DA and DBA work together to ensure seamless data access and synchronization across different locations.

Data Dictionary

• A tool that stores important details about the data in a database such as names, definitions, and attributes.
• It holds metadata, which is simply information about the data, helping to keep everything organized, consistent, and easy to manage.

Two types of Data Dictionaries

• Integrated Data Dictionary is built into the Database Management System (DBMS) and automatically updates whenever changes happen.
• Stand Alone Data Dictionary is separate from the DBMS and requires manual updates, like a document, spreadsheet, or external software.

Classification of Data Dictionaries

• An active data dictionary is automatically updated by the DBMS with every database access.
• Passive data dictionary must be updated manually and usually requires a batch process.

Data Dictionary typically Stores Descriptions of All:

• Database Information defines databases: creator, date, location, DBA, etc.
• End Users and Administrators lists who can access and manage everything.
• Programs that access the database: screen formats, report formats, application formats, SQL Queries, etc.
• User Access Authorization: Defines who can view, modify, delete, or update data.
• Relationships Among Data Elements: Describes how data elements are connnected.
• Database Architecture: it is a representation of DBMS design.
• DBMS Architecture allows dividing the database system int individual components that can be modified.

Types of DBMS Architecture

1-Tier Architecture (Single-Tier)

• The database and any application interfacing with the database are kept on a single server or device.
• Generally a fast way to access data because there are no network delays involved.
• Microsoft Example: spreadsheet

2-Tier Architecture

• Consist of multiple clients connecting directly to the database.
• Also known as client-server architecture.
• A banking system where the app (client) connects to a database (server) to process transactions.

3-Tier Architecture

• Three layers between the client and the server.
• Intermediate Layer, acts as a medium for the exchange of partially processed data between the server and the client.
• Commonly used in large web applications

3-Tier Architecture Layers

• User Interface (Client): Where users interact (e.g., website, app).
• Application Layer (Middleware): Processes user requests and communicates with the database.
• Database Layer: Stores and retrieves data.

Example of 3-Tier Architecture

• Online Shopping websites (Shopee, Lazada) where the app connects to a database through a web server.

Database Software

• Helps engineers organize, store, retrieve, and analyze data systematically, reducing errors and improving efficiency.
• Enables collaboration among engineers, architects, project managers, and stakeholders by providing a centralized system for accessing and updating critical project information.

Microsoft Access

• Enables business and enterprise users to manage data and analyze vast amounts of information efficiently.
• Provides programming capabilities for creating easy-to-navigate forms.
• Similar to Microsoft Excel in that you can store, edit, and view data. However, Access offers much more.
• Allows engineers to manage databases without requiring deep technical knowledge
• Struggles with handling large amounts of data and does not perform well in multi-user environments
• Security features are also limited

Applications

• Storing construction material inventory
• Managing project schedules and timelines.
• Keeping track of personnel and subcontractor details.

MySQL

• An open-source relational database management system is used for large-scale engineering data storage and analysis.
• It works well with web applications and Geographic Information Systems (GIS).
• Structural Query Language (SQL) used to manage and manipulate relational databases; allows users to create, read, update, and delete data within databases.
• Highly reliable and widely used, community support.
• Requires technical expertise to sets up and lacks built-in analytics tools, which may necessitate third-party solutions.
• Performance can also be an issue for extremely high data loads without proper optimization.
• Managing geotechnical and environmental data.
• Storing and analyzing survey and topographic data.
• Handling real-time project monitoring systems.

Oracle

• System is built around a relational database framework, so in which data objects may be directly accessed by users through SQL.
• This scalable relational database architecture is often used by large enterprises for data management across wide and local area networks.
• Oracle Database ideal for large-scale, enterprise-level Civil Engineering projects due to scalability, data management features.
• Construction Project Management
• Geospatial Data Management
• Asset and Resource Management

MS SQL

• MS SQL Server is a user-friendly, affordable database management system, with other Microsoft tools like Excel, Power BI, and Azure.

Database Design

• It refers to the process of structuring a database to ensure efficient data storage, retrieval, and management.
• Involves defining how data will be stored, how it will relate to other data, and how it will be accessed by users or applications.
• SQL used to manages data in relational databases.

Key steps in database design

• Identify data requirements
• Define entities
• Establish relationships
• Normalize data

Database Schema

• It defines how data is organized and how different data elements relate to each other.
• A blueprint, outlining tables, fields, relationships, and rules that govern the data.

Entity-Relationship Model

• A diagram-based approach to visually represents entities things, attributes details, and relationship how things are connected.

Entity

• An object with a physical existence whether it's a person, car, house, and employee or might be some object with a conceptual existence, whether it be a compnay, a job, or a university course.

Key entities in a construction database

• Project, worker, material, equipment, schedule, contractor, invoice.

Attributes

Project → Project_ID, Name, Location, Budget
Worker → Worker_ID, Name, Position, Hourly_Rate
Material → Material_ID, Name, Quantity, Supplier_ID
Equipment → Equipment_ID, Type, Cost, Maintenance_Schedule
Schedule → Schedule_ID, Task, Start_Date, End_Date
Contractor → Contractor_ID, Name, Contact_Info
Invoice → Invoice_ID, Amount, Payment_Status, Project_ID

Relationships

• A project has multiple workers; a contractor is assigned to one or more projects.
• A project requires multiple materials; an invoice is linked to a project.

Degree of Relationship Set

1. Unary Relationship
2. Binary Relationship
3. Ternary Relationship
4. N-ary Relationship

Cardinality

• Measures the number of entities in relation with each other.

1. One-to-one
2. One-to-many
3. Many-to-one
4. Many-to-many

Normalization

• The process of organizing attirbutes in databases to reduce/eliminate data redundancy.
• Dividing large tables into smaller, more manageable ones to help data integrity and improve the efficiency of database operations

Steps in Normalization

1. First Normal Form (1NF)
   • Each column should have atomic indivisible values. Each row should have unique identity.
2. Second Normal Form (2NF)
   • Happens when a non-prime attribute like name or value depends on only part the composite key. To achieve 2NF, we remove partial dependencies by separating the tables.
3. Third Normal Form (3NF)
   • Transitive Dependency is when a non prime attribute depends on another non prime attribute instead of a primary key. Transitive Dependencies can be removed to get to 3NF.