ISYS1055/1057 Database Concepts S2/2017

    Need Solution - Download from here

    ISYS1055/1057 Database Concepts S2/2017
    Assignment 2
    This is an individual assignment. Plagiarism in oral, written or visual presentations is the
    presentation of the work, idea or creation of another person, without appropriate
    referencing, as though it’s one’s own. Plagiarism is not acceptable and may result in
    charges of academic misconduct which carries a range of penalties. It is also a
    disciplinary offence for students to allow their work to be plagiarised by another student.
    For details, please check the course guide.
    Due time
    Final submission is due at 10:00pm 11 October 2017 Wednesday in Week 12. Submit one
    PDF file to the “on-time submission” folder in the Blackboard submission system.
    ● Make sure you take a screenshot after submission and keep that screenshot as
    proof of your submission receipt.
    ● Do not leave your submission to the last minute as you may experience difficulty
    uploading your file.
    ● You can re-submit multiple times before the due time – any re-submission will
    override previous submissions. Do not worry about any system warning message
    like “ Originality reports for resubmissions can take up to 24 hours to generate”.
    Late submissions of assignments after the due time (even if with approved extensions)
    need to be submitted to the “late submission” folder and will be penalised as follows. For
    every (up to) 24 hours late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of
    your marks) applies. For assignments more than 5*24 hours late, 100% penalty applies.
    100 marks in total, which is 30% of the overall assessment for ISYS1057 and ISYS1055.
    The assessment components and weights are:
    Assignment 1 Assignment 2 Exam
    20% 30% 50%
    There is not a hurdle for any assessment component.

    Question 1. The Relational model (30 marks).
    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) ( 3 marks) Give {itemNo, shopNo} + .
    1.2) (3 * 3 marks = 9 marks) Give the candidate keys for each of the given
    relations Shop, Item and Order. Show your workings of using FDs.
    1.3) (6 marks) Compute the minimal basis for the given FDs.
    1.4) (6 marks) Use the minimal basis for FDs from question 1.3) to explain if
    Shop, Item and Order are in BCNF.
    1.5) (6 marks) If any of the relations Shop, Item or Order is not in BCNF,
    decompose it into BCNF/3NF. Give the relations after decomposition and specify
    the primary key and any foreign key for each relation.
    Question 2. SQL (30 marks).
    In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial
    on Blackboard (“Using Oracle → SQL*Plus tutorial”) and other resources for Oracle
    syntax and useful functions.
    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).
    Primary keys are underlined and foreign keys are marked with *. You should download
    the SQL script for defining and populating the database academics.sql from Blackboard
    (“Using Oracle”) and run academics.sql in your Oracle account to build the database.
    Each question is worth 3 marks. Write ONE SQL query for each of questions 2.1)–2.7).
    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 a
    query to find details of research fields where the first two parts of the ID are D
    and 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 given
    below does not return the correct result. Explain the reason why it does not return
    the correct result and give the correct SQL query.
    select *
    from field
    where 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 and
    instname, but it has syntax errors. Identify the syntax errors in the query and give
    the 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 (20 marks).
    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 therapist
    consultation sessions: A therapist has consultation sessions at specific date and
    time and in a room; therapists can have consultation sessions at the same time but
    in different rooms. Consultation sessions can be short (15 minutes) or long (30
    minutes). A patient can book several consultation sessions with different
    therapists but each consultation session is for one patient.
    ● Treatments like “back massage” are often suggested by therapists after
    consultation sessions. Treatments have a unique treatment code and a name, and
    comprises multiple treatment sessions (details given next). A therapist
    consultation session results in only one treatment and a treatment may be used for
    several consultation sessions.
    ● A treatment comprises multiple treatment sessions numbered sequentially.
    Equipments need to be booked for treatment sessions. A treatment session uses
    one equipment, and the date, time and duration for using the equipment should be
    recorded. An equipment can be used for several treatment sessions.
    Give the complete Entity Relationship (ER) diagram for the database of the Beta Health
    Physiotherapy Centre, including the original and additional information given above .
    ● Your ER diagram must use notations from the lecture notes and must not be hand
    drawn. ER diagrams using other notations will receive zero mark for this question.
    ● You can use the ER diagramming tool Dia, which can be downloaded from the course
    Blackboard (“Tools → ER Diagram Tools”) and is also available as an App on When exporting your ER diagram in Dia to a pdf file, you
    need to first set “page setup” to “Fit to 1 by 1” so that your pdf diagram scales
    properly. You can also use any other diagramming tool.
    Question 4. ER to relational schema mapping. (20 marks).
    Consider the Omeria Hotel database ER diagram as shown in Figure 1.
    4.1) (10 marks) Give the FDs for the constraints in the ER diagram. You should
    not include trivial or redundant FDs.
    4.2) (10 marks) 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.
    Fig. 1 The Oreria Hotel database ER diagram

    By |2017-10-07T13:57:28+00:00October 7th, 2017|Categories: database|0 Comments

    Leave A Comment