BCA 3rd sem /BCSL - 034/Solved Assignment/ DBMS Lab /2017-2018 New

Q.1. 

A.1.(I)

ER diagram

Ans 1.(II)

TABLE NAME: ACCOUNT HOLDER DETAILS

PRIMARY KEY: CUSTID

FIELD NAME	DATA TYPE	SIZE	description
CUSTID	NUMBER	20	Customer id
NAME	TEXT	50	Name
PER  STREET	TEXT	50	Present street
PER  CITY	TEXT	50	Present city
PIN CODE	NUMBER	25	Pincode
PER  STATE	TEXT	50	Present state
PER  PH  NO	NUMBER	20	Present phone number
OFF  STREET	TEXT	50	Office street
OFF  CITY	TEXT	50	Office city
OFF  PIN  CODE	NUMBER	25	Office pin code
OFF  STATE	TEXT	50	Office state
OFF  PH  NO	NUMBER	25	Office phone number
E.MAIL  ID	TEXT	50	Email id
BRAND NUMBER	NUMBER	20	Brand number
AMOUNT	CURRENCY	20	Amount
DATE	DATE/TIME	10	Date

TABLE NAME=NON ACOUNT HOLDER DETAILS

PRIMARY KEY: NONMEMBERID

	Description

 

FIELD  NAME	DATA  TYPE	SSSSSSSIZE
NON MEMBER  ID	TEXT	55	Nonmember id
FROM NAME	TEXT	50	From name
FROM  SREET	TEXT	50	From sreet
FROM  CITY	TEXT	50	From city
FROM  STATE	TEXT	50	From stste
PH  NO	NUMBER	20	Phone number
BR/FR  NO	NUMBER	20	Br /Fr number
DATE	DATE/TIME	10	Date

TABLE NAME=COURIER DETAILS

Primary key: courier id

FIELD  NAME	DATA  TYPE	SIZE	Description
COURIER  ID	NUMBER	20	Courier id
MEM/NM  ID	TEXT	50	Member nonmember id
BR/FR  NO	TEXT	55	BR/ FR number
TO  NAME	TEXT	50	To name
TO  STREET	TEXT	50	To street
TO  CITY	TEXT	50	To city
TO  PIN  CODE	NUMBER	20	To pin code
TO  STATE	TEXT	50	To stste
TO  PH  NO	NUMBER	20	To phone number
DATE	DATE/TIME		Date
WEIGHT	NUMBER	20	Weight
RATE	CURRENCY	20	Rate

TABLE NAME=COURIER RETURN DETAILS

Foreign key: courier no

FIELD  NAME	DATA  TYPE	SIZE	Description
COURIER  NO	NUMBER	20	Courier number
RETURN  DATE	DATE/TIME		Return date
BRANCH  NO	TEXT	20	Branch number
REASON	TEXT	50	Reason

TABLE   NAME=EXPENCES DETAILS

Primary key: expensesid

FIELD  NAME	DATA   TYPE	SIZE	Description
expensesid	TEXT	10	expenses id
PETTY  EXP	CURRENCY	20	Petty expenses
TRAVEL  EXP	CURRENCY	20	Travel  expenses
REPAIR   EXP	CURRENCY	20	Repair  expenses
E  DATE	DATE/TIME	20	Expenses date

TABLE NAME=STAFF DETAILS

Primary key: staff no

FIELD  NAME	DATA  TYPE	SIZE	Description
STAFF  NO	TEXT	55	Staff no
STAFF  NAME	TEXT	50	Staff name
STREET	TEXT	50	Street
CITY	TEXT	50	City
PIN  CODE	NUMBER	20	Pin code
STATE	TEXT	50	State
PH  NO	NUMBER	25	Phone no
QUALIFICATION	TEXT	50	Qualification
DESIGNATION	TEXT	50	Designation
RIGHTS	TEXT	55	Rights
SALARY	CURRENCY	45	Salary

TABLE NAME=BRANCH DETAILS

FIELD  NAME	DATA  TYPE	SIZE	Description
BRANCH  NAME	TEXT	50	Branch name
BRANCH  NO	TEXT	55	Branch number
BRANCH  STREET	TEXT	50	Branch street
BRANCH  CITY	TEXT	55	Branch city
PIN  CODE	NUMBER	20	Pin code
BRANCH  STATE	TEXT	50	Branch state
BRANCH  PH  NO	NUMBER	20	Branch phone number

FIELD  NAME	DATA  TYPE	SIZE	Description
BR/FR  NO	TEXT	55	br/fr no
EACH _OVER	CURRENCY	45	Each day turn over
COURIER  RECEIVED	NUMBER	20	Courier received
COURIER  DISPATCH	CURRENCY	25	Courier dispatch
DATE	DATE/TIME		Date

Ans 1. (III)

Ans 1(IV)