ISYS224 Database Design Project: Conceptual, Logical Models, Queries

This project report details the database design for an advertising agency, Shine. It begins with a conceptual data model using an Enhanced Entity-Relationship Diagram (EERD), followed by a relational data model. The report outlines the conceptual model, relational model, and functional dependencies. The report also includes SQL queries to retrieve specific data, such as campaigns with costs below average and lists of staff members. The project addresses the implementation of database schemas and the use of Data Manipulation Language (DML) scripts to resolve queries and generate outputs. The report concludes with a summary of the database design and the successful implementation of the queries.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN
Table of Contents
Introduction................................................................................................................................2
Conceptual Data Model..............................................................................................................2
Relational Data Model...............................................................................................................3
Functional Dependencies...........................................................................................................4
Queries.......................................................................................................................................5
Conclusion..................................................................................................................................8
References..................................................................................................................................9

2DATABASE DESIGN
Introduction
The relational database managements system is a collection of interrelated data. The
report indents to deal with the conceptual modelling of the given case study about a company
named shine. The conceptual model is consist with the Enhanced Entity-relationship
Diagram. Along with, the relational (logical) model is developed by Entity-relationship
Diagram (Connolly and Begg 2015). In the later sections of the report the Data manipulation
language scripts are used to resolve the provided queries along with the screenshot of the
output.
Conceptual Data Model
Conceptual model of a database is the most basic and abstract level of designing
(Krogstie 2013). Other implementation information or unnecessary procedures are eliminated
from this model to develop the logical model.

3DATABASE DESIGN
Figure 1: EERD (conceptual)
Source: created by author
Relational Data Model
Relational Model shows the relationship among the data and entities in a database and
how the data is stored and retrieved (Harrington 2016). The relational model can directly
transformed into the schema of the database.

4DATABASE DESIGN
Figure 2: Entity Relationship Diagram (logical)
Source: created by author
Functional Dependencies
The functional dependency is when an attribute determines another attribute uniquely
in a relation (Amshakala, Nedunchezhian and Rajalakshmi 2014). For each relation in the
database with functional dependencies need to have some assumptions that are mentioned
below:

5DATABASE DESIGN
a. An employee can be either Fulltime or Part-time.
b. Only fulltime members (director and account manager) can contact the clients as other
companies.
c. Every clients have one campaign to work on.
d. Each fulltime employee can manage multiple clients.
e. All employees can work on the campaign.
f. One employee can work on multiple campaign.
g. One campaign needs one or more adverts.
h. One type of adverts need one or more studios.
The following Figure 2 ERD can be said in 2NF as the transitive dependency in the
grade table is seen between the grade_id and grade attributes. Otherwise the transitive
dependency is not seen anywhere in the rest of the tables.
Queries
1. List the titles and themes of the Campaigns’ whose actual costs are below the average
estimated cost of all the campaigns.
select campaign.theme,((studio.rateperhour*studio.hours) +
(employee.rateperhour*campaign.hourwork)) as
total
from employee, campaign,adverts,studio where studio.adverts_aid=adverts.aid and
adverts.campaign_id=campaign.id
and
campaign.employee_empid=employee.empid and ((studio.rateperhour*studio.hours) +
(employee.rateperhour*campaign.hourwork))<

6DATABASE DESIGN
(select avg(campaign.estimatedcost) from campaign);
2. List the campaign titles and the number of their advertisements whose completion
dates were earlier than their target dates.
select campaign.theme, COUNT(adverts.type), campaign.date,studio.date from
adverts,campaign,studio
where
campaign.id=adverts.campaign_id and studio.adverts_aid=adverts.aid and
studio.date<campaign.date group by campaign.theme;
3. List all the names of full-time staff members whose supervisor(s) were not the
managers of the campaigns they worked on.
select DISTINCT(fulltime.name) from fulltime,employee,clients,campaign where
employee.empid=fulltime.employee_empid AND
employee.empid=campaign.employee_empid and campaign.employee_empid!
=clients.fulltime_employee_empid;

7DATABASE DESIGN
4. For campaigns with more than two staff members working on them, list the
campaign title and the number of staff members who had salary grade greater than 2.
select campaign.theme from employee,campaign,parttime where
campaign.employee_empid=employee.empid and
employee.empid=parttime.employee_empid
and parttime.grade_gid>2 having COUNT(campaign.id)>2
UNION
select campaign.theme from employee,campaign,fulltime where
campaign.employee_empid=employee.empid and
employee.empid=fulltime.employee_empid
and fulltime.grade_gid>2 having COUNT(campaign.id)>2;
5. List all the full-time staff members who do not manage any campaigns.
SELECT fulltime.name from fulltime, employee,clients where
employee.empid=fulltime.employee_empid and

8DATABASE DESIGN
fulltime.designation!='director' and fulltime.designation!='account manager' UNION
SELECT parttime.name from parttime,
employee where employee.empid=parttime.employee_empid;
Conclusion
The database implementation for the shine company was problematic in terms of the
generalization and the price calculations. However, the case study helps to build the E-ERD
and Relational model of the database. After the relational model the ERD is transformed into
the schema. The DML scripts also have shown accurate output according to the provided
queries. Hence, it can be said that the database implementation for Shine Company has
successfully done.

9DATABASE DESIGN
References
Amshakala, K., Nedunchezhian, R. and Rajalakshmi, M., 2014. Extracting functional
dependencies in large datasets using mapreduce model. International Journal of Intelligent
Information Technologies (IJIIT), 10(3), pp.19-35.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
Krogstie, J., 2013. Quality of conceptual data models.