000708229-MD.Mahmudur Rashid-DDI-1302_2012

7/28/2019 000708229-MD.Mahmudur Rashid-DDI-1302_2012

1/16

A

COURSEWORK REPORT

ON

Quorum Party Database

Submitted in partial fulfillment for the

Award of degree of

BECHELOR OF SCIENCE

INBUSINESS INFORMATION TECHNOLOGY

(2011-12)

COURSE: COMP1302

DATABASE DESIGN AND IMPLEMENTATION

Submitted By:

MD. MAHMUDUR RASHID

000708229

Submitted to:

Dr Ala Al-Zobaidie

Greenwich Coordinator


2/16

Statement and Confirmation of Own Work

Student declaration

I have read and understood UOG Policy on Academic Dishonesty and Plagiarism. I can confirm

the following details:

Student ID:000708229

Name: MD. Mahmudur Rashid

Centre Name: Daffodil Institute of IT

Module Name: Database Design and Implementation (DDI)

Number of words: 924

I confirm that this is my own work and that I have not plagiarized any part of it. I have also noted

the assessment criteria and pass mark for assignments.

Due Date: 4/12/12

Submitted Date:4/12/12


3/16

Table of Contents

Section Page

Introduction ------------------------------------------------------------------------------------------------- 5

Assumption -------------------------------------------------------------------------------------------------- 6

Conceptual Data Modeling ----------------------------------------------------------------------------- 7

Relational Schema ------------------------------------------------------------------------------------------ 8

SQL Code ------------------------------------------------------------------------------------------------------ 9

Screen Dumps ------------------------------------------------------------------------------------------------ 11-14

D6 ------------------------------------------------------------------------------------------------- 11

D7 -------------------------------------------------------------------------------------------------- 12

D8 -------------------------------------------------------------------------------------------------- 13

D9 -------------------------------------------------------------------------------------------------- 14

Conclusion ----------------------------------------------------------------------------------------------------- 15

Bibliography --------------------------------------------------------------------------------------------------- 16


4/16

List of Figures

Figure-1 ------------------------------------------------------------------------------------------ 7

Figure-2 ------------------------------------------------------------------------------------------ 11

Figure-3 ------------------------------------------------------------------------------------------ 12

Figure-4 ------------------------------------------------------------------------------------------ 13

Figure-5 ------------------------------------------------------------------------------------------ 14

Tools used:

Microsoft Access 2007 Microsoft Visio 2007


5/16

DATABASE DESIGN AND IMPLEMENTATION University Of Greenwich

B.Sc. Hons in Business Information Technology 5

Introduction:

In this coursework of Database Design and Implementation I have been asked to design a

database for Quorum Party Database which store information about parties, members,

venues, commercial subscribers, invitations etc. I have done the work as specified in the

coursework specification.

At first I produce an Entity Relationship Diagram for design the Conceptual Data Model (CDM)

of the database with all entities, relations and their primary key attributes.

Then I mapped the CDM to relational schema by following some specific mapping techniques I

have learned during my course of study.

After that I checked that relational schema is in 3NF or not. Although my schema was satisfy

3NF criteria.

Then using Microsoft Access 2007 DBMS I created a Database to implement the relational

schema and populate it with some sample data.

Finally I implemented all of the applications, forms and report as specified in the deliverables

successfully with this database.


6/16



D1. Assumptions

There are several things that need to be assumed for completing this coursework. The

assumptions that I made about the business rules and factors are outlined below:

About Calendar year: I assumed the calendar year that maintained for this system isfrom June to May. Last calendar year means calendar year before the June of the

running calendar year.

In PartyDetails table I add some additional data like memIn, memOut to count thetime of one member stay in a party.

When a new member will register with the system the registration date will be thecurrent system date which will automatically added from the system. Similarly

whenever a member will turned into party animals or/and gold member the date of

membership will also pick from the system date.

In the party table I added some additional data like: party tag, dress-code to store moreinformation about parties.

As commercial subscriber has to also sign up to the system for getting services I alsostore some login information about them like: user name, password, email.

Party animals preferences are stored in a different table where they can also choosetheir preferred commercial subscriber name from a drop down list.

The date in joinDate field in Quorum_Members table is assign for storing the datethe member has joined as well as every categories of membership table has a field

named DOM that is used to store their membership date for the particular category.

This is essential for calculating many duration related queries in future.

I have created email ID validations rule where-ever this type of field used in thedatabase. This is because simplify the input procedure so that valid email id can be

inserted during data entry.


7/16



D2. Conceptual Data Model (CDM)

Entity Relationship Diagram for Quorum Party Database

Quorum Member Party

Full Member

Gold Member Party Animal

Venue

Commercial SubscriberInvitation

Attends

Arranges In

Create

Promote By

Send To

mID pNo

vID

CS_IDinvNo

Starts

O

Owns

M N

1

M

M

N

M N

1

M

M 1

M

N

Preference

Has

1

M

mIN

mOutGuestWith

Figure-1: ERD for Quorum Party Database.


8/16



D3. Relational Schema

Relational Data Model

Quorum_Members (mID, mFName, mLName, alias, email, passw, gender dateJoined).

