Database Generation file

[demeshFernando]

Specification

We are using mysql database with SSMS server to manage it. The table required for the database with the table names are displayed below.

• [x] Database name: 'bbms_repository'

• [x] staffList table: this table is to store staff details

• NIC varchar(100) primary key,

• firstName VARCHAR(100),

• lastName VARCHAR(100),

• otherName VARCHAR(100),

• line1 VARCHAR(100),

• line2 VARCHAR(100),

• line3 VARCHAR(100),

• city VARCHAR(100),

• homeLand VARCHAR(100),

• gender VARCHAR(100),

• email VARCHAR(100),

• beginDate varchar(100),

• positionId VARCHAR(100),

• employeeLevel VARCHAR(100),

• applicableLeaves varchar(100),

• accountActivation VARCHAR(100),

• foreign key (positionId) references staffPosition(ID)

• [x] acessorySellersDetails: this table is to store the details of the persons who sell the acessories to the bank

• Id int primary key,

• firstName varchar(100),

• lastName varchar(100),

• otherNames varchar(100),

• line1 varchar(100),

• line2 varchar(100),

• line3 varchar(100),

• city varchar(100),

• homeLand varchar(100),

• regBy varchar(100), --the person who registered the product

• registeredDate varchar(100),

• email varchar(100),

• fax varchar(100),

• foreign key (regBy) references staffList(nic)

• [x] acessoryDetails: this will be store all the acessories that sold by the vendors

• id int identity(1, 1) primary key,

• acessoryName varchar(100),

• quantity varchar(100),

• acessoryDescription varchar(200),

• regby varchar(100),

• regDate varchar(100),

• expDate varchar(100),

• sellerId varchar(100),

• rate varchar(100),

• foreign key(regby) references staffList(nic),

• foreign key(sellerId) references acessorySellersDetails(Id)

• [x] acessoryReservation: If any one reserved an acessory or set of them those details are being stored here.

• id int identity(1, 1) primary key,

• acessoryId varchar(100),

• acessoryDescription varchar(100),

• reservedDate varchar(100),

• wantDate varchar(100),

• recievedBy varchar(100),

• reason varchar(100),

• quantity varchar(100),

• foreign key(acessoryId) references acessoryDetails(id),

• foreign key (recievedBy) references staffList(nic)

• [x] attendanceRegistry: this will responsible to store all the attendance details of the staff

• id int identity(1, 1) primary key,

• nic varchar(100),

• inTime varchar(100),

• outTime varchar(100),

• foreign key (nic) references staffList(nic)

• [x] bloodGroups: all the blood groups available.

• bloodID int identity(1, 1) primary key,

• bloodName varchar(100),

• bloodDescription varchar(200),

• beginDate varchar(100),

• regBy varchar(100),

• quantity varchar(100),

• foreign key (regBy) references staffList(nic)

• [x] bloodLog: any blood related transactions were happened this will be resposible to store them

• Id int identity(1, 1) primary key,

• requesterName varchar(100),

• bloodType varchar(100),

• regDate varchar(100),

• regBy varchar(100),

• packetOwner varchar(100),

• donDate varchar(100),

• quantity varchar(100),

• resDate varchar(100),

• packetId varchar(100),

• foreign key (packetId) references acessoryDetails(Id),

• foreign key (bloodType) references bloodGroups(bloodID),

• foreign key (regBy) references staffList(nic)

• [x] bloodRequest: any request for the blood will be stored here.

• id int identity(1, 1) primary key,

• fullName varchar(100),

• reqBloodType varchar(100),

• reqDate varchar(100),

• quantity varchar(100),

• wantDate varchar(100),

• reason varchar(100),

• requestStatus varchar(100),

• sendingDate varchar(100),

• statusUpdatedBy varchar(100),

• decReason varchar(100),

• decBy varchar(100),

• foreign key (reqBloodType) references bloodGroups(bloodID),

• foreign key (statusUpdatedBy) references staffList(nic),

• foreign key (decBy) references staffList(nic)

• [x] diseaseList: any related diseace of the donor will be stored here

• diseaceID int identity(1, 1) PRIMARY KEY,

• diseaceName VARCHAR(100),

• diseaceDescription VARCHAR(300),

• registeredDate varchar(100),

• registeredBy varchar(100),

• foreign key (registerdBy) references staffList(nic)

• [x] diseace_donor: which donor related with which donor details are stored here.

• diseaceId int,

• donor int,

• registeredBy varchar(100),

• registeredDate varchar(100),

• primary key (diseaceId, donor),

• foreign key(diseaceId) references diseaseList(diseaceID),

• foreign key (donor) references donors (nic),

• foreign key (registeredBy) references staffList(nic)

