Sunday 19 March 2017

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.


 

1 comment: