Relational Data Structure CLIENT(FlCompanyName,FlWebsite,FlPhone,FlFax,FlEmail) CLIENTCONTACTS(FlContactID,FlCompanyName,FlFirstName,FlLastName,FlPhone, FlFax) CLIENTADDRESS(FlAddressID,FlCompanyName, FlStreet, FlCity, FlState, FlPostcode, FlAddressType) MAGAZINEISSUE(FlMagazineIssueID,FlPublicationName,FlIssueMonthYear) PAYMENTTYPE(FlPaymentType) STAFFTYPE(FlStaffType) STAFF(FlStaffID,FlStaffName,FlStaffType,FlAddress,FlPhone,FlTFN,FlSalary) STAFFEMAIL(FlStaffID,FlEmail) USERS(FlStaffID, FlFirstName, FlLastName, FlLoginName, FlPassword, FlEmail, FlLevel) ORDER(FlOrderID, FlOrderDate, FlONumber,FlStaffID, FlSpInstruction, FlCopyNotes, FlInvoiceDate, FlCost, FlPageSize, FlShape, FlColour, FlPosition, FlProdNotes, FlMagazineIssueID,FlCompanyName) PAYMENT(FlPaymentID,FlAmount,FlPaymentDate,FlPaymentType,FlOrderID) CREDITCARDPAYMENT(FlPaymentID,FlCreditCardType,FlCreditCardNumber, FlCreditCardName,FlCreditCardExpiryMonthYear) CHEQUEPAYMENT(FlPaymentID, FlChequeNumber) ADVERTISINGAGENCY(FlAgencyBusinessName, FlContactName, FlStreet, FlCity, FlState, FlPostcode, FlMobile, FlBusinessPhone, FlFax, FlOther) CLIENTAGENCY(FlCompanyName,FlAgencyBusinessName, FlCommission) SUPPLIER(FlSuppCompanyName, FlContactName, FlTitle, FlStreet, FlCity, FlState, FlPostcode, FlPhone, FlWebsite, FlEmail, FlComment) Assumptions There are some assumptions that are used in the Express Media database designing like below- -Staffs are of different types e.g. HR, Sales etc. therefore; a staff type table is created to store their types. -Magazine detail should be saved into the database like date of publish etc. -Types of Payments are different e.g. Cash, Cheque, and Credit Card etc. therefore; a payment type table is created to store their types. 5
Relational Database Schema Table NameFieldTypeDescription CLIENTFlCompanyNamevarchar(30)primary key FlWebsitevarchar(50) FlPhonevarchar(20) FlFaxvarchar(20) FlEmailvarchar(30) CLIENTCONTACTSFlContactIDintegerprimary key FlCompanyNameForeign key reference to Client.FlCompanyName FlFirstNamevarchar(30) FlLastNamevarchar(30) FlPhonevarchar(20) FlFaxvarchar(20) CLIENTADDRESSFlContactIDintegerprimary key FlCompanyNameForeign key reference to Client.FlCompanyName FlStreetvarchar(30) FlCityvarchar(20) FlStatevarchar(20) FlPostcodevarchar(5) FlAddressTypevarchar(20) MAGAZINEISSUEFlMagazineIssueI D integerprimary key FlPublicationNam e varchar(30) FlIssueMonthYea r varchar(20) PAYMENTTYPEFlPaymentTypevarchar(20)primary key STAFFTYPEFlStaffTypevarchar(20)primary key STAFFFlStaffIDintegerprimary key FlStaffNamevarchar(50) FlStaffTypevarchar(20)foreign key reference to StaffType.FlStaffType FlAddressvarchar(30) FlPhonevarchar(20) FlTFNvarchar(20) FlSalaryDouble(6,2) STAFFEMAILFlStaffIDintegerprimary key; foreign key reference to Staff.FlStaffID FlEmailvarchar(30)primary key USERSFlStaffIDintegerprimary key; foreign key reference to Staff.FlStaffID 6
FlFirstNamevarchar(20) FlLastNamevarchar(20) FlLoginNamevarchar(20) FlPasswordvarchar(20) FlEmailvarchar(30) FlLevelvarchar(20) ORDERFlOrderIDintegerprimary key FlOrderDatedate FlPONumberinteger FlStaffIDintegerforeign key reference to Staff. FlStaffID FlSpInstructionvarchar(50) FlCopyNotesvarchar(50) FlInvoiceDatedate FlCostDouble(6,2) FlPageSizevarchar(10) FlShapevarchar(10) FlColourvarchar(20) FlPositionvarchar(20) FlProdNotesvarchar(50) FlMagazineIssueI D integerforeign key reference to MagazineIssue. FlMagazineIssueID FlCompanyNamevarchar(30)foreign key reference to Client.FlCompanyName PAYMENTFlPaymentIDintegerprimary key FlAmountdouble(6,2) FlPaymentDatedate FlPaymentTypevarchar(20)foreign key reference to PaymentType. FlPaymentType FlOrderIDintegerforeign key reference to Order.FlOrderID CREDITCARDPAYMENTFlPaymentIDintegerprimary key; foreign key reference toPayment. FlPaymentID FlCreditCardTypevarchar(20) FlCreditCardNum ber varchar(20) FlCreditCardNam e varchar(20) FlCreditCardExpi ryMonthYear varchar(20) CHEQUEPAYMENTFlPaymentIDintegerprimary key; foreign key reference toPayment. 7
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Database Design Experience The database assignment has lot of advantages and experiences which I gained while developing the database of Express Media. Some of them are below- -It is showing complete knowledge about normalization concept. -It is showing complete knowledge about pictorial representation of the entities. -It is showing complete knowledge about database relations. -It is showing complete knowledge about database integrity. 9
References Gabry EI O. (2016). Database – Modeling: Entity Relationship Diagram (ERD) (Part 5) [online]. Available from: https://medium.com/omarelgabrys-blog/database-modeling-entity- relationship-diagram-part-5-352c5a8859e5 [Accessed 3 May 2018]. Datanamic.com. (n.d.). What is an Entity Relationship Diagram (ERD) [online]. Available from: http://www.datanamic.com/support/lt-dez006-what-is-an-erd.html [Accessed 3 May 2018]. Sisense. (2018). Entity Relationship Diagram [online]. Available from: https://www.sisense.com/glossary/entity-relationship-diagram/ [Accessed 3 May 2018]. Codecademy. (2018). What is a Relational Database Management System (RDBMS) [online]. Available from:https://www.codecademy.com/articles/what-is-rdbms-sql [Accessed 3 May 2018]. 10