ISY1002: Database Management for Business - Entity-Relationship Report

This report, created for the ISY1002 Database Management for Business module, presents a comprehensive analysis of database design principles. It begins with an ER diagram illustrating the relationships between entities within a business context, followed by the relational data structure, including tables such as ADVERTISERCLIENT, ADVCLIENTCONTACTS, MAGAZINEISSUE, and others. A detailed relational database schema is provided, outlining field types and descriptions for each table. The report documents the database designing experience, highlighting the understanding of ER diagrams, database normalization, and data types. Finally, it includes a list of references used in the creation of the report. This assignment demonstrates a practical application of database concepts and modeling techniques.

Student ID –
Student Name -
Date -
ISY1002 (ISY103) Database Management for Business
Entity-Relationship Modelling
Module Tutor -
1 | P a g e

Report
Declaration
I hereby declare that everything in this assignment is unique and created by me.
Table of Contents
2 | P a g e

ER Diagram.................................................................................................................................................4
Relational Data Structure.............................................................................................................................5
Relational Database Schema........................................................................................................................6
Database Designing Experience..................................................................................................................9
References.................................................................................................................................................10
3 | P a g e

ER Diagram
4 | P a g e

(Cinergix Pty Ltd. 2011) (Studytonight. 2018)
5 | P a g e

Relational Data Structure
ADVERTISERCLIENT(CompanyName, Website, Phone, Fax, Email)
ADVCLIENTCONTACTS(ContactID, CompanyName, FirstName, LastName, Phone, Fax)
ADVCLIENTADDRESS(AddressID, CompanyName, StreetAddress, City, State, Postcode,
AddressType)
MAGAZINEISSUE(MagazineIssueID, PublicationName, IssueMonthYear)
PAYMENTTYPE(PaymentType, Detail)
STAFF(StaffID, StaffName, StaffType, Address, Phone, TFN, Salary)
STAFFEMAIL(StaffID, Email)
HR(StaffID, UserFirstName, UserLastName, LoginName, Password, Email, Level)
SALES(StaffID, UserFirstName, UserLastName, LoginName, Password, Email, Level)
OTHER(StaffID)
ORDER(OrderID, OrderDate, PurchaseOrderNumber, StaffID, SpInstruction, CopyNotes,
InvoiceDate, Cost, PageSize, Shape, Colour, Position, ProdNotes, MagazineIssueID,
CompanyName)
PAYMENT(PaymentID, Amount, PaymentDate, PaymentType, OrderID)
PAYMENTDETAIL(PaymentID, ChequeNumber, CreditCardType, CreditCardNumber,
CreditCardName, CreditCardExpiryMonthYear)
ADVERTISINGAGENCY(AgencyBusinessName, ContactName, Street, City, State, Postcode,
Mobile, BusinessPhone, Fax, Other)
CLIENTAGENCY(CompanyName, AgencyBusinessName, Commission)
SUPPLIER(SuppCompanyName, ContactName,Title, Street, City, State, Postcode, Phone,
Website, Email, Comment)
Assumptions
There are some assumptions while designing the database of Express Media-
- Staff members are divided into – HR, Sales and Other as HR and Sales are allowed to
access the database.
- The payment detail should be stored into the database e.g. payment date, credit card
detail etc.
- The staff details e.g. salary, TFN etc is mandatory to be stored into the database.
6 | P a g e

Relational Database Schema
Table Name Field Type Description
ADVERTISERCLIENT CompanyName varchar(30) primary key
Website varchar(50)
Phone varchar(20)
Fax varchar(20)
Email varchar(30)
ADVCLIENTCONTACTS ContactID integer primary key
CompanyName Foreign key reference to
AdvertisementClient.Compan
yName
FirstName varchar(30)
LastName varchar(30)
Phone varchar(20)
Fax varchar(20)
ADVCLIENTADDRESS ContactID integer primary key
CompanyName foreign key reference to
AdvertisementClient.Compan
yName
StreetAddress varchar(30)
City varchar(20)
State varchar(20)
Postcode varchar(5)
AddressType varchar(20)
MAGAZINEISSUE MagazineIssueID integer primary key
PublicationName varchar(30)
IssueMonthYear varchar(20)
PAYMENTTYPE PaymentType varchar(20) primary key
Detail varchar(30)
STAFF StaffID integer primary key
StaffName varchar(50)
StaffType varchar(20)
Address varchar(30)
Phone varchar(20)
TFN varchar(20)
Salary Double(6,2)
STAFFEMAIL StaffID integer primary key; foreign key
reference to Staff.StaffID
Email varchar(30) primary key
HR StaffID integer primary key
UserFirstName varchar(20)
UserLastName varchar(20)
7 | P a g e

