Database Design with Multivalued Attributes

This database design assignment focuses on a specific scenario involving advertising agencies, clients, magazines, and publications. The key challenge is incorporating multivalued attributes into the design. The document provides an Entity Relationship Diagram (ERD) illustrating the relationships between entities like Advertiser_Client, Advertising_Agency, and Magazine_Issue. A detailed data model for each entity is also presented, specifying attribute names and data types.

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