• [x] donorDiseaceRates: this will be responsible for storing percentage any history diseace rates of the donor

• diseaceID int,

• NIC int,

• currentStatus varchar(100),

• beginDate varchar(100),

• endDate varchar(100),

• primary key (diseaceID, NIC),

• foreign key (deseaceID) references diseaseList(diseaceID),

• foreign key (NIC) references donors(nic)

• [x] donorPhoneNumbers: this will store all the donor phone number details

• phoneNumber varchar(100),

• nic varchar(100),

• numberType varchar(100), -- This will store a reserved key for phoneNumber --

• primary key (phoneNumber, nic),

• foreign key (nic) references donors(nic)

• [x] donorRelativeDetails: this will store any donor relative details if available

• id int identity(1, 1) primary key,

• donorId varchar(100),

• registeredDate varchar(100),

• regBy varchar(100),

• firstName varchar(100),

• lastName varchar(100),

• otherNames varchar(100),

• foreign key (donorId) references donors (nic),

• foreign key (regBy) references staffList(nic)

• [x] donorRelativePhoneNumbers: this will used to store all the donor relatives phone numbers

• phoneNumber varchar(100),

• id int,

• primary key (phoneNumber, id),

• foreign key (id) references donorRelativeDetails (id)

• [x] donorStatus: all the donated details will be stored here.

• id int identity(1, 1) primary key,

• donorId varchar(100),

• cardStatus varchar(100),

• donorDescription varchar(300),

• lastDonationDate varchar(100),

• beginDate varchar(100),

• regBy varchar(100),

• lastEditedBy varchar(100),

• foreign key (donorId) references donors(nic),

• foreign key (regBy) references staffList(nic),

• foreign key (lastEditedBy) references staffList(nic)

• [x] donors: this will store the donor details specifically

• nic varchar(100) primary key,

• firstName varchar(100),

• lastName varchar(100),

• otherNames varchar(100),

• birthDate varchar(100),

• line1 varchar(100),

• line2 varchar(100),

• line3 varchar(100),

• city varchar(100),

• homeland varchar(100),

• gender varchar(100),

• email varchar(100),

• beginDate varchar(100),

• bloodGroup int,

• cardStatus varchar(100),

• editedBy varchar(100),

• foreign key (bloodGroup) references bloodGroups(bloodID),

• foreign key (editedBy) references staffList(nic),

• foreign key (cardStatus) references donorStatus (cardStatus),

• [x] donationLog: All the blood related inner bank company activities will be stored here.

• id int identity(1, 1) primary key,

• donorId varchar(100),

• packetId varchar(100),

• doctorId varchar(100),

• specialDescription varchar(300),

• regBy varchar(100),

• regDate varchar(100),

• relatedCamp varchar(100),

• donatedDate varchar(100),

• donatedType varchar(100),

• foreign key (donorId) references donors(nic),

• foreign key (doctorId) references staffList(nic),

• foreign key (regBy) references staffList(nic)

• [x] bloodCamps: All the registered blood camps will be stored here

• id int identity(1, 1) primary key,

• nic varchar(100),

• reqName varchar(100),

• heading varchar(100),

• campDescription varchar(300),

• expectedDonors varchar(100),

• regDate varchar(100),

• regBy varchar(100),

• expectingDate varchar(100),

• foreign key (regBy) references staffList(nic),

• foreign key (nic) references donors (nic)

• [x] leaveApplication: staff leave application are managed using this table

• id int identity(1, 1) primary key,

• userId varchar(100),

• reason varchar(300),

• regDate varchar(100),

• wantedDate varchar(100),

• leaveType varchar(100),

• approvedStatus varchar(100),

• approvedBy varchar(100),

• foreign key (userId) references staffList(nic),

• foreign key (approvedBy) references staffList(nic)

• [x] logBook: all the staff related activities that the person has done using the account will be stored here.

• id int identity(1, 1) primary key,

• staffId varchar(100),

• logDescription varchar(1000),

• createdDate varchar(100),

• foreign key(staffId) references staffList(nic)

• [x] medicineDetails: medicine prescription that are proposed to the donor or any prescription that are suggested to the donor will be displayed here.

• ID int identity(1, 1) PRIMARY KEY,

• medicineName VARCHAR(100),

• medicineDescription VARCHAR(500),

• regDate varchar(100)

• [x] meetings: all the arranged meeting schedules

• id int identity(1, 1) primary key,

• regBy varchar(100),

• manageAcceptance varchar(100),

• managerRejectionReason varchar(100),

• phiAcceptance varchar(100),

• phiRejectionReason varchar(100),

• nurseAcceptance varchar(100),

• nurseRejectionReason varchar(100),

• doctorAcceptance varchar(100),

• doctorRejectionReason varchar(100),

