+1 678 648 4277 

Relational models assignment sample

Get help with relational models assignments from the leading programming help site. We are here to help you submit accurate relational models assignment solutions and achieve academic excellence.

Table Of Contents
  • Assignment

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.
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