Database Modelling

profileelizabethP
Advertisers_Data1.xlsx
Home>Information Systems homework help>Database Modelling

Partial ERD

EM Advertisers

Advertisers

Agencies

Orders

Order Details

Copy Status

Remarks

Issues

Payments

Payment Methods

Users

Page Size

Advertisers

TABLE DEFINITION: Advertisers
Attr name Advertiser ID Company name First Name Last Name Title Address City State Postal Code Country Business Phone Fax Number MobileNumber Email Website Agency Id
Key? PK FK(Agencies)
Required? Y Y N Y N N N N Y N N N N N N N
Rules Integer Text, 1-35 chars Text, 1-25 chars Text, 1-25 chars Text Text, 1-50 chars Text, 1-25 chars Text, 1-15 chars Text, 4 nos Text, 1-35 chars Text Text Text Text Text, 1-50 chars Integer
Indexed? Y Y Y
Advertiser ID Company name First Name Last Name Title Address City State Postal Code Country Business Phone Fax Number MobileNumber Email Website Agency Id
1 3M Tapes & Adhesive Division Glenn Mortimer 3M Industrial & Transportation Business 950 Pacific Highway Pymble NSW 2073 (02) 9498 9225 (02) 9498 9247 0402 222 123 [email protected] 1
2 Freightcorp Glenn Stephens 50 Rocklands Road CROWS NEST NSW 2065
3 Industrial Packaging Systems Pty Ltd Warren Jennings Unit 1/ 10 Southey St Williamstown VIC 3004 (03) 9399 8955 (03) 9399 8655 www.industrialpackaging.com.au 3
4 Siemens Ltd - Industrial Automation & Control Garry Russell 885 Mountain Highway Bayswater Vic 3153 (03) 9721 7230 0419 341 900 www.siemens.com.au
5 Visy Industrial Packaging Elizabeth Wilson 6 Koornag Rd Scoresby Vic 3179 (03) 9765 2690 (03) 9763 4914 [email protected] ww.visy.com.au 4
6 AMTAS Pty Ltd Allan Russell Mr 56 Dooley St NAVAL BASE WA 6165 (08) 9410 2300 [email protected] www.amtas.com.au 3
7 ActionLaser Pty Ltd Stephen Morris Mr [email protected] www.actionlaser.com.au

Agencies

TABLE DEFINITION: Agencies
Attr name Agency Id Agency Name Prefix First Name Last Name Address Address 2 State Code Work Phone Email Address Agency Percentage
Key? PK
Required? Y Y N Y Y N N N N N N Y
Rules Integer Text Text Text, 1-25 chars Text, 1-25 chars Text, 1-35 chars Text, 1-25 chars Text, 3 chars Text, 4 chars Text, 1-25 chars Text, 1-50 chars Decimal
Indexed? Y
Agency Id Agency Name Prefix First Name Last Name Address Address 2 State Code Work Phone Emali Address Agency Percentage
1 Adcorp Marketing Communications Mr Annette Skinner Level 16, 77 Eagle St Brisbane QLD 4000 (07) 3229 6111 [email protected] 0.1
2 Tradewinds Advertising Mr Myles Connell Level 1, 240 Chapel St PRAHRAN VIC 3181 (03) 9529 4744 0.1
3 Media Communications Mrs Rhonda Dunlop Level 1, 195 Little Collins Street Melbourne Vic 3806 (03) 9769-4516 [email protected] 0.1
4 Breakthrough Creative Mr Andy Jones 96 Hay Street Subiaco WA 6008 [email protected] 0.1

PageSize

TABLE DEFINITION: PageSize
Attr name PageSize PageValue
Key? PK
Required? Y Y
Rules Integer Decimal
Indexed? Y (unique)
PageSize PageValue
Full page 1
Half page 0.5
Quarter page 0.25
Third page 0.33
Two thirds page 0.66
Insert 0
Other 0
Enhanced 0
Front Cover 1

Copy Status

TABLE DEFINITION: Copy Status
Attr name CopyStatus Id CopyStatus JobNoReq
Key? PK
Required? Y Y
Rules Integer Text, up to 25 char Bit/Boolean
Indexed? y (unique) 1=True, 0=False
CopyStatus Id CopyStatus JobNoReq
1 Repeat 1
2 Repeat & changes 1
3 New complete on disk 1
4 New complete via email 1
5 New complete + changes 1
6 New to make up 1
7 TBA 1
8 New to come 0

Notes

