Study On Database Design And Development Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

The word DBMS stands for "Database Management System." In short, a DBMS is a database program. Technically speaking, it is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs.

Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

2) The ANSI-SPARC Architecture, where ANSI-SPARC stands for American National Standards Institute, Standards Planning And Requirements Committee, is an abstract design standard for a Database Management System (DBMS), first proposed in 1975.

The objective of the three-level architecture is to separate the users' view(s) of the database from the way that it is physically represented. This is desirable since:

It allows independent customised user views: Each user should be able to access the same data, but have a different customised view of the data. These should be independent: changes to one view should not affect others.

It hides the physical storage details from users: Users should not have to deal with physical database storage details. They should be allowed to work with the data itself, without concern for how it is physically stored.

The database administrator should be able to change the database storage structures without affecting the users' views : From time to time rationalisations or other changes to the structure of an organisation's data will be required.

The internal structure of the database should be unaffected by changes to the physical aspects of the storage : For example, a changeover to a new disk.

The database administrator should be able to change the conceptual or global structure of the database without affecting the users : This should be possible while still maintaining the desired individual users' views.

A standard three level approach to database design has been agreed

External level

Conceptual level

Internal level (includes physical data storage)

The Three Level Architecture has the aim of enabling users to access the same data but with a personalised view of it. The distancing of the internal level from the external level means that users do not need to know how the data is physically stored in the database. This level separation also allows the Database Administrator (DBA) to change the database storage structures without affecting the users' views.

External Level (User Views) : A user's view of the database describes a part of the database that is relevant to a particular user. It excludes irrelevant data as well as data which the user is not authorised to access.

Conceptual Level : The conceptual level is a way of describing what data is stored within the whole database and how the data is inter-related. The conceptual level does not specify how the data is physically stored.

Some important facts about this level are ...

DBA works at this level.

Describes the structure of all users.

Only DBA can define this level.

Global view of database.

Independent of hardware and software.

Internal Level : The internal level involves how the database is physically represented on the computer system. It describes how the data is actually stored in the database and on the computer hardware.

There are three different types of schema corresponding to the three levels in the ANSI-SPARC architecture.

The external schemas describe the different external views of the data and there may be many external schemas for a given database.

The conceptual schema describes all the data items and relationships between them, together with integrity constraints (later). There is only one conceptual schema per database.

The internal schema at the lowest level contains definitions of the stored records, the methods of representation, the data fields, and indexes. There is only one internal schema per database.


The relational model used the basic concept of a relation or table. The columns or fields in the table identify the attributes such as name, age, and so. A tuple or row contains all the data of a single instance of the table such as a person named Doug. In the relational model, every tuple must have a unique identification or key based on the data. In this figure, a social security account number (SSAN) is the key that uniquely identifies each tuple in the relation. Often, keys are used to join data from two or more relations based on matching identification. The relational model also includes concepts such as foreign keys, which are primary keys in one relation that are kept in another relation to allow for the joining of data. As an example of foreign keys is storing your mother's and father's SSAN in the tuple that represent you. Your parents' SSANs are keys for the tuples that represent them and they are foreign keys in the tuple that represents you.

Relational model concepts

The Relation

The Relation is the basic element in a relational data model.

Relation (file, table) is a two-dimensional table.

Attribute (i.e. field or data item) is a column in the table.

Each column in the table has a unique name within that table.

Each column is homogeneous. Thus the entries in any column are all of the same type (e.g. age, name, employee-number, etc).

Each column has a domain, the set of possible values that can appear in that column.

A Tuple (i.e. record) is a row in the table.

The order of the rows and columns is not important.

Values of a row all relate to some thing or portion of a thing.

Repeating groups (collections of logically related attributes that occur multiple times within one record occurrence) are not allowed.

Duplicate rows are not allowed (candidate keys are designed to prevent this).

Four key function that support DBMS are data definition, data manipulation, data retrieval and data control.

A Data Definition Language or Data Description Language (DDL) is a computer language for defining data structures. The term DDL was first introduced in relation to the Codasyl database model, where the schema of the database was written in a Data Description Language describing the records, fields, and "sets" making up the user Data Model. Later it was used to refer to a subset of SQL, but is now used in a generic sense to refer to any formal language for describing data or information structures, like XML schemas.

Data Manipulation Language (DML)

Data Manipulation Language (DML) Once the structure is defined the database is ready for entry and manipulation of data. Data Manipulation Language (DML) includes the command to enter and manipulate the Data, with these commands the user can Add new records, navigate through the existing records, view contents of various fields, modify the data, delete the existing record, sort the record in desired sequence.

Data retrieval involves the capability to easily select data for graphic or attribute editing, updating, querying, analysis and/or display. The ability to retrieve data is based on the unique structure of the DBMS and command interfaces are commonly provided with the software. Most GIS software also provides a programming subroutine library, or macro language, so the user can write their own specific data retrieval routines if required. Querying is the capability to retrieve data, usually a data subset, based on some user defined formula. These data subsets are often referred to as logical views. Often the querying is closely linked to the data manipulation and analysis subsystem. Many GIS software offerings have attempted to standardize their querying capability by use of a Standard Query Language (SQL). This is especially true with systems that make use of an external relational DBMS. Through the use of SQL, GIS software can interface to a variety of different DBMS packages. This approach provides the user with the flexibility to select their own DBMS. This has direct implications if the organization has an existing DBMS that is being used for to satisfy other business requirements. Often it is desirable for the same DBMS to be utilized in the GIS applications. This notion of integrating the GIS software to utilize an existing DBMS through standards is referred to as corporate or enterprise GIS. With the migration of GIS technology from being a research tool to being a decision support tool there is a requirement for it to be totally integrated with existing corporate activities, including accounting, reporting, and business functions.

Data Control Language (DCL) authorizes users and groups of users to access and manipulate data. Its two main statements are:

GRANT authorizes one or more users to perform an operation or a set of operations on an object.

REVOKE eliminates a grant, which may be the default grant.

Examples of DCL commands include:

GRANT to allow specified users to perform specified tasks.

REVOKE to cancel previously granted or denied permissions.

The following privileges can be GRANTED TO or REVOKED FROM a user or role: