Data Models, Schemas, and Instances

Data abstraction generally refers to the suppression of details of data organization and storage and highlighting of the essential features for an improved understanding of data. One of the main characteristics of the database approach is to support data abstraction so that different users may perceive data at their preferred level of detail

A data model – a collection of concepts that can be used to describe the structure of a database – provides the necessary means to achieve this abstraction.

High-level or conceptual data models provide concepts that are close to the way many users perceive data

low-level or physical data models provide concepts that describe the details of how data is stored in the computer

representational (or implementation) data models, which provide concepts that may be understood by end users but that are not too far removed from the way data is organized within the computer

An entity represents a real-world object or concept, such as an employee or a project that is described in the database.

An attribute represents some property of interest that further describes an entity, such as the employee’s name or salary.

A relationship among two or more entities, for example, a works-on relationship between an employee and a project.

database schema, which is specified during database design and is not expected to change frequently
A displayed schema is called a schema diagram

The data in the database at a particular moment in time is called a database state or snapshot or the current set of occurrences or instances in the database.

the descriptions of the schema constructs and constraints –is called the meta-data

The schema is sometimes called the intension, and a database state is called an extension of the schema

The Three-Schema Architecture

1.The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database

2.The conceptual level has a conceptual schema, which describes the structure of the whole database for a community of users

3.The external or view level includes a number of external schemas or user views.. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group

The process of transforming requests and results between levels are called mappings.

Centralized DBMS Architecture

Earlier architectures used mainframe computers to provide the main processing for all system functions, including user application programs and user interface programs, as well as DBMS functionality

The client/server architecture was developed to deal with computing environments in which a large number of PCs, workstations, file servers, printers, database servers, Web servers, and other equipment are connected via a network

A standard called Open Database Connectivity (ODBC) provides an application programming interface (API), which allows client-side programs to call the DBMS, as long as both client and server machines have the necessary software installed.

Many Web applications use an architecture called the three-tier architecture, which adds an intermediate layer(which could be the application server or web server depending on the application) between the client and the database server

Data Modelling Using the Entity-Relationship (ER) Model

database application refers to a particular database and the associated programs that implement the database queries and updates

diagrammatic notation associated with the ER model, known as ER diagrams

creating a conceptual schema for the database, using a high-level conceptual data model is called conceptual design

Entity Types, Entity Sets, Attributes, and Keys

Composite attributes can be divided into smaller subparts, which represent more basic with independent meanings. For example, the Address attribute of the EMPLOYEE entity can be subdivided into Street_address, City, State, and Zip, with the values ‘2311 Kirby,’ ‘Houston,’ ‘Texas,’ ‘77001.’ 

Attributes that are not divisible are called simple or atomic attributes

Most attributes have a single value for a particular entity; such attributes are called single-valued. For example, Age is a single-valued attribute of a person.

different persons can have different numbers of values for the College_degrees attribute. Such attributes are called multivalued. 

For a particular person entity, the value of Age can be determined from the current (today’s) date and the value of that person’s Birth_date. The Age attribute is hence called a derived attribute and is said to be derivable from the Birth_date attribute, which is called stored attribute.

NULL can be used if we do not know the value of an attribute for a particular entity – for example, if we do not know the home phone number of ‘John Smith’. 

An entity type defines a collection ( or set) of entities that have the same attributes. 

An entity type usually has an attribute whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute

Weak Entity Type is an entity that does not have a key attribute

Each simple attribute of an entity type is associated with a value set (or domain of values)

Each entity type will have a collection of entities stored in the database called the entity set

Relationship Types, Sets, and Instances

Relationships of the same type are grouped or typed into a relationship type

Relationship Set is the current set of relationship instances represented in the database. The current state of a relationship

The degree of a relationship type is the number of participating entity types

A relationship type of degree two is called binary, and one of degree three is called ternary

The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in

The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type


CREATE statement, which can be used to create schemas, tables (relations), and domains (as well as other constructs such as views, and triggers)

The ALTER TABLE statement enables you to modify a table definition by altering, adding, or dropping columns and constraints or by disabling or enabling constraints and triggers.

The DROP TABLE statement removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. 

The ADD keyword allows you to add a column to specific table

The SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables.

To try your knowledge on some carefully picked out questions click on this post

Leave a Reply

Your email address will not be published. Required fields are marked *