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