Database Management System Shorty Study

1 - Introduction to Database Systems

What is a Database?

• Data — Known facts that can be recorded and have an implicit meaning.
• Mini-world — Data in a database representing some parts of the real world. E.g., student grades and transcripts at a university.
• Database System — DBMS software and its data, and sometimes applications.
• Commercial Products: Oracle, DB2 (IBM), Microsoft SQL Server, Sybase, Ingres, Informix or PC “DBMSs”: Paradox, Access, ...
• Open Source Products:  PostgreSQL, MySQL or MonetDB

Traditional File-Based System

• A collection of application that performs services for end-users.
• Each application defines and manages its own data.
• Limitation:

• Separation and isolation of data
• Duplication of data
• Data dependence
• Incompatible file formats
• Fixed queries/proliferation of applications

The Database

• Database → Shared collection of related data and its description to meet an organisation’s needs.
• System Catalog / Data Dictionary / Metadata → The description of its data
• Data abstraction → Changes the internal definition without affecting user’s object (external definition).
• Its logical definition is analyzed by identifying entities, attributes and relationship.

• Entity — Distinct object such as person, thing, place
• Attribute — Characteristic of an object 
• Relationship — Association between entities

Database Management System (DBMS)

• Software system to define, create, maintain and control the database.
• Typically facilities:

• Data Definition Language (DDL) — Define the database. Specifying data types, structures and constraints.
• Data Manipulation Language (DML) — Insert, update, delete and retrieve data.  With a query facilities using Structured Query Language (SQL).

• Views → A subset view of a database.

Components of DBMS Environment

• DBMS major components:

• Hardware — Ranges from personal computer, to mainframes or network computers
• Software — Consists of DBMS, operating system and network applications
• Data — Scheme the database structure.
• Procedure — Design and use of the database
• People :

• Database Analyst (DA) — Managing the database. E.g. Planning, development and standard, security control, policies and procedures, and database design.
• Database Administrator (DBA) ― Create, implements and maintain the database.  E.g. Implementation, security control, maintenance of operation and performance.
• Database Designer ― Define the content, structure, constraints, and functions of the database. Need close communicate with the end-users to understand their needs.
• Application Developer ― Provide functionality for end users usually written in 3rd or 4th generation language.
• End-user ― Use data for queries, reports and some update the database.

Database Design — The Paradigm Shift

• Paradigm shift → Need to analyse the data first before the application for a successful database.
• Correct information is from a well structured database.
• Therefore the database design is crucial.
• ER diagrams are used in the database designing.

[pic 1]

History of Database Management System (DBMS)

• Early Database Applications:

• Hierarchical and Network Models in mid 1960s and 1970s.
• Many worldwide database still uses them especially the hierarchical model.

• Relational Model:

• Introduced in 1970 by E. F. Codd, was researched by IBM Research and several universities.
• Used in the early 1980s.

• Object-oriented:

• Object-Oriented Database Management System (OODBMS) → Introduced in 1980s and 1990s to solving complex processing in CAD and other applications.
• Their use has not taken off much.
• Object-relational DBMS (ORDBMS) → Relational DBMSs with object database concepts
• Extended relational system → Add further capabilities (e.g. for multimedia data, XML, and other data types)

• Web and E-commerce Applications:

• Web contains data in Hypertext Markup Language (HTML).
• eXtensible Markup Language (XML) → New e-commerce standard.
• PHP and JavaScript → Scripting tool to create dynamic Web pages which are partially updated from a database
• Data warehouse → Specialised database which draw data from several source.  E.g. Enterprise Resource Planning (ERP)

• New functionality areas → Scientific Applications, eXtensible Markup Language (XML), Data Warehousing and Data Mining, Image Storage and Management, Audio and Video Data Management, Spatial Data Management, Time Series and Historical Data Management
• These leading to new research and development in new data types, complex data structures, new operations and storage and indexing schemes.

Advantages and Disadvantages of DBMSs

• Advantages:

