Database Coursework

1) The E.R Model (Entity Relationship Model) Wedding_Id Contact_Id * Weddin g

Wedding_Id* Gift_Id* Consist s

Wedd_Gift

Gift_Id Consists

Gift

a has

Contact Contact_Id Wedding_Id * 1.1) Explanation of Entity Relationship Model for The Giftland. (Assumptions) * Wedding consist of Wedd_Gift: A wedding can have more one gift; A Guest can by more than one type of a gift and more than one quantity of a gift. * Gift consists of many Wedd_gift; A Gift can be brought by a guest in many wedding. * Wedding has a Contact details; Assuming that wedding couple (Bride and Groom) dwell together before marriage and also after the marriage. 2) NORMATIZATION

U.N.F Wedding (Wedding_date, Groom, Bride, {Address, City, Postcode, Tel_no, Mobile_no, E-mail}, {Gift_id, Gift_des, Gift_purchased} )

Assumption: * Introducing Wedding_id it enables to specify a particular wedding record. on the other hand using Wedding date as primary key can cause chaos to the Giftland, because, on a particular given date , there may be more than one wedding could /var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

happen. Hence, introducing Wedding_id would be appropriate for the Giftland Gift shop. Which will uniquely identify wedding. * A wedding can have more than one gift type and more than one quantity of a particular Gift type, for instance. Gift K178 Kitchen Scales can be brought by many guests and could be brought more than one time. Hence Wedding and Gift has been separated from many to man to one to many relationship, by introducing the Wedd_gift table.

* Introducing Gift_price will enable guest to choose a gift/s costing appropriate amount, which they wish to buy.

1.N.F Wedding-1 (Wedding_id, Wedding_date, Groom, Bride,{ Address, City, Postcode, Tel_no, Mobile_no, E-mail }) Wedd_Gift-1 (Wedding_id*, Gift_id*, Gift_purchased) Gift-1 (Gift_id, Gift_purchased, Gift_price )

2.N.F Assumption: Wedding- 2 (Wedding_id, Wedding_date, Groom, Bride,{ Address, City, Postcode, Tel_no, Mobile_no, E-mail }) Wedd_Gift- 2 (Wedding_id*, Gift_id*, Gift_purchased) Gift- 2 (Gift_id, Gift_price )

3.N.F /var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

* it is more convenient to separate contact details, than having in the Wedding Table, because for consistence. for instance; If Contact details were to held in Wedding it would look like these; Wedding ( Wedding_Date, Groom, Bride, Address, City, Postcode, Tel_no, Mobile_no, E-mail, Gift_id, Gift_Des, Gift_purchased) these would make table wider and inconsistent. Gift- 2 (Gift_id, Gift_price, Gift_des ) Wedding -2(Wedding_id, Wedding_date, Groom, Bride,{ Address, City, Postcode, Tel_no, Mobile_no, E-mail }) Weddig_id time ) Weddig_date wedding couple.)