Full_Members (DOB, phn, address, city, DOM,mID).

Gold_Members (DOM,rating,mID).

Party_Animals (DOM,hobby,mID).

PA_Preferences (PreferenceType,prefCS_ID,mID).

Party_Details (mIn, mOut,guestWith,mID,pNo).

Party_Starter (mID,pNo).

Parties (pNo ,pDate, p_s_time, p_e_time, pTag, dressCode ,vID).

Venues (vID ,vName, vType, vLoc, postcode, city).

Promotions ( vID,CS_ID).

Commercial_Subscribers (CS_ID ,CS_Name, service_Type, address, contact, email, reg_Date, username,

passw, owner_CS,).

Invitations (invNo, invCat , message,CS_ID).

Invitation_Messages (msgDate,invNo,mID).

Relational Schema in MS Access Relationships:


9/16



D4. Normalization Declaration

Assurance Statement:

The Relational Schema satisfies 3NF criteria


10/16



D6. SQL code for the required applications (A1-A4)

A1.SELECT V.vID, V.vName, V.vType, V.vLoc, V.postCode, V.City, QM.gender,

COUNT(PD.mID) AS Total_AttendFROM Venues AS V, Quorum_Members AS QM, Parties AS P, Party_Details AS PDWHERE V.vID=P.vID And QM.mID=PD.mID And P.pNo=PD.pNo And P.pDate Between#1/1/2011# And #1/31/2011# And V.city='London'GROUP BY V.vID, V.vName, V.vType, V.vLoc, V.postCode, V.City, QM.gender,QM.gender;

A2.SELECT V.vType, V.vName, V.postCode, V.city, COUNT(P.vID) AS Total_PartiesFROM Venues AS V, Parties AS PWHERE V.vID=P.vID AND P.pDate BETWEEN #6/1/2010# AND #5/31/2011#GROUP BY V.vType, V.vName, V.postCode, V.city, P.vIDHAVING COUNT(P.vID)>2ORDER BY P.vID;

A3.SELECT PA.mID, QM.mFName, QM.mLName, QM.Gender, QM.email, PA.DoM,COUNT(PD.mID) AS Total_AttendFROM Party_Animals AS PA, Party_Details AS PD, Parties AS P, Quorum_MembersAS QMWHERE QM.mID =PA.mID AND PD.pNo=P.pNo AND PD.mID=PA.mID AND PA.mID NOT IN(SELECT GM.mID FROM Gold_Members AS GM ) AND PA.DoM


11/16



Screen dumps for all required applications in D6.

Application A1:

Figure-2.1: Screen Dump of application A1.

Application A2:


Application A3:


Application A4:



12/16



Screen dumps for D7.

New Member registration form:

Figure-3: Member Registration Form.


13/16




Master Details form with listed venues managed by a given Commercial Subscriber picked up

from the drop down menu:

Figure-4: Master Details Form.


14/16




Report: Invitation message details

Figure-5: Invitation Messages Sent to Members.


15/16



Conclusion:

Although this course work is a part of the total system that described in the specification but it has a

major role over the system. After doing this course work I have gathered a sound knowledge about the

design and development a relational DBMS using MS Access. I also learned to produce forms and reports

as well.Finally I have done all the deliverables as specified in the coursework.


16/16



BibliographyThomas M. Connolly, Carolyn E. Begg, Database Systems, 4th edn, Published by Dorling kindersely (India)

Pvt Ltd, 482, F.I.E, Pataprgonj, Dellhi 110 092, India

Create and use an index to improve performance, Support / Access / Access 2010 Help and How-to /

Designing applications , viewed 18 March 2012,

Validation rules,Support / Access / Access 2007 Help and How-to / Database design , viewed 25 March2012,
http://office.microsoft.com/en-us/support/?CTT=97http://office.microsoft.com/en-us/access-help/?CTT=97http://office.microsoft.com/en-us/access-help/redir/CL010253345.aspx?CTT=97http://office.microsoft.com/en-us/access-help/redir/CH010369201.aspx?CTT=97http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/support/?CTT=97http://office.microsoft.com/en-us/support/?CTT=97http://office.microsoft.com/en-us/access-help/?CTT=97http://office.microsoft.com/en-us/access-help/redir/CL010072900.aspx?CTT=97http://office.microsoft.com/en-us/access-help/redir/CH010064569.aspx?CTT=97http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/redir/CH010064569.aspx?CTT=97http://office.microsoft.com/en-us/access-help/redir/CL010072900.aspx?CTT=97http://office.microsoft.com/en-us/access-help/?CTT=97http://office.microsoft.com/en-us/support/?CTT=97http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspxhttp://office.microsoft.com/en-us/access-help/redir/CH010369201.aspx?CTT=97http://office.microsoft.com/en-us/access-help/redir/CL010253345.aspx?CTT=97http://office.microsoft.com/en-us/access-help/?CTT=97http://office.microsoft.com/en-us/support/?CTT=97

000708229-MD.Mahmudur Rashid-DDI-1302_2012

Documents

Transcript of 000708229-MD.Mahmudur Rashid-DDI-1302_2012