TABLE DEFINITION: Notes
Attr name RemarkID Advertiser Id Entry date Initials Remarks FollowupDate Complete
Key? PK FK(Advertisers)
Required? Y Y Y Y Y N Y
Rules Integer Integer Date / Time Integer Text, up to 500 char Date / Time Bit / Boolean
Indexed? Y Y 1=True, 0=False
RemarkID Advertiser Id Entry date User ID Remarks FollowupDate Complete
1 1 28-Apr-16 1 still no answer on advertising. Will discuss at mgmt 3.12.14 Gave me a lead for Keeble Australia---can't locate. 2.11.14 PS says don’t call us we will call you.. Will email my details. Possible meeting next Friday. PD Note: Used to be regular b 1
2 1 28-Apr-16 1 Paul Schulz not optimistic for advertising. Has new system up for patent approval in new fin year.fu late july. 1
3 1 24-Nov-16 2 Update contact details - company has moved. Paul's mobile is 0400-019 111 1
4 1 24-Nov-16 2 Paul Schultz decline el for 06 EG - company direction is changing, not worthwhile to spend money until this is clarified. 1
5 1 30-Aug-17 3 Paul Schultz now works for Birrus International. New phone no is 03 9769 2646, Left a message to ring back cl or pd re advertising. Will F/U next week. 06-Sep-17 0
6 2 04-Dec-14 6/10/14 Called and discussed displacing a mining magazine with REVIEW - listened. Will review advertising in Feb. F/U late jan. 1
7 2 05-Dec-14 1 Hi Peter, We have received the letter from Brian Mc re advertising in the upcoming February Review issue. We are currently in discussions with John Warman ( as we are their Vic, Tas , SA agents for Kason ) and hope to have some indication of our call 1
8 2 16-Dec-14 1 will C/B. busy 1
9 2 18-Dec-14 1 No Go. Terse response to my F/U. try again in New Year. 1
10 2 21-Jan-15 1 Wants a prop now. Plans in March for June. 14/15 budget. 1
11 2 23-Jan-15 1 Hi Peter, As we are fully committed for our advertising budget this year we are unable to take up your offer - we will be reviewing budgets again in April/May. Best regards, Jan Brown 1
12 2 05-Apr-15 1 Discussed sending a prop for advertising Full page full colour 2-3 times per annum. Sent 05/04/2015 [3rd prop]. 2x FP $2610 3x $2570 Insert $2000. 1
13 2 14-May-15 1 NOT IN cb Monday 18/5 1
14 2 19-May-15 1 L/M. fu on prop. 1
16 2 31-Aug-15 1 going away for 2 weeks. Not able to decide now. 1
17 2 24-Jan-16 From Renee, supplied pr on expansion. Passed to ed. - We are hoping to do some advertising in June this year for Bisalloy's 25 year anniversary but nothing definite has been decided upon as yet. F/U Michael Sampson 03-Jan-16 0
18 2 07-Dec-16 2 Emailed proposal and survey to Mike Sampson - not in 8 Dec, call 9 Dec - Mike will review this pm and advise yes or no. Decline email received 09-Dec-15 1
30 3 15-Sep-15 1 L/M . Renewal? Oct/Nov? Subsequently advised no more till Feb/Mar 06 at least. ABHR still gens leads. Check for Review 12-Oct-15 1
31 3 14-Jun-16 3 Peter Turner will return from holidays next week. Phone for renewal then. 21-Jun-16 1
32 3 20-Jun-16 1 Peter Turner emailed booking for 1/2 page full colour Aug/Sept Oct/Nov 16 at best agency rate. Charged 6x $1870. 23-Jun-16 1
33 3 28-Jun-16 1 Obliged to revise order to $1780 for Aug/Sept Oct/Nov. Wants advertisements to be more industry oriented. Client may go to FP F/U for Oct/Nov and 2016. 27-Sep-16 1
34 3 08-Nov-16 2 Peter Turner says client is still thinking about budgets and to call back in a couple of weeks. Will do. 22-Nov-16 1
35 3 15-Nov-16 1 Peter Turner wants early right hand placement - page 5 or 7 if poss. In Innovative Products columns or matetials handling editorial if poss. Sent listing forms for Review. Planning now for 2017 1st half. 11-Dec-16 1
36 3 23-May-17 3 Peter says ring back in a month after budgets have been finalised. 13-Jun-17 1
37 3 13-Jun-17 3 Peter waiting on client coming back from NZ at end of this week. F/U next week. 20-Jun-17 1
38 3 20-Jun-17 3 Peter has reserved a half-page on behalf of client in July/August edition. 19-Sep-17 1
39 3 08-Feb-17 1 meeting Monday 11th Feb 1430 or 1630. 11-Feb-17 1
40 3 21-May-17 3 Peter rang requesting rates for planning next campaign for client. Have sent them through but he is not in a position to book for a couple of months. F/U then. 02-Jul-17 1
41 3 23-May-17 1 Peter Turner emailed booking for 2x 1/2 page July/Aug and Sept/Oct.17 . Booked @ $1960 as advised 27/11/17 26-May-17 1
42 3 28-Aug-17 1 Peter Turner called to complain abour our invoicing which I then endeavoured to resolve. He is sending his payment slip for Nov/Dec 17 to me as evidence of payment. 04-Sep-17 1
43 3 04-Sep-17 1 Called and emailed PT to requerst record of payment for Nov/Dec 17. f/u re accounts and 2017 bookings. 04-Sep-17 1
44 3 04-Sep-17 1 PT called back - paid Nov/Dec 17 with AMEX Service refence # 0003721 $1751.30. Passed info to Chris P, Peter L, Jesse Liu. Need to confirm receipt of payment with PT today. 05-Sep-17 1
45 3 30-Oct-17 1 Peter says has cut all Nov/Dec advertising. Has plan for New Year - not yet decided. Will send copies of payments for Nov/Dec 17 n07-5017 $1751.30. 06-Nov-17 0
46 3 30-Oct-17 1 Peter admitted that the Nov/Dec 17 payment had been overlooked at his office. Has authorised payment today! Peter Lagios advised! 1
47 3 24-Nov-17 1 Peter Turner booked 1/2 page in Mar/Apr 2017 edition booked at 2016 6x rate of $2038 - should be 2017 casual rate of $2283 - saving is $245. Agency to provide new material or repeat material instructions. 15-Dec-17 0
53 5 07-Feb-17 1 Hi Peter, Please use existing Artwork from June/July 2014 issue: Also Attached: An on screen pdf version for viewing only. Media Advice Ref: # 4717 1
54 5 24-Mar-17 1 emailed sharon an offer to transfer April/May cover to June/July so allowing time for in situ shot. 29-Mar-17 1
55 5 07-Apr-17 1 Sharon shot pics for cover this am. Will advise tomorrow. 08-Apr-17 1
56 5 05-May-17 1 Malcolm Lewis not happy with the cover lines sent to us by Brent White for April/May 15 edition. Received 10 free copies. Hasn seen Review sent him 2 copies with the offer of more. 09-May-17 1
57 5 27-Oct-17 1 Sb expects to book same as 2016 for 2017. Sent Review for approval. Rest will follow. 09-Nov-17 1
58 5 29-Nov-17 1 Quoted 5x 2290, 1x 2800, 1x360. Had to drop Review to $2290 Shazza will present to client 30/11/17. 01-Dec-17 1
59 5 07-Dec-17 1 recd material for Review 1
60 4 16-Feb-16 1 received advertising material for Feb/Mar 06.1/6/16 received advertising material for June/July 16. 1
61 4 01-Sep-16 1 received advertising material for Oct/Nov 16. 1
62 4 03-Nov-16 1 meeting Sharron B for lunch re 2017 plan. 03-Nov-16 1
63 4 04-May-17 1 OFC for March/April 17 arrived by email. No cover description - f/u with Brent Whyte 1
64 4 30-May-17 1 Please find attached Artwork : 5019 BH HDO 07_07_HR.pdf Media Advice Ref: Ref # 5019 Product Featured: HDO Publication: Review Size:Full Page Early Right hand page PLEASE for July/August 2017 1
65 4 16-Oct-17 1 renewal due. L/M Nov/Dec in next schedule? 17-Oct-17 1
66 4 24-Oct-17 3 Sharon doesn't want to go in the Nov/Dec edition. She said she will be in contact when she's ready to book for next year. 05-Dec-17 1
67 4 26-Oct-17 1 scheduled same bookings as 2017 for 2017 +5% $2525, $3100 cover, $380 enhanced listing. 29-Oct-17 1
68 4 05-Feb-17 1 Is Malcolm still keen to sponsor the 2018 Awards? Do you want a quote? 12-Feb-17 1
69 4 31-Mar-17 1 SB requested 6x extra copies of Review. Also advised change of address for Sydney. Has interest in Exibition in 2018 could plan for 2017 budget.Meeting 11/4/17 11-Apr-17 1
71 4 20-Nov-17 1 SB booked full page $2651 [should be $3079] plus enhanced listing $399 [should be $460] for Review 2017. Also booked Jan/Feb Mar/Apr May/June July/Aug Sept/Oct 2017 @ $2651 [should be $3080] OFC $3255 [should be $3735] Total saving $2362 21-Nov-17 0