• Control data redundancy — Eliminates redundancy.
• Data consistency — Reduce data inconsistency.
• Shared data — Data accessible by authorised personnel
• More information from the same amount of data — Within one location, many data can be extracted
• Improved data integrity — Constraints are applied to control integrity
• Improve security — Integrated security features
• Enforced standard — May includes standards for systems, naming convention, documentation and standards, update procedures and access rules.
• Economy of scale — Lower cost by combining all organisation operations
• Balance requirements — Optimal performance design controlled by the DBA
• Improved accessibility and responsiveness — Integrated data is accessed.
• Increased concurrency — Maintained by the system
• Improved backup and recovery — To reduce loss of data.

• Disadvantages:

• Complexity — Users need to know its functionality fully to take full advantage.
• Size — Require higher memory and disk space.
• Performance — Application is written for many application rather for just one.
• Cost of DBMS — Cost o DBMS varies with environment and functionality which also includes annual maintenance cost
• Additional hardware cost — Higher disk space for DBMS and better performance hardware.
• Conversion Cost — Cost of conversion maybe higher than cost of DBMS and hardware.
• Greater impact of a failure — Failure of the DBMS will bring the system to a halt.

Types of Databases

• Traditional Applications → Numeric and Textual Databases
• More Recent Applications → Multimedia, Geographic Information Systems (GIS), Data Warehouses, Real-time and Active, Spatial-Temporal Databases, Genomic,  (Bioinformatics),  etc
• Examples → Bank (accounts), Stores (inventory, sales), Reservation systems, University (students, courses, rooms), Online sales (amazon.com), Online newspapers (www.TheStar.com.my), Real-time and Active

Usage & Application

• Define a particular database
• Construct or load database
• Manipulating the database → Retrieval, Modification and Accessing
• Sharing by a set of users and application while keeping all data valid and consistent
• Other features of DBMSs:

• Protection or Security 
• Active processing 
• Presentation and Visualization of data
• Maintaining the database and associated programs

Main Characteristics of Database Approach

• Self-describing nature of a database system.
• Data independence → Insulation between programs and data
• Data abstraction
• View → Support of multiple views of the data
• Sharing and multi-user transaction:

• Concurrent users can retrieve and update the database.
• Concurrency control → Guarantees each transaction is correctly executed or aborted
• Recovery subsystem → Ensures each completed transaction is permanently recorded
• Online Transaction Processing (OLTP) → A major part of database, allows hundreds of concurrent transactions to execute per second.

ANSI/SPARC Three Schema Architecture

• American National Standards Institute (ANSI) / Standards Planning and Requirements Committee (SPARC) →  A framework for data modeling In the 1970s
• Supports:

• Data independence
• Multiple views of the data

• Useful in explaining database system organization
• Defines DBMS schemas at three levels:

• Internal → Physical storage structures and access paths (e.g indexes).

• Uses physical data model

• Conceptual → Structure and constraints of a database for users.

• Uses conceptual / implementation data model.

• External → Various user views

• Uses conceptual / implementation data model.

• Mappings among schema to transform requests and data:

• Programs (external schema) are mapped to the internal schema for execution.
• Internal DBMS data is reformatted to the user’s external view (e.g., formatting the results of an SQL query for a Web page display)

  ---

2 – Data Models

Data Model

• It is an collection of concepts for describing and manipulating data and constraints in an organisation.
• It is a relatively simple representation, usually graphical, of real-world structures.
• Communication among database designers, programmers and end users should be frequent and clear.
• Clarifies communication by reducing database design complexities.
• 3 related data models:

• External → User’s view of the organisation called the Universe of Discourse (UoD)
• Conceptual → Logical/community view that is DBMS-independent
• Internal → Conceptual schema known to the DBMS

• Data models categories → object-based, record-based and physical.

Data Model Basic Building Bocks

• Entity → Particular object in the real world.  E.g., person, place, thing or event
• Attribute → Characteristics of an entity.  E.g., Last Name, First Name, Phone, Address
• Relationship → Association among entities.  E.g., one-to-many (1:M or 1..*), many-to-many (M:M or *..*), one-to-one (1:1 or 1..1).
• Constraint → Restriction on data.  Help ensure data integrity.  Expressed in rules.  E.g., Employee’s salary (6 000 to 350 000), student CGPA (0 to 4), each class (1 lecturer)

