Database Schemas and States

Introduction

• Databases and associated technology have significantly impacted computer usage across various sectors like business, e-commerce, engineering, medicine, law, education, and library science
• Relational databases are a collection of related data, representing facts with implicit meaning
• Databases represent a real-world aspect, or miniworld, with changes reflected in the data
• They are logically coherent, designed for a specific purpose, and have an intended user group
• A database management system (DBMS) allows users to create and maintain databases by defining data types, structures, and constraints, with definitions stored as meta-data

Database Management System Functions

• A DBMS manages data storage, provides querying capabilities, updates the database to match real-world changes, and generates reports
• It ensures data sharing among multiple users and programs
• Protection measures provided by DBMS involves protection against malfunctions and unauthorized access
• DBMS must evolve with changing requirements over time, by adapting to the data
• Database system = database + DBMS software

University Database Example

• A university database maintains data about students, courses, and grades, and is organized into five files: STUDENT, COURSE, SECTION, GRADE_REPORT, and PREREQUISITE
• Each file stores records of the same type
• STUDENT records include Name, Student_number, Class, and Major
• COURSE records include Course_name, Course_number, Credit_hours, and Department
• Types assigned to each data element within a record, such as Name of STUDENT being a string
• Coding schemes are used to represent data item values, like Class of a STUDENT: 1 for First Year, 2 for Second Year, etc.

Database Approach vs File Processing

• Traditional file processing involves each user defining and implementing files for specific software applications, leading to data redundancy and wasted storage
• The database approach maintains a single data repository, avoiding redundancy and ensuring data is up-to-date
• In databases, data element names are defined once and reused across queries, transactions, and applications
• Key characteristics of the database approach: self-describing nature, insulation between programs and data, support for multiple data views, and sharing with multiuser transaction processing

Self-Describing Database Systems

• Database systems contain a complete database structure with constraints
• Descriptions stored in the DBMS catalog include file structure, data item types, storage formats, and constraints
• Catalog information is named meta-data, describing the primary database structure

Insulation, Data Abstraction, and Multiple Data Views

• Traditional file processing embeds data file structure within application programs, requiring program changes with file structure changes
• DBMS access programs are not changed in most cases, giving program-data independence
• A database can have multiple users, each needing a different data perspective which creates a virtual data view

Sharing, Multiuser Transaction Processing, and History

• Multiuser DBMS allows concurrent database access
• DBMS includes concurrency control software to manage simultaneous updates, ensuring correct results
• Database origins began with libraries and governmental/medical records, with design principles evolving over time to enhance reliability
• In the 1960s, cost-effective computers and storage led to network (CODASYL) and hierarchical (IMS) models with low-level pointer operations that depended on storage details
• Adding a database field required rewriting the access/modification scheme, due to physical structure knowledge

Relational Model and Subsequent Developments

• E.F. Codd's relational model, proposed in 1970-72, separated the logical organization (schema) from physical storage, becoming a standard
• The 1970s saw debates over competing systems and database theory advancements, leading to relational system prototypes such as Ingres (UCB) using QUEL. & System R (IBM) using SEQUEL
• The term RDBMS (Relational Database Management System) was coined during this period
• In 1976, P. Chen proposed the Entity-Relationship (ER) model for database design, to focus designers on data use rather than logical table structure

Commercialization, Standards, and the Rise of SQL

• Commercial relational systems boomed with the growth of computer purchasing in the early 1980s
• Mid-1980s: SQL (Structured Query Language) became an intergalactic standard and DB2 became IBM's lead product
• Network/hierarchical models faded, while the IBM PC spurred DB companies like RIM, RBASE 5000, PARADOX, and Watcom SQL

The Internet, Object Databases, and Modern Trends

• Early 1990s: An industry shakeout resulted in fewer companies with complex, expensive products centered on application development tools
• The client-server model became standard and personal productivity tools like Excel/Access emerged
• Object Database Management Systems (ODBMS) began prototyping
• Mid-1990s: The Internet/WWW caused a frenzy for remote access to legacy data, popularizing the client-server approach

Web/Database Integration

