ISY1002 Database Management Report: ER Modeling and Database Design

This report, submitted by a student for the ISY1002 Database Management for Business module, provides a comprehensive overview of database design. It includes an ER diagram, relational data structures, and a detailed relational database schema with table descriptions and field types. The report covers various tables such as CLIENT, CLIENTCONTACTS, STAFF, ORDER, and PAYMENT, among others. It also outlines assumptions made during the database design process, such as the categorization of staff types and payment methods. The report concludes with a section on the database design experience, highlighting the knowledge gained in normalization, entity representation, and database relations. References to supporting resources are also included.

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

Report
Acknowledgement
Complete work is completed by me. I have learned lot of things in this assignment.
Table of Contents
2

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

ER Diagram
(Gabry EI O. (2016) (Datanamic.com. n.d) (Sisense. 2018)
4

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 Name Field Type Description
CLIENT FlCompanyName varchar(30) primary key
FlWebsite varchar(50)
FlPhone varchar(20)
FlFax varchar(20)
FlEmail varchar(30)
CLIENTCONTACTS FlContactID integer primary key
FlCompanyName Foreign key reference to
Client.FlCompanyName
FlFirstName varchar(30)
FlLastName varchar(30)
FlPhone varchar(20)
FlFax varchar(20)
CLIENTADDRESS FlContactID integer primary key
FlCompanyName Foreign key reference to
Client.FlCompanyName
FlStreet varchar(30)
FlCity varchar(20)
FlState varchar(20)
FlPostcode varchar(5)
FlAddressType varchar(20)
MAGAZINEISSUE FlMagazineIssueI
D
integer primary key
FlPublicationNam
e
varchar(30)
FlIssueMonthYea
r
varchar(20)
PAYMENTTYPE FlPaymentType varchar(20) primary key
STAFFTYPE FlStaffType varchar(20) primary key
STAFF FlStaffID integer primary key
FlStaffName varchar(50)
FlStaffType varchar(20) foreign key reference to
StaffType.FlStaffType
FlAddress varchar(30)
FlPhone varchar(20)
FlTFN varchar(20)
FlSalary Double(6,2)
STAFFEMAIL FlStaffID integer primary key; foreign key
reference to Staff.FlStaffID
FlEmail varchar(30) primary key
USERS FlStaffID integer primary key; foreign key
reference to Staff.FlStaffID
6

FlFirstName varchar(20)
FlLastName varchar(20)
FlLoginName varchar(20)
FlPassword varchar(20)
FlEmail varchar(30)
FlLevel varchar(20)
ORDER FlOrderID integer primary key
FlOrderDate date
FlPONumber integer
FlStaffID integer foreign key reference to Staff.
FlStaffID
FlSpInstruction varchar(50)
FlCopyNotes varchar(50)
FlInvoiceDate date
FlCost Double(6,2)
FlPageSize varchar(10)
FlShape varchar(10)
FlColour varchar(20)
FlPosition varchar(20)
FlProdNotes varchar(50)
FlMagazineIssueI
D
integer foreign key reference to
MagazineIssue.
FlMagazineIssueID
FlCompanyName varchar(30) foreign key reference to
Client. FlCompanyName
PAYMENT FlPaymentID integer primary key
FlAmount double(6,2)
FlPaymentDate date
FlPaymentType varchar(20) foreign key reference to
PaymentType.
FlPaymentType
FlOrderID integer foreign key reference to
Order. FlOrderID
CREDITCARDPAYMENT FlPaymentID integer primary key; foreign key
reference to Payment.
FlPaymentID
FlCreditCardType varchar(20)
FlCreditCardNum
ber
varchar(20)
FlCreditCardNam
e
varchar(20)
FlCreditCardExpi
ryMonthYear
varchar(20)
CHEQUEPAYMENT FlPaymentID integer primary key; foreign key
reference to Payment.
7

FlPaymentID
FlChequeNumber varchar(20)
ADVERTISINGAGENCY FlAgencyBusines
sName
varchar(30) primary key
FlContactName varchar(30)
FlStreet varchar(30)
FlCity varchar(20)
FlState varchar(20)
FlPostcode varchar(5)
FlMobile varchar(20)
FlBusinessPhone varchar(20)
FlFax varchar(20)
FlOther varchar(20)
CLIENTAGENCY FlCompanyName varchar(30) primary key; foreign key
reference to Client.
FlCompanyName
FlAgencyBusiness
Name
varchar(30) foreign key reference to
AdvertisingAgency.
FlAgencyBusinessName
FlCommission Double(6,2)
SUPPLIER FlSuppCompany
Name
varchar(30) primary key
FlContactName varchar(30)
FlTitle varchar(10)
FlStreet varchar(30)
FlCity varchar(20)
FlState varchar(20)
FlPostcode varchar(5)
FlPhone varchar(20)
FlWebsite varchar(30)
FlEmail varchar(30)
FlComment varchar(50)
(Codecademy. 2018)
8

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