CLIENT(FlldCompanyName, FlldWebsite, FlldPhone, FlldFax, FlldEmail)
CLIENTCONTACTS(FlldContactID, FlldCompanyName, FlldFirstName, FlldLastName, FlldPhone, FlldFax)
CLIENTADDRESS(FlldAddressID, FlldCompanyName, FlldStreet, FlldCity, FlldState, FlldPostcode, FlldAddressType)
MAGAZINEISSUE(FlldMagazineIssueID, FlldPublicationName, FlldIssueMonthYear)
PAYMENTTYPE(FlldPaymentType)
STAFFTYPE(FlldStaffType)
STAFF(FlldStaffID, FlldStaffName, FlldStaffType, FlldAddress, FlldPhone, FlldTFN, FlldSalary)
STAFFEMAIL(FlldStaffID, FlldEmail)
USERS(FlldStaffID, FlldFirstName, FlldLastName, FlldLoginName, FlldPassword, FlldEmail, FlldLevel)
ORDER(FlldOrderID, FlldOrderDate, FlldONumber, FlldStaffID, FlldSpInstruction, FlldCopyNotes, FlldInvoiceDate, FlldCost, FlldPageSize, FlldShape, FlldColour, FlldPosition, FlldProdNotes, FlldMagazineIssueID, FlldCompanyName)
PAYMENT(FlldPaymentID, FlldAmount, FlldPaymentDate, FlldPaymentType, FlldOrderID)
CREDITCARDPAYMENT(FlldPaymentID, FlldCreditCardType, FlldCreditCardNumber, FlldCreditCardName, FlldCreditCardExpiryMonthYear)
CHEQUEPAYMENT(FlldPaymentID, FlldChequeNumber)
ADVERTISINGAGENCY(FlldAgencyBusinessName, FlldContactName, FlldStreet, FlldCity, FlldState, FlldPostcode, FlldMobile, FlldBusinessPhone, FlldFax, FlldOther)
CLIENTAGENCY(FlldCompanyName, FlldAgencyBusinessName, FlldCommission)
SUPPLIER(FlldSuppCompanyName, FlldContactName, FlldTitle, FlldStreet, FlldCity, FlldState, FlldPostcode, FlldPhone, FlldWebsite, FlldEmail, FlldComment)
The database design of Express Media is built by using following assumptions-
- There are so many types of payment modes in Express Media like cash, credit card etc.. Therefore sub entities are created for payment types.
- A client can hire advertising agency. That detail will also be maintained into the database.
- Magazine detail will also be maintained into the database.
- All types of staffs will be categorized according to their type.
|
Table Name |
Field |
Type |
Description |
|
CLIENT |
FlldCompanyName |
varchar(30) |
|
|
FlldWebsite |
varchar(50) |
||
|
FlldPhone |
varchar(20) |
||
|
FlldFax |
varchar(20) |
||
|
FlldEmail |
varchar(30) |
||
|
CLIENTCONTACTS |
FlldContactID |
integer |
primary key |
|
FlldCompanyName |
Foreign key reference to Client.FlldCompanyName |
||
|
FlldFirstName |
varchar(30) |
||
|
FlldLastName |
varchar(30) |
||
|
FlldPhone |
varchar(20) |
||
|
FlldFax |
varchar(20) |
||
|
CLIENTADDRESS |
FlldContactID |
integer |
primary key |
|
FlldCompanyName |
Foreign key reference to Client.FlldCompanyName |
||
|
FlldStreet |
varchar(30) |
||
|
FlldCity |
varchar(20) |
||
|
FlldState |
varchar(20) |
||
|
FlldPostcode |
varchar(5) |
||
|
FlldAddressType |
varchar(20) |
||
|
MAGAZINEISSUE |
FlldMagazineIssueID |
integer |
primary key |
|
FlldPublicationName |
varchar(30) |
||
|
FlldIssueMonthYear |
varchar(20) |
||
|
PAYMENTTYPE |
FlldPaymentType |
varchar(20) |
primary key |
|
STAFFTYPE |
FlldStaffType |
varchar(20) |
primary key |
|
STAFF |
FlldStaffID |
integer |
primary key |
|
FlldStaffName |
varchar(50) |
||
|
FlldStaffType |
varchar(20) |
foreign key reference to StaffType.FlldStaffType |
|
|
FlldAddress |
varchar(30) |
||
|
FlldPhone |
varchar(20) |
||
|
FlldTFN |
varchar(20) |
||
|
FlldSalary |
Double(6,2) |
||
|
STAFFEMAIL |
FlldStaffID |
integer |
primary key; foreign key reference to Staff.FlldStaffID |
|
FlldEmail |
varchar(30) |
primary key |
|
|
USERS |
FlldStaffID |
integer |
primary key; foreign key reference to Staff.FlldStaffID |
|
FlldFirstName |
varchar(20) |
||
|
FlldLastName |
varchar(20) |
||
|
FlldLoginName |
varchar(20) |
||
|
FlldPassword |
varchar(20) |
||
|
FlldEmail |
varchar(30) |
||
|
FlldLevel |
varchar(20) |
||
|
ORDER |
FlldOrderID |
integer |
primary key |
|
FlldOrderDate |
date |
||
|
FlldPONumber |
integer |
||
|
FlldStaffID |
integer |
foreign key reference to Staff. FlldStaffID |
|
|
FlldSpInstruction |
varchar(50) |
||
|
FlldCopyNotes |
varchar(50) |
||
|
FlldInvoiceDate |
date |
||
|
FlldCost |
Double(6,2) |
||
|
FlldPageSize |
varchar(10) |
||
|
FlldShape |
varchar(10) |
||
|
FlldColour |
varchar(20) |
||
|
FlldPosition |
varchar(20) |
||
|
FlldProdNotes |
varchar(50) |
||
|
FlldMagazineIssueID |
integer |
foreign key reference to MagazineIssue. FlldMagazineIssueID |
|
|
FlldCompanyName |
varchar(30) |
foreign key reference to Client. FlldCompanyName |
|
|
PAYMENT |
FlldPaymentID |
integer |
primary key |
|
FlldAmount |
double(6,2) |
||
|
FlldPaymentDate |
date |
||
|
FlldPaymentType |
varchar(20) |
foreign key reference to PaymentType. FlldPaymentType |
|
|
FlldOrderID |
integer |
foreign key reference to Order. FlldOrderID |
|
|
CREDITCARDPAYMENT |
FlldPaymentID |
integer |
primary key |
|
FlldCreditCardType |
varchar(20) |
||
|
FlldCreditCardNumber |
varchar(20) |
||
|
FlldCreditCardName |
varchar(20) |
||
|
FlldCreditCardExpiryMonthYear |
varchar(20) |
||
|
CHEQUEPAYMENT |
FlldPaymentID |
integer |
primary key |
|
FlldChequeNumber |
varchar(20) |
||
|
ADVERTISINGAGENCY |
FlldAgencyBusinessName |
varchar(30) |
primary key |
|
FlldContactName |
varchar(30) |
||
|
FlldStreet |
varchar(30) |
||
|
FlldCity |
varchar(20) |
||
|
FlldState |
varchar(20) |
||
|
FlldPostcode |
varchar(5) |
||
|
FlldMobile |
varchar(20) |
||
|
FlldBusinessPhone |
varchar(20) |
||
|
FlldFax |
varchar(20) |
||
|
FlldOther |
varchar(20) |
||
|
CLIENTAGENCY |
FlldCompanyName |
varchar(30) |
primary key; foreign key reference to Client. FlldCompanyName |
|
FlldAgencyBusinessName |
varchar(30) |
foreign key reference to AdvertisingAgency. FlldAgencyBusinessName |
|
|
FlldCommission |
Double(6,2) |
||
|
SUPPLIER |
FlldSuppCompanyName |
varchar(30) |
primary key |
|
FlldContactName |
varchar(30) |
||
|
FlldTitle |
varchar(10) |
||
|
FlldStreet |
varchar(30) |
||
|
FlldCity |
varchar(20) |
||
|
FlldState |
varchar(20) |
||
|
FlldPostcode |
varchar(5) |
||
|
FlldPhone |
varchar(20) |
||
|
FlldWebsite |
varchar(30) |
||
|
FlldEmail |
varchar(30) |
||
|
FlldComment |
varchar(50) |
There is so much knowledge and benefit I’ve learned in this assignment like below-
- Database Normalization is the main learning feature in this assignment.
- ER diagram learning is also one of the best features in this task.
- Database relations among tables are also one of the best features in this task.
- Functional dependency is also one of the best features in this task.
References
Visual-Paradigm. (n.d.). What is Entity Relationship Diagram (ERD) ? [online]. Available from: https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/ [Accessed 3 May 2018].
Intellipaat.com. (2018). RDBMS [online]. Available from: https://intellipaat.com/tutorial/sql-tutorial/rdbms/ [Accessed 3 May 2018].
Database.guide. (2018). What is an RDBMS [online]. Available from:
https://database.guide/what-is-an-rdbms/ [Accessed 3 May 2018].
Databasedir.com. (n.d.). What is a database [online]. Available from:
https://www.databasedir.com/what-is-database/ [Accessed 3 May 2018].
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
