ISY1002 Database Management for Business: ER Modeling and Schema

This report presents an ER diagram and relational database schema for a business database, addressing key entities such as clients, staff, orders, and payments. It outlines the relational data structure, detailing tables like CLIENT, CLIENTCONTACTS, ORDER, PAYMENT, and STAFF, including field types and primary/foreign key relationships. The report also includes assumptions made during database design, such as storing staff details, order information, and payment details separately. The student reflects on their experience in database design, highlighting their learning in database normalization, data types, and relationship types. References to external resources are provided for further reading on ER diagrams, database normalization, and SQL Server database basics. Desklib offers students access to this and similar documents to aid in their studies.

Student ID –
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.
2

Table of Contents
ER Diagram.................................................................................................................................................4
Relational Data Structure.............................................................................................................................5
Relational Database Schema........................................................................................................................6
Experience of Database Designing..............................................................................................................9
References.................................................................................................................................................10
3

ER Diagram
(Oracle. 2000) (TechTarget. 2016)
4

Relational Data Structure
CLIENT(FldCompanyName, FldWebsite, FldPhone, FldFax, FldEmail)
CLIENTCONTACTS(FldContactID, FldCompanyName, FldFirstName, FldLastName,
FldPhone, FldFax)
CLIENTADDRESS(FldAddressID, FldCompanyName, FldStreet, FldCity, FldState,
FldPostcode, FldAddressType)
MAGAZINEISSUE(FldMagazineIssueID, FldPublicationName, FldIssueMonthYear)
PAYMENTTYPE(FldPaymentType)
STAFFTYPE(FldStaffType)
STAFF(FldStaffID, FldStaffName, FldStaffType, FldAddress, FldPhone, FldTFN, FldSalary)
STAFFEMAIL(FldStaffID, FldEmail)
USERS(FldStaffID, FldFirstName, FldLastName, FldLoginName, FldPassword, FldEmail,
FldLevel)
ORDER(FldOrderID, FldOrderDate, FldONumber, FldStaffID, FldSpInstruction, FldCopyNotes,
FldInvoiceDate, FldCost, FldPageSize, FldShape, FldColour, FldPosition, FldProdNotes,
FldMagazineIssueID, FldCompanyName)
PAYMENT(FldPaymentID, FldAmount, FldPaymentDate, FldPaymentType, FldOrderID)
CCPAYMENT(FldPaymentID, FldCreditCardType, FldCreditCardNumber,
FldCreditCardName, FldCreditCardExpiryMonthYear)
CHEQUEPAYMENT(FldPaymentID, FldChequeNumber)
ADVERTISINGAGENCY(FldAgencyBusinessName, FldContactName, FldStreet, FldCity,
FldState, FldPostcode, FldMobile, FldBusinessPhone, FldFax, FldOther)
CLIENTAGENCY(FldCompanyName, FldAgencyBusinessName, FldCommission)
SUPPLIER(FldSuppCompanyName, FldContactName, FldTitle, FldStreet, FldCity, FldState,
FldPostcode, FldPhone, FldWebsite, FldEmail, FldComment)
Assumptions
Following assumptions are used during database designing-
- Complete staff details should be saved into the database along with staff type e.g. HR,
Sales etc.
- Complete order detail should be saved along with magazine in which the advertisement
will be publish.
- Payment detail should be saved separately for different type e.g. Credit Card Detail,
Cheque Detail.
5

Relational Database Schema
Table Name Field Type Description
CLIENT FldCompanyNam
e
varchar(30) primary key
FldWebsite varchar(50)
FldPhone varchar(20)
FldFax varchar(20)
FldEmail varchar(30)
CLIENTCONTACTS FldContactID integer primary key
FldCompanyNam
e
Foreign key reference to
Client.FldCompanyName
FldFirstName varchar(30)
FldLastName varchar(30)
FldPhone varchar(20)
FldFax varchar(20)
CLIENTADDRESS FldContactID integer primary key
FldCompanyNam
e
Foreign key reference to
Client.FldCompanyName
FldStreet varchar(30)
FldCity varchar(20)
FldState varchar(20)
FldPostcode varchar(5)
FldAddressType varchar(20)
MAGAZINEISSUE FldMagazineIssue
ID
integer primary key
FldPublicationNa
me
varchar(30)
FldIssueMonthYe
ar
varchar(20)
PAYMENTTYPE FldPaymentType varchar(20) primary key
STAFFTYPE FldStaffType varchar(20) primary key
STAFF FldStaffID integer primary key
FldStaffName varchar(50)
FldStaffType varchar(20) foreign key reference to
StaffType.FldStaffType
FldAddress varchar(30)
FldPhone varchar(20)
FldTFN varchar(20)
FldSalary Double(6,2)
STAFFEMAIL FldStaffID integer primary key; foreign key
reference to Staff.FldStaffID
FldEmail varchar(30) primary key
USERS FldStaffID integer primary key; foreign key
6

