assn3_w24 (1)

pdf

School

University of Windsor *

*We aren’t endorsed by this school

Course

3150

Subject

Computer Science

Date

Apr 3, 2024

Type

pdf

Pages

Uploaded by AdmiralCamelMaster944

1 School: Computer Science Institution: University of Windsor Term: Winter 2024 Course: 03-60-315-1 : Database Management Systems Instructor: Dr. C. I. Ezeife Assignment # 3 : Total: 50 marks Handed Out:Thurs. Feb. 29, 2024; Due Thurs Mar. 21, 2024 Objective of Assignment : To test on knowledge and use of relational database query languages SQL and relational algebra for implementing relational databases. Scope : Assignment covers materials from Chapters 6, 7 and 8 of book discussed in class. Electronic Assignment Submission: Done through https://brightspace.uwindsor.ca/d2l/login . Marking Sheme : The mark for each of the questions is indicated beside each question. Academic Integrity Statement : Remember to submit only work that is yours and include the following confidentiality agreement and statement at the beginning of your assignment. CONFIDENTIALITY AGREEMENT & STATEMENT OF HONESTY I confirm that I will keep the content of this assignment/examination confidential. I confirm that I have not received any unauthorized assistance in preparing for or doing this assignment/examination. I confirm knowing that a mark of 0 may be assigned for copied work. ________________________________________ ________________________________________ Student Signature Student Name (please print) ________________________________________ ________________________________________ Student I.D. Number Date Marking Scheme : The mark for each question and sub question is shown with the question below. Place your solutions in tables where possible. For office Use only Question Mark 1 /15 2 /10 3 /5 4 /5 5 /5 6 /10 Total /50

2 CHAPTER 6: Basic SQL 1. Given a database state of the Windsor Music Records database shown in Figure 1.1, with schema shown in Figure 1.2. (Total for que 1 is 15 marks) Fig 1.1: An Example Database State of Windsor Music Records Database Musician SSN MNAME MADDR PHONE 111111111 Majid Peters 4747 Oakwood Dr 1113331112 222222222 Steven Markel 1231 Berry Ave 2221113332 333333333 Melanie Good 423 Walker Rd 1231235555 444444444 John Doe 5431 Hefty Lane 6751231231 Instrument INAME MUSICKEY Flute C-Flat Trumpet E-Flat Guitar A-Minor Drums B-Minor Album AID ATITLE COPYRIGHTDATE FORMAT 111 Recovery 2012-08-08 CD 222 Revival 2015-09-09 CD 333 Welcome 2014-04-03 CD Song STITLE AUTHOR Hello World 21 Savage Happy All 21 Savage Love Sosa Chief Keef Ball For Me Post Malone Play SSN INAME 111111111 Drums 111111111 Flute 111111111 Guitar 222222222 Trumpet 333333333 Guitar

3 Contains AID STITLE 111 Hello World 111 Happy All 222 Love Sosa 333 Ball For Me Performs SSN STITLE 111111111 Hello World 222222222 Love Sosa 333333333 Ball For Me 333333333 Hello World Produces SSN AID 111111111 111 222222222 222 333333333 333 Fig 1.2: Schema of the Windsor Music Records database of Figure 1.1 Musician SSN MName Maddress Phone Instrument Iname Musickey Album Aid atitle copyrightdate format Song stitle author Play SSN Iname

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

