7

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.

5
  • Have you searched "SDL Storage Definition Language"? There appear to be several references. Feb 11, 2013 at 21:40
  • Yes, but sadly, most of them are really shallow definitions or simply definitions of the acronym itself and nothing more. I even came across this very question on just the second page of Google's results.
    – Andrei Bârsan
    Feb 11, 2013 at 21:51
  • 2
    My guess is that "Storage Definition Language" refers to all those bits and pieces of a CREATE TABLE statement that describe the physical properties of a table and/or the different kinds of indexes on a table. I've never heard the term before myself but I can see how an academic would want to differentiate with respect to column attributes. I was going to make this an answer but I'm not sure this is a valid SO question (although interesting).
    – Bob Duell
    Feb 11, 2013 at 22:14
  • The SDL stuff is almost anything that's not specified by the SQL standard that is different in every DBMS and that specifies anything to do with how or where the data in the relevant table is stored. So 'yes: SET storage_engine=MYISAM is a MySQL-specific example of SDL'. Feb 12, 2013 at 1:17
  • 1
    Take a look at my cited answer here dba.stackexchange.com/a/195646/2639 Jan 18, 2018 at 2:07

3 Answers 3

2

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.

1
  • That's a fine line: the SQL spec actually says a big int is a "long long" Jan 18, 2018 at 1:45
1

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

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.