Database Management for Business: Design, Schema, and ERD Project

This database project focuses on designing a database for a business context. The assignment begins with an Entity Relationship Diagram (ERD) illustrating the entities and their relationships. The relational structure is then defined, including tables for orders, payments, suppliers, staff, advertising clients, agencies, and magazine issues. The relational database schema provides detailed information about each table, specifying field types, primary keys, and foreign key relationships. The project includes assumptions about the data model, such as the handling of many-to-many relationships and the treatment of multivalued attributes. The author reflects on the learning experience, highlighting the understanding of database design principles, normalization, and the practical application of theoretical knowledge. The design's main strength lies in the avoidance of many-to-many relationships, ensuring proper database functionality. The author references several academic sources related to database design and management.

Running head: DATABASE MANAGEMENT FOR BUSINESS
Database Management for Business
Name of the Student
Name of the University
Signature of the Students:

1DATABASE MANAGEMENT FOR BUSINESS
Table of Contents
Entity Relationship Diagram: 2
Relational Structure: 2
Relational Database Schema: 4
Description: 7
Bibliography: 9

2DATABASE MANAGEMENT FOR BUSINESS
Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)

3DATABASE MANAGEMENT FOR BUSINESS
Relational Structure:
Order (Order_ID, Invoice_Date, Magazine_Issue, Cost_Price, Page_Size, Shape, Colour,
Position, Production_Notes)
Order_Payment (Order_ID, Payment_ID, Paid_Amount, Date, Due_Amount)
Payments (Payment_ID, Amount, Cheque_Number, Credit_Card_Type,
Credit_Card_Number, Credit_Card_Name, Expiry_Date, Payment_Method)
Suppliers (Supplier_ID, Company_Name, Contact_Name, Title, City, State, Post_Code)
Staff (Staff_ID, Name, Phone, Address, Tax_File_Number, Email_Address)
Advertising_Order (Purchase_Order_Number, Order_date, Sales_representative,
Special_Instruction, Copy_Notes)
Advertiser_Client (Advertiser_ID, Design_Agency, Advertising_Agency,
Agency_Commission, Company_name, Website_address, Business_Phone_number, Fax,
Contact_First_Name, Contact_Second_Name, Contact_Telephone, Contact_Fax, Street_Name,
City, State, Post_Code)
Magazine_Issue (Issue_ID, Publication_Name, Issue_Month, Issue_Year)
Advertising_Agency (Agency_ID, Business_Name, Contact_Name, Street, City, State,
Postcode, Mobile_Number, Business_Number, Fax)
Editorial_Address (Advertiser_ID, Street, City, State, Postcode)
Invoicing_Address (Advertiser_ID, Street, City, State, Postcode)

4DATABASE MANAGEMENT FOR BUSINESS
Invoicing_Address (Advertiser_ID, Street, City, State, Postcode)
Design_Agency (Agency_ID, Company_Name, Contact, Phone, Email_Address)
Assumptions:
i. The order and payment has many-to-many relationship. Therefore,
Order_Payment table has been included in the database. This table decomposes
the many-to-many relationship into one-to-many and many-to-one relation.
ii. The staff supplier id data are not stored in the database against any odder. As they
are associated with every order their relationship with the order table is not
identified.
iii. Each advertiser client has different editorial and invoicing address.
iv. One advertising and designing agency can be working with different advertisers.
Therefore, both of the table’s relation with Advertiser_Client table is many-to-
one.
Relational Database Schema:
Table
name
Field Type Description
Order Order_ID
Invoice_Date
Magazine_Issue
Varchar (40)
Varchar (40)
Varchar (40)
Primary Key

5DATABASE MANAGEMENT FOR BUSINESS
Cost_Price
Page_Size
Shape
Colour
Position
Production_Notes
Decimal (13,2)
Int
Varchar (40)
Varchar (40)
Varchar (40)
Varchar (40)
Order_Pa
yment
Order_ID
Payment_ID
Paid_Amount
Date
Due_Amount
Varchar (40)
Varchar (40)
Decimal (13,2)
Date
Decimal (13,2)
Foreign Key reference to
Order.Order_ID
Foreign Key reference to
payments. Payment_ID
Payments Payment_ID
Amount
Cheque_Number
Credit_Card_Type
Credit_Card_Number
Varchar (40)
Decimal (13,2)
Long
Varchar (40)
Long
Primary Key

6DATABASE MANAGEMENT FOR BUSINESS
Credit_Card_Name
Expiry_Date
Payment_Method
Varchar (40)
Date
Varchar (40)
Staff Staff_ID
Name
Phone
Address
Tax_File_Number
Email_Address
Varchar (40)
Varchar (40)
INT
Varchar (40)
Varchar (40)
Varchar (40)
Primary Key
Multivalued Attribute
Multivalued Attribute
Advertiser
_Client
Advertiser_ID
Design_Agency
Advertising_Agency
Agency_Commission
Company_name
Website_address
Varchar (40)
Varchar (40)
Varchar (40)
Varchar (40)
Varchar (40)
Varchar (40)
Primary Key
Foreign Key reference to
Advertising_Agency.Agency_ID
Foreign Key reference to
Design_Agency. Agency_ID

7DATABASE MANAGEMENT FOR BUSINESS
Business_Phone_number
Fax
Contact_First_Name
Contact_Second_Name
Contact_Telephone
Contact_Fax
Street_Name
City
State
Post_Code
INT
INT
Varchar (40)
Varchar (40)
INT
INT
Varchar (40)
Varchar (40)
Varchar (40)
INT
Magazine
_Issue
Issue_ID
Order_ID
Publication
Name
Issue_Month
Issue_Year
Varchar (40)
Varchar (40)
Varchar (40)
Varchar (40)
Month
Year
Primary Key
Foreign Key reference to
Order.Order_ID