4 Contains Aid stitle Performs SSN stitle ProducesS SSN Aid i. List all the referential integrity constraints that should hold on the database schema? (2.5 marks) ii. Write appropriate SQL DDL statements to define the database with the integrity constraints and store in a text file called userid_musicschema.sql. Attach this file or also show it in your script file of (v) using more file.sql command before or after running sqlplus. Do the same for the files in (iii) and (iv). (2.5 marks) iii. To insert the data in the database tables, also write appropriate SQL DML instructions in a text file called userid_musicdata.sql. (2.5 marks) iv. To remove any inserted data and destroy all created tables in the music database, write appropriate SQL DML and DDL statements in a text file called userid_musicdroptable.sql to first delete all data in the tables and then drop the tables. (2.5 marks) v. Using Oracle Sqlplus, implement this database design by creating all the tables with the integrity constraints using the SQL DDL you defined in (ii) above. You can create all these SQL DDL for creating the 8 tables by running your .sql file at the SQL prompt with the command: @userid_musicschema.sql. After creating your tables successfully, you load your data with the .sql file you created in (iii) above by running @userid_musicdata.sql. If there are errors and you need to correct them, you might want to delete the tuples and drop the tables first using the .sql file you created in (iv) above as with @userid_musicdroptable.sql before re-creating the schema and re-loading the data.Then, using a script file, show the contents of all 8 tables in the database by selecting * from each of the tables and saving on script file called username_assn3que1.txt . You can do this using the following sequence of Unix/Linux commands after you have created the database and inserted data. (5 marks) (Note: remember to create the entity tables with primary keys before the relationship tables that reference them through foreign key attributes. When inserting data, do the same. If you need to delete the data and tables at any time, go in the reverse order (that is, delete the tuples that reference a primary key attribute tuple in another table, before deleting the parent primary keyed tuple)) >script username_assn3que1.txt >sqlplus <username> >password sqlplus> select * from Musician; //repeat this instruction for each table sqlplus> exit //to exit sqlplus exit // to exit and create script file

5 **Now attach the saved log of your session that is in username_assn3que1.txt with an inclusion in this script file of all the 3 .sql files in questions (ii), (iii) an (iv) or the attachment of those files as your solution . *** NOTE THAT TYPING YOUR DDL and DML instructions into a text file.sql does not constitute building a database and querying it. You need to run Oracle Sqlplus to load up those files in order to build your database and query them. This is the main thing this assignment is testing (knowledge of SQL from building databases and not creating a text file). Solution 1 (i) (mark: 2.5) We will write a referential integrity constraint as R.A --> S (or R.(X) --> T) whenever attribute A (or the set of attributes X) of relation R form a foreign key that references the primary key of relation S (or T). Solution 1 (ii): (mark: 2.5) One possible set of CREATE TABLE statements to define the database is given below given in the file userid_musicschema.sql is:

6 Solution 1 (iii): (mark: 2.5) One possible set of INSERT INTO TABLE statements to define the database is given below given in the file userid_musicdata.sql is:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

8 Solution 1 (iv): (mark: 2.5) One possible set of DELETE FROM TABLE statements and DROP TABLE statements to delete data from the database and drop the tables is given below given in the file userid_musicdroptable.sql is: 1 (v). (5 marks) for the script file showing correct interaction with Oracle Sqlplus creating and loading data in these 8 tables. 2. Specify the following 5 queries in SQL on the Music record database schema of Figure 1.1. (Total for que 2 is 10 marks)

9 i. List all your 5 queries in the table below first in SQL. (5 marks) ii. Implement the answering of your 5 queries in 2(i) using Sqlplus and the same database you created in question 1, providing your execution and answers to these questions in a script file called username_assn3que2.txt. (5 marks) (a) Retrieve the names and phone numbers of all producers of songs by Chief Keef. (b) Retrieve the copyrightdate and format of all albums that have more than one song. (c) For each song, retrieve its title, author, album, copyrightdate, and number of performances of the song. (d) Retrieve the title, Performing musician ’s name, and album title of each song. (e) Retrieve the instrument name, musickey and playing musician name of all instruments that are not played by more than one musician. Solution 2 (i): Queries (5 marks) and 2(ii) Results (5 marks) (a) Retrieve the names and phone numbers of all producers of songs by Chief Keef. (b) Retrieve the copyrightdate and format of all albums that have more than one song.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

10 (c ) For each song, retrieve its title, author, album, copyrightdate, and number of performances of the song. (d) Retrieve the title, Performing musician’s name , and album title of each song. (e) Retrieve the instrument name, musickey and playing musician name of all instruments that are not played by more than one musician.