Suppliers

TABLE DEFINITION: Suppliers
Attr name SupplierID Company Name Contact Name Address City Region Postal Code Phone Fax Website
Key? PK
Required? Y Y N N N N N N N N
Rules Integer Text Text Text Text Text Text Text Text Text
Indexed? Y (unique)
SupplierID Company Name Contact Name Address City Region Postal Code Phone Fax Website
1 Kwik Kopy Anyone 28-30 Market Street Sydney NSW 2000 9290 2566 9299 8143 www.kwikkopy.com.au
2 Crisis Couriers Anyone 02 9964 0000 www.crisis.com.au
3 D & D Mailing Services Glenn Reidy 21 Ormby Place Wetherill Park NSW 2164
4 Couriers to You Sam 1 Bridge Street Hornsby NSW 2077

Staff

TABLE DEFINITION: Staff
Attr name Staff ID First Name Last Name Title Work Phone EmploymentType
Key? PK
Required? Y Y Y N N Y
Rules Integer Text, 1-25 char Text, 1-25 char Text Text Text
Indexed? Y
Staff ID First Name Last Name Title Work Phone EmploymentType
1 Peter Lagios Mr FT
2 Charles Macdonald FT
3 Cindy Lynch FT
4 Jessie Liu FT
5 Sandy Perazza Ms PT
6 Andy Jones C
7 Bob Parker FT
8 Tina Parker PT
9 Tom Fenton PT
10 Brad White PT

