Structured Data Types -: A structured data type is a user defined data type with elements that are not atomic rather they are divisible and can be used either separately or as a single unit as per requirements. It is a form of user defined object that contains a sequence of attributes, each of which has a data type. An attribute is a property that helps to describe an instance of a particular type for example if we want to define a structured type called address to store addresses, in which city might be one of the attributes of this structured type.
A structured data type can be used as the type for a column in a regular table, the type for an entire table or as an attribute of another structured type. When used as the type for a table, the table is known as typed table.
CREATE TYPE statement is used to create a structured data type and DROP statement is used to delete the structured data type, Consider ‘Emp-Dept’ schema discussed previously. In this schema, table ‘Emp ‘ is created with four column namely EmpNo, it is system generated identity column, Name contains name of the employee, Address which is used to hold the address of employee, it is a structured type column of type ‘ADDRESS-T’ and ProjNo which is a list that stores project number of project taken by employee and eImage that will contain images of an employee.
CREATE TYPE ADDRESS-T as Row (street varchar (12), city varchar (12), state varchar (12),postal code varchar (12))
Now, the ‘Emp’ table is created having ‘ADDRESS-T’ and jpeg-amage as data type of Address field as eimage as shown below:
CREATE TABLE EMP (Empno integer system generated, Name varchar (12),Address ADDRESS-T,Proj-no set of (varchar 12),Eimage jpeg-image);
Similarly, all the tables regarding ‘company’ database is also created by using DDL statements shown in below :
CREATE TABLE PROJECT
(Projno integer, Pname varchar (20),
Location REF (address-t) SCOPE LOCS,
Empno set of (integer));
CREATE TABLE LOCS OF ADDRESS-T REF is locid system generated.
CREATE TABLE DEPT
(Deptno integer, Dname varchar (20),
Dlocation REF (address-t) SCOPE LOCS,
Projno set of (integer));
CREATE TABLE CLIPS
(clipno integer, Cname varchar ( 20),
Objects varchar (20) ARRAY [20],
Budget float,
Projno integer Ctime time);
Above shown table LOCS has an attribute locid which is of ADDRESS-T type and is system generated.
PROJECT is a table which specifies the name, project number along with the location of the project which refers to the LOCS table and also specifies the no. of employees working on the project.
DEPT is a table which specifies name and deptno (unique value) along with location of department. It also specifies the project completed or undertaken by the department.
CLIPS is a table which contains the information of the clip showing project demonstration like the objects used in the clip, time of the clip as well as the projno with which clip is associated.
Now suppose there arises a need to show the clip of the project ‘HR Management’ along with location of the project. Consider the company database, it has a number of inbuilt methods which provides different functionality to the scheme, among which a ‘show’ method is also available in the schema which shows the, clipno specified, in windows media player. Thus method is used in the following query to produce the result as described :
SELECT C.Cname, C.Projno,B.Location; show (Clipno)
FROM CLIP C PROJECT P
Where C.Projno = P.Projno and P.Pname = ‘HR Management’;
Suppose , there is a need to find the names of employees along with their images who are living in ‘Gandhi Nagar’ of ‘Ahmedabad’.
SELECT Name, Image FROM EMP WHERE Address.street = ‘Gandhinagar’ and address.city = Ahmedabad’;
The address attribute of EMP table is a structured user defined data type of which street and city are one of the attributes which are used as separate data item for comparison.
Structure Data Types in SQL 99 :-
SQL 99 allows user to define ‘distinct’ types but they are confined to relational model as these data types are atomic. SQL 99 also provides the facility of defining structured types which extends the relation model, that deals with atomic values only. When we create such structured type, SQL creates a constructor function for the type and creates both ‘mutator’ and ‘observer’ methods for the attribute of type.
Constructor function has the same name as structured type with which it is associated. The constructor function has no parameter and returns an instance of type with all of its attribute set to null values.
MUTATOR method exists for each attribute of a structured type. When a mutator method is invoked on an instance of structured type and specify a new value for its associated attribute method, returns a new instance with the attribute update to a new value.
When an ‘OBSERVER’ method is invoked on an instance of structured type, the method returns the value of attribute for that instance.
Structured types available in SQL 1999 are
ROW (f1t1,f2t2…….fntn)–
1. It represents a row, or a tuple of fields f1,f2,…..fn of types t1,t2,..tn respectively. ‘ROW’ data type specifies every table as a collection of rows or every table as set of rows or multi-set of rows for example, the ‘address-t’ is declared as of ROW data type as shown below which contains area, city and state as its components :-
CREATE TTOE address-t
AS ROW (area: varchar (20),city: varchar (20), state : varchar (20))
2. ARRAY [i] : It represents an array of ‘i’ items of ‘base’ type for example, the ‘objects’ field of CLIP table used an array of 10 objects, each of which is of varchar (20) type. A multidimensional array can not be created in SQL 99. An array can be used as component in ROW type as shown but not in array type :-ROW (pno : integer, object : varchar (20) ARRAY [10])
3. list of (base) : It represents a list of all items of ‘base’ type, fo example,
PROJECT (Projno : integer, Pname: varchar (25), Emp no: list of (integer))
4. set of (base) : It represents a set of ‘base’ type items. A set does not contain duplicate elements unlike lists otherwise it is used in the same manner as list.
5. bag of (base) : It represents a bag or a multi-set of base type items.
Collection type or build data types are types using list of, set of, bag of and ARRAY. But SQL does not provide any efficient method for manipulation of these collection type objects. So now we will discuss how these data types can be manipulated and also discuss the operations which can be applied on these data types.
Operation on Structured Data :-
Structured data can be manipulated using built in methods for types defined using type constructor. These methods are similar to operations used for data types (atomic) of traditional RDBMS.
1. Operations on Arrays
Array is used in the same manner as in traditional RDBMS. ‘Array index’ method is used to return the number of elements in the array for example. Suppose we want to find those projects whose clips contain more than 10 items or objects then following query can be used :
SELECT P.Pname, P.Projno
FROM project P, Clip C
WHERE CARDINALITY (C.Objects)>10 AND C.Projno = P.Projno
The above query select project name and projectno from “PROJECT” whose clips contain more then 10 items which can calculated by using CARDINALITY operation.
2. Operations on Rows
Row type is a collection of fields values whose each fields can be accessed by the same traditional notation for example, address-t.city specify the attribute ‘city’ of the type address-t. When operation is applied on collection of rows then result obtained is also a collection of values.
If a column or field whose type is ROW (f1t1, f2t2,…….fntn) and c1 fk gives us a list of values whose type is tk. If c1 is a set of rows or a bag of rows then c1 fk give us a set of values of type tk.
Consider ‘Emp-Dept’ schema in which we have to find the names of those employees who resides in ‘Malviya Nagar’ of ‘New Delhi’.
SELECT E Empno,E.Name
FROM Emp E
WHERE E.Address.area =’Malviya Nagar’ AND E.Address.city=’New Delhi’
AND E.Address.city = ‘New Delhi’
3. Operations on Sets and Multi-sets
Set and multisets are used in the traditional manner by using =,<,>,>,< comparison operators. An item of a set can be compared by other items using E (belongs to ) relation. Two set objects can create a new object using U, (Union Operation). They can also create a new object by subtracting a set of elements from other set by using ‘-‘ (set difference operator ). Multi-set also uses the same operations as used by the sets but the operations are applied on the number of copies of element into account.
4. Operations on Lists
List includes operations like ‘append’, ‘concatenate’, ‘head’, ‘tail’ etc. to manipulate the items of list for example, ‘concatenate’ or ‘append’ appends one list to another, ‘head’ returns the first element of list, ‘tail’ returns the list after removing the first element.
Nested Relations :-Attributes having complex types like setof (base), bagof (base) etc are known as ‘Nested Relation,. So ‘Unnesting’ is a process or transforming a nested relation into 1NF relation. The nested relational model is an extension of relational model in which domains may be either atomic or relational valued. The value of a tuple on an attribute may be a relation and relation may be stored within relations. Consider ’emp-dept’ schema in which for each employee, we store the following information in EMP table :-
1. Empno
2. Name
3. Address
4. Projno
The domains of some of the information stored for an employee are non atomic as, Projno, specifies the number of projects worked on by the employee. An employee may have a set of projects to be worked on. Suppose we want to find those employee who have worked on ‘HR Management’ project.
Address, unlike projno, does not have a set valued domain but it is a structured data consist of components, area, city and state. Thus, ‘Address’ is non atomic field.
Suppose we want to list employee along with projno’s associated with him i.e., we want one row for each (empno, projno) pair. then the nested relation shown above must have to be converted int 1NF relation through the process of ‘unnesting’. The ‘address’ attribute is replaced by its components i.e. area, city and code
1NF relation is easy to understand and adequate to represent the data buta nested relation maps the real world scenario more accurately. A 1NF flat relation is generated by using the query as :
SELECT E.Empno,E.Name,P,Projno,E.Address.area,E.Address.city,E.address.state
FROM E.mp E,E.Projno P
The variable E is bound to tuples in Emp and for each value of E, the variable P is successively bound to the set in projno field of E. Besides this, each component of address field is used in same traditional manner by using ‘-‘ operator.
The reverse process of transforming a relation into a rested relation is called ‘Nesting’. This process of grouping can be accomplished by using an aggregate function on temporary multi-set relation created in flat table. we want to generate the Emp relation from flat Emp then the following query can be used :-
SELECT E.Empno,E.Name,
FROM flat-Emp
GROUP BY E.Empno,E.Name