11 2 (ii). (5 marks) distributed as: 2.5 marks for the script file showing correct interaction with Oracle Sqlplus posing these queries; and 2.5 marks for correctly posing the queries and retrieving correct results. 3. Write four SQL update statements to do the following updates on the database schema shown in Figure 1.2. Show the affected tables after update through script file in sqlplus and in a script file created as before and named username_assn3que3.txt. (5 marks) (Total for que 3 is 5 marks) (a) Insert a new musician <555555555, ‘Tintin Arial ’, ‘ 401 Sunset Ave ’ , 5192311010> in the database. (b) Change the address of musician ‘‘ John Doe ’ to 500 Airport Rd. (c) Insert a new album <444, ' Good Heart’ , ’14 -Feb-2 017’, ‘CD’ >. (d) Delete all performance records for songs whose name is ' Hello World '. Solution 3 (i): (5 marks) ( a) Insert a new musician <555555555, ‘Tintin Arial’, ‘401 Sunset Ave’, 5192311010> in the database. (b) Change the address of musician ‘‘ John Doe’ to 500 Airport Rd. (c) Insert a new album <444, 'Good Heart’, ’14 -Feb- 2017’, ‘CD’>. (d) Delete all performance records for songs whose name is ' Hello World '.

12 CHAPTER 7: More SQL: Complex Queries, Triggers, Views, and Schema Modification 4. (i) Write the following 2 queries in SQL on the database schema of Figure 1.2 using EXISTS or NOT EXISTS as appropriate. (2.5 marks) (ii) Implement the answering of your 2 queries in 4(i) using Sqlplus and the same database you created in question 1 and modified in earlier question with updates, deletes and inserts, providing your execution and answers to this question in a script file called username_assn3que4. (2.5 marks) (Total for que 4 is 5 marks) (a) Retrieve the album title and copyrightdate of all albums with 2 songs. (b) Retrieve the album title and copyrightdate of all albums that do not have 2 songs. Solution 4 (i): (2.5 marks) (a) Retrieve the album title and copyrightdate of all albums with 2 songs. (b) Retrieve the album title and copyrightdate of all albums that do not have 2 songs. 4 (ii). (2.5 marks) distributed as: 0.5 marks for the script file showing correct interaction with Oracle Sqlplus posing these queries; and 2 marks for the correctly posing the queries and retrieving correct results. 5 . In SQL, specify the following 3 queries on the COMPANY database of Figure 5.5 using the concept of nested queries and the concepts described in chapter 7. (Total for que 5 is 5 marks) a. Retrieve the names of all employees who work in the department that has the employee with the lowest salary among all employees. b. Retrieve the names of all employees whose supervisor’s supervisor has ' 333445555' for Ssn. c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

13 Solution 5: (5 marks) a) b) c) Figure 5.5 Schema diagram for the COMPANY relational database schema.

14 CHAPTER 8: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS 6. Specify the following 5 queries on the COMPANY relational database schema shown in Figure 5.5, using the relational operators discussed in chapter 8. Also show the result of each query as it would apply to the database state of Figure 5.6. (Total for que 6 is 10 marks) Some symbols for solving queries you may copy and reuse are:  , σ ,   Figure 5.6 One possible database state for the COMPANY relational database schema.

15 (i) List the names of employees who have a dependent with the same first name as themselves. (ii) Find the names of employees that are directly supervised by 'James Borg'. (iii) For each project, list the project name and the total hours per week (by all employees) spent on that project. (iv) Retrieve the names of employees who work on every project. (v) Retrieve the maximum salary of all female employees. Solution 6: (10 marks) In the relational algebra, as in other languages, it is possible to specify the same query in multiple ways. We give one possible solution for each query. We use the symbol s for SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION. (i) (ii) ) (iii)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

16 (iv) (v) Or (v)

assn3_w24 (1)

Related Documents