Complete ER for models and write queries

Assignment 

Question 1

The ​ ​Relational ​ ​model

Consider ​ ​the ​ ​below ​ ​relational ​ ​database ​ ​schema ​ ​for ​ ​the ​ ​MX ​ ​department ​ ​store:
Shop(shopNo, ​ ​street_addr, ​ ​suburb, ​ ​postcode, ​ ​phoneNo)
Item(itemNo, ​ ​description, ​ ​price, ​ ​shopNo, ​ ​stock)
Order(orderNo, ​ ​itemNo, ​ ​quantity, ​ ​supplier, ​ ​supplier_contactNo)
MX has many shops throughout Australia. The following Functional Dependencies (FDs)
are ​ ​collected ​ ​at ​ ​the ​ ​database ​ ​design ​ ​stage:
shopNo​ ​→​ ​street_addr, ​ ​suburb, ​ ​postcode, ​ ​phoneNo
suburb ​ ​→​ ​postcode
itemNo​ ​→​ ​description, ​ ​price
shopNo, ​ ​itemNo​ ​→ ​ ​stock, ​ ​price
orderNo, ​ ​itemNo​ ​→​ ​quantity
itemNo​ ​→​ ​supplier, ​ ​supplier-contactNo
supplier ​ ​→​ ​supplier_contactNo
​ ​

Answer ​ ​questions:
1.1) ​ ​Give ​ ​{itemNo, ​ ​shopNo} ​+ ​.
1.2) Give the candidate keys for each of the givenrelations ​ ​Shop, ​ ​Item ​ ​and ​ ​Order. ​ ​Show ​ ​your ​ ​workings ​ ​of ​ ​using ​ ​FDs.
1.3) ​Compute ​ ​the ​ ​minimal ​ ​basis ​ ​for ​ ​the ​ ​given ​ ​FDs.
1.4) Use the minimal basis for FDs from question 1.3) to explain ifShop, ​ ​Item ​ ​and ​ ​Order ​ ​are ​ ​in ​ ​BCNF.
1.5) If any of the relations Shop, Item or Order is not in BCNF,decompose it into BCNF/3NF. Give the relations after decomposition and specifythe ​ ​primary ​ ​key ​ ​and ​ ​any ​ ​foreign ​ ​key ​ ​for ​ ​each ​ ​relation.

Question ​ ​2

SQL

The ​ ​relational ​ ​schema ​ ​for ​ ​the ​ ​Academics ​ ​database ​ ​is ​ ​as ​ ​follows:
DEPARTMENT( ​deptnum​, ​ ​descrip, ​ ​instname, ​ ​deptname, ​ ​state, ​ ​postcode)
ACADEMIC( ​acnum​, ​ ​deptnum*, ​ ​famname, ​ ​givename, ​ ​initials, ​ ​title)
PAPER( ​panum​, ​ ​title)
AUTHOR( ​panum*, ​ ​acnum* ​)
FIELD( ​fieldnum​, ​ ​id, ​ ​title)
INTEREST( ​fieldnum*, ​ ​acnum* ​, ​ ​descrip)

Some ​ ​notes ​ ​on​ ​the ​ ​Academics ​ ​database:
● ​An academic department belongs to one institution (instname) and often has many
academics. ​ ​An ​ ​academic ​ ​only ​ ​works ​ ​for ​ ​one ​ ​department.
● ​Research papers (PAPER) are often authored by several academics, and of course an
academic ​ ​often ​ ​writes ​ ​several ​ ​papers ​ ​(AUTHOR).
● ​A research field (FIELD) often attracts many academics and an academic can have
interest ​ ​in ​ ​several ​ ​research ​ ​fields ​ ​(INTEREST).
Each ​ ​component ​ ​of ​ ​an ​ ​SQL ​ ​statement ​ ​must ​ ​be ​ ​on​ ​a ​ ​separate ​ ​line. ​ ​For ​ ​example,
SELECT ​ ​*
FROM ​ ​Department
WHERE ​ ​State=’VIC’;
Do not include the result of the query or the script used to create the tables. Your query
should ​ ​not ​ ​produce ​ ​duplicates ​ ​in ​ ​output ​ ​but ​ ​use ​ ​DISTINCT ​ ​only ​ ​if ​ ​necessary.

