Spring2024_HW1

CSE 412 Spring 2024 WinRDBI ASSIGNMENT #1 Relational Algebra This is the first of three assignments that will allow you to execute query languages of the relational model. This first assignment focuses specifically on queries in relational algebra. Using the Music Rental Agency Enterprise on the following pages, you are to: 1. Load the music1.rdb file in WinRDBI. This data file is located in the folder for the first assignment on myASU. 2. Develop relational algebra solutions for the five queries specified in this assignment. Use descriptive intermediate table and attribute names. See the resources below for additional information on WinRDBI: 1. The WinRDBI Web ( https://winrdbi.asu.edu/ ) provides online demos and sample queries. 2. Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001. On the due date Thursday, Feb. 15 before midnight, you will turn in the following: 1. Please submit your answers in a single (.doc, .docx or .pdf) document through Course Canvas page Assignments > WinRDBI Assignment 1 . To avoid name clashes please rename your .alg file to fname_lname_assignment1.doc or fname_lname_assignment1.docx or fname_lname_assignment1.pdf. 2. In your submission file please include the following: For each query (1-5), (a) please repeat the English query, (b) please show the corresponding formal relational algebra query that could in- clude several intermediate tables and a final query named QUERY1 for the first query, for exam- ple, and (c) please show a screen capture of the matching results for each query from WinRDBI. The assignment submission on Canvas will be disabled at midnight on the due date. Solutions will be published in the WinRDBI Assignment Folder after the due date. REMINDER: THIS IS AN INDIVIDUAL ASSIGNMENT! CSE 412 Spring 2024 1 WinRDBI: Relational Algebra

Quality-Based Assessment The following table is part of an approach to quality-based assessment of your homework assignment, itemizing the Expected/Required features of ALL WinRDBI homework assignments. Points will be deducted for each negative answer. Each query will also be rated for correctness and clarity. YES NO EXPECTED/REQUIRED FEATURES The assignment was submitted electronically on myASU by the due date & time? The name appears as a comment line at the beginning of the query file? Hardcopy of queries and results submitted for assessment? Queries handed in are readable (not truncated)? All queries and variables have descriptive names? Queries are at least documented with the query statement and output schema? Tables representing identified query results are clearly indicated? Query results submitted for assessment were run against the published test data? Test Data Disclaimer We are providing you with an instance of the Music Agency Enterprise on which you can run your queries. The test data has been developed as a grading aid. The printed results that you submit must use the published test data. The goal of the test data is to provide a rich environment in which to test query solutions and to provide an initial metric for grading. However, your query solutions will be graded on the correct logic – not the correct answer. Since the test data may not contain cases that illustrate every possible incorrect approach to answering a query, it may be possible for you to get the same result as our published solutions even when your logic is not correct . Feel free to modify the test data for purposes of testing your solutions; however, the printout of result data handed in for assessment must use the published test data . CSE 412 Spring 2024 2 WinRDBI: Relational Algebra

ER Diagram for the Music Rental Agency Enterprise CSE 412 Spring 2024 4 WinRDBI: Relational Algebra

The Music Rental Agency Database contains the following entities and relationships. Entity Summary Table Entity Attribute Type Description CD cdCode C A code that uniquely identifies each CD. cdTitle C The title of the CD. numberSold N The number of CDs that have been sold. year N The year the CD is published. AvailableCopy cdCode C A code that uniquely identifies each CD. seqNum N A sequence number for a copy of CD. Client clientID C An ID that uniquely identifies a client. firstName C The first name of the client. lastName C The last name of the client. phone C The phone number of the client. address C The address of the client. Song songCode C A code that uniquely identifies each song. songTitle C The title of the song. MusicalGroup groupCode C A code that uniquely identifies a musical group. groupName C The name of the musical group. Artist artistID C An artist ID that uniquely identifies each artist instance in the music agency database. firstName C The first name of the artist. lastName C The last name of the artist. yearBorn N The year the artist was born. Year year N A valid year between 1900 and 2004. CSE 412 Spring 2024 5 WinRDBI: Relational Algebra

WinRDBI Music Agency Relational Database Schema client(clientID , firstName, lastName, phone, address) cd(cdCode , cdTitle, numberSold, year, groupCode) availableCopy(cdCode, seqNum ) rentedBy(cdCode, seqNum, clientID, fromDate, endDate, pricePerDay) song(songCode , songTitle) musicalGroup(groupCode , groupName) artist(artistID , firstName, lastName, yearBorn) topCDs(cdCode, year , rating) composedOf (cdCode, songCode , trackNumber) topSongs(songCode, year , rating) member(groupCode, artistID, fromDate , toDate) writtenBy(songCode, artistID ) Summary of Primary and Foreign Keys Relation Primary Key Foreign Key References Cd cdCode groupCode musicalGroup.groupCode availableCopy cdCode, seqNum cdCode cd.cdCode Client clientID rentedBy cdCode, seqNum, clientID, fromDate, toDate cdCode, seqNum availableCopy.cdCode, availableCopy.seqNum clientId client.clientID songTitle songCode - - musicalGroup groupCode - - Artist artistID - - topCDs cdCode, year cdCode cd.cdCode composedOf cdCode, songTitle cdCode cd.cdCode songCode song.songCode CSE 412 Spring 2024 7 WinRDBI: Relational Algebra

topSongs songCode, year songCode song.songCode Member groupCode, artistID, fromDate groupCode musicalGroup.groupCode artistID artist.artistID writtenBy songCode, artistID songCode song.songCode artistID artist.artistID Queries The output schema for the query is given in parentheses following the query specification. 1. List the CD title, the clients who have rented the CDs, and number of CDs sold for CDs with a top “5” rating in 2003 (i.e. ratings vary between 1<= topCDs.rating <= 5). (cdCode, cdTitle, clientID, firstName, lastName, numberSold) 2. Which songs appeared on the best-selling CDs (i.e. max number of CDs sold) published in 2003? (songCode, songTitle) 3. Find the list musical groups such that every client in Detroit rented at least one of their CD’s. (This is a division query). (groupCode, groupName) 4. List names of artists that are song writers and have never had a song received a top 5 rating. (artistID, firstName, lastName) 5. List the artists that have been a member of more than one group. (artistID, firstName, lastName) CSE 412 Spring 2024 8 WinRDBI: Relational Algebra