Database Systems

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