Database Management System (CS403)
Assignment No.2
Deadline
Your assignment must be uploaded before or on 08 May 2013.
Rules for Marking
It should be clear that your assignment will not get any credit if:
1. The assignment is submitted after due date
2. The assignment is copied
Objectives
The objectives of this assignment are,
- Giving the idea of a most commonly used semantic data model for graphical representation of conceptual database design i.e. Entity Relationship Data model.
- Giving the idea of entities and their attributes, cardinalities and the relationship between them.
- Providing an understanding of types of relationships i.e. in how many ways an entity interact with other entity in real world.
Important Note:
This Assignment is the continuation of First Assignment. Take same case study given in Assignment 1 (also provided at the end of this assignment), apply the techniques and methods given in lectures and by utilizing the solution of first assignment uploaded on LMS in the form of DFD in order to finalize the tasks of second assignment listed below in task description. Some hints are provided below to link DFD with ERD which can be helpful in your design process.
Hints:
- Every data store in DFD must correspond to at least one entity in the ERD.
- There should be processes in DFD which create, modify and delete instances of the entities in the ERD.
- For every relationship in ERD there should be a process in DFD which uses it.
Reference:
Please refer to the case study given in assignment no. 1.
Note: Same case study is also given at the end of this assignment for your convenience.
Tasks:
In this assignment you will focus on the tasks given below:
Task#1: [Marks 8]
Identify all the Entities along with proper attributes.
Task#2: [Marks 12]
Draw a comprehensive ER-diagram consisting of these Entities (identified in Step-1) along with their relationships and cardinalities.
You have to perform all these steps given above in your solution file.
Note:
Use all concepts you have studied so far and techniques in order to identify Entities, attributes and their relationships of the given case study discussed particularly from 7 to 10 regarding ER data model.
Drawing Final ER Data Model:
You can use any tool for drawing like MS Office or Visio.
Important things to consider:
- As happens in real world that everyone visualize a problem in different way so the solutions of all students should be according to their own thinking not taken from some source.
- As this is your first comprehensive design, so you have to work very keenly and with concentration. This design requires your full attention as this conceptual design will be transformed into a relational model in next assignment. Any mistake in this conceptual design can cause you much burden afterwards.
Case Study
Consider a real world scenario of a university system. We are going to develop a security information system for employees so that there may be no chances of error or illegal access of an employee record or account. To complete this system we will divide this whole project in small assignment chunks (probably 4 to 5) and will try to cover every phase of the database development life cycle from scratch to implementation (SQL Queries). Scenario of the Security information system is given below.
Scenario:
The top management of the university wants to computerize the university employee’s records for security reason. The security information system will comprise of three sub applications.
1. Fingerprint system (deployed through a biometric device)
2. Online Employee Record Management (ERM) that will be a web based application deployed on a central server and access able through LAN
3. Live Activity Monitoring System (LAMS) deployed through security camera (CCTVs) installed on various location in university premises.
Detail of each application is given below:
Fingerprint system:
To achieve this task the management wants to fix an automated fingerprint identification system for employee’s attendance. This system automatically matches fingerprints against a database of known prints. Attendance system uses biometrics to guarantee the accuracy in reporting. Attendance system works by having an employee place their finger on a small scanner upon entering and exiting the office, the attendance record is stored in the database. This information can later be checked by the management to track the employee’s check in, checkout times and location as well.
Online ERM:
To guarantee security, the employee’s are provided with a web account. Employees are provided with the user name and password so that no one can access his / her record. Using their account, employees can mark attendance, apply for leave, apply for loan, manage his/her bank account or see salary slip. Employee can also generate a new password by using the “Forgot your password?” link on the login page.
Live Activity Monitoring System (LAMS):
To better enhance the security system, management wants to install the security cameras to monitor the employee’s activities more closely, to keep an eye on precious assets and properties of the university. With the help of security cameras unauthorized and illegal activities are controlled. The security cameras capture images and record videos. These records are stored in the database. The management can view all the details about their employees at any time to monitor the productivity and job related skills of employees. Further than case use the information to ensure that he/she might not indulge in any immoral activities.
Note that all the three applications share a central database (as shown in figure-1), deployed on a dedicated database server.