CSC 222: Data Models and DBMS Architecture

• CSC 222 Database Management Systems covers Data Models and Architecture of DBMS.
• The course lecturer is DR Modinat A. Mabayoje.

Module 2 Objectives

• Describe the evolution of data models.
• Identify traditional data models.
• Discuss the advantages and disadvantages of various data model types.
• Explain the three-level architecture of a database management system.
• Understand languages used to define objects, manage data, and control transactions.

Evolution of Data Models

• Data is critical for applications and requires appropriate storage for easy access
• Early automation used programming languages, but file-based systems proved insufficient.
• COBOL and FORTRAN were primary languages in the 1950s, but their file systems struggled with application data needs.
• In the 1960s, IBM and Rockwell developed the IMS hierarchical database system.
• C. W. Bachman proposed the Network Data Model, leading to General Electric's IDS.
• IMS and IDS simplified application development via interfaces but remained complex.
• Edgar F. Codd introduced the relational data model in 1970, using tables for data representation.
• Relational data could be accessed without lengthy programs.
• Modern RDBMS vendors include Microsoft (MS-Access, MS-SQL Server), Oracle, and IBM (DB2).
• The object-oriented concept evolved alongside RDBMS.
• Increased use of object-oriented languages in the 1980s led to object-oriented data models.
• Object-oriented database management systems (OODBMSs) emerged.
• Object-relational DBMS emerged as a combination of object-oriented and relational approaches.
• Vendors like Oracle and IBM integrated object-oriented concepts into their RDBMS.

Hierarchical Data Model

• A data model describes data, its definition, and behavior (for object-oriented models).
• A data model is a logical concept, with a database management system as its physical implementation.
• The entity-relationship model is a conceptual model showing entities and relationships.
• The hierarchical data model was the earliest, developed in the 1960s.
• IBM and Rockwell developed IMS, widely used in the 1960s and 1970s.
• Entities and relationships in the hierarchical model are managed with a tree-like structure.
• The tree has roots (parents) and children.
• A parent can have many children, but a child has only one parent.
• Redundant child records are created if a child relates to multiple parents, linking to both.
• Entity occurrences are called segments. Root segment = parent entity. Dependent segment = child.
• Root and dependent segments connect through links.
• A root segment can have many dependent segments, but a dependent segment has only one root segment.
• Many-to-many relationships between root and dependent segments are not possible in a hierarchical structure.
• Entity occurrence from parent is shown as a root, and related entities as dependent segments.
• Same entities appear on the same tree level. Related entities are a branch.
• Example: Zone, Region, Item, Area entities related to Salesman and Sales entities.
• Zone contains many Regions (1:N), Region contains many Areas (1:N).
• Each Area contains many Salesman (1:N).
• Each Salesman sells many Items, and each Item is sold by many Salesman (M:N).
• Hierarchical models effectively represent one-to-many relationships.
• Many-to-many relationships require two independent trees.
• To represent "Each Salesman sells many Items and each Item is sold by many Salesmen," one tree has Salesman as the parent and Item as the child, the second has Item as the parent and Salesman as the child.
• Advantages: easy to understand, effective for one-to-many relationships.
• Disadvantages: Dependent records cannot be inserted without a parent record.
• Details of an item cannot be inserted until it's sold by a salesman.
• Deleting a root segment also deletes its dependent segments.
• The salesman selling only Bulldozers will be deleted if the Bulldozer is deleted.
• Records are inserted again, only if the salesman supplies another Item
• Updating child segments is difficult as the tree structure is too complex.
• Hierarchical models can only represent one-to-many relationships, causing redundant data in many-to-many relationships.
• A tree representing item supplied by various salesman
• A tree representing Salesman supplies various items

Network Data Model

• Represents data by linking records.
• Parent record: Owner Record. Child record: Member Record.
• Many-to-many relationships connect through connector records, known as Sets.
• Zone records are Owner records of Region records, which are Member records.
• Region records are Owner records of Area records, which are Member records.
• Area records are Owner records of Salesman records, which are Member records.
• Salesman records are Owner records of Item records, Items are Member records.
• Sales records connect Salesman items.
• The Owner record links to the first Member record. This links to the second Member, and so on to last Member.
• The Last Member record links back to Owner. Managing many-to-many relationships is simple.
• Advantages: many-to-many relationships easily represented, supports Data Definition Language, Data Manipulation Language.
• To insert a new Item, (item no. 6), requires a new Item record.
• Disadvantages: complicated search and complex DML.

Relational Data Model

