Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Normalized Database Schema-Computer Sciences Applications-Project Report, Study Guides, Projects, Research of Applications of Computer Sciences

Birla Institute of Technology and ScienceApplications of Computer Sciences

This report is for final year project to complete degree in Computer Science. It emphasis on Applications of Computer Sciences. It was supervised by Dr. Abhisri Yashwant at Bengal Engineering and Science University. Its main points are: Template, Interface, Accounts, Main, Page, Cash, Related, Processes, Budget, Expense, Normalized, Database, Schema

Typology: Study Guides, Projects, Research

2011/2012

Uploaded on 07/18/2012

padmini
padmini 🇮🇳

4.4

(207)

175 documents

1 / 49

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Interfaces
Template interfaces for the following processes were also included in the last
presentation.
x Accounts main page.
x Cash related processes.
x Bank related processes.
x Expense related processes.
x Budget related processes.
5
docsity.com
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30
pf31

Partial preview of the text

Download Normalized Database Schema-Computer Sciences Applications-Project Report and more Study Guides, Projects, Research Applications of Computer Sciences in PDF only on Docsity!

Interfaces

Template interfaces for the following processes were also included in the last

presentation.

 Accounts main page.

 Cash related processes.

 Bank related processes.

 Expense related processes.

 Budget related processes.

Normalized Database Schema

Normalized database schema for the finance module is as follows

