The database management for the Al Jabriya Media Store
Prepared by Dr Kamel Rouibah
College of Business Administration
Quantitative Methods and Information Systems
Project in Management Information Systems
Established in 1987 Al Jabriya Media Store is an audios and videos store company owned by Abdoulkareim Al-Lougman (tel: 5318121; e-mail: ; web: www.cba.edu.kw/misclub/jabriya./index.html). Located in Al Jabriya, Al Jabriya Media Store is a shop where customers can rent and buy videos and buy audios. The store works according the Islamic norms. Pornographic movies are not allowed to be sold any where. In addition, only Indian movies can be legally copied. The rent should have insurance amount refunded after the customer returns the movie (i.e. if a customer is not a member he should leave an amount as a deposit in the store. He can take his money upon item back to the store). The rented movies to the customers should be returned back to the shop in 3 days only, and every 3 days of delay will charge the customer with 1 KD and the customer will receive an email reminding him/her that he/she is late to pay and will charged extra fees.
There are five employees working in the store.
During the past years, the company faced several problems including the following
· Dependence on a manual paper work system.
· Delay of rented movies.
· Difficulty of anticipating the number of days required to return movies.
· Unspecified number of movies, CD’s rented or sold to customers.
· Difficulty to determined number of merchandise in store.
· Movies and CD’s are not categorized (Horror, Comedy, Action…) that can help in forecasting the demand in future if it is well categorized.
· No feed back between customers and the management of the video store.( No website or email)
· An increase of work size.
· Payment methods are only by cash.
After having several meeting with the owner of the company, and analyzing several existing documents (see annex) you have been asked to develop a database system that delivers accurate information to the end user and the manager about every thing in the system, with the control over the functions and the operations of the system. In particular, you need to develop a data base to organize, arrange and store information about renting and selling according to the next scenario.
1 Scenario description or requirements of the expected system
There are two types of customer: Member customers and non-member customers. The objective of having tow types of customer is to determine whether a customer is illegible for a discount or not, also to collect more information about him / her.
Customer information is vital to help in categorizing eligible movies according to his/ her age. There are six categories of movies. These are: G (General Audience), PG (Parental guide), PG13 (parents strongly cautioned), NC-17 (Not for audience 17 and under), R (Restricted), and NR (Not Rated).
When a transaction occurs the manager need to record specific information needed from customer whenever a transaction is made. These pieces of information varied dependence of the type of customer (member vs. non member).
If he (she) is a member, the following pieces of information are needed: Member name, member’s serial number, member’s address, member’s phone number, member’s email address, member’s birth date. (optional), member’s number of rented videos, member’s points for discount (If customer is a member), and member’s balance.
If he (she) is a member, the following information are needed: Non-member customer: Customer’s name, customer’s address, customer’s phone number, customer’s email address, customer’s number of rented videos, and customer’s balance.
The manager also whish to record data about the supplier from whom he (she) gets his merchandise (VHS, tapes and DVDs). The store could have many suppliers for its merchandise. The wishes to record the following pieces of information about each supplier: Supplier name, supplier address, supplier phone number and email, supplier fax number (Optional), and supplier serial number.
An Employee can make multiple transactions while transaction must have only one employee to do it. When customer could have many transactions, in the other hand the transaction must have only one customer; before the customer starts his/her transaction he/she must give his/her information to the store.
Since there are five employees working in the store (two of them are working in the shop, and the other three are working in the studio), the manager needs to record personal information about each employees. Before an employee gets in the work the company asks some information and makes profile for him. Information requested include the following: Employee’s name, employee’s address, employee’s ID, employee’s serial number (once he starts his work in the store), employee’s phone number / email, employee’s salary, employee’s date of assignment (Date of employment), employee’s nationality, employee’s Gender, employee’s Position. (Name of his position) and employee’s Picture.
There are four categories of product items (audio and video) sold and rented in the store. These are: DVD, VCD, VHS, and Cassette. Videos are original from the producer except for Indian movies, which they could be copied but with license from the ministry of information for every single movie to copy. The manager wishes to record in the system the following pieces of information: V/A Name [Video/ Audio], V/A serial number, V/A Format (DVD, VHS, VCD…), video category (Horror, action, Love, etc.), Video episode (Part one, part two, etc.), V/A price (Depending on the type of the movie. DVD, VHS, etc.), year of production (Produced in which year?), video cost per Unit, Audio Genre, Quantity, Video rating, Copied video, V/A Picture, Item For Rent/Sale, Audio author, Movie Actress/ Actor, Subtitled, Language, and Video Type.
In addition to the above requirements, the manager wishes the system to be of ease of use to perform the following process:
· Add and delete information About customers, employees, suppliers, videos and audios.
· Update and customize information about customers, employees, suppliers, videos and audio.
· Add security level for employees, i.e. make access for some employees to see private information.
· Generate different types of reports (weekly and monthly reports).
· Create membership for customer whishing to become members and have discount (5%)
· Ease the process of retrieving an item or information about stored entities.
· Create an alarm process that allows anticipating the number of days required to return movies.
· Create a parental rating for under aged customers.
· Generate more detailed information (reports) presented to the owner.
· Create a black list for bad customers. is not created, we did a report of late rents instead.
· Warn Customers when the days of rental are due.
· Create profile for members and get them discounts for points.
· Retrieve information easily and faster than before.
· Alarm for the inventory to notify the end user if the quantity is finished or near to finish.
· The data base is password security protected.
· Create a multiple ways of payments cash, Key Net, and MasterCard. They said it will be expensive to attach it to the shop.
The manager wishes to create different reports. Employees’ reports are shown only to the manager only. Employees’ reports are classified: "Employee Information" report and "Transaction Per Employee" report. Supplier’s reports are also classified in to two types: "Supplier Information" and "supplier shipment" reports.
Customer reports are classified in to the following reports: "Customer Personal Information", "Customer Nationality" (It includes the nationality of customer who mostly purchases from the store), "Customer Purchases" report ( populates all the purchases that the customer did), "Customer Rented Movies" report (populates all the rents that a customer did), "Membership Points Card" report, "Number of Customer Type" report (it shows the total number of members and non member customers), "Top Area Purchases" report (purchases per area in Kuwait).
As for the Audio and video reports, they are classified into the following reports:
"Audio Information" report, "Video Information" report, "Most Wanted Video Category" report (most purchased video category ); "Most Wanted Video Format" report (most purchased video format), "Quantity on Hand" report, "Top 3 Audio Genres Selling" report, "Top 5 Audio Purchased" report, "Top 5 Video Purchased" report, "Top 5 Video Rented" report.
As for daily reports, the manager needs two types of reports: "Daily Income" and "Number of Transactions per Day" reports.
In addition, the manager needs to generate invoices any transaction occurs. An invoice contains information about the transaction that has been made between vendor and the store. It includes the following information: Quantity on hand (Videos, audios and posters), Rented or purchased quantity expected to be received (This includes blank video and audio CD’s, DVD’s and cassettes to make copies), price of inventory, Name of the most videos sales and type (Horror, action…), and reported date.
Questions
- Identify major entities of the system including associative entities if there are.
- List all attributes of each entity including multi-valued attributes
- Identify the primary key for each entity
- Identify relationship between each pair of entities
- Report minimal and maximal cardinalities between each pair of entities
- Generate the final entity relationship diagram
- Normalize the E-R model and create relations
- Design the system in the MS ACCESS as well as in MSQL
4
Annex
4