Microsoft Word - DbF22_Lab10_SUBmissionDetails.docxLab #10: Submission details Database: Library, database implementation Due date  The lab is due Sunday December 4 @ midnight. This means...

c


Microsoft Word - DbF22_Lab10_SUBmissionDetails.docx Lab #10: Submission details Database: Library, database implementation Due date  The lab is due Sunday December 4 @ midnight. This means that we expect that you have completed the lab.  We accept lab submissions without penalty until Sunday December 11 @ midnight. However, we consider this an extension to the due date: expect to be entirely responsible for completing the submission as further support after the due date may not be available.  After the extension date the assigned grade is zero. Submission instructions Any submission instructions below not followed result in a grade of zero.  Submission file formats o One PDF file with filename: lab10_dbsql_.pdf that includes, in that order:  Screenshot #1 (database): query exercises, as per lab instructions  Screenshot #2 (Python program): sample program run of all three query options and the quit option. o No ZIP files o No cover page  Submission location: In Brightspace of your CST8245 course theory section under Assignment labelled “Lab10”.  Submission checklist: o You read & followed the submission instructions. Make sure that you submit:  the completed lab  in the right file format  in the right course section  in the right course o You read the marking guide & verified that your code meets the requirements. Marking guide The lab is worth 8 marks.  [4] screenshot #1: queries  [4] screenshot #2: sample program run Microsoft Word - DbF22_Lab10.1_SQLqueries.docx Lab #10.1: Form SQL queries Overview Objective The objective of this lab is to form SQL queries to search the database. Outcomes  Create SQL queries against one or more tables.  Use psql output functionality to facilitate data management. Knowledge expected  Form SQL queries against one or more tables: o Display all records. o Display sorted records. o Display all records that match a condition. o Display all records that match a compound condition.  To use the psql \w sub-command to save SQL statements to a file.  To use the psql \g sub-command to save SQL query results to a file. SQL syntax  The semicolon at the end of the statement is part of the SQL syntax.  SQL commands are case-insensitive. Common syntax failures  Example of a regular prompt for a superuser: dabase_name=#  Example of a regular prompt for a non-superuser: dabase_name=>  Example of prompt indicating a missing closing bracket: dabase_name(#  Example of prompt indicating a missing closing semi-colon: dabase_name-#  To cancel a bad command use: \r. Scenario: Library logical design author book_author book_detail loanmember book_copy publisher author_idPK first_name last_name author_idFKPK phone isbnPK title return_date loan_idPK loan_date due_date (derived) member_idPK first_name last_name birth_country copy_idPK isbnFK acquisition_date isbnFKPK member_idFK copy_idFK rental_days Library ERD: Logical design, final publisher_idPK publisher contact publisher_idFK Note on loan: A separate primary_key (loan_id) rather than a composite key of the foreign keys (copy_code + member_id) enables the database to store multiple loans of the same book by the same member. Note on book_author: The primary key is a composite of both foreign keys. Note: If there is a discrepancy between the logical ERD and the lab description the ERD is authoritative. Section A – Form SQL queries on one table Note: The query examples and exercises given below may not reflect your data. Adjust where necessary to achieve successful queries. Syntax practice #1: Display all records  Select all rows o Syntax: SELECT * FROM table; o Example: SELECT * FROM book_detail;  Select specified attributes for all records. Note: To display only certain attributes, replace the ‘*’ with a comma-separated list of column names. o Syntax: SELECT column(s) FROM table; o Example: SELECT first_name, last_name FROM member; o Exercise: Display the book title and rental days of all records in the book_detail table. Syntax practice #2: Display all records, sorted  Sort all tuples: ORDER BY o Syntax: SELECT * FROM table ORDER BY column(s) [ASC|DESC]; o Example: SELECT * FROM member ORDER BY member_lname ASC, member_fname ASC; o Exercise: Display the book title and ISBN number of all rows in the book table in ascending order by book title. o Exercise: Display first and last name of all members in ascending order by member last name, and in descending order by member first name. Syntax practice #3: Display all records that match a condition using comparison operators. We are selecting all records that match a condition using the WHERE clause. The following comparison operators are accepted by the WHERE clause: =, !=, <,>, <=,>=  Select records in the book table using the WHERE clause with a comparison operator. o Syntax: SELECT * FROM table WHERE column = value; o Example: SELECT * FROM book_detail WHERE title = ‘Moby Dick’; o Exercise: Query the author table: Display the first and last name of all records where the birth country is the UK. o Exercise: Query the book_detail table: Display the title and rental days of all records where the number of rental days is not equal to 7 days. o Exercise: Query the book_copy table: Select all records where the acquisition date is less or equal than 2021-12-31 (or any other reasonable date). Syntax practice #4: Select all records that match a condition using alternative match criteria. We are selecting all records that match a condition using the WHERE clause with alternative match criteria.  BETWEEN AND (equivalent to >= and <=) o example: select * from book_copy where acquisition_date between ‘2010-12-31’ and ‘2020-12-31’;  like o example: select * from publisher where publisher like ‘book w%’; o example: select * from publisher where publisher like ‘%book%’;  is [not] null o example: select * from loan where return_date is not null;  in o example: select * from book_detail where title in (‘moby dick’, ‘emma’); syntax practice #5: select all records that match a compound condition.  and o example: select * from author where last_name = ‘austen’ and birth_country = ‘usa’;  or o example: select * from author where last_name = ‘austen’ or birth_country = ‘usa’; section b – save a query to a file the interactive psql utility has sub-commands to save sql statements, written at the psql prompt, and query results to a file in the current directory. syntax practice #6: save a query.  syntax: \w file # saves the last executed sql command to a file from the query buffer. note: to view the query buffer use \p.  exercise: o in psql display all books by title only. o save the query to a file named book_title.query using the psql sub- command \w. o verify that the query command has been saved in the file. fyi: save a query result. to save the query result of a previously executed sql query to a file use the psql subcommand: \g file. section c – sql queries: join multiple tables overview  there four types of joins: inner join, full join, left join, right join. the most common join type is the inner join.  tables are joined on a common column provided by the primary key in one table that is referenced as a foreign key in the related table.  the basic join syntax identifies the fields to be displayed, the tables to join, the common field used for the join, and an optional matching clause. join two tables syntax example for joining two tables select column(s) from table1 join table2 on table1.pkey = table2.fkey [where condition] [order by]; note: attributes (columns) may include the table name; used to disambiguate same column name in different tables. example: select author.last_name, member.last_name syntax practice #7: join two tables and sort records.  request: display all publishers for all books and sort by book title.  syntax: select title, publisher from book_detail join publisher on book_detail.publisher_id = publisher. publisher_id order by title; syntax practice #8: join two tables and filter result set based on condition.  request: display all book copies for the book ‘moby dick’.  syntax: select title, copy_id from book_detail join book_copy on book_detail.isbn = book_copy.isbn where title = ‘moby dick’; exercise #9: join two tables, filter and sort result set.  request: display all books published by ‘book worm’ and sort by book title. o which tables do you join? o which column(s) do you use to join the tables?  request: display title and acquisition date of all book copies & sort by book title and acquisition date. join tables with intersecting table syntax example for joining three tables that represent a many-to-many relationship select table1.column, table3.column from table1 join linktable on table1.primarykey = linktable.foreignkey join table2 on table2.primarykey = linktable.foreignkey exercise #10: join three tables that include an intersecting table, filter and sort result set.  request: display all books written by jane austen, sort by title. o join tables: display all books and their authors. o filter result set: add the condition to filter the result set using the where clause. o sort result set: lastly, sort the result set by title. note: there should be at least two records displayed. join multiple tables syntax practice #11: join more than three tables, sort result set  request: display member first and last name, book title, and loan date for all books on loan and sort by member last name.  syntax: select last_name, first_name, title, loan_date from book_detail join book_copy on book_detail.isbn = book_copy.isbn join loan on book_copy.copy_id = loan.copy_id join member on member.member_id = loan.member_id order by last_name; syntax practice #12: join more than three tables with derived attribute  request: display due dates for all current book loans.  syntax: select last_name, title, loan_date, loan_date + rental_days as due_date, return_date from book_detail join book_copy on book_detail.isbn = book_copy.isbn join loan on book_copy.copy_id = loan.copy_id join member on member.member_id = loan.member_id where return_date is null; exercise #13: join more than three tables, filter and sort result set  request: display all books by title, author first and last name, and publisher name; sort by title. prepare for submission  select two of the following three exercises for submission: o exercise #9: two queries with two tables each o exercise #10: one query with three tables o exercise #13: one query with four tables  write each query respecting the following formatting: o use ‘courier new’ for the font. o follow the spacing exactly as presented in this lab. o write sql keywords in all upper case and the values in all lower case. microsoft word - dbf22_lab10.2_pyprog.docx lab #10.2: execute sql statement using python database: library, database implementation scenario: library logical design author book_author book_detail loanmember book_copy publisher author_idpk first_name last_name author_idfkpk phone isbnpk title return_date loan_idpk loan_date due_date (derived) member_idpk first_name last_name birth_country copy_idpk isbnfk acquisition_date isbnfkpk member_idfk copy_idfk rental_days library erd: logical design, final publisher_idpk publisher contact publisher_idfk note on loan: a separate primary_key (loan_id) rather than a composite key of the foreign keys (copy_code + member_id) enables the database to store multiple loans of the same book by the same member. note on book_author: the primary key is a composite of both foreign keys. note: if there is a discrepancy between the logical erd and the lab description the erd is authoritative. background information displaying an sql query in python requires a two-step procedure:  execute the sql query.  display the result set. execute an sql query to execute an sql query the following steps have to be followed:  form the sql query and store it in a variable. use the variable name sql_query.  if a query is based on values provided by user input, store the values in a variable named sql_data.  execute the sql statement using the o="" example:="" select="" *="" from="" book_copy="" where="" acquisition_date="" between="" ‘2010-12-31’="" and="" ‘2020-12-31’;="" ="" like="" o="" example:="" select="" *="" from="" publisher="" where="" publisher="" like="" ‘book="" w%’;="" o="" example:="" select="" *="" from="" publisher="" where="" publisher="" like="" ‘%book%’;="" ="" is="" [not]="" null="" o="" example:="" select="" *="" from="" loan="" where="" return_date="" is="" not="" null;="" ="" in="" o="" example:="" select="" *="" from="" book_detail="" where="" title="" in="" (‘moby="" dick’,="" ‘emma’);="" syntax="" practice="" #5:="" select="" all="" records="" that="" match="" a="" compound="" condition.="" ="" and="" o="" example:="" select="" *="" from="" author="" where="" last_name="‘Austen’" and="" birth_country="‘USA’;" ="" or="" o="" example:="" select="" *="" from="" author="" where="" last_name="‘Austen’" or="" birth_country="‘USA’;" section="" b="" –="" save="" a="" query="" to="" a="" file="" the="" interactive="" psql="" utility="" has="" sub-commands="" to="" save="" sql="" statements,="" written="" at="" the="" psql="" prompt,="" and="" query="" results="" to="" a="" file="" in="" the="" current="" directory.="" syntax="" practice="" #6:="" save="" a="" query.="" ="" syntax:="" \w="" file="" #="" saves="" the="" last="" executed="" sql="" command="" to="" a="" file="" from="" the="" query="" buffer.="" note:="" to="" view="" the="" query="" buffer="" use="" \p.="" ="" exercise:="" o="" in="" psql="" display="" all="" books="" by="" title="" only.="" o="" save="" the="" query="" to="" a="" file="" named="" book_title.query="" using="" the="" psql="" sub-="" command="" \w.="" o="" verify="" that="" the="" query="" command="" has="" been="" saved="" in="" the="" file.="" fyi:="" save="" a="" query="" result.="" to="" save="" the="" query="" result="" of="" a="" previously="" executed="" sql="" query="" to="" a="" file="" use="" the="" psql="" subcommand:="" \g="" file.="" section="" c="" –="" sql="" queries:="" join="" multiple="" tables="" overview="" ="" there="" four="" types="" of="" joins:="" inner="" join,="" full="" join,="" left="" join,="" right="" join.="" the="" most="" common="" join="" type="" is="" the="" inner="" join.="" ="" tables="" are="" joined="" on="" a="" common="" column="" provided="" by="" the="" primary="" key="" in="" one="" table="" that="" is="" referenced="" as="" a="" foreign="" key="" in="" the="" related="" table.="" ="" the="" basic="" join="" syntax="" identifies="" the="" fields="" to="" be="" displayed,="" the="" tables="" to="" join,="" the="" common="" field="" used="" for="" the="" join,="" and="" an="" optional="" matching="" clause.="" join="" two="" tables="" syntax="" example="" for="" joining="" two="" tables="" select="" column(s)="" from="" table1="" join="" table2="" on="" table1.pkey="table2.fkey" [where="" condition]="" [order="" by];="" note:="" attributes="" (columns)="" may="" include="" the="" table="" name;="" used="" to="" disambiguate="" same="" column="" name="" in="" different="" tables.="" example:="" select="" author.last_name,="" member.last_name="" syntax="" practice="" #7:="" join="" two="" tables="" and="" sort="" records.="" ="" request:="" display="" all="" publishers="" for="" all="" books="" and="" sort="" by="" book="" title.="" ="" syntax:="" select="" title,="" publisher="" from="" book_detail="" join="" publisher="" on="" book_detail.publisher_id="publisher." publisher_id="" order="" by="" title;="" syntax="" practice="" #8:="" join="" two="" tables="" and="" filter="" result="" set="" based="" on="" condition.="" ="" request:="" display="" all="" book="" copies="" for="" the="" book="" ‘moby="" dick’.="" ="" syntax:="" select="" title,="" copy_id="" from="" book_detail="" join="" book_copy="" on="" book_detail.isbn="book_copy.isbn" where="" title="‘Moby" dick’;="" exercise="" #9:="" join="" two="" tables,="" filter="" and="" sort="" result="" set.="" ="" request:="" display="" all="" books="" published="" by="" ‘book="" worm’="" and="" sort="" by="" book="" title.="" o="" which="" tables="" do="" you="" join?="" o="" which="" column(s)="" do="" you="" use="" to="" join="" the="" tables?="" ="" request:="" display="" title="" and="" acquisition="" date="" of="" all="" book="" copies="" &="" sort="" by="" book="" title="" and="" acquisition="" date.="" join="" tables="" with="" intersecting="" table="" syntax="" example="" for="" joining="" three="" tables="" that="" represent="" a="" many-to-many="" relationship="" select="" table1.column,="" table3.column="" from="" table1="" join="" linktable="" on="" table1.primarykey="linktable.foreignkey" join="" table2="" on="" table2.primarykey="linktable.foreignkey" exercise="" #10:="" join="" three="" tables="" that="" include="" an="" intersecting="" table,="" filter="" and="" sort="" result="" set.="" ="" request:="" display="" all="" books="" written="" by="" jane="" austen,="" sort="" by="" title.="" o="" join="" tables:="" display="" all="" books="" and="" their="" authors.="" o="" filter="" result="" set:="" add="" the="" condition="" to="" filter="" the="" result="" set="" using="" the="" where="" clause.="" o="" sort="" result="" set:="" lastly,="" sort="" the="" result="" set="" by="" title.="" note:="" there="" should="" be="" at="" least="" two="" records="" displayed.="" join="" multiple="" tables="" syntax="" practice="" #11:="" join="" more="" than="" three="" tables,="" sort="" result="" set="" ="" request:="" display="" member="" first="" and="" last="" name,="" book="" title,="" and="" loan="" date="" for="" all="" books="" on="" loan="" and="" sort="" by="" member="" last="" name.="" ="" syntax:="" select="" last_name,="" first_name,="" title,="" loan_date="" from="" book_detail="" join="" book_copy="" on="" book_detail.isbn="book_copy.isbn" join="" loan="" on="" book_copy.copy_id="loan.copy_id" join="" member="" on="" member.member_id="loan.member_id" order="" by="" last_name;="" syntax="" practice="" #12:="" join="" more="" than="" three="" tables="" with="" derived="" attribute="" ="" request:="" display="" due="" dates="" for="" all="" current="" book="" loans.="" ="" syntax:="" select="" last_name,="" title,="" loan_date,="" loan_date="" +="" rental_days="" as="" due_date,="" return_date="" from="" book_detail="" join="" book_copy="" on="" book_detail.isbn="book_copy.isbn" join="" loan="" on="" book_copy.copy_id="loan.copy_id" join="" member="" on="" member.member_id="loan.member_id" where="" return_date="" is="" null;="" exercise="" #13:="" join="" more="" than="" three="" tables,="" filter="" and="" sort="" result="" set="" ="" request:="" display="" all="" books="" by="" title,="" author="" first="" and="" last="" name,="" and="" publisher="" name;="" sort="" by="" title.="" prepare="" for="" submission="" ="" select="" two="" of="" the="" following="" three="" exercises="" for="" submission:="" o="" exercise="" #9:="" two="" queries="" with="" two="" tables="" each="" o="" exercise="" #10:="" one="" query="" with="" three="" tables="" o="" exercise="" #13:="" one="" query="" with="" four="" tables="" ="" write="" each="" query="" respecting="" the="" following="" formatting:="" o="" use="" ‘courier="" new’="" for="" the="" font.="" o="" follow="" the="" spacing="" exactly="" as="" presented="" in="" this="" lab.="" o="" write="" sql="" keywords="" in="" all="" upper="" case="" and="" the="" values="" in="" all="" lower="" case.="" microsoft="" word="" -="" dbf22_lab10.2_pyprog.docx="" lab="" #10.2:="" execute="" sql="" statement="" using="" python="" database:="" library,="" database="" implementation="" scenario:="" library="" logical="" design="" author="" book_author="" book_detail="" loanmember="" book_copy="" publisher="" author_idpk="" first_name="" last_name="" author_idfkpk="" phone="" isbnpk="" title="" return_date="" loan_idpk="" loan_date="" due_date="" (derived)="" member_idpk="" first_name="" last_name="" birth_country="" copy_idpk="" isbnfk="" acquisition_date="" isbnfkpk="" member_idfk="" copy_idfk="" rental_days="" library="" erd:="" logical="" design,="" final="" publisher_idpk="" publisher="" contact="" publisher_idfk="" note="" on="" loan:="" a="" separate="" primary_key="" (loan_id)="" rather="" than="" a="" composite="" key="" of="" the="" foreign="" keys="" (copy_code="" +="" member_id)="" enables="" the="" database="" to="" store="" multiple="" loans="" of="" the="" same="" book="" by="" the="" same="" member.="" note="" on="" book_author:="" the="" primary="" key="" is="" a="" composite="" of="" both="" foreign="" keys.="" note:="" if="" there="" is="" a="" discrepancy="" between="" the="" logical="" erd="" and="" the="" lab="" description="" the="" erd="" is="" authoritative.="" background="" information="" displaying="" an="" sql="" query="" in="" python="" requires="" a="" two-step="" procedure:="" ="" execute="" the="" sql="" query.="" ="" display="" the="" result="" set.="" execute="" an="" sql="" query="" to="" execute="" an="" sql="" query="" the="" following="" steps="" have="" to="" be="" followed:="" ="" form="" the="" sql="" query="" and="" store="" it="" in="" a="" variable.="" use="" the="" variable="" name="" sql_query.="" ="" if="" a="" query="" is="" based="" on="" values="" provided="" by="" user="" input,="" store="" the="" values="" in="" a="" variable="" named="" sql_data.="" ="" execute="" the="" sql="" statement="" using="">
Dec 11, 2022
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here