F_cashbook ( sr#, particulars, amount, name, date, paymenttype, CASHIERid )

F_bankbook ( sr#, particulars, amount, name, date, paymenttype, accountantid,

entrynumber )

F_expenseregister( sr#, particulars, amount, id, acc_code, date)

F_estimatedbudget( deptname, head_of_account, acc_code, estd_amount)

F_conslidatedbudget( head_of_account, acc_code, cons_amount)

F_reappropriation( head_of_account_from, acc_code_from, head_of_account_to,

acc_code_to, reap_amount, date)

Chartofaccount( head_of_account, acc_code)

Database Tables

Following tables of the finance module are mounted on the database using Oracle 9i.

VOLUME

There will be more than one million records in this table. Table Type: Dynamic

3- Table : F_EXPENSEREGISTER

Purpose : Expense transactions.

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 SR# NUMBER 10

2 PARTICULARS VARCHAR2 150
3 AMOUNT FLOAT 10
4 ID VARCHAR2 10

5 ACC_CODE VARCHAR2 15  PK Generated by acc_code. 6 DATE DATE sysdate  PK Generated by date.

DATA

This table maintains the record of the expenses. VOLUME There will be more than one million records in this table. Table Type: Dynamic

4- Table : F_ESTIMATEDBUDGET

Purpose : Gather estimated budget from head of departments.

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 DEPTNAME VARCHAR2 30  PK Generated by department name.

HEAD_OF_ACCOU
NT
VARCHAR

3 ACC_CODE VARCHAR2 15  PK Generated by account Code. 4 ESTD_AMOUNT FLOAT 10

DATA

This table is for the estimated record of budget form each head of department. VOLUME There will be more than one million records in this table. Table Type: Dynamic

5- Table : F_CONSOLIDATEDBUDGET

Purpose : Consolidating the estimated budget head wise.

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 HEAD_OF_ACCOU NT

VARCHAR2 50

3 ACC_CODE VARCHAR2 15  PK Generated by account Code.

4 CONS_AMOUNT FLOAT 15

DATA

This table is for maintaining the record of the budget that is consolidated head wise. VOLUME There will be more than five hundred records in this table. Table Type: Static

Table : F_REAPPROPRIATION

Purpose : Maintaining the re-appropriated amounts.

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1

HEAD_OF_ACCOU
NT _FROM
VARCHAR

3 ACC_CODE_FROM VARCHAR2 15  PK Generated by account Code.

4

HEAD_OF_ACCOU
NT_TO VARCHAR^

Previous presentation

Previous report (1st^ half of 7th^ semester) focused mainly on database and interfaces

design. It included following component.

Database design

Two types of DFDs were created for Stores & Procurement System (S&P).

Context DFD

Here the emphasis is on system and its environment. The system as a whole is

represented as a big bubble, and external entities are represented as squares from which

input flows and to which output is directed.

Diagram Zero

Diagram zero is representation of system itself. It depicts the major processes along with

the external entities, data stores and data flows. It is single top level diagram and does not

represent each process in detail.

Entity Relationship Diagram

E-R Diagram is used to represent the logical structure of the organizations. It depicts

entities and relationships between entities along with the attributes.

Interfaces

Template Interfaces for the following processes were also created.

 Employee related processes

 Stores related processes

 Procurement related processes

 Accounts related processes

 Approval process

Database Tables

Following database table has been created in Oracle 9i for Stores and Procurement

Module.

1- Table : SP_DRR

Purpose : Daily Receipt Register Entry

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 SR# NUMBER 10

2 DATE DATE

3 DRR# VARCHAR2 10  PK Generated by DRR# 4 INVOICE# VARCHAR2 10

5 STORES_NATURE CHAR 15 6 DESCRITION CHAR 150  7 QUANTITY NUMBER 10 8 PURCHASE_MODE CHAR 10 9 SUPPLIER CHAR 35 10 SR_NOTE# VARCHAR2 10

DATA

This table is the persistent record of the daily receipts in stores VOLUME

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 CODE# VARCHAR2 10  PK Generated by item’s CODE#

2 ITEM_NAME CHAR 15
3 ACC_UNITS CHAR 10
4 LOCATION VARCHAR2 10
5 DATE DATE
6 REQUISITION# VARCHAR2 10 
7 TO CHAR 50 
8 RATE_PER_ITEM FLOAT 10
9 QUANTITY NUMBER 10
10 VALUE FLOAT 10
11 IN_STOCK NUMBER 10
NATURE_OF_STOR
ES CHAR^
DATA

This table is the persistent record of the daily non-consumable issues from stores VOLUME There will be more than one million records in this table. Table Type: Dynamic

4- Table Name : SP_LEDGER_ISSUE

Purpose : Ledger entry for store receipts in case of non-consumables only

S# Column Name Data Type Size Default Constraints Description/ Remarks

Primary Null Unique

1 CODE# VARCHAR2 10  PK Generated by item’s CODE#

2 ITEM_NAME CHAR 15
3 ACC_UNITS CHAR 10
4 LOCATION VARCHAR2 10
5 DATE DATE
6 SUPPLY_ORDER# VARCHAR2 10 
7 FROM CHAR 50
8 RATE_PER_ITEM FLOAT 10
9 QUANTITY NUMBER 10
10 VALUE FLOAT 10
11 IN_STOCK NUMBER 10
NATURE_OF_STOR
ES CHAR^
DATA

This table is the persistent record of the daily non-consumable receipts in stores. VOLUME There will be more than one million records in this table. Table Type: Dynamic

5- Table Name : SP_PERSONAL_INVENTORY

Purpose : To maintain employee’s record of non-consumable items

S# Column Name Data Type Size Default Constraints

Description/

Remarks

Primary Null Unique

1 SR# NUMBER 10

2 INVENTORY# VARCHAR2 10 

PK Generated by employee’s inventory# 3 REQUISITION# VARCHAR2 10

4 DESCRIPTION CHAR 150 5 LEDGER_REF VARCHAR2 10

6 QTY_TRFD NUMBER 10 7 QTY_RETD NUMBER 10 8 QTY_ISSUED NUMBER 10 9 VOUCHER# VARCHAR 10 10 DATE_VOUCHER DATE 11 DEPT NUMBER 10 12 NAME CHAR 15

7 DATE_TO DATE
8 DESCRIPTION CHAR 150
9 QTY_REQD NUMBER 10
10 QTY_ISSUED NUMBER 10
DATA

This table keeps the record of requisitions related to consumable items. VOLUME There will be more than one lak records in this table. Table Type: Dynamic

7- Table Name : SP_REQUISITION_NON_CONSUMABLE

Purpose : To maintain record of non-consumable items’

S# Column Name Data Type Size Default Constraints

Description/

Remarks

Primary Null Unique

1 INDENTOR CHAR 35

2 REQUISITION# VARCHAR2 10 

PK Generated by requisition#

3 DATE DATE Sysdate 4 SR# NUMBER 10

5 DEPT_INDENTOR CHAR 10 6 DESCRITION CHAR 150 7 ACC_UNITS CHAR 10 8 QTY_REQD NUMBER 10 9 QTY_ISSUED NUMBER 10

DATA

This table keeps the record of requisitions related to non-consumable items. VOLUME There will be more than one lak records in this table. Table Type: Dynamic

8- Table Name : SP_STOCK_RECORD_ISSUE

Purpose : To maintain record of issued consumable items

S# Column Name Data Type Size Default Constraints

Description/

Remarks

Primary Null Unique

1 SR# NUMBER 10

2 CODE# VARCHAR2 10 

PK Generated by item’s code#

3 NAME CHAR 35 4 DATE DATE sysdate

5 REQUISITION# VARCHAR2 10 6 QUANTITY NUMBER 10 7 PER_UNIT_RATE FLOAT 10 8 VALUE FLOAT 10 9 ACC_UNITS CHAR 10 10 SUPPLIER_NAME CHAR 35 11 NATURE_OF_STORES CHAR 15

DATA

This table keeps the record of issued consumable items. VOLUME There will be more than one lak records in this table. Table Type: Dynamic

9- Table Name : SP_STOCK_RECORD_RECEIT

Purpose : To maintain record of received consumable items

S# Column Name Data Type Size Default Constraints

Description/

Remarks

Serial No

ACR Abbreviation used in Database design.

ACR Forms

1 ACR_Assist_NT ACR form for Assistant/UDC/LDC/Library Assistant/Jr Assist (^2) ACR_Car_NT ACR form for Staff car/Dispatch riders 3 ACR_BPS_16_NT ACR form for Officers in BPS-16/SPS- 4 ACR_Supdt_Inchg_NT ACR form for Superintendent/Assistant In-charge 5 ACR_Steno ACR form for Steno (^6) ACR_BPS_17_18_NT ACR form for Officers above BPS-16/ SPS 7 (BPS 17/BPS 18) 7 ACR_Misc_PAEC ACR form for Miscellaneous Category of Employee in PAEC (^8) ACR_Tech_Staff_T ACR form for Technical Staff 9 ACR_BPS_19_20_NT ACR form for Officers in BPS 19/BPS- 10 ACR_BPS_21 ACR form for Officers in BPS- 11 ACR_Tele_NT ACR form for Telephone Operator (^12) ACR_Tech_Off_T ACR form for Technical Officer

Database tables

1- Table : ACR_Assist_NT

Purpose : To provide ACR form for evaluation of Assistants.

S

# Column Name^ Data Type Size

Def

aul

t

Constraints Description

Primary Null Foreign

(^1) EMP_ID Varchar2 1 5

(^2) ACR_Date_From Date @ (^3) ACR_Date_To Date @

(^4) ACR_Assist_NT_Q1 Varchar

5 Referencing and paging of notes

(^5) ACR_Assist_NT_Q2 Varchar

5 Keeping of files and papers well

(^6) ACR_Assist_NT_Q3 Varchar2 5 Maintenance of records

(^7) ACR_Assist_NT_Q4 Varchar2 5 Skills in noting and drafting

(^8) ACR_Assist_NT_Q5 Varchar

5 Other duties e.g. Cashiers duties etc

(^9) ACR_Assist_NT_Q6 Varchar2 5 Other^ Clerical duties

(^10) ACR_Assist_NT_Q7 Varchar2 5 Regularity^ and punctuality

(^11) ACR_Assist_NT_Q8 Varchar

5 Standard of work like quality, output (^12) ACR_Assist_NT_Q9 Varchar2 5 Personal traits (^13) ACR_Assist_NT_Q10 Varchar2 5 Intelligence

(^14) ACR_Assist_NT_Q11 Varchar

5 Perseverance and devotion to duty

(^15) ACR_Assist_NT_Q12 Varchar2 5 Cooperation and tact

(^16) ACR_Assist_NT_Q13 Varchar2 5 Amenability^ to discipline (^17) ACR_Assist_NT_Q14 Varchar2 5 Integrity

(^3) ACR_Date_To @ (^4) ACR_Tele_NT_Q1 150

Date Varchar2 Intelligence

(^5) ACR_Tele_NT_Q2 Varchar

150 Knowledge of trade and Profession

(^6) ACR_Tele_NT_Q3 Varchar

150 Skills in the trade and Profession

(^7) ACR_Tele_NT_Q4 Varchar

150 Whether employ is polite ,courteous

(^8) ACR_Tele_NT_Q5 Varchar2 150 Is^ the^ employ punctual

(^9) ACR_Tele_NT_Q6 Varchar

150 Is the employ amenable to discipline

(^10) ACR_Tele_NT_Q7 Varchar

300 Has the employ been responsible for any outstanding work during the period under review meriting special commendation s? If so, What?

(^11) ACR_Tele_NT_Q8 Varchar

300 Has the employ ever been reprimanded for indifferent work Or for any other cause? If so, give details.

(^12) ACR_Tele_NT_Q9 Varchar

300 General remarks concerning Defects of

character and indebtedness or other matters. Desired tube brought to notice.

(^13) ACR_Tele_NT _Q10 Varchar

300 Is the employ doing job satisfactory? (^14) CSO_ID Varchar2 15 @ (^15) RO_ID Varchar2 15 @

DATA

This table is the persistent record of the ACR form for Telephone Operator

VOLUME There will be more than one million records in this table. Table Type: Dynamic

3- Table : ACR_BPS_16_NT

Purpose : To provide ACR form for evaluation of BPS 16 Officers(SPS-7).

S# Column Name Data Type Size Default Constraints Description

Primary Null Foreign (^1) EMP_ID Varchar2 15 @ @ (^2) ACR_Date_From Date @ (^3) ACR_Date_To Date @

(^4) ACR_BPS_16_NT _Q1 Varchar2 5 Intelligence^ and mental alertness

(^5) ACR_BPS_16_NT _Q2 Varchar

5 Judgment and sense of proportion (^6) ACR_BPS_16_NT _Q3 Varchar2 5 Initiative^ and drive