Reference no: EM131099806

Description

Consider the design of an XML database system to store and query a collection of confidential XML documents so that only authorized users can read them. Each XML document is identified by a document id, subject, the content of the document, and a list of keywords to facilitate the search of XML documents. XML documents are organized by categories and each category is identified by a unique category id, the name of the category, and a list of keywords to facilitate the search of categories. An XML document is posted to exactly one category. Each user of the system is identified by a user id, password, name, and email address. If a category is assigned to a user, then the user will be able to read all the XML documents under that category unless we explicitly prohibit that the user cannot read a particular XML document. A category can be assigned to multiple users and a user can have multiple category assignments.

Part 1

Design the database using the ER approach and then create the tables accordingly. Populate the tables

so that each table contains at least 10 tuples. Then using Java and SQL, implement the following

functionality:

1. Insert a new user, make sure that each email can be registered at most once.

2. Delete an existing user;

3. Update an existing user by any attribute.

Some simple GUI interfaces are expected for each functionality.

Part 2

Based on part 1, implement the following functionality using Java and SQL with simple GUI interface:

1. Insert/delete/update an XML document.

2. Insert/delete/update a category.

3. Assign a user to a category or delete a user from a category.

4. Ban a user from reading a particular XML document.

5. Search for all XML documents that have keywords “Java, SQL”.

6. Search for all categories that have keywords “SQL”.

7. List all the documents under category “SQL” that can be read by user “John”.

8. List all categories that are not assigned to any user.

9. List all users that are not assigned to any categories.

10. List the total number of users, documents, and categories in the system.