Standard Database Concepts

• Schema

  • Conceptual organization of the entire database as viewed by the database administrator
  • To add a new entity attribute in the relational model, you need to modify the table definition. To addanew attribute in the key-value store, you addarow to the key-value store, which is why it is said to be “schema-less."
  • Schema is of three types: Physical schema, logical schema and view schema.

• The design ofadatabase at physical level is called physical schema. It describes how the data stored on the disk or the physical storage.

• Design of database at logical level is called logical schema, programmers and database administrator work at this level. At this level data can be described as certain types of data records gets stored in data structures.

• Design of database at view level is called view schema. This generally describes end user interaction with database systems.

• Subschema

  • Portion of the database seen by the application programs that produce the desired information from the data within the database
  • A subschema providesaview of the database as seen by an application program.

  • This view is oftenasubset of the complete schema definition.

  • A subschema is used at run time to provide the DBMS witha description of those portions of the database that are accessible to the application program.

  • The subschema allows the user to view only that part of the database that is of interest to him.

  • The subschema defines the portion of the database as seen by the application programs and the application programs can have different view of data stored in the database.

• Data manipulation language (DML)

  • Environment in which data can be managed and is used to work with the data in the database

  • SQL includes commands to insert, update, delete, and retrieve data within the database tables.

  • PL/SQL blocks can contain only standard SQL data manipulation language (DML) commands such as SELECT, INSERT, UPDATE, and DELETE.

  • The use of data definition language (DDL) commands is not directly supported in a PL/SQL block.

• Schema data definition language (DDL)

  • Enables the database administrator to define the schema components

  • DDL allowsadatabase administrator to define the database structure, schema, and subschema.

  • Sub-Schema DDL, allows application programs to define the database components that will be used.

• Schema data definition language

  • Data Definition allows the specification of not onlya set of relations but also information about each relation, including:

  • The schema for each relation.

  • The domain of values associated with each attribute.
  • Integrity constraints
  • The set of indices to be maintained for each relations. Security and authorization information for each relation.
  • The physical storage structure of each relation on disk.

• Schema data definition language (DDL) Commands

  CREATE

  create table r (A, D,, A, D2, .... A,D (integrity-constraint,), (integrity-constraint,))

• r is the name of the relation

• each A, is an attribute name in the schema of relationr
• $D_i$ is the data type of values in the domain of attribute A,

·ALTER

• The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns inatable. The Oracle ALTER TABLE statement is also used to rename a table.

·DROP

• To remove a relation from an SQL database, we use the drop table command. The drop table command deletes all information about the dropped relation from the database.

  The command

  drop table r

SQL> drop table student;