• heldingDate varchar(100),

• reason varchar(300),

• nurseApproval varchar(100),

• nurseCampRejectionReason varchar(100),

• doctorApproval varchar(100),

• doctroCampRejectionReason varchar(100),

• managerApproval varchar(100),

• managerCampRejectionReason varchar(100),

• campId varchar(100),

• createDate varchar(100),

• foreign key (managerAcceptance) references staffList(nic),

• foreign key (phiAcceptance) references staffList(nic),

• foreign key (nurseAcceptance) references staffList(nic),

• foreign key (doctorAcceptance) references staffList(nic),

• foreign key (nurseApproval) references staffList(nic),

• foreign key (doctorApproval) references staffList(nic),

• foreign key (managerApproval) references staffList(nic),

• foreign key (campId) references bloodCamps(id)

• [x] shareMessages: all the messages shared in the office

• ID int identity(1, 1) primary key,

• sharedMessage varchar(1000),

• sendingTo varchar(100),

• recievedFrom varchar(100),

• seenStatus varchar(100),

• sentDate varchar(100),

• sentTime varchar(100),

• recievedDate varchar(100),

• recievedTime varchar(100),

• foreign key (sendingTo) references staffList(nic),

• foreign key (recievedFrom) references staffList(nic)

• [x] outSideContacts: Contact available from the outside.

• id int identity(1, 1) primary key,

• nic varchar(100),

• fullName varchar(100),

• reason varchar(500),

• company varchar(100),

• regDate varchar(100),

• line1 varchar(100),

• line2 varchar(100),

• line3 varchar(100),

• city varchar(100),

• homeLand varchar(100)

• [x] outsideNumbers: numbers of those outside contacts.

• phoneNumber varchar(100),

• nic int,

• numberStatus varchar(100),

• primary key (phoneNumber, nic),

• foreign key (nic) references outSideContacts (id)

• [x] prescribedMedicine: prescribed medicine details to the donor will be displayed here.

• medicineID int,

• NIC varchar(100),

• medicineDescription VARCHAR(100),

• regDate varchar(100),

• primary key (medicineID, NIC),

• foreign key (medicineID) references medicineDetails(ID),

• foreign key (NIC) references donors(nic)

• [x] reminders: reminder will be displayed here.

• id int identity(1, 1) primary key,

• nic varchar(100),

• heading varchar(100),

• reminderDescription varchar(1000),

• regDate varchar(100),

• remDate varchar(100),

• seenDate varchar(100),

• priorityLevel varchar(100),

• seenStatus varchar(100),

• foreign key (nic) references staffList(nic)

• [x] staffContactDetails: all the staff contact details will be displayed here.

• phoneNumber varchar(100),

• nic varchar(100),

• primary key(phoneNumber, nic),

• foreign key (nic) references staffList(nic)

• [x] staffCredentials: all the staff credentials will be stored here.

• username varchar(100) primary key,

• userPassword varchar(100),

• nic varchar(100),

• foreign key (nic) references staffList (NIC),

• [x] staffEducationalQulaification: education qualifications of the staff members will be displayed here.

• id int identity(1, 1) primary key,

• studies varchar(100),

• institute varchar(100),

• beginYear varchar(100),

• endYear varchar(100),

• lastExperiancePosition varchar(100),

• businessFirm varchar(100),

• experiance varchar(100),

• counting varchar(100),

• birthCertificateLink varchar(100),

• degreeCertificateLink varchar(100),

• nicFrontLink varchar(100),

• nicBackLink varchar(100),

• relatedNic varchar(100),

• foreign key (relatedNic) references staffList(nic)

• [x] staffPosition: positions available inside the office will be displayed here.

• ID int identity(1, 1) primary key,

• position varchar(100)

• [x] timeTable: time table of the registration.

• id int identity(1, 1) primary key,

• nic varchar(100),

• heading varchar(100),

• eventDetails varchar(100),

• regDate varchar(100),

• alertDate varchar(100),

• seenStatus varchar(100),

• foreign key (nic) references staffList(nic)

In here all the table related values have been described so please create the tables. Just create the sql file later adjustmetns can be made.

[demeshFernando]

The sql file has been created with the name of bbms_repository.sql

[demeshFernando]

New table addon

In here I have noticed that there is no table to store the details of camp requesters to contact them once the camp get confirmed. So I have added another table,

• [x] bloodCampContacts: this will store all the contact details of those who wish to have a camp organized.
• userNIC varchar(100)
• phoneNumber varchar(100),
• primary key (userNIC, phoneNumber),
• category varchar(100): this category will store the details regarding the following Home (0), Personal (1), Office(2) use the bracketed digits to store in the database.
• foreign key (userNIC) refereces bloodCamps(nic)