• The late 1990s saw investments in Internet companies fueling tools for Web/Internet/DB connectors (e.g., Java Servlets, JDBC) with open-source solutions like Apache and MySQL
• Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) matured with point-of-sale technology
• Early 21st century: The Internet industry declined, but DB applications grew, integrating PDAs, POS transactions, and vendor consolidation with IBM, Microsoft, and Oracle dominating

Future Trends

• Future trend is the growth of very large database systems, requiring innovative ways to handle and analyze data, including applications in science, national security, and clickstream analysis: Data mining, data warehousing, and data marts are now common techniques
• Mobile database use is growing. The focus would be to standardize SQL successors, possibly overtaken by XML and emerging techniques
• Distributed transaction processing is becoming standard for business planning

Database System Concepts and Architecture

• Data abstraction suppresses data organization and storage details, emphasizing essential features
• Data models provide concepts to describe database structure, data types, relationships, and constraints
• Basic data model operations specify retrievals and updates

Categories of Data Models

• High-level or conceptual data models are are close to user perception
• Low-level or physical data types describe the storage of data in a computer for technical specialists.
• Representational or implementation data models are understandable to end-users but also close to how data is organized in the computer

Conceptual Data Models

• Conceptual data models use entities, attributes, and relationships: An entity is a real-world object
• An attribute is a property of interest of the entity
• A relationship is the relationship among the two or more entities

Schemas, Instances, and Database State

• Database schema describes database and is specified during design with conventions for displaying as diagrams
• Schema diagrams display record type structure without actual data instances; schema constructs are objects like STUDENT or COURSE
• A database state or snapshot is the actual data at a point in time and changes frequently as new data is added, and is known as the current set of occurences or instances in the database

Three-Schema Architecture

• The goal of the three-schema architecture is to separate user applications and the physical database with an internal, conceptual, and external level.
• The internal level has an internal schema & describes physical storage structure using a physical data model
• The conceptual level has a conceptual schema & describes database structure for all users using an implementation conceptual schema on a high-level model
• The external or view level includes external schemas or user views describing database portions for user groups using representative data model

Centralized and Client/Server Architectures

• Centralized architectures used mainframe computers for all system functions via terminals
• As hardware prices declined, users switched from terminals to independent PCs
• A client/server architecture deals with networks of PCs, workstations, and servers in which servers have specialized functionality, being accessed by many client machines

Two-Tier and Three-Tier Architectures

• Client/server architecture is increasingly incorporated into commercial DBMS packages: User interfaces and application programs moved to the client-side
• SQL provided a client and server dividing point
• The query and transaction functionality was SQL-related and remained on the server
• Open Database Connectivity (ODBC) supplies an API for client programs to make calls, processing the queries at server sites and sending results back to the client
• Two-tier architectures divide software components over two systems
• The emergence of the Web led to three-tier architecture, adding an intermediate layer between the client and DB server

Three-Tier Architecture

• Many Web applications use a three-tier architecture with a middle tier that stores business rules
• The middle-tier server improves database protection by checking client credentials before sending database requests
• Clients contain GUIs and some business rule
• Intermediate servers get user requests, process the request and then sends the results back
• Presentation layers display information: Business logic layers handle rules/constraints & data is passed up/down to DBMS
• Bottom layers include data management services with possible split in between the Web Server and the DMBS

Data Modeling With The Entity-Relationship Model

• Conceptual modeling is an important phase in database design so developers can translate a real world business model into technical specifications
• A database application includes a database & programs that uses database queries and updates
• User-friendly graphical user interfaces (GUIs) facilitate end-user access to database through menus or other interface

ER Model Applications

• The ER model is a popular high-level conceptual data model that uses a diagrammatic notation (ER diagrams)
• ER tools are used today by DBAs to make conceptual design and constraint decisions

Usage of Conceptual Data Models

• The first step shown is requirements collection and analysis that interviews prospective users
• This involves documenting their data needs
• The result is concisely written set of users' requirements that specifies the known functional needs of the application
• Next is to create a conceptual schema for the database, using a high-level conceptual data model
• The conceptual schema includes detailed entity, type, relationship, and constraint descriptions, which are easier to understand as they don't include implementation details

