Database Management for Business (ISY1002): ER Modeling Report

This report presents a comprehensive database design for Express Media, focusing on Entity-Relationship (ER) modeling and relational database structures. The report includes an ER diagram, detailed relational data structures, and a relational database schema, encompassing tables for clients, contacts, addresses, magazine issues, payment types, staff, users, orders, payments, credit card payments, cheque payments, advertising agencies, client agencies, and suppliers. Assumptions are clearly outlined, such as the handling of various payment methods and the inclusion of advertising agency details. The relational database schema provides field types and descriptions for each table, ensuring data integrity and organization. The report also highlights the author's learning experience, emphasizing database normalization, ER diagram comprehension, database relations, and functional dependencies. References to relevant sources are included to support the database design process. This report is a valuable resource for understanding database management and ER modeling principles.

Student ID –
Student Name -
Date -
ISY1002 (ISY103) Database Management for Business
ER Modelling
Module Tutor -
1

Report
Acknowledgement
The assignment is completed by me. I took the help from internet in completing this assignment.
Table of Contents
2

ER Diagram.................................................................................................................................................4
Relational Data Structure.............................................................................................................................5
Relational Database Schema........................................................................................................................6
Database Design Experience and Learning.................................................................................................9
References.................................................................................................................................................10
3

ER Diagram
(Visual-Paradigm. n.d.)
4

Relational Data Structure
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)
Assumptions
The database design of Express Media is built by using following assumptions-
1. There are so many types of payment modes in Express Media like cash, credit card etc..
Therefore sub entities are created for payment types.
2. A client can hire advertising agency. That detail will also be maintained into the database.
3. Magazine detail will also be maintained into the database.
4. All types of staffs will be categorized according to their type.
5

Relational Database Schema
Table Name Field Type Description
CLIENT FlldCompanyNa
me
varchar(30) primary key
FlldWebsite varchar(50)
FlldPhone varchar(20)
FlldFax varchar(20)
FlldEmail varchar(30)
CLIENTCONTACTS FlldContactID integer primary key
FlldCompanyNa
me
Foreign key reference to
Client.FlldCompanyName
FlldFirstName varchar(30)
FlldLastName varchar(30)
FlldPhone varchar(20)
FlldFax varchar(20)
CLIENTADDRESS FlldContactID integer primary key
FlldCompanyNa
me
Foreign key reference to
Client.FlldCompanyName
FlldStreet varchar(30)
FlldCity varchar(20)
FlldState varchar(20)
FlldPostcode varchar(5)
FlldAddressType varchar(20)
MAGAZINEISSUE FlldMagazineIssu
eID
integer primary key
FlldPublicationNa
me
varchar(30)
FlldIssueMonthY
ear
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
6

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)
FlldMagazineIssu
eID
integer foreign key reference to
MagazineIssue.
FlldMagazineIssueID
FlldCompanyNa
me
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
FlldCreditCardTy
pe
varchar(20)
FlldCreditCardNu
mber
varchar(20)
FlldCreditCardNa
me
varchar(20)
FlldCreditCardEx
piryMonthYear
varchar(20)
CHEQUEPAYMENT FlldPaymentID integer primary key
FlldChequeNumb varchar(20)
7

er
ADVERTISINGAGENCY FlldAgencyBusin
essName
varchar(30) primary key
FlldContactName varchar(30)
FlldStreet varchar(30)
FlldCity varchar(20)
FlldState varchar(20)
FlldPostcode varchar(5)
FlldMobile varchar(20)
FlldBusinessPhon
e
varchar(20)
FlldFax varchar(20)
FlldOther varchar(20)
CLIENTAGENCY FlldCompanyNam
e
varchar(30) primary key; foreign key
reference to Client.
FlldCompanyName
FlldAgencyBusine
ssName
varchar(30) foreign key reference to
AdvertisingAgency.
FlldAgencyBusinessName
FlldCommission Double(6,2)
SUPPLIER FlldSuppCompan
yName
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)
(Intellipaat.com. 2018)
(Database.guide. 2018)
8

Database Design Experience and Learning
There is so much knowledge and benefit I’ve learned in this assignment like below-
1. Database Normalization is the main learning feature in this assignment.
2. ER diagram learning is also one of the best features in this task.
3. Database relations among tables are also one of the best features in this task.
4. Functional dependency is also one of the best features in this task.
(Databasedir.com. n.d.)
9

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:
http://database.guide/what-is-an-rdbms/ [Accessed 3 May 2018].
Databasedir.com. (n.d.). What is a database [online]. Available from:
http://www.databasedir.com/what-is-database/ [Accessed 3 May 2018].
10