Database Design Language Incorporating Collection Types for Web

Database Design Language Incorporating Collection Types for Web Database Applications Bandung, 11 September 2002 Eric Pardede Wenny Rahayu David Taniar

Structure of Presentation o Background o Collection Types o Current Standard/Products o Proposed Database Design Language o Example o Conclusion and Future Work

Background o Relational database (RDB) has been extended to capture complex data structure in real world problem. One of them is by adding the collection types o In web database application, current implementation of collection types in XML schema and the need for metadata repository are few examples of the collection type significance o Currently, the implementation of this feature still follows the traditional relational model and thus the efficiency and performance can become an issue

Background (2) rating isbn Book name rate title o Reviewer contact Traditional: Book (isbn, title); Reviewer (name, contact) Rate (isbn, name, rating)

Background (3) rating isbn Book title o name rate Reviewer contact With Collection: Book (isbn, title, set (name, rating)); Reviewer (name, contact)

Background(4) o Although Object-Relational Database (ORDB) accommodate them, the implementation of few collection types is not supported by good design o There is necessity to extend the way in modeling the problem domain, so it can capture the new data structures correctly and efficiently o The aim of this paper is to propose an extension to RDB design as opposed to the existing design in OODB or existing implementation in ORDB

Collection Types o A set is unordered collection of elements that do not allow duplicates. The objects that belong to a set are all unique. Book title isbn authors {Author} Author name address contact

Collection Types (2) o A list is ordered collections of elements that allows duplicates Borrower borrower_no borrower_name borrower_book Book borrower_book title [Book] isbn authors

Collection Types (3) o A Bag is an unordered collection that allows duplicates Book title isbn authors book_rating Rating book_rating <Rating> point comment

Collection Types in Current Database Standard/Products o SQL 3 as new standard database language has provided few collection types and also proposed foundation for full collection types in SQL 4 o Some DBMS products (such as Oracle 9 i, Informix, etc) has implemented collection types for relational model in their ORDB products

Collection Types in Current Database Standard/Products (2) o Existing SQL 3: Table-Name (attribute 1 DATATYPE, attribute 2 DATATYPE ARRAY(N)); o SQL 3 Proposal: Table-Name (attribute 1 DATATYPE, attribute 2 LIST(DATATYPE))

Collection Types in Current Database Standard/Products (3) o Existing Oracle 9 i: Set Example Table-Name (attribute DATATYPE, nested_table_type) Nested Table <attribute name> STORE AS <nested table name>( (PRIMARY KEY (IDs)) ORGANIZATION INDEX);

Proposed Database Design o o In relational model, the result of design processes is a list of tables that conforms to a certain level of normal forms These tables however do not allow the designer to include details of various design constraints or issues that are essential to a good design Database Design Language (DBDL) is needed to express the results of database design, including the attributes, keys, domains, referential integrity treatment, etc. In this research, DBDL is used as the starting point of designing the collection types in the RDB, before full research on conceptual and logical design is done

Database Design Language 1. Relation and its attributes are represented in the format of Table Name(A 1, A 2, …, An) 2. Set collection attributes are represented using curly brackets, e. g. Table Name(A 1, {A 21, A 22, …, A 2 m}}, …, An) 3. List collection attributes are represented using square brackets, e. g. Table Name(A 1, [A 21, A 22, …, A 2 m]], …, An) 4. Bag collection attributes are represented using angle brackets, e. g. Table Name(A 1, <A 21, A 22, …, A 2 m>>, …, An)

Database Design Language (2) 5. Attributes that are allowed to be null are followed by an asterisk (*). An asterisk on a collection attribute applied to every sub-attributes of the particular attribute, e. g. Table Name(A 1, {A 21, A 22, …, A 2 m}*}, …, An*) 6. Primary keys are identified by the underline, e. g. Table Name(A 1, A 2, …, An) 7. Collection keys are identified by the dotted underline, e. g. Table Name(A 1, {A 21, A 22, …, A 2 m}}, …, An)

Database Design Language (3) 8. Collection keys with index are identified by the dotted underline and super-script i after the attribute, e. g. Table Name(A 1, [A 2 i, [A 21 i, A 22, …, A 2 m]], …, An) 9. Alternative keys are identified by the letters AK followed by the attribute(s) that comprise the alternative key 10. Secondary keys are identified by the letters SK followed by the attribute(s) that comprise the secondary key

Database Design Language (4) 11. Foreign keys are identified by the letters FK followed by the attributes that comprise the foreign keys. These attributes are followed by an arrow pointing to the relation identified by the foreign key. After the parent relation name, determine the keys (including primary keys and collection keys where applicable) and the actual attribute being referred, FK referencing-attribute Table Name (PK, CK, referenced-attribute)

Database Design Language (5) 12. The rules for maintaining the integrity constraints are specified after the foreign keys declaration mentioned before. After determining the referenced attributes, the action to anticipate update and delete operations are specified, following these rules: n For delete action: DLT NLS (delete nullifies), DLT RSTR (delete restricted), DLT CSCD, (delete cascades) n For update action: UPD NLS (update nullifies), UPD RSTR (update restricted), UPD CSCD (update cascades)

Example Given Relation: Faculty (faculty-number, faculty-name, {degree-code, degree-name, degree-length}) Department (dept-number, dept-name, dept-head) Detail Relation: Student (stu-number, stu-name, address*, dept-number, {degree}, {subject, {assign-name, assign-mark}, {exam-no, exam-mark}}*, email) AK email FK dept-number degree Department (dept-number) DLT RSTR UPD CSCD Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD

Example (2) Relation: Student (stu-number, stu-name, address*, dept-number, {degree}, {subject, {assign-name, assign-mark}, {exam-no, exam-mark}}*, email) Table o Table Student has attributes listed with stu-number as the primary key (underlined) o Table Student has two set collection types degree and subject, shown by the curly brackets. The latter has sub-collection inside it o Table Student has four collection keys, degree, subject, assign-name, exam-no, indicate by the dotted underlines

Example (3) Relation: Student (stu-number, stu-name, address*, dept-number, {degree}, {subject, {assign-name, assign-mark}, {exam-no, exam-mark}}*, email) AK email Attributes (1) o Attribute address and all collection on subject may accept nulls indicated by the asterisks o Attribute email is another possible key, shown by the symbol AK

Example (4) Relation: Student (stu-number, stu-name, address*, dept-number, {degree}, {subject, {assign-name, assign-mark}, {exam-no, exam-mark}}*, email) FK dept-number degree Department (dept-number) DLT RSTR UPD CSCD Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD Attributes (2) o Attribute dept-number is a foreign key pointing to the Department table that has dept-number as the attribute being referenced o Attribute degree is a foreign key pointing to the Faculty table. Notice the primary key and collection key have to be specified first before specifying the actual attribute being referred

Example (5) Relation: FK dept-number degree Department (dept-number) DLT RSTR UPD CSCD Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD Operations: o Deletion of a department is restricted if any student exist (DLT RSTR) and deletion of a degree is restricted if any student exists with the particular degree o Update of dept-number in Department and degree-name in Faculty may be updated and be cascaded in Student (UPD CSCD)

Conclusion and Future Work o This paper proposes a database design, focusing in Database Design Language, for relational model using collection types due to the inexistence of standardized design for relational model using collection types o Further work includes further design steps such as the transformation method of the collection into relations, the normalization, and the integrity constraint issue of the collection types