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

Q.1.
A.1.
(i)

  * The database tables that have been identified for storing data are:

1.       Staff

Attribute Name	Datatype	Feature
EmpId	Varchar(50)	Primary Key
Password	Varchar(30)
Designation	Varchar(30)

2.       Client

Attribute Name	Datatype	Feature
ClientId	Varchar(50)	Primary Key
Password	Varchar(30)
ContactPerson	Varchar(50)
ContactNo	Varchar(11)
Address	Varchar(100)
CompanyName	Varchar(80)

3.       Expenditure

Attribute Name	Datatype	Feature
TransId	Numeric(10)	Primary Key
EventId	Numeric(10)	Foreign Key
ProviderId	Numeric(10)	Foreign Key
EmpId	Varchar(50)	Foreign Key
Amount	Numeric(10)
DateExp	Datetime

4.       EventDesc

Attribute Name		Datatype	Feature
EventId	Numeric(10)		Primary Key
ClientId	Varchar(50)		Foreign Key
Objective	Varchar(300)
Audience	Varchar(50)
TimePeriod	Varchar(10)
Invitation	Varchar(50)
TypeEvent	Varchar(30)
DateEvent	DateTime
Venue	Varchar(100)
Hospitality	Varchar(100)
Performance	Varchar(100)

5.       Estimate

Attribute Name	Datatype	Feature
EventId	Numeric(10)	Foreign Key
EmpId	Varchar(50)
Rate	Numeric(6)
ApproveStatus	Boolean

6.       ServiceProvider

Attribute Name	Datatype	Feature
ProviderId	Numeric(10)	Primary Key
CompanyName	Varchar(100)
Contact	Varchar(50)
ContactNo	Varchar(11)
ServiceType	Varchar(100)
ServiceRate	Numeric(6)
ServiceUnit	Varchar(20)

7.       EventService

Attribute Name	Datatype	Feature
EventId	Numeric(10)	Foreign Key
ProviderId	Numeric(10)	Foreign Key
Expenses	Numeric(10)

8.       Sponsors

Attribute Name	Datatype	Feature
SponsorId	Numeric(10)	Primary Key
SponsorName	Varchar(100)
Product	Varchar(100)

9.       EventSponsor

Attribute Name	Datatype	Feature
EventId	Numeric(10)	Foreign Key
SponsorId	Numeric(10)	Foreign Key
SponsorType	Varchar(15)
SponsorService	Varchar(50)
Revenue	Numeric(10)

10.     PurchaseOrder

Attribute Name	Datatype	Feature
EventId	Numeric(10)	Foreign Key
ProviderId	Numeric(10)	Foreign Key
Description	Varchar(300)
DateDelivery	Datetime
Units	Numeric(10)
Rate	Numeric(10)
Taxes	Numeric(10)
Others	Varchar(20)
OtherRate	Numeric(10)

11.     DeliveryChallan

Attribute Name	Datatype	Feature
ChallanNo	Numeric(10)	Primary Key
EventId	Numeric(10)	Foreign Key
ProviderId	Numeric(10)	Foreign Key
EmpId	Varchar(50)	Foreign Key
ChallanDate	Datetime
Quality	Boolean
Amount	Numeric(10)
AcceptStatus	Boolean

Entity Relationship Diagram

A.1.(ii)

A.1.(iii)

A.1.(iv)

·         The System should answer the following questions in the form of reports or queries:

• What are the pending activities in a specific Event, when are they expected to be completed  and by whom ?
• What is the comparison between the budget for a specific event and actual amounts already spent ?
• What are the pending service deliverables from a specific Service Provider across all events that purchased from that provider ?
• What is the Revenue Vs Expenditure summary of a specific event ? To what extent has revenue sources supplemented total Event expenditure ?
• Which Service Provider has been contracted the maximum number of times previously for a particular kind of service? (Across all locations and in a specific location)
• Which Service provider, under a given category of service, has been always punctual with delivery and quality of delivery?
• Which service provider is consistently delaying delivery over the specified period? (Eg. Upto 10% of the time, 20 – 30%, more than 40 % and so on).
• Summary and details of NC Techsoft earnings:
• Across all Events
• Across events of a specific Event type
• Across events for a specific Client
• Across events for a specific Location (where possible)
• Across events of a  specific Event Coordinator

Implementation of Security Mechanism of

·         Login Process has proper check for  authentic user to login.

·         No login process shall happen until unless username and password are provided.

·         Users after login shall be able to view the proposal available for their events only.

·         No staff member unless authenticate shall be able to login.