Business Rules

• A brief, precise, and unambiguous description of policy, procedure or principle of an organisation.
• Apply to any organisations → big or small, government, religious or research laboratory.
• Derived from organisation’s operations details.
• Must be in writing and updated to any changes of the organisation.
• Properly defined can be used to define entities, attributes, relationships and constraints.
• Must be easy to understand and widely disseminated to be effective.
• Advantages:

• Standardize company’s view of data
• Communication tool between users and designers
• Designer can understand the nature, role and scope of data
• Designer can understand the business process
• Designer can develop appropriate relationship on participating rules and constraints for an accurate data model

Data Model Type

• Object-based data models (OODM):

• Increasingly complex real-world requires a data model more closely resembles the real world.
• Uses the concept of entities, attributes, and relationships.
• Includes the definition, attributes, state and behaviour of the object.
• Objects encapsulate both state and behaviour.
• OODM is a basis for object-oriented database management system (OODBMS).
• OODM executes an object containing all operations.
• OO data model components:

• Object → An abstraction of the real-world entity
• Attribute → Characteristics of object
• Class → Collection of similar object with shared attributes and behaviour / methods
• Class hierarchy → An unside-down tree where each class has only one parent
• Inheritance → Object’s ability to inherit attributes and methods of the parent class.

• OO data models → Typically depicted in Unified Modeling Language (UML) diagram.
• OODM advances allowed support more complex objects.

[pic 2]

• Newer Data Model : Object/Relational and XML

• Extended Relational Data Model (ERDM) → Relational database supporting OO features such as objects, classes and inheritance.
• Also known as Object/Relational Database Management System (O/R DBMS).
• With the widespread of internet, organisation began to integrate their business models and the internet.
• Extensible Markup Language (XML) → De facto standard for efficient and effective exchange of structured, semi-structured and unstructured data.
• O/R DBMSs added support for XML-based documents.

• Future Data Models

• O/R DBMS is the dominant database for business applications.
• Their advantages → simple conceptual model, easy query, high performance, high availability, secure, scalability and expandability.
• OO DBMS is popular in niche market such as CAD/CAM, GIS, telecommunications and multimedia which requires support for complex objects.
• OO data model was to solve engineering needs while relational model is for data management.
• Growth of OO is slow compared to relational data models.
• OO concepts are significant in system development and programming languages such as Java, Ruby, Perl, C#, .NET etc.
• There is growing need to support organisation’s unstructured data.
• Future development of database models is difficult to speculate.
• 2 examples evolved:

• Hybrid DBMS → Combination the relational and object model.
• SQL data services → Cloud-based processing.  E.g. Microsoft SQL Data Services (SDS) on Azure.

• Summary of Data Models:

• Characteristics of popular data models :

• Conceptual simplicity with database efficiency
• Represents real-world closely
• Consistency and integrity

• Each new model addresses the shortcomings of the previous models.
• It is important that not all data models are equal such as:

• Conceptual → High-level modeling. Eg. ER Modeling
• Implementation →  Managing stored data. E.g., Hierarchy and Network
• Conceptual and Implementation → E.g., Relational model and OODM.

• Relational data model

• Based on mathematical relations concepts.
• Data and relationships are in tables, each columns with unique name.
• Requires only it to be perceived by the user as tables.
• However, this perception is to the logical structure which is external and conceptual level.
• Does not apply to the physical structure which can be implemented in a variety of storage structure.

[pic 3]

• Entity Relationship Model

• Conceptual simplicity of relational database trigger the demand of RDBMSs.
• However lacking an effective designing tool.
• Thus the Entity-Relationship (ER) model or ERM has become widely accepted standard for data modeling.
• ER model components:

• Entity → Anything can be stored or collected.  

• Represented as a rectangle with the entity name in the centre.  
• E.g., PAINTER, EMPLOYEE
• Each row in a RDMBS → Entity instance / occurrence in the ER model.
• Entity set → Collection of entities.

• Relationships → Association among data.  E.g., one-to-many, many-to-many, one-to-one

• E.g, paints, learns, manages

[pic 4]