is there anyone good in database management? 3
neny_easy
it_project_1_-_creating_a_database_en.docxCreating database
4
Creating a database MyFlicks.com
IST 365
December 1, 2014 The database will store information about a catalogue of movies. Information to be stored about each movie includes their titles, description, genre, artists and directors. Each artist and director may be involved in several movies. Movies will have a title and a running time (in minutes). Actors will have names associated with them and it should be possible to search the database by artist names. The same case will apply to directors and producers. Finally, in order to search the catalogue by title, each movie will have a number of keywords, which are the words in the title of the movie.
Identifying Entities
The first step is to identify entities, attributes, and relationships from the problem description. It is also necessary to identify the cardinality ratios of the relationships found. For example, Movie and Director are two entities from the description above. Director has the attributes First Name and Last Name. There is also a relationship between directors and movies in that directors produce movies. This relationship is one-to-many (one director directs many movies).
Drawing the ERD Diagram.
Having identified all the entities, it will be crucial to draw an ERD diagram which will give a useful picture of the layout of the database. The partial ERD below is based on the information above on the different fields of the database.
Below is a list of the tables and their attributes that will be present in the database
|
DATA DICTIONARY |
||||||||
|
TABLE NAME |
ATTRIBUTE NAME |
CONTENTS |
TYPE |
FORMAT |
RANGE |
REQUIRED |
PK/FK |
FK REFERENCED TABLE |
|
MYFLICKS |
USER MOVIE_REVIEW TVEPISODE_REVIEW
|
USER DETAILS MOVIE DETAILS TV EPISODE DETAILS |
|
|
|
|
FK FK FK |
USER ACCOUNT MOVIE TV EPISODE |
|
USER ACCOUNT |
USER_ID USER_FIRSTNAME USER_LASTNAME |
USER_ID USERS FIRST NAME USERS LAST NAME |
CHAR(5) VCHAR(20) VCHAR(20) |
99999 XXXXXX XXXXXX |
10000-99999 |
Y Y Y |
PK |
|
|
MOVIE |
MOVIE_ID MOVIE_TITLE MOVIE_DESCRIPTION MOVIE_CATEGORY MOVIE_RUNNING TIME MOVIE_RELEASE DATE MOVIE_DIRECTOR MOVIE_PRODUCER
|
MOVIE ID TITLE OF THE MOVIE BRIEF DESCRIPTION OF MOVIE CARTEGORY OF MOVIE DURATION OF MOVIE RELEASE DATE MOVIE DIRECTOR
|
CHAR(5) VCHAR (20) VCHAR(100) VCHAR(20) TIME DATE VCHAR(20)
|
99999 XXXXXXX XXXXXX XXXXXX HH-MM-SS DD-MM-YYYY XXXXXXX
|
10000-99999 |
Y Y Y Y |
PK
FK
|
PRODUCER
|
|
TV EPISODE |
TVEPISODE_ID TVEPISODE_TITLE TVEPISODE_DESCRIPTION TVEPISODE_CATEGORY TEVEPISODE_RUNNING TIME TVEPISODE_RELEASE DATE TVEPISODE_PRODUCER TVEPISODE_GENRE TVEPISODE_RATING TVEPISODE_DIRECTOR |
TVEPISODE ID TITLE OF THE TVEPISODE BRIEF DESCRIPTION OF TVEPISODE CARTEGORY OF TVEPISODE DURATION OF TVEPISODE RELEASE DATE TVEPISODE PRODUCER GENRE OF TVEPISODE RATING OF TV EPISODE |
CHAR(5) VCHAR (20) VCHAR(100) VCHAR(20) TIME DATE VCHAR(20) VCHAR(20) CHAR(1) |
99999 XXXXXXX XXXXXX XXXXXX HH-MM-SS DD-MM-YYYY XXXXXXX XXXXXXX 9 |
10000-99999 |
Y Y Y Y |
PK
FK FK FR |
PRODUCER GENRE RATING |
|
DIRECTOR |
DIRECTOR_ID DIRECTOR_FIRSTNAME DIRECTOR_LASTNAME |
DIRECTORS ID DIRECTORS FIRST NAME DIRECTORS LAST NAME |
CHR(5) VCHAR(20) VCHAR(20) |
99999 XXXXXX XXXXXX |
|
Y Y Y |
PK |
|
|
RATING |
MOVIE_ID RATING_ID RATE TIME RATE_MOVIE RATE_TVEPISODE |
RATING OF MOVIE OR TVEPISODE TIME OF RATING RATED MOVIE RATED TV EPISODE |
CHAR(1) TIME
|
99999 HH-MM-SS |
10000-99999 |
Y |
PK
FK FK |
MOVIE TV EPISODE |