LoginName varchar(20)
Password varchar(20)
Email varchar(30)
Level varchar(20)
SALES StaffID integer primary key
UserFirstName varchar(20)
UserLastName varchar(20)
LoginName varchar(20)
Password varchar(20)
Email varchar(30)
Level varchar(20)
OTHER StaffID integer primary key
ORDER OrderID integer primary key
OrderDate date
PurchaseOrderNu
mber
integer
StaffID integer foreign key reference to
Staff.StaffID
SpInstruction varchar(50)
CopyNotes varchar(50)
InvoiceDate date
Cost Double(6,2)
PageSize varchar(10)
Shape varchar(10)
Colour varchar(20)
Position varchar(20)
ProdNotes varchar(50)
MagazineIssueID integer foreign key reference to
MagazineIssue.MagazineIssue
ID
CompanyName varchar(30) foreign key reference to
AdvertiserClient.
CompanyName
PAYMENT PaymentID integer primary key
Amount double(6,2)
PaymentDate date
PaymentType varchar(20) foreign key reference to
PaymentType. PaymentType
OrderID integer foreign key reference to
Order.OrderID
PAYMENTDETAIL PaymentID integer primary key; foreign key
reference to Payment.
PaymentID
ChequeNumber varchar(20)
8 | P a g e

CreditCardType varchar(20)
CreditCardNumb
er
varchar(20)
CreditCardName varchar(20)
CreditCardExpiry
MonthYear
varchar(20)
ADVERTISINGAGENCY AgencyBusiness
Name
varchar(30) primary key
ContactName varchar(30)
Street varchar(30)
City varchar(20)
State varchar(20)
Postcode varchar(5)
Mobile varchar(20)
BusinessPhone varchar(20)
Fax varchar(20)
Other varchar(20)
CLIENTAGENCY CompanyName varchar(30) primary key; foreign key
reference to AdvertiserClient.
CompanyName
AgencyBusinessN
ame
varchar(30) foreign key reference to
AdvertisingAgency.
AgencyBusinessName
Commission Double(6,2)
SUPPLIER SuppCompanyNa
me
varchar(30) primary key
ContactName varchar(30)
Title varchar(10)
Street varchar(30)
City varchar(20)
State varchar(20)
Postcode varchar(5)
Phone varchar(20)
Website varchar(30)
Email varchar(30)
Comment varchar(50)
(Chalise Bdr. H. 2013)
(teach-ict.com. n.d.)
9 | P a g e

Database Designing Experience
There are so many good experiences in database designing like below-
- Learned about ER Diagram.
- Learned about database normalization.
- Learned about different data types.
- Learned about database relations to a great extent.
This database assignment has no limitation everything is going to be covered in this assignment.
All the strength in the assignment has been discussed above.
10 | P a g e

References
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams [online]. Available from:
http://creately.com/blog/diagrams/er-diagrams-tutorial/[Accessed 1 May 2018].
Studytonight. (2018). Working with ER Diagrams [online]. Available from:
http://www.studytonight.com/dbms/er-diagram.php [Accessed 1 May 2018].
Chalise Bdr. H. (2013). SQL Database [online]. Available
from :http://harkachalise.blogspot.in/2013/03/simple-and-composite-attributes.html
[Accessed: 1-May-2018]
teach-ict.com. (n.d.). Third Normal Form [online]. Available from:
http://www.teach-ict.com/as_a2_ict_new/ocr/AS_G061/315_database_concepts/
normalisation/miniweb/pg9.htm [Accessed: 1-May-2018].
11 | P a g e