Database Systems
CIT 365 – Database Systems
Topic #1: Internal Email Box
The IT
department of a reputed company hired you to reinforce their teams. They have
an exciting idea to develop a new system to enable the employees to communicate
through it (for confidentiality matters). The system is seen as an emailing system
and has to be accessible through a Web browser. The system will enable an
administrator to add accounts, suspend accounts, and remove accounts. The
initial description of the system is as follows:
An email account
is associated to each employee in the company. An employee is described by a
unique ID, a first name, a last name, a date of joining the company, a
position, the date of taking that position, a login, a password and his/her
department. A department is described with an ID, a name, and the date of creation.
Each position has an ID and a description. We can also associate a minimum
salary and a maximum salary of that position. An employee can send an email to
one another employee or several employees. The email has the sender, the
receiver(s), the subject, and the content.
A special
person, called a root, can add employees to the system, remove employees, or
update them. Other employees can only access their information (details, email
box, etc.), remove emails for their own box, and access the address book for
consultation. Finally, an alias email address can be created by the
administrator which has a unique id and a set of employees included in that
alias. When an email is sent to that alias, all included employees receive the
email. An employee can be included in several aliases and an alias contains at
least 1 employee.
Requested
Work
Part
I: Modeling and foundations
1. Build
the conceptual model for the business problem
2. Provide
the logical/Relational model
3. Provide
the full physical model
4. Provide
the data dictionary for your system
Part
II: Population/Deletion of data
Populating
the database: Add at least 10 rows per table: We need the SQL code
forperforming the operations
1. Add
an employee
2. Add
a department
3. Add
a position
4. Create
an Alias and add people to it.
Update the database:
remove at least 2 rows per table: We need the SQL code for performing the
operations)
1. Update/remove
employee information
2. Update/remove
department information
3. Update/remove
position information
4. Update/remove
alias information (add employees, remove employees, etc.)
Part III: Consulting the database
1. Employees
can send emails to other employees
2. See
the list of all emails a specific employee has received (from, subject, date
and ifread or not yet) in a specific order (by date or subject)
3. Simulate
the reply to an email and keep track of that reply
4. See
all sent emails by a specific employee (to, subject, date)
5. Employees
can remove an email (only from their email box)
6. Number
of emails received
7. Number
of email sent
8. Number
of email received from each employee
9. Number
of email sent for each employee
10.
Number of email received from which position
11.
Number of email sent for each position
12.
The average time between each email received
13.
The average time between each email sent
15.
Search for emails by sender, or date, or content, or a date
interval.
Part IV:
16. Build
a simple Web application (using PHP) that illustrates one addition, one update,
one deletes, and one search. The application should allow the user to interact
with the database using a Web interface.
We
expect the following:
1.
A
database with at least 10 rows per table;
2.
The
code of the application;
3.
A
report describing the different components and capabilities the application
does. ;
4.
A
presentation of the application for the user.
Comments
Post a Comment