2.1) List ​ ​the ​ ​deptnum​ ​and ​ ​total ​ ​​ ​number ​ ​of ​ ​academics ​ ​for ​ ​departments ​ ​with
postcode ​ ​in ​ ​the ​ ​range ​ ​3000..3999,​ ​in ​ ​descending ​ ​order ​ ​of ​ ​total ​ ​number ​ ​of
academics ​ ​for ​ ​each ​ ​department.
2.2) Are​ ​there ​ ​any ​ ​academics ​ ​who ​ ​have ​ ​not ​ ​written ​ ​any ​ ​papers? ​ ​List ​ ​the ​ ​acnum,
givename​ ​and ​ ​famname​ ​of ​ ​these ​ ​academics. ​ ​​ ​Your ​ ​query ​ ​must ​ ​contain ​ ​a ​ ​subquery.
2.3) Find​ ​the ​ ​departments ​ ​that ​ ​have ​ ​more ​ ​than ​ ​10​ ​academics. ​ ​List ​ ​the ​ ​deptnum,
instname​ ​and ​ ​deptname​ ​of ​ ​these ​ ​departments.
2.4) Find​ ​the ​ ​department ​ ​in ​ ​Victoria ​ ​that ​ ​is ​ ​the ​ ​strongest ​ ​in ​ ​”Software ​ ​Engineering”
(field.title) ​ ​research, ​ ​that ​ ​is ​ ​the ​ ​department ​ ​has ​ ​the ​ ​largest ​ ​of ​ ​academics ​ ​interested
in ​ ​”Software ​ ​Engineering” ​ ​research. ​ ​Output ​ ​the ​ ​deptnum, ​ ​deptname​ ​and ​ ​instname
of ​ ​the ​ ​department. ​ ​You ​ ​must ​ ​NOT ​ ​use ​ ​the ​ ​MAX ​ ​aggregate ​ ​function.
2.5) Give​ ​the ​ ​total ​ ​number ​ ​of ​ ​academics ​ ​that ​ ​do​ ​not ​ ​have ​ ​any ​ ​research ​ ​interests.
2.6) Are​ ​there ​ ​any ​ ​research ​ ​fields ​ ​where ​ ​less ​ ​than ​ ​10,​ ​including ​ ​zero, ​ ​academics
are ​ ​interested ​ ​in. ​ ​​ ​List ​ ​the ​ ​fieldnum, ​ ​id ​ ​and ​ ​number ​ ​of ​ ​interested ​ ​academics ​ ​for
these ​ ​research ​ ​fields.
2.7) Find​ ​the ​ ​papers ​ ​authored ​ ​by​ ​academics ​ ​from ​ ​departments ​ ​located ​ ​in ​ ​Victoria.
List ​ ​the ​ ​panum​ ​and ​ ​title ​ ​of ​ ​these ​ ​papers. ​ ​You ​ ​must ​ ​use ​ ​the ​ ​NATURAL ​ ​JOIN ​ ​and
EXISTS​ ​​ ​operators.
2.8) Write ​ ​a ​ ​CREATE ​ ​VIEW ​ ​statement ​ ​to ​ ​define ​ ​a ​ ​view ​ ​PAPER_VIEW ​ ​that ​ ​has
three ​ ​columns ​ ​to ​ ​keep ​ ​data ​ ​for ​ ​each ​ ​paper: ​ ​panum​ ​(the ​ ​paper ​ ​number), ​ ​​ ​n_author​ ​(
the ​ ​total ​ ​number ​ ​of ​ ​authors) ​ ​and ​ ​title ​ ​(the ​ ​paper ​ ​title).
2.9) The ​ ​ID ​ ​of ​ ​research ​ ​fields ​ ​has ​ ​three ​ ​parts ​ ​separated ​ ​by​ ​two ​ ​periods. ​ ​Consider ​ ​aquery ​ ​to ​ ​find ​ ​details ​ ​of ​ ​research ​ ​fields ​ ​where ​ ​the ​ ​first ​ ​two ​ ​parts ​ ​of ​ ​the ​ ​ID ​ ​are ​ ​Dand ​ ​2​ ​and ​ ​the ​ ​last ​ ​part ​ ​is ​ ​one ​ ​character ​ ​(digit). ​ ​​ ​IDs ​ ​like ​ ​D.2.1 ​ ​and ​ ​D.2.3 ​ ​are ​ ​in ​ ​the
query ​ ​result ​ ​whereas ​ ​IDs ​ ​like ​ ​D.2.12 ​ ​or ​ ​D.2.15 ​ ​are ​ ​not. ​ ​The ​ ​SQL ​ ​query ​ ​givenbelow ​ ​does ​ ​not ​ ​return ​ ​the ​ ​correct ​ ​result. ​ ​Explain ​ ​the ​ ​reason ​ ​why ​ ​it ​ ​does ​ ​not ​ ​returnthe ​ ​correct ​ ​result ​ ​and ​ ​give ​ ​the ​ ​correct ​ ​SQL ​ ​query.
select ​ ​*from ​ ​fieldwhere ​ ​ID ​ ​like ​ ​’B.1._’;
2.10) The ​ ​following ​ ​SQL ​ ​query ​ ​is ​ ​intended ​ ​to ​ ​find ​ ​the ​ ​departments ​ ​with ​ ​the ​ ​largest
total ​ ​number ​ ​of ​ ​academics ​ ​and ​ ​print ​ ​their ​ ​department ​ ​num, ​ ​deptname​ ​andinstname, ​ ​but ​ ​it ​ ​has ​ ​syntax ​ ​errors. ​ ​Identify ​ ​the ​ ​syntax ​ ​errors ​ ​in ​ ​the ​ ​query ​ ​and ​ ​givethe ​ ​correct ​ ​SQL ​ ​query.
​ ​​ ​​ ​​ ​​ ​select​ ​deptnum, ​ ​deptname, ​ ​instname, ​ ​count(acnum)​from ​ ​department, ​ ​academic
​ ​​ ​​ ​​ ​​ ​where ​ ​academic.deptnum​ ​=department.deptnum​group ​ ​by ​ ​deptnum​​ ​having ​max(count(acnum));