• Introduced by E. F. Codd in 1970.
• Data is represented in a tabular form called a relation (table).
• Each entity is converted into a relation.
• Relationships are handled through primary and foreign keys.
• Entity occurrence: tuple (record). Entity characteristic: attribute (column).
• Many-to-many relationships are easily represented.
• Used widely for modern data storage.
• Relations are associated: Zone with Region (zone id), Region with Area (region id).
• Area with Salesman (area code), Salesman with Sales (salesman id), Item with Sales (item id).
• Zone - 'zone id' is a primary key in the Zone relation, referenced in the Region relation.
• Region - 'region id' is a primary key, and ‘zone id’ is referenced from the Zone relation.
• Area - ‘area code’ is the primary key, with 'region id' referenced from the region relation in Area relation.
• Salesman - 'salesman id' is a primary key referred in the Sales relation, with 'area code' referenced from the Area relation.
• Sales - Combination of 'salesman id' and 'item id' is a primary key with 'salesman id' and ‘item id’ referenced from Salesman and Item relations.
• Item 'item id' is a primary key in the Item relation, referenced in the Sales relation.
• Advantages: Easy, data can be managed properly, structured query language, transactions, insert, delete, and update operations can be performed without any loss of data, and data dictionary management.
• Disadvantage: Difficult to handle complex data types.

Object-Oriented Data Model

• Entities are represented as a class.
• Classes contain methods and data.
• Data are attributes of objects, and methods are behavior.
• Class Zone has 'zone id' and 'zone name' attributes, with methods to manage attribute values.
• Attributes and methods need not be defined in the same class, methods can be separated.
• Each zone record is an object and a class member.
• Zone class has four objects, each with different 'zone id' and 'zone name' values sharing methods.
• Methods are procedures to manage attribute values.
• For example; add zone details, remove zone details, etc..
• Methods are invoked by messages.
• Many built-in classes and methods are available such as integer and <>, =, etc. operators.
• Each object has an object identifier (OID) which is unique and not visible to users.
• OID is similar to a pointer used for addressing.
• Object-oriented systems support abstract data types.
• A class may contain other classes as attributes. Zone contains region
• "Containership" uses OID by referencing the region object rather than containing it.
• Object-oriented data models are ideal for complex data types (video, audio, image).
• Object-oriented databases support features like message passing, class inheritance, method overriding, encapsulation, polymorphism, and operator overloading.
• Advantage: easy-to-handle complex data types.
• Disadvantages: more difficult to use than the relational model, limited ad hoc query capability, and integrity issues. The procedural approach of object oriented needs separate code to handle updates.
• Object systems are procedural 3GLs lack SQL (4GL) support.
• Data dictionary management is not automated.

Object-Relational Data Model

• Combination of object-oriented and relational.
• SQL support for complex data types (geographical, geometrical, spatial).
• These database not possible by relational or object-oriented
• Some vendors such as informix, IBM, and oracle provide plug-ins by implementing SQL for complex data, types.
• Example data cartridge type package name for Oracle.
• By using these packages, users can define custom data types, operators.
• Advantage: Define, access user-defined data and operators with SQL.
• Disadvantages: Requires redesigned system catalog, more complex access methods, indexing and optimization of user-defined types.

Three-Level Architecture of Database

• ANSI/SPARC architecture has three levels namely: internal, conceptual, external.
• The internal level deals with physical storage.
• The conceptual level is the intermediate level.
• Multiple user can connect through external level, using views.
• Internal level is also physical level understandable by computers.
• conceptual level communicates between internal and external.
• The data schema, is defined by DDL at this level.
• The number of users accessing database are at the external level. Different external views are offered to different users.
• External view means how users see data, multiple User can be working, on many platform
• SQL retrieve, language common to databases.
• Translator is the works, like conceptual level.
• There is, internal conceptual external views.
• The are, program analyst user end users.

Database Languages

• Generalized Query Language
• Data definition language which, creates, databases.
• Create tables, the Create table zone code, int table name
• Alter table table type
• Drop tables deletes it.
• Data Manipulation Language, DML insert, manipulates or delete retrieval
• Inserts, insert into zone values
• Changes Update zonal setzone where East
• Deletes, or selects, through, the, select * / drop table zone
• Data Control Language, controlling Data
• Grants or Revokes roles
• Transactional controls, checkpoints, commit saves
• Dat Sub-Languages combines Data definition, an manipulations.

Data and Structural Independence

• Data if we change data types. without. change in application
• Data structural if table type. without code change the code,
• If data structures are provided, database cannot full fulfill.
• Data dependency converse, cannot change the type
• Structural. dependency, structural table