Case Study-Open University Of Srilanka

Case Study

3266- Information Systems and Technology.

Introduction

   We are setting up a Database for this private institute "Thooba Private Institute" as a group project to record the details about it's staffs and departments they belongs to. Also we have to develop a system to manage the day-to-day business of the institution.

Team members with the student numbers

Group Number:WE-E-G3-Team 09

M.S Zaky Ahamed-s92077257

Mohamed Shakeel - s92066425

Jeganathan Sarujan-s92069135

 Entities

1. Staff

2 . Head

3. Departement

Attributes

1. Staff

     Attributes: ID,Name,JobTitle,Salary

2. Departement

      Attributes: Name, D-No, Location

Relationships

1. Staff(1)-Department(N)

2. Head(1)-Department(1)

ER Diagram with Explanation

ER Diagram for Academic Institute.

This includes these constraints, as required by the specification:

• A thick line of total participation from entity Staff to relationship BelongsTo, recording the requirement that every member of staff must belong to some department (although still possibly more than one).

• A thick line with an arrowhead from Department to Head, capturing the constraint that every department has exactly one head of department. 

Actors

• STAFF

    A group of teachers who impart education to the students in school or college is known as faculty. staff , indicates the entire group of people, employed by the organization.

Duties and responsibilities of staffs in our Institute,

effective classroom teaching, academic advising and counseling of students, participation in departmental committee work, continuous development of the curriculum through assessment, applied research or scholarly activity, and service such as assisting in recruitment of students.

• HEAD

Head of the institution means the Principal of a College / Institution who is responsible for all academic activities of that College / Institution and for implementation of relevant Rules and Regulations.

  Duties and Responsible of  head in our institute,

Monitor, lead, train, and manage staff. 

conduct research and set goals. 

Department heads ensure that overall day-to-day operations run smoothly.

Business Process

Functional Requirements of the system

1.Internal Process

 creating new procedure, workflow,work group, basic knowledge, services and communication

.

2.Organizational Structure

The information technology(IT) unit will be created to enable information communication to the staffs and students and to produce the institute report accurately, faster and complete.

3.Interaction

Non-Functional Requirements of the system

performance

reliability

availability

recoverability

Normalization of the Relation drawn using the ER Diagram

Relational Database Schema

SQL statements and Table creation

CREATE DATABASE Wisdom;

USE Wisdom;

CREATE TABLE Departments (

    DepId INT ,

    DepName TEXT,

    Address VARCHAR(30),

    PRIMARY KEY (DepId)

);

INSERT INTO Departments values

(001,"Educational Department","No 76, OfficeRoad"),

(002,"Administrative Deparment ","No 56, LibraryRoad"),

(003,"Financial Department","No 46, MainRoad");

CREATE TABLE Staffs (

    Id_No INT NOT NULL,

    Job TEXT,

    Name varchar(30),

    Salary DECIMAL,

    PRIMARY KEY (Id_No)

);

INSERT INTO Staffs values

(101,"Teacher","M.S.Zaky",75000.00),

(102,"Accountant","R.V.Dussan",56000.00),

(103,"Assistant Accountant","R.Raganar",80000.00);

CREATE TABLE DepartmentHeads (

    Id_No INT,

    Name TEXT,

    DepId INT,

    FOREIGN KEY (DepId)

        REFERENCES Departments (DepId),

    FOREIGN KEY (Id_No)

        REFERENCES Staffs (Id_No)

);

INSERT INTO Departments values

(102,"R.Floki",001),

(101,"M.S.M .Abdullah",002),

(103,"A.W.M Shafie",003);

SHOW TABLES;

SELECT * From Departments;

UPDATE Departments

SET DepName="Health Department"

WHERE  DepId= 2;

DELETE FROM Departments WHERE DepId=3;