Multiset types - SQL

An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an SQL table is in general a bag of rows, rather than a set of rows, because SQL does indeed permit the same row to appear more than once in the same table. Although SQL has no names for table types, it does support multisets in general and it does have names for multiset types. A multiset type name consists of a type name followed by the key word MULTISET. For example, INTEGER MULTISET is the name of the type each of whose values is either (a) a bag, consisting of zero or more appearances of each value of type INTEGER and zero or more appearances of the null value of type INTEGER, or (b) the null value of type INTEGER MULTISET.

It would seem at first glance, then, that we perhaps do have a type name for a table type after all. For example, our enrolments table could perhaps be of type

ROW ( Name VARCHAR(50), StudentId VARCHAR(5),

CourseId VARCHAR(5) ) MULTISET

In fact one could declare a local variable to be of this type and its value could indeed consist of the rows. However, such a type cannot be the declared type of a base table, in spite of the fact that the elements of a base table are indeed rows of the same type. Moreover, as I have already mentioned, there is such a thing as the null value of that multiset type, whereas NULL can never appear in place of a table-no table expression in SQL can ever evaluate to NULL-nor can NULL appear in place of a row in a table. So the set of values of a multiset type whose element type is a row type includes bags that are not tables as well as bags that are.

Posted Date: 1/18/2013 2:12:50 AM | Location : United States

Ask an Expert

Related Discussions:- Multiset types - sql, Assignment Help, Ask Question on Multiset types - sql, Get Answer, Expert's Help, Multiset types - sql Discussions

Write discussion on Multiset types - sql
Your posts are moderated

Write your message here..

Related Questions

Program, heap sort program in pl/sql heap sort program in pl/sql

Closing a cursor variable, Closing a Cursor Variable The CLOSE stateme... Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

Write sql code that will create the writes table, Question: Consider th... Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va... DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

Defining autonomous transactions, Defining Autonomous Transactions To ... Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

Parameter and keyword description - %type attribute, Parameter and Keyword ... Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

%rowcount, %ROWCOUNT When its cursor or cursor variable is opened, the... %ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

Scope and visibility- pl/sql, Scope and Visibility The References to an ... Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

Using invoker rights in pl sql, Using Invoker Rights: By default, the ... Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Assignment source not a literal - variable, Assignment Source Not a Literal... Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');