Users

TABLE DEFINITION: Users
Attr name User_ID StaffId Initials FName Surname LoginName Password
Key? PK FK(Staff)
Required? Y Y N Y Y Y Y
Rules Integer Text, 2 char Text Text Text Text
Indexed? Y Y Y (unique)
User_ID StaffId Initials FName Surname LoginName Password
1 1 CM Peter Lagios PL PL
2 2 CM Charles Macdonald CM CM
3 3 CL Cindy Lynch CL CL
4 5 JL Jessie Liu JL JL
5 6 SP Sandy Perazza SP SP

Orders

TABLE DEFINITION: Orders
Attr name Order ID Client ID PO Number Order Date AgencyPercentage CopyDue SpecialInstruction GST_Exempt CopyNotes SalesRep
Key? PK FK(Advertisers)
Required? Y Y N N N N N Y N Y
Rules Integer Integer Text Date Decimal Text Text Bit / Boolean Text Integer
Indexed? Y Y 1=True, 0=False
Order ID Client ID PO Number Order Date AgencyPercentage CopyDue SpecialInstruction GST_Exempt CopyNotes User ID
1 1 01-Jul-17 July 3 This is a booking for 2 x 1/4 page course advertisements 0 Danielle Harris - 02 4921 6067 - [email protected]. 1
2 3 02-Jul-17 Repeat Tim has agreed to repeat his advert in the July/August edition at the discounted and protected rate of $800 (plus gst). 0 Tim Roberts - [email protected] - 03 5521 7699 3
3 3 08-Jul-17 0 2
4 1 09-Jul-17 Dec 15 This is a free enhanced listing in the 2017 edition of Services Guide booked as a make good for miissing picture in Full page ad in Winners booklet. 0 Lee-Anne Ryan ph 03 9237 0752, fax 03 9237 0710 - [email protected] 1
5 5 09-Jul-17 Repeat Melissa has agreed to repeat her quarter-page advert in the Sept/Oct edition at the protected and discounted rate of $1480 (plus gst). 0 [email protected] melissa barrett 03 9720 4235 3
6 4 09-Jul-17 We hold This is a booking for 1/4 page advertisements for a short course in Perth 0 Peter Wypych - [email protected] - ph.02 4221 3488 - fax 02 4221 4577 1
7 4 10-Jul-17 Sept 12, Nov 17, Dec 3, Jan 14, Mar 10, May 12, July 3 This is a schedule of 6 x 1/4 page full colour advertisements at two colour rate $1005 instead of $1591 each. Plus 1 x 1/4 page plus 1 x free enhanced listing in Services Guide. Total saving - $4552 0 Tim Mellor 03 5977 1181 [email protected] 1
8 6 14-Jul-17 0.1 Sept 12 This is a booking for a full page full colour in Sept/Oct edition of Review 0 Nicola - Universal / McCann - 02 -9994 4333 1
9 2 FC170716_7 16-Jul-17 Repeat and September 11 Toby has booked two half-page adverts in the Powder Handling feature at the discounted and protected rate of $2038ea (plus gst). 0 Anton Hearn - [email protected] - 1300 731 998 - 0418 254 229 Toby Burns 02 4735 7112 3
10 2 FC170717_3 17-Jul-17 Sept 11 Marilyn has booked a half-page advert at the discounted and protected rate of $2038 (plus gst) to be published in the Powder Handling feature in Sept/Oct 2008. 0 Marilyn Davy [email protected] 03 9305 4099 4
11 6 28-Jul-17 Sept 12 This is a booking for a 1/2 page full colour ad @ $2174 +gst for Traction Tape 5401 0 Ann Mortimer - 02 9498 9225 - [email protected] 1
12 5 29-Jul-17 Nov 17 this is an ad booking for Vic Transport Infrastructure conference - Media value $2933 0 Chrystelle Duncan - [email protected] - 02 9080 4319 1
13 3 30-Jul-17 September 11 Tim has booked three half-page adverts, with first to run in the Powder Handling feature in the Sept/Oct 'Spring' edition, followed by Nov/Dec and January Equipment Guide 2009 editions at the discounted and protected rate of $1500 (plus gst). 0 [email protected] 02 63413369 042 7413369 3
14 6 31-Jul-17 Sept 12 This is a booking for a 1/2 page full colour ad in Sept/Oct edition of Australian Review. Production charge approx $252 0 Gene Zemaitis - [email protected] - 02 9411 3722 1
15 4 06-Aug-17 Sept 12 This is a booking for 1/4 page course advertisements to appear in Sept/Oct and Nov/Dec editions of Review. Production charge $150. 0 Peter Wypych - [email protected] - ph.02 4221 3488 - fax 02 4221 4577 1
16 5 17-Jun-17 July 3, Sept 12, Nov 17, Dec 3 This is a booking for 1x 1/2 page ad. and 3x 1/4 page advertisements at the 3x discount rate. 1 Bruce Warner - [email protected] - Phone +64 9 820 7720 - Fax +64 9 820 7721 1

