While coming with the entity relationship diagram the following assumptions were made;
- One credit card can be used to make more than one payment. For example if a client has an order and instead of paying using one payment the client pays using two payments but uses the same credit card to make the payments.
- A cheque can only be used to make one payment. One the cheque has been used the cheque number cannot be used to make another payment even if it’s for the same order.
- Supplier relation
Supplier (supplierID, companyName, contactName, title, street, city, state, postcode, phone, webAddress, email, comment)
The relation is in3NF because;
- There are no repeating groups in the relation thus its already in 1NF.
- The relation does not contain partial dependencies. All attributes depend on the supplierID.
- The relation is in 3NF because all attributes are determined by by the primary key which is supplierID
- PurchaseOrder relation
PurchaseOrder (poNumber,orderDate,salesRep,instructions, copyNotes, clientID)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the PoNumber which is the primary key.
- All attributes are determined by the primary key PoNumber.
- Client relation
Client (clientID, companyName, webAddress, businessPhone, taxNO, streetName, city, state, postcode, advertiserID)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the clientID which is the primary key.
- All attributes are determined by the primary key clientID.
- advertisingAgent relation advertisingAgent (advertiserID, businessName, contactName, street, city, state, postcode, mobile, business, fax, other, commission)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the advertiserID which is the primary key.
- All attributes are determined by the primary key advertiserID.
- ClientDesignAgency relation
ClientDesignAgency (clientID, companyName, contactName, phone, email)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the clientID which is the primary key.
- All attributes are determined by the primary key PoNumber.
- Client relation
clientInvoiceAddress (clientID, street, city, state, postcode)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the clientID which is the primary key.
- All attributes are determined by the primary key clientID.
- clientEditorialAddress relation
clientEditorialAddress (clientID, street, city, state, postcode)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the clientID which is the primary key.
- All attributes are determined by the primary key clientID .
- Order relation
Order (orderID, poNumber, invoiceDate, issueID, costPrice, pageSize, shape, colour, position, productionNotes)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the orderID which is the primary key.
- All attributes are determined by the primary key orderID.
- Payment relation
Payment (paymentID, date, paymentMethod, amount, orderID)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the paymentID which is the primary key.
- All attributes are determined by the primary key paymentID.
- magIssue relation
magIssue (issueID, publicationName, issueYear, issueMonth)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the issueID which is the primary key.
- All attributes are determined by the primary key issueID.
- Cheque relation
Cheque (chequeNO, paymentID)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the chequeNO which is the primary key.
- All attributes are determined by the primary key chequeNO.
- CreditCard relation creditCard (cardNO, paymentID, creditCardName, expiryMonth/Year)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the combination of cardNO and paymentID which is the primary key.
- All attributes are determined by the primary key which is a combination of the cardNO and paymentID.
- Staff relation
Staff (staffID, type,address,phone, TFN, supervisorID, department)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the staffID which is the primary key.
- All attributes are determined by the primary key staffID.
- StaffEmail relation
StaffEmail (email, staffID)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the email which is the primary key.
- All attributes are determined by the primary key email.
- Users relation
Users (staffID, firstName, lastName, loginName, password, email, userlevel)
The relation is 3NF because;
- It has no repeating groups
- All attributes depend on the staffID which is the primary key.
- All attributes are determined by the primary key staffID.
|
Table Name |
Field |
Type |
Description |
|
Supplier |
SupplierID |
INTEGER |
Primary key |
|
companyName |
VARCHAR(50) |
||
|
contactName |
VARCHAR(50) |
||
|
title |
VARCHAR(25) |
||
|
street |
VARCHAR(25) |
||
|
city |
VARCHAR(50) |
||
|
state |
VARCHAR(50) |
||
|
postcode |
INTEGER |
||
|
phone |
VARCHAR(25) |
||
|
webAddress |
VARCHAR(100) |
||
|
|
VARCHAR(100) |
||
|
comment |
VARCHAR(500) |
||
|
AdvertisingAgent |
advertiserID |
INTEGER |
Primary key |
|
businessName |
VARCHAR(50) |
||
|
contactName |
VARCHAR(50) |
||
|
Street |
VARCHAR(50) |
||
|
City |
VARCHAR(50) |
||
|
State |
VARCHAR(50) |
||
|
Postcode |
INTEGER |
||
|
Mobile |
VARCHAR(25) |
||
|
Business |
VARCHAR(25) |
||
|
Fax |
VARCHAR(25) |
||
|
Other |
VARCHAR(25) |
||
|
commission |
DECIMAL |
||
|
Client |
clientID |
INTEGER |
Primary key |
|
companyName |
VARCHAR(50) |
||
|
webAddress |
VARCHAR(100) |
||
|
businessPhone |
VARCHAR(25) |
||
|
faxNO |
VARCHAR(25) |
||
|
streetName |
VARCHAR(50) |
||
|
City |
VARCHAR(50) |
||
|
State |
VARCHAR(50) |
||
|
Postcode |
INTEGER |
||
|
advertiserID |
INTEGER |
Foreign key references advertisingAgent.advertiserID |
|
|
PurchaseOrder |
poNumber |
INTEGER |
Primary key |
|
orderDate |
DATE |
||
|
saleRep |
CHAR (5) |
||
|
Instructions |
VARCHAR(500) |
||
|
copyNotes |
VARCHAR(500) |
||
|
clientID |
INTEGER |
Foreign key references client.clientID |
|
|
ClientDesignAgency |
clientID |
INTEGER |
PRIMARY KEY;FOREIGN KEY REFERENCES Clien.clientID |
|
companyName |
VARCHAR(50) |
||
|
contactName |
VARCHAR(50) |
||
|
Phone |
VARCHAR(50) |
||
|
|
VARCHAR(50) |
||
|
ClientInvoiceAddress |
clientID |
INTEGER |
PRIMARY KEY;FOREIGN KEY REFERENCES Clien.clientID |
|
Street |
VARCHAR(50) |
||
|
City |
VARCHAR(50) |
||
|
State |
VARCHAR(50) |
||
|
Postcode |
VARCHAR(50) |
||
|
ClientEditorialAddress |
clientID |
VARCHAR(50) |
PRIMARY KEY;FOREIGN KEY REFERENCES Clien.clientID |
|
Street |
VARCHAR(50) |
||
|
City |
VARCHAR(50) |
||
|
State |
VARCHAR(50) |
||
|
postcode |
INTEGER |
||
|
Order |
OrderID |
INTEGER |
Primary key |
|
PoNumber |
INTEGER |
FOREIGN KEY references purchaseOrder.poNumber |
|
|
invoiceDate |
DATE |
||
|
issueID |
INTEGER |
FOREIGN KEY: references magIssue.issueID |
|
|
costPrice |
NUMERIC |
||
|
pageSize |
INTEGER |
||
|
Shape |
VARCHAR(50) |
||
|
Colour |
VARCHAR(50) |
||
|
Position |
VARCHAR(50) |
||
|
productionNotes |
VARCHAR(50) |
||
|
Payments |
paymentID |
INTEGER |
Primary key |
|
Date |
DATE |
||
|
paymentMethod |
VARCHAR(50) |
||
|
Amount |
DECIMAL |
||
|
orderID |
INTEGER |
Foreign key references order.orderID |
|
|
staff |
staffID |
INTEGER |
PRIMARY KEY |
|
Type |
VARCHAR(25) |
||
|
Address |
VARCHAR(50) |
||
|
Phone |
VARCHAR(25) |
||
|
TFN |
VARCHAR(25) |
||
|
supervisorID |
INTEGER |
FOREIGN KEY references staff.staffID |
|
|
Salary |
DECIMAL |
||
|
Department |
VARCHAR(50) |
||
|
staffEmail |
|
VARCHAR(50) |
PRIMARY KEY |
|
staffID |
INTEGER |
FOREIGN KEY references staff.staffID |
|
|
users |
staffID |
INTEGER |
PRIMARY KEY; FOREIGN KEY references staff.staffID |
|
firstName |
VARCHAR(50) |
||
|
lastName |
VARCHAR(50) |
||
|
loginName |
VARCHAR(50) |
||
|
Password |
VARCHAR(250) |
||
|
|
VARCHAR(100) |
||
|
userLevel |
VARCHAR(25) |
||
|
cheque |
chequeNO |
INTEGER |
PRIMARY KEY |
|
paymentID |
INTEGER |
FOREIGN KEY references payments.paymentID |
|
|
CreditCard |
cardNO |
PRIMRY KEY PRIMARY KEY ; FOREIGN KEY references payment.paymentID |
|
|
paymentID |
|||
|
CreditCardName |
VARCHAR(50) |
||
|
expiryMonthYear |
VARCHAR(10) |
Designing the express media database was a very technical task for me but a good learning point as I got to design a database from just a case study to a relational schema which can be implemented into a database using MySQL. My main challenge was deriving the entities for the database from the case study. This was a challenge because the case study is not described in fine details thus all the information needed to design the database is not clear. However, I was allowed to make assumptions so I made the assumptions and proceeded with designing the database.
My database has a good overall design because all entities have been normalized to 3NF thus the relationships existing between relations are mapped correctly. If the database was to be implemented and put in use, the database would serve express media very well because it would ensure data consistency and integrity.
References
Guru99. (2018). What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples. [online] Available at: https://www.guru99.com/database-normalization.html [Accessed 26 Apr. 2018].
Hingorani (2017). reinforcing database concepts by using entity relationships diagrams. [online] ebscohost. Available at: https://web.b.ebscohost.com/abstract?direct=true&profile=ehost&scope=site&authtype=crawler&jrnl=15297314&AN=125260222&h=Igf%2fKVM34Y7EBoiarV74WdUA5bvhkR7PHoBbxJ8BSC2flYMZH6v5TKxRKHtiUN1zhYsbxV3vAxfQh5nUsmcswA%3d%3d&crl=c&resultNs=AdminWebAuth&resultLocal=ErrCrlNotAuth&crlhashurl=login.aspx%3fdirect%3dtrue%26profile%3dehost%26scope%3dsite%26authtype%3dcrawler%26jrnl%3d15297314%26AN%3d125260222 [Accessed 27 Apr. 2018].
Kaula, R. (2007). Normalizing with Entity Relationship Diagramming. [online] The Data Administration Newsletter. Available at: https://tdan.com/normalizing-with-entity-relationship-diagramming/4583 [Accessed 27 Apr. 2018].
Millamila, M. (2014). Entity Relationship Diagrams and Normalization. [online] prezi. Available at: https://prezi.com/tunrndt0nin4/entity-relationship-diagrams-and-normalization/ [Accessed 27 Apr. 2018].
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
