For our project we decided to implement a software system for a movie rental store. This system is designed for the use of a cashier. There are three entity sets in our system: movie, customer and critic. A customer has a number of attributes: Last Name, First Name, address, phone number, birthday, membership number, movies checked out and social insurance number. A Customer can either rent or buy a movie. A movie has a number of attributes: category, year of release, MPAA rating, studio, ISBN, category. Since we implemented Rental and Buying relationships, it was decided to use and ISA hierarchy to separate copies for sale and those for rent. In addition to the attributes of a movie, the merchandize entity set has the following attributes: merchandise fee (cost to buy a movie), number of available copies. The rental movie entity has the following additional attributes: number of available copies, fee per day to rent, late fee per day. Rent relatiobship involves renting and returning a movie. When a customer rents a movie a date of rental is assigned to a relationship, but also a date of return is assigned. If a customer returns a movie later then the actual due date, the late fee will be charged. Relationship Buy is not very fancy. It doesn't allow returns. We will be checking to make sure that a customer can't purchase more movies than there are copies. However, there is no restocking procedure right now.
In addition, to buying and renting movies, we thought it would be interesting to implement a relationship Reviews. This relationship will allow a customer to determine if a particular movie is worth seeing. An entity set Critic refers to the individual who rates movies. Critic has a number of attributes: biography, occupation, year of first movie review, critic id, e-mail, First Name, Last Name and country of birth. A critic can rate a movie, giving it a rating, a review article and date of review. Every review gets a review ID.
Constraints and candidate keys for entity sets.
-
Customer entity set
- Candidate Keys: {social_security}, {membership_id}
- Primary Key: {social_security}
- Constraints on the Customer attributes:
- Number of movies checked out by a customer is no greater than 5
- Memberships status is either active or suspended.
- Birthday must be such that customer is older than 16 years old.
- Social Security field must be non empty.
Movie entity set
- Candidate Keys: {ISBN}, {name, year_released}
- Primary Key: {ISBN}
- Constraints on the Movie attributes:
- Number of rental copies is no greater than 10.
- Late fee per day is no more than 1 dollar.
- Category is either horror, darma, action or comedy.
- ISBN can't be null.
Critic entity set
- Candidate Keys: {critic_id}, {email}
- Primary Key: {critic_id}
- Constraints on the Critic attributes:
- E-mail can't be null.
- Critic_id can't be null.
- Occupation must not be actor or producer or director.
- Biography must be more than 200 words long.
Candidate keys and Constraints for relationships.
Rental relationship
Candidate key for Rent relationship is {Rent_id}
Cardinality constraint for the relationship Rents is many-to-many, meaning that one movie can be rented by many customers (since we have multiple copies), but also, one customer can rent many movies. An additional constraint on the Rental relationship is that a customer can not check out a movie if there are no copies available.
There is no Participation constraint for the relationship Rents, meaning that a customer can rent no movies at all, and also a movie can remain "unrented". The Rent relationship is partial.
Buy relationship
Candidate key for Buys relationship is {transaction_id}.
Cardinality constraint for the relationship Buyss is many-to-many, meaning that a movie can be bought by several customers (since we have multiple copies), but also, one customer can buy several movies.
There is no Participation constraint on the Buys relationship. A customer can buy no movies. Also, a movie can remain unsold. Therefore, this relationship is partial.
An additional constraint on the Buy relationship is that a number of movies bought by a cutomer can not exceed the number of available copies.
Review relationship
Candidate key for Reviews relationship is {review_id}.
Cardinality constraint for the relationship Reviews is many-to-many, meaning that one movie can be reviewed by many critics, and also, one critic can review many movies.
There is no Participation constraint on the Reviews relationship. This relationship is partial.
Use of software by a cashier(employee).
Sample of the queries that will be asked at the demo:
- Does particular customer have overdue movies?
- Expected input: membership_number. Expected output: yes or no.
- How many critics rented a specific movie?
- Expected input: ISBN. Expected output: integer.
- How many copies of a particular movie are available for rent?
- Expected input: ISBN. Expected output: integer.
- What rating a particular reviewer gave to a particular movie?
- Expected input: critic_id and ISBN . Expected output: integer.
Update operations to be supported:
- Add a movie.
- Expected input: movie info fields (all). Expected output: success/fail.
- Remove a movie.
- Expected input: movie_id. Expected output: success/fail.
- Add a customer
- Expected input: customer info fields (all). Expected output: success/fail.
- Remove a customer.
- Expected input: membership_number. Expected output: success/fail.
- Modify customer's personal information such as address and phone number.
- Expected input: membership_number -> change fields . Expected output: success/fail.
- Suspend or activate a customer.
- Expected input: membership_number. Expected output: success/fail.
- Add a critic.
- Expected input: critic info fields (all). Expected output: success/fail.
- Remove a critic
- Expected input: critic_id. Expected output: success/fail.
- Modify personal information of a critic, such as his address and phone number.
- Expected input: critic_id -> change fields. Expected output: success/fail.
- Modify number of available copies of a movie as movies are being taken out and returned.
- Expected input: movie_id -> change copies_available. Expected output: success/fail.
Reports to be generated:
- List of movies sorted by rating in a particular caegory.
- Expected input: category name . Expected output: list of movies sorted by rating.
- List of best rated movies by a particular critic.
- Expected input: critic_id. Expected output: list of movies sorted in decreasing order by rating.
- List of worst rated movies by a particular critic.
- Expected input: critic_id. Expected output: list of movies sorted in increasing order by rating.
- Critic's rating comparing with average ratings for movies that critic rated.
- Expected input: critic_id. Expected output: list of movies showing critic's score and overall average.