OrderDetails

TABLE DEFINITION: OrderDetails
Attr name Order Detail ID Order ID Issue Id Unit Price Production ProductionDetails GST Year Size Shape Special Colour PageSize CopyDue CopytoUse StatusofCopy Position Cancelled
Key? PK FK(Orders) FK(Issues)
Required? y y Y Y N Y Y Y Y Y N N Y N N N N Y
Rules Integer Integer Integer Integer Decimal(6,2) Text Decimal(5,2) Integer Text Text Text Text Decimal Text Text Text Text Bit / Boolean
Indexed? Y Y 1=True, 0=False
Order Detail ID Order ID Issue Id Unit Price Production ProductionDetails GST Year Size Shape Special Colour PageSize CopyDue CopytoUse StatusofCopy Position Cancelled
1 1 5 $ 662 $ 95 Production Cost $ 75.70 2017 Quarter page Normal Full Colour 0.25 New to make up Pag 90 0
2 1 5 $ 662 $ 95 Production Cost $ 75.70 2017 Quarter page Normal Full Colour 0.25 New to make up Pag 107 0
3 2 6 $ 800 $ 80.00 2017 Quarter page Normal Full Colour 0.25 Repeat Pag 12 0
4 4 8 $ - 0 $ - 0 2017 Enhanced Full Colour 0 Repeat & changes 0
5 5 6 $ 1,480 $ 148.00 2017 Quarter page Normal Full Colour 0.25 Repeat Pag 113 0
6 6 5 $ 670 $ 150 Production Cost $ 82.00 2017 Quarter page Normal Full Colour 0.25 New complete via email pag 50 0
7 6 6 $ 670 $ 67.00 2017 Quarter page Normal Full Colour 0.25 Repeat 1
8 7 6 $ 1,005 $ 95 Production Cost $ 110.00 2017 Quarter page Normal Full Colour 0.25 New to make up Pag 92 0
9 7 7 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 Repeat 0
10 7 8 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 Other 0
11 7 8 $ - 0 $ - 0 2017 Enhanced Normal Full Colour 0 Repeat 0
12 7 2 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 0
13 7 3 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 0
14 7 4 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 0
15 7 5 $ 1,005 $ 100.50 2017 Quarter page Normal Full Colour 0.25 0
16 8 6 $ 3,141 $ 314.10 2017 Full page Normal Full Colour 1 Pag 21 0
17 9 6 $ 2,038 $ 270 Production Cost $ 230.80 2017 Half page Normal Full Colour 0.5 New complete via email pag 51 0
18 9 6 $ 2,038 $ 203.80 2017 Half page Normal Full Colour 0.5 New to make up Pag 61 0
19 10 6 $ 2,038 $ 203.80 2017 Half page Normal Full Colour 0.5 New to come Pag 58 0
20 11 6 $ 2,174 $ 217.40 2017 Half page Horizontal Full Colour 0.5 New to come Pag 82 0
21 12 6 $ - 0 $ - 0 2017 Full page Normal 1 New to come up the back out of the way 0
22 13 6 $ 1,500 $ 295 Production cost $ 179.50 2017 Half page Normal Full Colour 0.5 New to come Pag 57 0
23 14 6 $ 2,122 $ 252 Production Cost $ 237.40 2017 Half page Horizontal Full Colour 0.5 New to make up Pag 66 0
24 15 6 $ 670 $ 150 Production cost $ 82.00 2017 Quarter page Vertical Full Colour 0.25 New to make up Pag 93 0
25 15 7 $ 670 $ 67.00 2017 Quarter page Vertical Full Colour 0.25 Repeat erhp 0
26 16 5 $ 2,122 $ 212.20 2017 Half page Horizontal Full Colour 0.5 New complete via email Pag 73 0
27 16 6 $ 1,643 $ 164.30 2017 Quarter page Normal Full Colour 0.25 New to come Pag 103 0
28 16 7 $ 1,643 $ 164.30 2017 Quarter page Normal Full Colour 0.25 Repeat 0
29 16 2 $ 1,643 $ 164.30 2017 Quarter page Normal Full Colour 0.25 Other 0

