1 / 24

Database Design Language Incorporating Collection Types for Web Database Applications

Database Design Language Incorporating Collection Types for Web Database Applications. Bandung, 11 September 2002. Eric Pardede Wenny Rahayu David Taniar. Structure of Presentation. Background Collection Types Current Standard/Products Proposed Database Design Language Example

olivia-owen
Download Presentation

Database Design Language Incorporating Collection Types for Web Database Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Language Incorporating Collection Typesfor Web Database Applications Bandung, 11 September 2002 Eric Pardede Wenny Rahayu David Taniar

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

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

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

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

  6. Background(4) • Although Object-Relational Database (ORDB) accommodate them, the implementation of few collection types is not supported by good design • There is necessity to extend the way in modeling the problem domain, so it can capture the new data structures correctly and efficiently • 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

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

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

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

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

  11. Collection Types in Current Database Standard/Products (2) • Existing SQL3: Table-Name (attribute1 DATATYPE, attribute2 DATATYPE ARRAY(N)); • SQL3 Proposal: Table-Name (attribute1 DATATYPE, attribute2 LIST(DATATYPE))

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

  13. Proposed Database Design • 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

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

  15. 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(A1, {A2, {A21, A22, …, A2m}*},…, An*) 6. Primary keys are identified by the underline, e.g. Table Name(A1, A2,…, An) 7. Collection keys are identified by the dotted underline, e.g. Table Name(A1, {A2, {A21, A22, …, A2m}},…, An)

  16. 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(A1, [A2i, [A21i, A22, …, A2m]],…, 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

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

  18. 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: • For delete action: DLT NLS (delete nullifies), DLT RSTR (delete restricted), DLT CSCD, (delete cascades) • For update action: UPD NLS (update nullifies), UPD RSTR (update restricted), UPD CSCD (update cascades)

  19. 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 Department (dept-number) DLT RSTR UPD CSCD degree Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD

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

  21. 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) • Attribute address and all collection on subject may accept nulls indicated by the asterisks • Attribute email is another possible key, shown by the symbol AK

  22. 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 Department (dept-number) DLT RSTR UPD CSCD degree Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD Attributes (2) • Attribute dept-number is a foreign key pointing to the Department table that has dept-number as the attribute being referenced • 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

  23. Example (5) Relation: FK dept-number Department (dept-number) DLT RSTR UPD CSCD degree Faculty (fac-id, deg-code, deg-name) DLT RSTR UPD CSCD Operations: • 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 • Update of dept-number in Department and degree-name in Faculty may be updated and be cascaded in Student (UPD CSCD)

  24. Conclusion and Future Work • 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 • 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

More Related