reference to Staff.FldStaffID
FldFirstName varchar(20)
FldLastName varchar(20)
FldLoginName varchar(20)
FldPassword varchar(20)
FldEmail varchar(30)
FldLevel varchar(20)
ORDER FldOrderID integer primary key
FldOrderDate date
FldPONumber integer
FldStaffID integer foreign key reference to Staff.
FldStaffID
FldSpInstruction varchar(50)
FldCopyNotes varchar(50)
FldInvoiceDate date
FldCost Double(6,2)
FldPageSize varchar(10)
FldShape varchar(10)
FldColour varchar(20)
FldPosition varchar(20)
FldProdNotes varchar(50)
FldMagazineIssu
eID
integer foreign key reference to
MagazineIssue.
FldMagazineIssueID
FldCompanyNam
e
varchar(30) foreign key reference to
Client. FldCompanyName
PAYMENT FldPaymentID integer primary key
FldAmount double(6,2)
FldPaymentDate date
FldPaymentType varchar(20) foreign key reference to
PaymentType.
FldPaymentType
FldOrderID integer foreign key reference to
Order. FldOrderID
CCPAYMENT FldPaymentID integer primary key; foreign key
reference to Payment.
FldPaymentID
FldCreditCardTy
pe
varchar(20)
FldCreditCardNu
mber
varchar(20)
FldCreditCardNa
me
varchar(20)
FldCreditCardEx
piryMonthYear
varchar(20)
7

CHEQUEPAYMENT FldPaymentID integer primary key; foreign key
reference to Payment.
FldPaymentID
FldChequeNumbe
r
varchar(20)
ADVERTISINGAGENCY FldAgencyBusine
ssName
varchar(30) primary key
FldContactName varchar(30)
FldStreet varchar(30)
FldCity varchar(20)
FldState varchar(20)
FldPostcode varchar(5)
FldMobile varchar(20)
FldBusinessPhon
e
varchar(20)
FldFax varchar(20)
FldOther varchar(20)
CLIENTAGENCY FldCompanyNam
e
varchar(30) primary key; foreign key
reference to Client.
FldCompanyName
FldAgencyBusine
ssName
varchar(30) foreign key reference to
AdvertisingAgency.
FldAgencyBusinessName
FldCommission Double(6,2)
SUPPLIER FldSuppCompany
Name
varchar(30) primary key
FldContactName varchar(30)
FldTitle varchar(10)
FldStreet varchar(30)
FldCity varchar(20)
FldState varchar(20)
FldPostcode varchar(5)
FldPhone varchar(20)
FldWebsite varchar(30)
FldEmail varchar(30)
FldComment varchar(50)
(Guru99. 2016)
(Brombarg P. n.d.)
8

Experience of Database Designing
I have learned lot of new things in Express Media’s database designing task. Some of them are
following-
- I learned how to make normalized database.
- I learned different data types.
- I learned different types of relationships e.g. one-to-one, one-to-many etc.
9

References
Oracle. (2000). Drawing the Entity Relationship Diagram [online]. Available from:
http://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/05_dev1.htm / [Accessed 1
May 2018].
TechTarget. (2016), entity relationship diagram [online]. Available from:
http://searchcrm.techtarget.com/definition/entity-relationship-diagram / [Accessed 1 May
2018].
Guru99. (2016). Learn Database Normalization with the help of a case study [online]. Available
from:
http://www.guru99.com/database-normalization.html / [Accessed 1 May 2018].
Brombarg P. (n.d.) SQL SERVER Database Normalization Basics for Developers [online].
Available from: http://www.nullskull.com/a/1629/sql-server-database-normalization-
basics-for-developers.aspx / [Accessed 1 May 2018].
10