The Process of Conceptual Design

• Basic data model operations are used to specify high-level user operations during functional analysis, confirming the conceptual schema meets all requirements.
• Use of commercial DBMS to implement the data model, using relational or object-relational models and transforming from high-level to implementation data
• The result is a database schema in the implementation data model
• Last the physical database design has internal storage structures, indexes, and access paths specified
• Create application programs as transactional high level specifications

Company Database Application Example

• Conceptual schema step-by-step development by introducing modeling concepts: Tracks company employees, departments, and projects
• After the requirements phase, the database designer provided the following description: The company is organized into departments, which have a unique name, unique number, a particular employee who manages the department and a location.
• A department controls a number of projects, each of which has a unique name, a unique number, and a single location
• Store employee names, social security numbers, addresses, salaries, sex, and birthdates, and which department they are assigned to
• Employee's may work on several projects, which are not controlled by the same department
• Track the number of hours an employee works on the project and the supervisor
• Track all insurance dependents by first name, birth date, and relationship to the employee

ER Diagrams

• ER model data depiction: entities, relationships, and attributes
• Explanation as step-by-step process
• ER describes data as entities, relationships, and attributes, first introducing entity and attribute concepts
• Define entity types vs key attributes
• Initial conceptual design uses the COMPANY database, with relationships to be described in the final section

Entities and Attributes

• ER model is a entity with independent existence consisting of either physical or conceptual
• ER model is described by attributes or particular properties
• Each entity has a value for each its attributes, making entity a major part of the database
• Examples of attributes
  • simple vs composite
  • single value vs multi valued
  • and stored value vs derived value
• Introduction of NULL for a null attribute

Attribute Properties

• Composite attributes can be divided into smaller subparts that represent more basic units with independent meanings
• Attributes that are not divisible are simple or atomic with no particular type or need to represent
• Single valued attributes have one value per entity while multi valued attributes can have several values for the same entity

Relationships

• Stored Vs Derived attributes -Some value attribute is related, with age and birth date -Derived is for the value of age known from value of birthdate from the database

• NULL Value -Sometimes a particular entity doesn't have a particular value for a attribute -Created a value called NULL, where address can have NULL

Entity Types, Sets, Keys Attributes and Values

• Key point, databases has similar groups
• Employee is an entity type and has certain attributes like employee’s name that are equal across
• Each entity has it’s of unique data
• Key is for unique constrain or entities or entity types value , unique identify
• Type has an attribute whose values are distinct
• Example, name for types

Conceptual Design

• Based on requirements
• Defined several entity types to refine
  • DEPARTMENT
    • Name
    • Number
    • Location
    • Manager start date
  • Project
    • Name
    • Number
    • location
    • attributes Name and Number
  • EMPLOYEE - NAME - SSN - SEX - ADDRESS , -SALARY

Relationship Types, Sets, Roles, and Structural Constraints.

• The following relationship type in figure 3.6 contains various types among the various types of data
• Attributes of employee and department have explicit relationships between other entities
• Relationships should represented as relationships

Relationship Types, Sets, and Instances

• A type among the N entities is a set of assosciates or a relationship set among entities
• R is a set of INSTANCES that have 1 or more entities that are in a mathematical setting
• Type represents fact entities that are related in the mini world with employees and departments -Each instant has 1 employee and 1 department

Relationship Degree and Role Names

• The degree is amount of entity types
• Relationship to another degree calls it binary
• Relations with more are called ternary
• Usually types have some constraints -Limit possible cobo

Cardinality, Participation, Contraints

• Cardinality for a a binary sets for many instances that can participate for example it can relate 1 and many
• Participations: Where entities depends if other entities have relationship
• Specification of minimum -There are types or participates total or partial.

Database Design Refinement

• You can refine the relationships by changing the relationships
• Cardio and each are used determine total
• Relationships: MANAGES, WORKS FOR, CONTROLS, SUPOERVISION -Relationships for total employee on total supervisor.

Explore database states, schemas, and the three-schema architecture. Understand how user applications are separated from the physical database. Learn about transforming requests between levels in a DBMS.