AllFreePapers.com - All Free Papers and Essays for All Students
Search

Database Management System Shorty Study

Autor:   •  March 27, 2016  •  Study Guide  •  6,314 Words (26 Pages)  •  1,078 Views

Page 1 of 26

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]

...

Download as:   txt (33.6 Kb)   pdf (1.2 Mb)   docx (1.7 Mb)  
Continue for 25 more pages »