Shine Advertising Campaign Database: ER Model and SQL Queries

Desklib provides solved assignments and past papers to help students succeed.

Assignment1

Table of Contents
Introduction......................................................................................................................................3
Task 1...............................................................................................................................................4
Conceptual model........................................................................................................................4
TASK 2............................................................................................................................................5
Logical model..............................................................................................................................5
TASK 3............................................................................................................................................6
Functional Dependencies.............................................................................................................6
TASK 4............................................................................................................................................7
Database Creation........................................................................................................................7
TASK 5..........................................................................................................................................10
Queries.......................................................................................................................................10
Conclusion.....................................................................................................................................14
References......................................................................................................................................15
List Of Figures
Figure 1 conceptual model...............................................................................................................4
Figure 2 logical model.....................................................................................................................5
Figure 3: Table Campaign...............................................................................................................7
Figure 4: Table Adverts...................................................................................................................7
Figure 5: Table Casual_Staff...........................................................................................................8
Figure 6: Table Full_Time_Staff.....................................................................................................8
Figure 7: Table Staff........................................................................................................................9
Figure 8: Table Studio.....................................................................................................................9
Figure 9: Q1...................................................................................................................................10
Figure 10: Q2.................................................................................................................................11
Figure 11: Q3.................................................................................................................................11
Figure 12: Q4.................................................................................................................................12
Figure 13: Q5.................................................................................................................................13

Introduction
Shine is an advertising company which provides the service to its clients like campaigns,
advertisements. Shine runs an advertising campaign for their clients and also maintains all the
necessary records. Every campaign has its specific theme which makes campaign different from
other campaigns. The final cost will be finalized in the last on the basis of customer’s hourly
attendance, actor’s fees, and model’s fees, etc. Logical and conceptual model of ER model will
be discussed here so shine will not face issue. The best database model will design for Shine so
things will proceed smoothly.

Task 1
Conceptual model
The Conceptual model is a model that helps people to understand the model and the model
performs the workflow (An, A.I., 2018). The Primary key is a unique key in a table that uniquely
defines the system for better use of the database. According to the given scenario, Shine is
advertising company that wants to improve the system so client did not use one booking for
multiple purposes. In this model, Shine has two kinds of staff. One is full-time staff which can be
director or account manager, accountants, administrative personnel, account managers and
technical personnel and they have office. Every campaign has director or account manager who
manages the campaign and also responsible for all the things. Another side Shine has casual staff
also like actors, models, graphic designers, etc which will be contacted at the time of relevant
work.

Figure 1 conceptual model
TASK 2
Logical model
The logical model is almost similar to the conceptual model. In the logical model, all the primary
key and foreign key define properly so it can understand how data will retrieve from other tables.
The logical model is totally different from the physical model. The logical model describes the
model as much as possible. This model describes all the entities and their relationships with
other entities. in this diagram, Staff is an entity that has attributes and primary key for this entity
is staff_id. Staff_id is also seen as a foreign key for other different entities.
Figure 2 logical model

TASK 3
Functional Dependencies
Functional dependencies could be defined as when one of the attributes completely depends
upon one of the other attributes in a database management system (Harikumar, S. and Reethima,
R., 2015). It mainly helps in differentiating between the good and the bad design of a database.
Functional dependencies are of different types that are named as:
 Multi-valued Dependency
 Trivial Functional Dependency
 Non-trivial Functional Dependency
 Transitive Dependency
Multi-valued Dependency
Multi-valued as well as Trivial Dependencies could be seen in the above created relational
database model. Multi-value dependencies could be defined as the dependencies where a single
attribute is not dependent upon the prime attribute but more than one attributes depend upon the
single prime attribute but are independent of each other.
 In Casual_Staff Table-
Hour_Salary, Staff_type are dependent upon the Staff_id. Staff_id here is the Prime attribute
and also other attributes are independent of each other.
 In Staff Table-
Staff_Address, Staff_Contact are dependent upon the Staff_id. Staff_id here is the Prime
attribute and also other attributes are independent of each other.
 In Studios Table-
Status, TotalCost and PerStudioCharges are dependent upon the St_id. St_id here is the
Prime attribute and also other attributes are independent of each other.
The above-mentioned tables show the Multi-valued dependencies. Many other tables also follow
the Multi-valued dependencies.

TASK 4
Database Creation
Figure 3: Table Campaign

Figure 4: Table Adverts
Figure 5: Table Casual_Staff

Figure 6: Table Full_Time_Staff
Figure 7: Table Staff

Figure 8: Table Studio
TASK 5
Queries
1. Select CampaignTitle,CampaignTheme
from Campaign
where actualCost < (Select avg(estimatedcost) from Campaign);