Question ​ ​3

​ER ​ ​model ​
An ​ ​ER ​ ​diagram ​ ​for ​ ​the ​ ​Beta ​ ​Health ​ ​Physiotherapy ​ ​Centre ​ ​is ​ ​given ​ ​below. ​ ​The ​ ​names ​ ​of entities, ​ ​relationships ​ ​and ​ ​attributes ​ ​express ​ ​the ​ ​meanings ​ ​clearly.

Some ​ ​limitations ​ ​of ​ ​the ​ ​given ​ ​ER ​ ​model ​ ​are ​ ​identified ​ ​and ​ ​listed ​ ​below, ​ ​together ​ ​with
additional ​ ​information ​ ​for ​ ​refining ​ ​and ​ ​extending ​ ​the ​ ​given ​ ​ER ​ ​diagram.
● In​ ​the ​ ​given ​ ​ER ​ ​model, ​ ​the ​ ​“See” ​ ​relationship ​ ​only ​ ​contains ​ ​simple ​ ​information
on​ ​patients ​ ​seeing ​ ​doctors. ​ ​Extend ​ ​this ​ ​part ​ ​to ​ ​include ​ ​details ​ ​on​ ​therapistconsultation ​ ​sessions: ​ ​​ ​A ​ ​therapist ​ ​has ​ ​consultation ​ ​sessions ​ ​at ​ ​specific ​ ​date ​ ​andtime ​ ​and ​ ​in ​ ​a ​ ​room; ​ ​therapists ​ ​can ​ ​have ​ ​consultation ​ ​sessions ​ ​at ​ ​the ​ ​same ​ ​time ​ ​butin ​ ​different ​ ​rooms. ​ ​Consultation ​ ​sessions ​ ​can ​ ​be ​ ​short ​ ​(15 ​ ​minutes) ​ ​or ​ ​long ​ ​(30minutes). ​ ​​ ​A ​ ​patient ​ ​can ​ ​book​ ​several ​ ​consultation ​ ​sessions ​ ​with ​ ​differenttherapists ​ ​but ​ ​each ​ ​consultation ​ ​session ​ ​is ​ ​for ​ ​one ​ ​patient.
● Treatments like “back massage” are often suggested by therapists afterconsultation sessions.Treatments have a unique treatment code and a name, andcomprises multiple treatment sessions (details given next). A therapistconsultation session results in only one treatment and a treatment may be used forseveral ​ ​consultation ​ ​sessions.
● A treatment comprises multiple treatment sessions numbered sequentially.Equipments need to be booked for treatment sessions. A treatment session usesone equipment, and the date, time and duration for using the equipment should berecorded. ​ ​An ​ ​equipment​ ​can ​ ​be ​ ​used ​ ​for ​ ​several ​ ​treatment ​ ​sessions.
Give the complete Entity Relationship (ER) diagram for the database of the Beta HealthPhysiotherapy ​ ​Centre, ​ ​including ​ ​the ​ ​original ​ ​and ​ ​additional ​ ​information ​ ​given ​ ​above.

