Database Management System Shorty Study
Autor: Strife HD • March 27, 2016 • Study Guide • 6,314 Words (26 Pages) • 1,069 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]
...