Wedding_date ( A wedding can happen only on one given date at a Wedding_id ( On a given date only one wedding is possible for

Weddig_id ,Wedding_date

Groom( One groom marries to one bride)

Weddig_id ,Wedding_date particular date)

Bride (One bride marries to on groom on a

Wedding_id,Wedding_date address)

Address ( Wedding couple lives at the same

Wedding_id,Wedding_date Address ( Wedding couple lives at the same address and address has a unique city name) Wedding_id,Wedding_date Postcode ( Bride and groom lives at the same address, which has a unique postcode ) * Assuming that a wedding couple lives at one address and have a telephone line, which is unique in its own, Tel_no would be more than enough to store in the database as it will also store mobile_no and Email id, therefore it is not require to store more than one Tel_no, also implies to Mobile_no and Email. Having store this data more than one it would require more data storage, well as it will be inconsistent Wedding_id,Wedding_date is unique)

Tel_no (One telephone number per wedding and

Wedding_id,Wedding_date and it is also a unique)

Mobile_no (One mobile number per couple,

/var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

* (a person can have more than one Email id ) but, Assuming a wedding couple have an email id, just one from couple either groom’s or bride’s email id. Wedding_id,Wedding_date

E-mail (one email id which is unique)

Wedding -3 (Wedding_id, Wedding_date, Groom, Bride ) Transitive dependency found; * Address has a unique city name, Postcode, Telephone number. * A person has one mobile no, and one email Id unique (in this case) Address

City

Postcode ( are unique)

Address

City

Tel_no( are unique)

Address

City

Mobile_no ( are unique)

Address

City

E-mail ( are unique)

City

Address

Postcode ( are unique)

City

Address

Tel_no ( are unique)

City

Address

Mobile_no ( are unique)

City

Address

E-mail ( are unique)

Postcode

Address

City ( They are unique to each other)

Postcode

Address

Tel_no ( unique)

Postcode

Address

Mobile_no

Postcode

Address

E-mail (unique)

(unique)

Hence we separate the transitive dependency to a separate table called Contact. * Wedding couple can have only one contact details. * Having Contact_id enable to specify a wedding contact details uniquely. Contact- 3 (Contact_id, Wedding_id* Address, City, Postcode, Tel_no, Mobile_no, E-mail)

/var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

Wedd_Gift- 3 (Wedding_id*, Gift_id*, Gift_purchased) Gift- 3 (Gift_id, Gift_price, Gift_des ) Gift_price Gift_des

Gift_des ( each Gift will have a price and a description ) Gift_price(Each description will have a price)

Wedding -3 (Wedding_id, Wedding_date, Contact_id*, Groom, Bride ) Contact- 3 (Contact_id, Wedding_id* Address, City, Postcode, Tel_no, Mobile_no, E-mail) Wedd_Gift- 3 (Wedding_id*, Gift_id*, Gift_purchased) Gift- 3 (Gift_id, Gift_price, Gift_des )

TABLES Wedding /var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

Wedding_id Wedding_date Contact_id* Groom

Bride

W01

24th/08/2007

C01

Smith John

Jones Ann

W02

25th/08/2007

C04

Bain Charles

Anna Frank

W03

29th/10/2007

C03

Charles Lara

Margate Moore

W04

25th/12/2007

C05

Benjamin Frank

Anna Frank

W05

26/02/2007

C08

Gautam Vivek

Swarna Shakti

W07

07/07/2007

C06

Rohit Malhotra

Mala Raj

W08

13/12/2007

C08

Marcus Trescothick

Gwends Abby

Contact Contact_id Wedding_id*

Address

City

Postcode Tel_no

Mobile_no

E-mail

C01

W01

63 Downing Street

London

SW7J70

020893 1 6037

0777174 5689

electro@yahoo .com

C02

W08

52 Bridge Rd

London

SW11 K1

020845 7 3278

0794567 9874

sam@hotmail .com

C03

W03

13 Riverside Rd

Manchester M4 K52

0112548 0793147 6798 9801

sandy@yahoo .com

C04

W02

59 Holloway Rd

London

N7 8DB

020845 7 2000

0798455 3214

way@google .com

C05

W04

84 Moorgate Rd

London

EC2M 6SQ

020857 9 3897

0799984 0149

[email protected] .uk

C06

W07

100 Minories rd

London

EC3N 1JY

020854 7 9004

0795485 9004

marsh@lan. com

C07

W06

133 Whitechapel High Street

London

E1 7QA

020854 7 3694

0798765 4321

earth@ universe .com

C08

W05

41 Commercial Road

London

E1 1LA

020857 4 3784

0788354 3974

data@system .com

/var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

Wedd_Gift Wedding_id* Gift_id*

Gift_purchased

W01

K178

1

W02

K144

2

W08

K420

2

W07

C117

8

W01

C242

8

W02

C200

4

W02

H100

1

W06

H101

2

W04

H102

1

W03

H102

1

W05

K178

1

W06

C242

12

W02

H101

1

W02

H103

1

Gift Gift_id

Gift_price Gift_des

K178

£30

Kitchen Scales

K144

£40

Kettel

K420

£10

Water Jug

C117

£40

Plate 12

C242

£55

Plate

C200

£70

Tea Cup

H100

£150

DVD Player

H101

£300

28 inch Plasma Flat TV

/var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc

(H.D) H102

£1000

LG Mobile phone LG KE850

H103

£1300

apple iphone

/var/www/apps/pdfcoke/pdfcoke/tmp/scratch3/10618342.doc