Question ​ ​4

​ER ​ ​to ​ ​relational ​ ​schema ​ ​mapping

Consider ​ ​the ​ ​Omeria​ ​Hotel ​ ​database ​ ​ER ​ ​diagram ​ ​as ​ ​shown ​ ​in ​ ​Figure ​ ​1. 4.1) Give the FDs for the constraints in the ER diagram. You should not ​ ​include ​ ​trivial ​ ​or ​ ​redundant ​ ​FDs.
4.2) Map the ER diagram to a relational database schema following the ER to a relational database schema mapping rules and indicate the primary key (underline) ​ ​and ​ ​any ​ ​foreign ​ ​keys ​ ​(asterisk) ​ ​in ​ ​each ​ ​relation.

 Solution

Question 1

Consider the below relational database schema for the MX department store:

Shop (shopNo, street_addr, suburb, postcode, phoneNo)

Item (itemNo, description, price, shopNo, stock)

Order (orderNo, itemNo, quantity, supplier, supplier_contactNo)

MX has many shops throughout Australia. The following Functional Dependencies (FDs) are collected at the database design stage:

shopNo → street_addr, suburb, postcode, phoneNo

suburb → postcode

itemNo → description, price

shopNo, itemNo → stock, price

orderNo, itemNo → quantity

itemNo → supplier, supplier-contactNo

supplier → supplier_contactNo

Question 1.1:

Give {itemNo, shopNo}+ = { temNo, shopNo, description, price, street_addr, suburb, postcode, phoneNo, stock, supplier, supplier-contactNo }

Question 1.2:

Give the candidate keys for each of the given relations Shop, Item and Order. Show your workings of using FDs.

Shop (shopNo, street_addr, suburb, postcode, phoneNo)

shopNo → street_addr, suburb, postcode, phoneNo

suburb → postcode

{shopNo}+ = {shopNo, suburb, street_addr, suburb, postcode, phoneNo }

{suburb}+ = {suburb, postcode}

{shopNo, suburb}+ = {shopNo, suburb, street_addr, suburb, postcode, phoneNo }

{shopNo, suburb} is a subset of all attributes and since it’s a minimum, it becomes the Shop’s candidate key

Item (itemNo, description, price, shopNo, stock)

itemNo → description, price

shopNo, itemNo → stock, price

{itemNo}+ = {itemNo, description, price}

{shopNo, itemNo}+ = {shopNo, itemNo, stock, price, description}

{shopNo, itemNo} is a subset of all attributes and since it’s a minimum, it becomes the Shop’s candidate key 

Order (orderNo, itemNo, quantity, supplier, supplier_contactNo)

orderNo, itemNo → quantity

itemNo → supplier, supplier-contactNo

supplier → supplier_contactNo

{orderNo, itemNo}+ = {orderNo, itemNo, quantity}

{itemNo}+ = {itemNo, supplier, supplier-contactNo}

{orderNo, itemNo}+ = {orderNo, itemNo, quantity, supplier, supplier_contactNo}

{supplier}+ = {supplier, supplier_contactNo}

{orderNo, itemNo, supplier}+ = {orderNo, itemNo, quantity, supplier, supplier_contactNo}

{orderNo, itemNo, supplier} is a subset of all attributes and since it’s a minimum, it becomes the Shop’s candidate key

Question 1.3: Compute the minimal basis for the given FDs.

  1. If:

shopNo → street_addr

shopNo → postcode

shopNo → suburb

shopNo → phoneNo

And:

suburb → postcode

Therefore:

shopNo → street_addr, suburb, phoneNo

suburb → postcode

Using the same logic:

itemNo → description, price

Hence:

i)itemNo → description

ii)itemNo → price

If ii) given:

shopNo, itemNo → stock, price

Then:

shopNo → stock

Question 1.4

Shop (shopNo, street_addr, suburb, postcode, phoneNo)

