What is a Storage Definition Language (SDL)?

Question

In my Databases lecture, the teacher mentioned that SQL is not just a data definition language (DDL), data manipulation language (DML) and view definition language (VDL), but also a storage definition language (SDL). The last usage doesn't come with any further explanations or examples.

Searching for extra information on the internet regarding using SQL as a SDL yields no relevant results. Could anyone give an example of using SQL as a storage definition language?

Edit:

Would a specification such as MySQL's SET storage_engine=MYISAM; count as a storage definition? It doesn't really relate to the data being stored, but rather, how it's stored.

Answer 1

You may want to ask your teacher to define "SDL". I don't think the term appears in the SQL standard. I found this reference (see page 3), and I disagree: SQL does not define the physical structure, the physical bytes per field, the physical field order, the physical sorting, or "mixed records", whatever that might mean. SQL doesn't define fields at all, or records, or say anything about how the data are stored on the disk if they're stored on the disk.

That said, every vendor does provide some means for the administrator to define where the data will be kept, and many offer varying levels of control over physical storage aspects (e.g. ISAM, clustered indexes). AFAIK all that stuff lies outside the SQL standard. I've used DBMSs that understand SQL DML, but completely separate mechanisms for defining tables and storage.

Answer 2

I know that acronym as a Schema Definition Language - schema(metadata) manipulation language http://docs.sqlalchemy.org/en/rel_0_9/core/schema.html

Answer 3

From Fundamentals of Database Systems (7th Edition) 7th Edition (pg 39)

In DBMSs where a clear separation is maintained between the conceptual and internal levels, the DDL is used to specify the conceptual schema only. Another language, the storage definition language (SDL), is used to specify the internal schema. The mappings between the two schemas may be specified in either one of these languages. In most relational DBMSs today, there is no specific language that performs the role of SDL. Instead, the internal schema is specified by a combination of functions, parameters, and specifications related to storage of files. These permit the DBA staff to control indexing choices and mapping of data to storage. For a true three-schema architecture, we would need a third language, the view definition language (VDL), to specify user views and their mappings to the conceptual schema, but in most DBMSs the DDL is used to define both conceptual and external schemas. In relational DBMSs, SQL is used in the role of VDL to define user or application views as results of predefined queries (see Chapters 6 and 7).

In current DBMSs, the preceding types of languages are usually not considered dis- tinct languages; rather, a comprehensive integrated language is used that includes constructs for conceptual schema definition, view definition, and data manipulation. Storage definition is typically kept separate, since it is used for defining physical storage structures to fine-tune the performance of the database system, which is usually done by the DBA staff. A typical example of a comprehensive database language is the SQL relational database language (see Chapters 6 and 7), which represents a combination of DDL, VDL, and DML, as well as statements for constraint specification, schema evolution, and many other features. The SDL was a component in early versions of SQL but has been removed from the language to keep it at the conceptual and external levels only.

The same source goes on to say, on page 201,

Each commercial RDBMS will have, in addition to the SQL commands, a set of commands for specifying physical database design parameters, file structures for relations, and access paths such as indexes. We called these commands a storage definition language (SDL) in Chapter 2. Earlier versions of SQL had commands for creating indexes, but these were removed from the language because they were not at the conceptual schema level. Many systems still have the CREATE INDEX commands; but they require a special privilege. We describe this in Chapter 17.

It seems these terms VDL and SDL come out of Three-schema, an idea in the 1970s. According to this author, they've more or less been merged into SQL a comprehensive language which tries to address all of these.

I believe they're also referenced in A Preliminary Architectural Design for the Functional Hierarchy of the INFOPLEX Database Computer. (1980) as "Database Sublanguage Faculty" and "View Translation Layer". I found numerious references in the the late 70s and early 80s to model languages, and storage languages.

Taking an example CREATE TABLE f (x int, y varchar(25));. Here, you're defining

• SDL: the storage for the row int, varchar(25).
• DDL: the schema: f(x,y) a relational tuple with two slots.
• VDL: the "view" into it, which is just a relationship by the same name as the table.

In addition to the things which seems to overlap with storage, most databases extend the spec to talk about indexes, and as you mention storage backends which are absolutely distinct from the conceptual model and address the specific storage-model.

You can see this again in Database Management Systems: Understanding and Applying Database Technology (pg 210) as "Data Storage Definition Language (DSDL)." Lots of talk about CODASYL, RDL (Relational Data Language), NDL (Network Data Language), IRDIS (Information Resource Dictionary System).