Kassapim Entertainment Ghana Limited (KEGL) has grown into a household name
when it comes to the Kumawood movie rentals in Ghana. Over the years, KEGL has
expanded into the four corners of the country with several branches dotted throughout
the country. The data held on each branch is the branch address made up of street,
city, state, and post code, and the telephone number. Each branch is given a branch
number, which is unique throughout the company. Each branch is allocated staff,
which includes a Manager. The Manager is responsible for the day-to-day running of a
given branch. The data held on a member of staff is his or her name, position, and
salary. Each member of staff is given a staff number, which is unique throughout the
company. Each branch has a stock of videos(kumawood movies). The data held on a
video is the catalog number, video number, title, category, daily rental, cost, status,
and the names of the main actors, and the director. The catalog number uniquely
identifies each video. However, in most cases, there are several copies of each video
at a branch, and the individual copies are identified using the video number. A video is
given a category such as Action, Adult, Children, Drama, Horror, or Comedy. The
status indicates whether a specific copy of a video is available for rent. Before hiring a
video from the company, a customer must first register as a member of a local branch.
The data held on a member is the first and last name, address, and the date that the
member registered at a branch. Each member is given a member number, which is
unique throughout all branches of the company. Once registered, a member is free to
rent videos, up to maximum of ten at any one time. The data held on each video
rented is the rental number, the full name and number of the member, the video
number, title, and daily rental, and the dates the video is rented out and date returned.
The rental number is unique throughout the company.

(i) Using an appropriate CASE tool (e.g. Erwin data modeler or Microsoft Visio) create
an Entity Relationship Diagram for KEGL specifying the key attributes of each
entity type, and the structural constraints on each relationship type. (You may note
any unspecified requirements from the scenario, and make appropriate
assumptions to make the specification complete)
[25 marks]
(ii) Discuss the integrity rules in your data model and demonstrate how these were
applied in your ERD. [10 marks]
(iii)KEGL has plans to create a single database system for all branches across the
country. To achieve this, KEGL will look at the possibility of creating a distributed
database (DDB), since it will further improve the overall performance of the
system. For the purpose of this DDB, KEGL will have computer sites located at
each of each regional capital of Ghana. At each of these sites, we expect frequent
access to the Staff, customers, videos and rental information for the branches
within that region. The Site at the head office in Accra houses a copy of the
complete database and accesses all Staff information including salary (since
salary is processed at this site). Using a modeling technique of your choice, create
a model of fragments to be replicated at each site. You may introduce additional
relations, if necessary. [15 marks]

As a database systems developer, you are appointed by your company to lead the team
of developers that will transform the database schemas designed in coursework 1 above
into a working database system. Write a 10-page proposal (at least 4000 words) to your
boss in the form of an academic paper highlighting the following:
 Your choice of a DBMS that is most suitable for the implementation of the above
distributed database system of KEGL; including information about vendor,
versioning history, market share, functionality, advantages, disadvantages, basis
for your choice, etc.
 How you intend to ensure the effective administration of the proposed distributed
database, in performing tasks such as End-user support, Data security, privacy,
integrity, Data backup, recovery, etc.
 Your choice of concurrency technique to ensure the noninterference or isolation
property of concurrently executing transactions for the proposed distributed
database system.
 All these must be analyzed within the context of KEGL and your selected DBMS

Share on Facebook

Tweet

Follow us