Section $1$

Using SQL$,$ create tables according to the given schema shown in Figure $1.$ Note that this

section is exactly the same as the one in Assignment $4.$ You can use your Assignment $4$

directly, or modify your Assignment $4($if needed$).$

doctor $($dssn$,$ dname, specialty$)$

prescription$($dssn$,$ pre$\_$no$,$ pdate$)$ foreign key $($dssn$)$ references to doctor$($dssn$)$

ph$\_$company$($pname$,$ phone$)$

drug $($pname$,$ trade name, formula$)$ foreign key $($pname$)$ references to

ph$\_$company$($pname$)$

consist$\_$of $($ dssn$,$ pre$\_$no$,$ pname, trade$\_$name$)$ foreign key $($dssn$,$ pre$\_$no$)$ references

prescription$($dssn$,$ pre$\_$no$),$ foreign key $($pname$,$ trade$\_$name$)$ references drug$($panme$,$

trade$\_$name$)$

pharmacy $($pcy name, address, phone$)$

sell $($pharmacy$\_$name, pname, trade$\_$name, price$)$ foreign key $($pname$,$ trade$\_$name$)$

references drug $($pname$,$ trade$\_$name$),$ foreign key $($pharmacy$\_$name$)$ references

pharmacy$($pcy$\_$name$)$

Figure $1$ Schema Figure $2$ ER Diagram

Section $2$

Populate the database. Please check sections $3$ for details.

Section $3$

Use SQL to specify the following queries. When you populate the database, insert data such that at least two rows will be display as the result of running each query. $1)$ Retrieve the drugs' information $($trade name and formula$)$ which are manufactured by company "Pfizer". Order the result by trade name in descending order.

$2)$ List drug information $($Pharmaceutical company name, trade name, formula$)$ that has been prescribed by at least one doctor.

$3)$ Obtain all the prescription drugs $($trade name, company name and prescription date$)$ given by Doctor "Kate Elmasri".

$4)$ Retrieve all the drugs $($trade$\_$name, formula$)$ sold by "Walmart". $("$Walmart$"$ is pharmacy name$).$

$5)$ Retrieve number of type of drugs made by "Pfizer". $($Type is distinguished by "trade$\_$name"$).$

$6)$ Obtain information $($pharmacy name, address$)$ of pharmacy that sells "Ibuprofen" with the highest price among all pharmacies. $("$Ibuprofen$"$ is a trade name$)$

$7)$ Retrieve the pharmacies that sell more than $2$ types of drugs. List pharmacy name and number of types of drugs sold. $($Type is distinguished by "trade$\_$name". However, if two drugs with the same trade name are produced by two different pharmaceutical companies, they are considered as two different types of drugs.$)$

Section $4$

Specify the statements to drop all the tables. Pay attention to the order of the drop statements in order to drop everything successfully. Note that this section is exactly the same as the one in Assignment $4.$ You can use your assignment $4$ directly, or modify your assignment $4($if needed$).$

Submission

Submit the following files to Canvas. Do NOT zip them. Please double check that you submit a correct version and your submission status is "submitted".

You must test that the create, insert, drop statements and queries can be executed

successfully before your submission.

$1.*$Create table statements $($file name must be: $1\_$create$\_$table.txt$)$

I will copy everything from this file and execute it$.$ Make sure the tables are

listed in the correct order such that I can execute it without any error. For any table that cannot be created, you will lose $40$ points, even if the error caused by the incorrect order of creating table. You should have already handled this task in Assignment $4.$

You must submit a file $($DDL$\_$screenshot.pdf$)$ to prove that you have ran your create table statements, insert tuples, and the operations were successful. You can reuse the create table part of the screenshots of your Assignment $4,$ but you have to include it in this DDL$\_$screenshot.pdf file. I still need to run your create table statements to do grading. A demo using your Canvas files may be scheduled if there is any grading disagreement.

$2.$ Insert statements to populate database $($file name must be: $2\_$populate$\_$db$.$txt$)0$ will be given to each non$-$executable insert statement

$3.$ Queries $($file name must be: $3\_$query.txt$)$

Note that if the create table statements do not work properly, your query may not be executable even it is correct.

$0$ point will be given to each non$-$executable query, even if the query is specified correctly, but cannot be executed because of incorrect create table statements.

$4.*$Statements to drop all tables $($file name must be: $4\_$drop$\_$all.txt$)0$ will be given to each non$-$executable drop statement.

$5.$ An output file showing query results $($file name must be: $5\_$output.pdf$).$ In this file, for each query, you must:

a$.$ Include the number and query description $($e$.$g Query $1$: Retrieve the drugs' information $....)$

b$.$ Your SQL query $($e$.$g$.$ select... from...$)$

c$.$ The result of running this query $($screen shots$).$

Note: $\backslash (1^\{*\}\backslash )$ and $\backslash (4^\{*\}\backslash )$ can be the same as the ones that you submitted for Assignment $4.$