CSCI 5333.1 DBMS
Spring 2020
Homework #1

Database Modeling in UML

Download and install the community version of Astah's UML Editor: http://astah.net/download. If Astah provides free version for students, that should also be acceptable. This is one of the best free UML editors available. It has some restrictions but should be more than sufficient for our class.

The homework assignment is to model a drastically simplified toy Online Task Management System Application (Taskster). Multiplicities for attributes and associations should be as specific as possible. Attribute and association documentation are optional, but they are encouraged when not trivial. For example, the roles or names of associations should be displayed when appropriate. Operations for classes are not necessary. Use the stereotypes <<PK>>, <<CK>>, <<unique>> and <<derived>> for attributes when appropriate. Create suitable data types for SQL, such as SQL::Datetime, SQL::time, SQL::PhoneType, SQL::Decimal, etc. For string types, you may use 'string', 'varchar', 'char' or user-defined types when appropriate.

Save your files as <<last-name>>_<<student>>_h1_class.asta (Astah's file format) and <<last-name>>_<<student>>_h1_documentation.docx (optional; for additional information). Examples: bajaj_0007007_h1_class.asta. Also include a PDF versions of your class diagram: <<last-name>>_<<student>>_h1_class.pdf.

Submit your homework through Blackboard.

Taskster

Create a data model to support a part of an online team project task application. This is a drastically simplified system that does not support many functions. Make reasonable assumptions.

The basic idea is to support teams to define, store, access, label, and update tasks and sub-tasks for a team project. Access to the system can be made through multiple types of devices such as computers, smartphones, web services, etc.

Users must be registered as members to use Taskster. The system should store the last name, first name, a unique screen name, and a working email address for every member. The email address does not need to be unique. A unique member id is assigned by the system and the start time of the membership should be recorded. A member may be referred by another member to join Taskster.

Each member must have a username and a password to access the system. However, an account may be used by a person other than a member, such as an Taskster's manager or a system administrator. The management and administration module of the system is not modeled in this assignment.

A member can create any number of projects. The creator of a project is known as the owner of the project. Each project may also has managers who have heightened privileges relative to other members of the project. However, only the owner can change the manager of a project. The owner and the manager can be the same person. A project can have any number of members. The time when a member joins a project should be recorded.

A member may have many roles in a project. Some of these role types are predefined by Taskster, for example, librarian, developer, team leader, supervisor, consultant, etc. They are standard role types. Some roles are project specific and can be defined by a manager of the project. For example, a software project may include roles such as modeler, programmer, technical writer, etc. When a project-specific role type is defined, an optional description and a creation time should be stored together with the role name. The manager who defined the role should also be noted. Every role, standard or user-defined, has a role level. The role level is an integer with a definition. Many roles can have the same role levels.

A project has an unique id. The project name and logo (an image) should be stored with a description and the creation time. A project has any number of tasks (to-do tasks). A task has a name and a description. A task may have any number of sub-tasks. There is no limit on the level of sub-tasks. A task can be created by any project member. The creator of a task should be noted. A task may have an expected completion time and an actual completion time. A task may be assigned to a team member. The creator and the assignee may or may not be the same project member.

A task has a current status. Taskster keeps track of status changes. A status has a name. There are standard predefined status names, such as 'start' or 'completed'. A standard status has a definition and an abbreviation that should be stored. Status may also be project-specific and user defined, such as 'first draft completed'. In this case, a description of the status should also be noted together with its name. For example, a status history for a task may be:

8/1/2019 13:22:05: 'started'
8/1/2019 13:23:15: 'assigned'
8/4/2019 17:24:15: 'first draft completed'
8/4/2019 17:25:12: 'routed to audit'
8/6/2019 09:24:25: 'second draft completed'
8/4/2019 09:25:32: 'routed to audit'
8/10/2019 11:11:28: 'completed'