MemberName PK nameID FirstName LastName Address PK addressID StreetAddress City
ID: 3755160 • Letter: M
Question
MemberName
PK nameID
FirstName
LastName
Address
PK addressID
StreetAddress
City
State
ZipCode
MemberAddress
PK MemberAddressID
FK nameID
FK addressID
Contribution
PK contributionID
amountPaid
Status
PK statusID
boolean (true or false)
MemberContribution
PK MemberContributionID
FK nameID
FK contributionID
MemberStatus
PK MemberStatusID
FK nameID
FK statusID
Develop a working database for a church. in Microsoft Access that fulfills a personal or business need. (This would be a great opportunity to develop a tool for a local church or non-profit organization.) The database needs to include at a minimum the following: Two tables Two queries Two joins Two forms Two reports
Explanation / Answer
First to create a working database, we need to organize and group data entities in proper manner.
We can Group Data in two Categories / Tables
1. Member Details (which includes name, address, contribution)
2. Status Details (which includes Member Status and Member Contribution)
Illustrated below how to create a database, create a table, write queries
* Create a database name example: church_db , Query follows as mentioned below
CREATE DATABASE church_db
* Create two tables (one - includes Member_Details, two - includes Status_Details)
Create Table Member_Details with Member Information as listed below and created three primary key params which should be 'Unique' PRIMARY KEY are == (nameID,addressID,contributionID) Query follows as mentioned below
CREATE TABLE Member_Details
(nameID INTEGER NOT NULL,
FirstName VARCHAR(5000) NOT NULL,
LastName VARCHAR(5000),
addressID INTEGER NOT NULL,
StreetAdress VARCHAR(5000) NOT NULL,
City VARCHAR(4000) NOT NULL,
State VARCHAR(4000) NOT NULL,
ZipCode INTEGER NOT NULL,
contributionID INTEGER NOT NULL,
amountPaid BIGINT,
PRIMARY KEY(nameID,addressID,contributionID));
Second Table Status_Details holds StatusID , MemberContibutionID as Primary Key and Import Foreign Key columns from Table Member_Details (so that columns need not be created extra) Query follows as mentioned below
CREATE TABLE Status_Details
(statusID INTEGER NOT NULL,
statusInfo BIT default 0,
MemberContributionID INTEGER NOT NULL,
nameID INTEGER FOREIGN KEY REFERENCES Member_Details(nameID),
contributionID INTEGER FOREIGN KEY REFERENCES Member_Details(contributionID),
PRIMARY KEY (statusID, MemberContributionID));
Will write Insert Query to add Member Details For example Member Name is 'Arun Balaji' then the Query follows as mentioned below
INSERT INTO Member_Details VALUES (1,'Arun','Balaji',1,'1st Street','Bangalore','Karnataka','600003',1,20000);
After Inserting the above Details you can fetch the added data using Select Query as mentioned below
SELECT * FROM Member_Details WHERE nameID = 1;
Will write Insert Query to add Status Details For added Member Name 'Arun Balaji' As multiple columns in Table 'Status_Details' are Foreign Key We can use 'SQL INSERT INTO SELECT' logic for insert Query
First will Insert Primary Key Columns for Status_Details table
INSERT INTO Status_Details COLUMNS (statusID,statusINFO,MemberContributionID) VALUES(1,1,1);
Second Fill the remaining Foreign Key params from Member_Details Table
INSERT INTO Status_Details COLUMSN (nameID,contributionID) SELECT nameID,contributionID FROM Member_Details where nameID=1;
This will directly copy the previous inserted values from Member_Details table to Status_Details table.
Inner Join Queries are possible in this architecture since these two table are inter-related.
We can Member Name (from Member_Details table) and StatusInfo (from Status_Details) using Inner Join Query
Query is
SELECT Member_Details.FirstName,Member_Details.LastName,Status_Details.statusInfo FROM Member_Details INNER JOIN where Member_Details.nameID=Status_Details.nameID;
Output for this Query is
<'Arun','Balaji',1>
We can Use FULL OUTER JOIN to print the all the params match between Table 1 (i.e) Member_Details and Table 2(i.e) Status_Details using the Below Query
SELECT Member_Details.FirstName,Member_Details.LastName,Status_Details.statusInfo FROM Member_Details FULL OUTER JOIN where Member_Details.nameID=Status_Details.nameID ORDER BY Member_Details.FirstName;
Output of the Query will be <'Arun','Balaji',1> (Since we have only one column value inserted, if more values are inserted then multiple outputs are poosible).