Essay: Database related definitions and explanations

1 a)
Database definition
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles. Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems such as the AS/400 and on personal computers. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database such as IBM’s DB2, Microsoft’s SQL Server, and database products from Oracle, Sybase, and Computer Associates.
b) ANSI-SPARC Architecture
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. Most modern commercial DBMS are based on this system. The ANSI-SPARC model however never became a formal standard.
Three-level architecture
The objective of the three-level architecture is to separate the users’ view,
• It allows independent customized user views: Each user should be able to access the same data, but have a different customized 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.
• The database administrator should be able to change the database storage structures without affecting the users’ views.
• 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.
Three levels are:
• External level
• Conceptual level
• Internal level
The Three Level Architecture has the aim of enabling users to access the same data but with a personalized 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 authorized 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:
1. DBA works at this level.
2. Describes the structure of all users.
3. Only DBA can define this level.
4. Global view of database.
5. 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.
Database schemas
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 overall description of a database is called the database schema.
2 a)
Common SQL Commands
SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.
A list of the more common DML commands follows:
• SELECT — used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
• INSERT — adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
• DELETE —  removes a specified row or set of rows from a table
• UPDATE —  changes an existing value in a column or group of columns in a table
The more common DDL commands follow:
• CREATE TABLE — creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. (See Metadata for a definition of metadata. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
• DROP TABLE — deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
• Data Manipulation Language (DML)
Definition – What does Data Manipulation Language (DML) mean?
A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.
Techopedia explains Data Manipulation Language (DML)
DML resembles simple English language and enhances efficient user interaction with the system. The functional capability of DML is organized in manipulation commands like SELECT, UPDATE, INSERT INTO and DELETE FROM, as described below:
• SELECT: This command is used to retrieve rows from a table. The syntax is SELECT [column name(s)] from [table name] where [conditions]. SELECT is the most widely used DML command in SQL.
• UPDATE: This command modifies data of one or more records. An update command syntax is UPDATE [table name] SET [column name = value] where [condition].
• INSERT: This command adds one or more records to a database table. The insert command syntax is INSERT INTO [table name] [column(s)] VALUES [value(s)].
• DELETE: This command removes one or more records from a table according to specified conditions. Delete command syntax is DELETE FROM [table name] where [condition].
References
ANSI/X3/SPARC Study Group on Data Base Management Systems: (1975), Interim Report. FDT, ACM SIGMOD bulletin. Volume 7, No. 2  –
Posted by: Margaret Rouse
Contributor(s): Allan Leaked