ItemsInShop(ShopNo,ItemNo)

Item (itemNo, description, price, stock)

Order (orderNo, itemNo, quantity, supplier, supplier_contactNo)

Question 2.1

SELECT dbo.DEPARTMENT.deptnum, COUNT(dbo.ACADEMIC.acnum) AS Number, dbo.DEPARTMENT.postcode

FROM     dbo.DEPARTMENT INNER JOIN

dbo.ACADEMIC ON dbo.DEPARTMENT.deptnum = dbo.ACADEMIC.deptnum

GROUP BY dbo.DEPARTMENT.deptnum, dbo.DEPARTMENT.postcode

HAVING (dbo.DEPARTMENT.postcode> ‘3000’) AND (dbo.DEPARTMENT.postcode< ‘4000’)

Question 2.2

SELECTacnum

FROMACADEMIC

whereNOTEXISTS(

SELECTAUTHOR.acnum

FROMAUTHORINNERJOIN

PAPERONAUTHOR.panum=PAPER.panum

)

Querstion 2.3

SELECTDEPARTMENT.deptnum,DEPARTMENT.instname,DEPARTMENT.deptname,COUNT(ACADEMIC.acnum)ASNumber

FROMDEPARTMENTINNERJOIN

ACADEMICONDEPARTMENT.deptnum=ACADEMIC.deptnum

GROUPBYDEPARTMENT.deptnum,DEPARTMENT.instname,DEPARTMENT.deptname

HAVING (COUNT(ACADEMIC.acnum)> 10)

Question 2.4

SELECTTOP (1)FIELD.title,COUNT(ACADEMIC.acnum)ASTotal,DEPARTMENT.deptnum,DEPARTMENT.instname,DEPARTMENT.state

FROMFIELDINNERJOIN

INTERESTONFIELD.fieldnum=INTEREST.fieldnumINNERJOIN

ACADEMICONINTEREST.acnum=ACADEMIC.acnumINNERJOIN

DEPARTMENTONACADEMIC.deptnum=DEPARTMENT.deptnum

GROUPBYFIELD.title,DEPARTMENT.deptnum,DEPARTMENT.instname,DEPARTMENT.state

HAVING (DEPARTMENT.state=N’VIC’)AND(FIELD.title=’Software Engneering’)

ORDERBYTotalDESC

Question no 2.5

SELECTACADEMIC.acnum,ACADEMIC.famname,COUNT(AUTHOR.panum)ASPcount

FROMACADEMICINNERJOIN

AUTHORONACADEMIC.acnum=AUTHOR.acnum

GROUPBYACADEMIC.acnum,ACADEMIC.famname,AUTHOR.panum

HAVING (COUNT(AUTHOR.panum)= 0)

Question no 2.6

SELECTCOUNT(AUTHOR.panum)ASPcount,FIELD.fieldnum,FIELD.id

FROMACADEMICINNERJOIN

AUTHORONACADEMIC.acnum=AUTHOR.acnumINNERJOIN

INTERESTONAUTHOR.acnum=INTEREST.acnumINNERJOIN

FIELDONINTEREST.fieldnum=FIELD.fieldnum

GROUPBYAUTHOR.panum,FIELD.fieldnum,FIELD.id

HAVING (COUNT(AUTHOR.panum)< 10)

Question no 2.7

SELECTAUTHOR.*,DEPARTMENT.state,PAPER.title

FROMAUTHORNATURALJOIN

ACADEMICONAUTHOR.acnum=ACADEMIC.acnumINNERJOIN

DEPARTMENTONACADEMIC.deptnum=DEPARTMENT.deptnumINNERJOIN

PAPERONAUTHOR.panum=PAPER.panum

WHERE  (DEPARTMENT.state=N’VIC’)

Question no 2.8

CREATEVIEWNoOfPapers

AS

SELECTCOUNT(PAPER.panum)ASNoPapers,PAPER.title,COUNT(AUTHOR.acnum)ASNoAuthers

FROMPAPERINNERJOIN

AUTHORONPAPER.panum=AUTHOR.panum

GROUPBYPAPER.title

Question no 2.9

This will not return the correct value because the it will consider that as complete value , for making comparison we need a special operator , like % at the end that will make proper search.

Question no 2.10

There should be group by clause that need every column in select clauseshould present, that will work. 

Question 3 

Question 4