is there someone who's good in database design? (2)
neny_easy
myflix.com_.docx( project deliverable 1&2 )
|
Entity Name |
Entity Description |
Attributes |
|
Movie Details |
This table has information about the movie; the information includes its genre, description, producer and director. It is an essential table in the database. |
Movie id(pk) Movie name Movie category Movie description Movie releasedate Movie producer |
|
User Ratings |
This table is concerned with information about the different ratings that clients give to movies, videos and directors as well. The table will contain the ID number of the rating, the star rating (out of five) and the comment on the rating. |
Rating id(pk) Rating star Rating comment Video id(fk) Movie id(fk) Director id(fk) User id (fk)
|
|
Director Details |
The database will also contain information about the directors of movies and TV shows, this information will be found in the directors details table. The directors will be identified by their age rather and their names. |
Director id(pk) Director name Director D.O.B Movie id(fk) Video id(fk) |
|
Video Details |
The video details table will contain information about videos. These videos include episodes of TV programs. The table will have such detail as the name of the video, its genre and its director as well. |
Video id(pk) Video name Video genre
|
|
Users Details |
This is a table that contains information about the users of myFlicks.com. Without this table the most essential activities of the company such as video ratings cannot be carried out since there has to be users to rate the videos. |
User id (pk) User name User gender User D.O.B
|
|
Friend Requests |
This table will include all the friend requests that have been made between users. The friend requests will have one of three statuses; accepted, pending or rejected. It is through this table that the friends will be able to see the statuses between them and their requested friends. |
Request id (pk) Request status User id (fk)
|
The data dictionary is not arranged in any particular arrangement.
Justification of attribute assignment
Most of the attributes that are used in the mentioned entities/tables have been selected specifically. While some of these attributes just tell about the entities, other attributes serve the purpose of relating one table with another one while other attributes are required for identifying the records in the tuple for the sake of quick retrieval of the records.
The movie details is one of the most essential database tables. The attributes given to the table are thus chosen to best identify the movies since the primary key is not the only item that users of the system use when searching for the movie. The first attribute, which is also the primary key, is movie id. This is the primary key of the table. The movie name attribute refers to the title of the movie. The producer is included as a field instead of a separate table because no further information about them might be required.
The second table; user rating has the primary keys of three tables as its foreign keys. These are Video id, Movie id, Director Id, User id all of which are necessary since this table has to take information from the user table about the video, movie and director. The attributes are used to further identify the rating information.
In the third table; director details, there is director id, the primary key as the first attribute. The movie id and the video id are also included that table since the director usually works for a movie or a video and this is the link. Next is the video details whose attributes are about the general information of the video.
User details is another important table in the database whose attributes are generally identification information. The last table is the friend request table which will handle such information as friend requests. The attributes include the request status which depends on whether the party being requested has rejected, accepted or still deciding on whether or not to accept the friendship request. The attribute user id is a foreign key from the user details table, the attribute is important for linking the information about the different users and the statuses of their requests.
The video table was necessary to handle TV shows. Since each TV episode was saved as a separated video, they had to be put in a separate table to be differentiated with the title episode. The reason for this is that creating a table for the T.V shows would mean creating a new table for each new TV series. This would be tiring in the long run and bad for the database functionality.
The friend request table was created to store the requests that friends had sent to their other friends. If a friend sends a request, the requests are rejected, accepted or left untouched. Whatever happens on the graphical user interface will depend on one of these three responses to the requests, it thus made sense to include the table in the database.
The directors table is an essential as it gives the information of the person who directed a particular movie or TV show. This was created as a separate table because to enable for the independent entry and removal of director records.
The user ratings were put in a different table because of the fact that the particular rating had other information that depended on it. For instance, a rating had a comment and a primary key alongside the title of the rating. This might be changed and unless it is on a table of its own the updates might be inconsistent. Another reason why it was not included as a column in the table is because a movie could have many different ratings.
Relationships
The user ratings table is one of the tables with the highest number of relationships due to the fact that the user comments on most of the other tables. The first relationship is between the user table and the friendship request table which is a one to many relationship with the user in the one side. The user table also has a relationship with the user ratings table. This relationship is necessary because the user gives ratings to videos, movies and directors. It is also a one to many relationship with the user details table one the one side.
A one to many relationship between the movies details and user ratings also exists where the movie details is on the one while the user ratings is on the many side. The movie details table is also has a relationship with the director details where the movie details table is on the one side and the director details is on the many side.
The video details has a relationship with the user ratings table where the video details video is on the one side and the user ratings on the many side. The reason for this is because the video can have many ratings at the same time. There is also a relationship between the video details and the director details table because a video can have a director.
|
Hazar
|
1 |
Movie_Details
PKMovieid
moviename
moviecategory
moviedescription
moviereleasedate
movieproducer
User_Ratings
PKRatingid
ratingstar
ratingcomment
FK1Directorid
FK2Userid
FK3Movieid
FK4videoid
Director_Details
PKDirectorid
directorname
directorDOB
FK1videoid
FK2Movieid
User_Details
PKUserid
username
usergender
userDOB
Video_Details
PKvideoid
videoname
videogenre
Friend Requests
PKRequestid
requeststatus
FK1Userid
Table