Issues

TABLE DEFINITION: Issues
Attr name IssueID Issue Code Extension Issue Year
Key? PK
Required? Y Y Y Y Y
Rules Integer Text Text Text Integer
Indexed? Y (unique)
IssueID Issue Code Extension Issue Year
1 CatX 0172 JF10 Catalogue 2017
2 Review Monthly 0180 JF11 Jan/Feb 2017
3 Review Monthly 0181 MA11 Mar/Apr 2017
4 Review Monthly 0182 MJ11 May/June 2017
5 Review Monthly 0183 JA11 Jul/Aug 2017
6 Review Monthly 0184 SO11 Sept/Oct 2017
7 Review Monthly 0185 ND11 Nov/Dec 2017
8 Review Monthly 0186 JF12 Jan/Feb 2018

Payments

TABLE DEFINITION: Payments
Attr name Payment ID Order ID OrderDetailId Payment Amount Payment Date Credit Card # Cardholder Name Card Exp. Date CC Authoriz. # Payment Method ID ChequeNo
Key? PK FK(Orders) FK(OrderDetails) FK(PaymentMethods)
Required? Y Y Y Y Y N N N N Y N
Rules Integer Integer Integer Currency, >1 Date / Time Text Text Text Text Integer Text
Indexed? Y
Payment ID Order ID OrderDetailId Payment Amount Payment Date Credit Card # Cardholder Name Card Exp. Date CC Authoriz. # Payment Method ID ChequeNo
1 1 2 $ 832.70 20-Aug-17 2
2 1 2 $ 832.70 28-Nov-17 INV 5777 4
3 5 5 $ 1,628.00 29-Oct-17 SO08-5780 2
4 7 8 $ 1,210.00 03-Dec-17 INV SO08-5783 243 1
5 8 16 $ 3,109.59 17-Nov-17 INV SO08-5791 2
6 10 19 $ 2,241.80 11-Nov-17 INVSO08-5794 418629 5
7 13 22 $ 1,974.50 27-Oct-17 INV SO08-5797 2042 3
8 14 23 $ 2,611.40 26-Nov-17 INV SO08-5798 2

PaymentMethods

TABLE DEFINITION: PaymentMethods
Attr name PaymentMethod ID PaymentMethod CreditCardYN
Key? PK
Required? Y Y Y
Rules Integer Text default to false
Indexed? Y (unique) Bit / Boolean
1=True, 0=False
PaymentMethodID Payment Method Credit Card?
1 Cheque 0
2 Direct Debit 0
3 Visa 0
4 American Express 1
5 Mastercard 2
6 Diners Club 0
7 Other 1
8 Prize 2