Create a database design to manage and organize songs
Create a database design to manage and organize songs, albums, artists, videos, playlists and radio for an on-line store like Apple’s iTunes or Pandora.
Your database will have the following requirements
- Songs and videos are purchased by customers
- Songs have lyrics
- Songs are assigned to albums
- An artist creates and/or performs the song
- Songs and videos are assigned to at least one genre
- Music and video quality needs to be tracked
- Songs and videos have a release date
- Identify when a video or song was last played
- Songs and videos can be assigned to playlists
- Identify the frequency songs and videos are played
- Ratings can be private to the user or public and visible to all users
- Videos can have actors
Identify or create the following
- Relationship between entities
- Relationship type
- Attributes
- Cardinality
- Degree
- Domains
- Keys
- Relations Tuples
- Attributes
- Single value
- Multi value
- Composite
- Derived
- Keys
- Primary
- Foreign
- Reduce duplication
Additional attributes might be required to answer the questions. You must include at least five relations and at least four attributes for each relation.
Create an E-R diagram.
Convert the E-R diagram to relations in the format of: relation(attribute1, attribute2, attribute3). For instance, book(ISBN, title, author, price).
Generate relational algebra for the following queries. Use standard notation and appropriate relational algebra terminology. You may need to modify you E-R design to answer the questions below. Substitute Tina Brown with names of your own choosing.
- Identify albums with incomplete songs for customer Tina Brown. Display the artist, album, year, song and track.
- Identify all albums for customer Tina Brown. Display the artist, album, year, song and track.
- Identify highly rated Disco music. Display the song name, album, rating and last played date.
- Identify artists without new songs in the last five years. Display the artist name.
- Identify actors without new movies in the last five years. Display the actor name.
- Identify all songs assigned to the Exercise playlist for customer Tina Brown.
- Identify all highly rated Lady Gaga songs. Display the song name, length, price and release date.
- Identify all episodes of the television show Deception. Display the episode number, episode name, release date and price.
- Identify customers who purchased Lady Gaga’s new album today. Display the customer name and email address.
Formatting and submission
- The E-R design must match your relational algebra.
- Your project must include the question, relational algebra operations to answer the question and output from the relational algebra operations
- Each question and output must be formatted to display on a new page.
- Clearly label each question and answer.
- Your project must be submitted during your lecture and Blackboard on the due date. No projects will be accepted if left under my door, deposited in my mailbox or delivered to any other member of the department. Projects will not be accepted after the cutoff date.
- Your project must be typed.
- Use appropriate terminology.
- Diagrams must be illustrated using a drawing program like Microsoft Word, Visio or LucidChart. If you manually create diagrams, they must be neat and clear.
- All pages of your output must include your name, class, date and project number in the header of each page and attached using one staple in the top left corner.
- The first page of your project must include your name, the last four digits of your student id, class, the due date and the project number.
- Don't submit special plastic covers or binders for your project output.
Projects and examinations must represent your own work. Group projects and exams are not permitted. Although you are encouraged to ask other students for information, you should neither copy another student's project nor permit another student to see your work. You can be asked to perform specific procedures and operations in the presence of the instructor. A student who submits a project that is too similar to another student's work will receive a ZERO for the project. Additional penalties may be imposed. Students found guilty of any form of academic dishonesty such as plagiarism or cheating on an exam or computer project are subject to discipline, including, but not limited to, failure in the course and suspension or dismissal from the College.
