ISYS1055/1057 Database Concepts assignment 代写

  • 100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信:120591129
  • ISYS1055/1057 Database Concepts assignment 代写

    Computer Science and Information Technology
    Document: Assignment 1
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 1 of 9
    School of Science/ Computer Science and IT
    ISYS1055/1057 Database Concepts
    Assignment 1
    Semester 1, 2017
    1 Introduction
    This is an individual assignment, to be submitted electronically using the Blackboard facility. A
    submission link will be enabled on blackboard closer to the submission date. It is due 23:59 Friday
    21st April 2017, and contributes 20% towards the aggregate of 100 marks.
    The objective of this assignment is to reinforce what you have learned in the lectures and tute/ lab
    sessions. Specifically, it covers the basic concepts in the relational database model, using entity-
    relationship model for database design, and using SQL for querying a relational database.
    1.1 Plagiarism
    All code or other material that is not original must be fully credited. That is, any material that is
    copied or derived from another source must be clearly identified as such and the original author
    must be identified. Sometimes students assist each other with an assignment, but end up working
    together too closely, so that the students' separate solutions have significant parts in common;
    unless the solutions were developed independently, they are regarded as plagiarised.
    Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will
    be deemed as an academic misconduct and harsh penalties apply. It is also an offence for
    students to allow their work to be plagiarised by another student. You should familiarize yourself
    with the university website for Academic Integrity Policy, Procedures and Guidelines.
    (http://www.rmit.edu.au/academicintegrity) All work is to be done individually and plagiarism of any
    form will be dealt with according to the RMIT plagiarism policy.
    1.2 What to Submit, When, and How
    1.2.1 When
    This assignment is due at 23:59 on Friday 21st April 2017.
    1.2.2 What
    You should submit one PDF document with all answers together. You can use Oracle SQL
    Developer and LucidChart to work on your assignment. You may use Word or any other word
    processor to compile your submission. At the end, convert it into PDF format. Do not submit Word
    files. if that option is not available on your system there are free pdf converters online you can
    utilise. e.g. http://convertonlinefree.com/
    1.2.3 How
    You are required to submit your solution electronically using the Blackboard facility. A submission
    link will be enabled on blackboard closer to the submission date.
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 2 of 9
    1.2.4 Penalties for late submissions
    Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of
    10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than
    5 days late, 100% penalty applies. If you submit between 00:00 22 nd April and 23:59 22 nd April,
    your penalty is 10%. If you submit between 00:00 23 rd April and 23:59 23 rd April, 20% penalty, and
    so on.
    1.2.5 Special Consideration
    If unexpected circumstances affect your ability to complete the assignment you can apply for
    special consideration. If you seek a short extension, you can directly contact the lecturer. For
    longer extensions, you must follow instructions provided at
    http://www1.rmit.edu.au/students/specialconsideration
    1.3 Preparation Tasks
    Part A of this assignment, you must be familiar with LucidChart diagramming application. You are
    required to complete the Week 4 Tute/ Lab session prior to attempt this assignment. Additional
    resources on using LucidChart are available under Databases and Tools section of the course
    Blackboard shell.
    2 Part A: Entity-Relationship Modelling (50 Marks)
    2.1 Designing an Entity-Relationship Model
    You have just been employed as a database designer in a well-established software development
    firm. Your first job is to design and implement a database system for a small medical centre in one
    of the Melbourne suburbs.
    The following information have been gathered after analysing the current practices of the medical
    centre.
    •  The system records information about the doctors work at the medical centre. Doctors
    have unique registration numbers. In addition to the registration number for each
    doctor, the system records the name, date of birth, gender, qualifications, and
    specialisations. Doctors can have multiple qualifications and multiple specialisations.
    •  The system keeps a record for each patient. When a new patient comes to the medical
    centre, the patient is given a registration form to be filled. The information gathered in
    this form will be stored on the system. When these information is entered to the system,
    a serial number is assigned to each patient. The registration form captures following
    information: name, address, date of birth, gender, telephone numbers (multiple
    numbers are allowed), Medicare number (if any), private health cover number (if any),
    and private health cover company (if any).
    •  When a patient attends a consultation with a doctor, following information are recorded:
    consultation number, date, time, description, symptoms, and special remarks.
    Symptoms is a multi-valued attribute.
    •  A number of prescriptions can be recommended after each consultation. For each
    prescription, following information are recorded: name of the medicine, dosage, number
    of repeats, and special instructions. None of these attributes are unique. However, for a
    given consultation a medicine is prescribed only once.
    •  The doctors sometimes make referrals for other procedures, such as X-ray, MRI, and
    physiotherapy. The system should be able to record such referrals made at
    consultations. For each referral, following information are recorded: name of the
    procedure, short description, referred practitioner, and whether this procedure essential
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 3 of 9

    ISYS1055/1057 Database Concepts assignment 代写
    or optional. Like prescriptions, referral for a given procedure is only made once in a
    consultation
    •  The system should be able to keep accounting information. In particular, the system
    should be able to store payments made for the consultations. There are three
    possibilities:
    1. bulk-billing:
    Some Medicare 1 patients (such as disabled and seniors) are bulk-billed. This means
    that the patient is not required to pay anything up-front. The medical centre will bulk-
    bill the Medicare (the government agency that deals with healthcare services in
    Australia) the costs associated with the consultation. For such transactions, the
    medical centre will record the payment number, timestamp, amount and the
    Medicare reference number
    2. full up-front payments (cash or card):
    The patients with no Medicare benefits are required to pay full cost of the
    consultation up-front. For such payments, the medical centre will record the
    payment number, timestamp, the amount paid, and the method of payment.
    3. settling part of the invoice using a Medicare benefits and the balance in cash or
    card:
    Some Medicare patients are required to pay a top-up payment (gap fee). That
    means, part of the consultation fee is covered by Medicare benefits and the rest is
    paid up-front by the patient. For such transactions, two payment records are created
    and stored. (The both are linked to one consultation). First record will be similar to
    (1) above. The second is similar to (2) above.
    Based on the information you gathered, model the activities in your client's business and present
    your model as an Entity-Relationship diagram. Carefully state any assumptions that you make. In
    your ER diagram, you must properly denote all applicable concepts, including weak or strong
    entities, keys, composite or multi-valued attributes; relationships and their cardinality and
    participation constraints.
    If you cannot represent any of these information in the ER model, clearly explain what limitations in
    the ER model restrict you from representing your model.
    You must use UML notation used in the lectures and tute/ lab sessions. You may use any
    diagramming tool to draw your diagram, however, use of LucidChart is highly recommended.
    Your diagram must be drawn to a high standard with minimal clutter.
    Note that you are not required to map the ER model to relational model.
    A special note: This is an open-ended question with many different models can be derived. Your
    model is assessed based on how accurately it represents business rules described above.
    1 If you are not familiar with how Medicare works, http://www.mydr.com.au/first-aid-self-
    care/australian-health-system-how-it-works has a simple explanation.
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 4 of 9
    2.2 Mapping an ER Model to a Relational Database Schema
    Consider the following E-R diagram, which models an online bookstore. Map this E-R diagram into
    a relational database schema. Show every step of the mapping. No marks are awarded to the final
    schema if you do not show the partially-built schema at the end of each step. Indicate the primary
    key (underlined) and foreign key (with an asterisk) in each relation.
    Book
    ISBN {PK}
    Title
    Edition
    year
    ListPrice
    Publisher
    Name {PK}
    Address
    URL
    ABN
    Author
    EMail {PK}
    Name
    Address
    Telephone {1..N}
    ShoppingCart
    CartID {PK}
    TimeStamp
    Customer
    Email
    Name
    Address
    Warehouse
    Code {PK}
    Address
    0..N
    1..N
    PublishedBy
    1..N
    0..N
    WrittenBy
    0..N
    0..N
    AddedTo
    0..N 1..1
    OwnedBy
    0..N 0..N StockedAt
    StockQty
    Qty
    BuyPrice
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 5 of 9
    3 Part B: SQL (30 Marks)
    LibraryDB is a database system that keeps track of information concerning the books and their
    circulation in an imaginary library.
    Disclaimer: The data that populates the database are artificially constructed and by no means
    correspond to actual real-world data.
    The schema for the LibraryDB database is given below.
    The primary keys are underlined. The foreign keys are denoted by asterisks (*).
    Description of the schema
    •  person -- keeps track of the people who borrow books from the library. The attributes
    contain personal and contact information.
    •  author -- keeps track of personal information about authors.
    •  publisher -- keeps track of the publisher information. To make simple, most of the attributes
    have been truncated in the sample database.
    •  subject -- this relation keeps information about the subjects on which the library collection
    have books (such as Mathematics, Database, etc)
    •  book -- contains information about the books that are available in the library. Every book
    can have one or more physical copies in the collection. Each book can have one or more
    authors and it is published by one or more publishers.
    •  book_copy -- keeps track of the physical copies of the books in the library collection.
    •  borrow -- keeps track of the check-ins and check-outs of the books. Every transaction is
    done by one person, however may involve with one or more book copies. If there is no
    return date, it means the book has been checked out but not returned.
    •  written_by -- associates books with authors. A book may be associated with several
    authors and an author may be associated with several books. There is also an attribute
    'role' that specifies the role of the author for the book (author/ editor/ translator/ etc).
    •  published_by -- associates publishers with books. There is an attribute 'role' here too.
    •  borrow_copy -- associates physical copies of books with a transaction. Members are
    allowed to borrow several books in a single transaction.
    A conceptual data model (shown as an entity-relationship diagram) which represents these data is
    given below.
    borrow(transactionID, personID*, borrowdate, duedate, returndate)
    author(authorID, firstname, middlename, lastname)
    book_copy(bookID, bookdescID*)
    book(bookdescID, title, subtitle, edition, voltitle, volnumber,
    language, place, year, isbn, dewey, subjectID*)
    borrow_copy(transactionID*, bookID*)
    person(personID, firstname, middlename, lastname, address, city,
    postcode, phonenumber, emailaddress, studentno, idcardno)
    publisher(publisherID, publisherfullname)
    written_by(bookdescID*, authorID*, role)
    published_by(bookdescID*, publisherID*, role)
    subject(subjectID, subjecttype)
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 6 of 9
    You can access a sample database instance of this library database system using Oracle SQL
    Developer (either on myDesktop or lab machines) or sqlplus on core teaching servers (titan, jupiter
    or saturn.) Please follow the instructions provided on the Blackboard to access the sample
    database.
    Username: rolibrary
    Password: library
    If you wish to do this part of the assignment from home, you can install SQLite (with SQLite
    Studio). The instructions for installing, configuring and using SQLite Studio is provided in the
    Databases and Tools section of the Blackboard. Also included is the pre-built Library database in
    SQLite format (Library.db) at the same location.
    Write SQL queries for the following tasks.
    1. Display the titles of all books on the subject "DataBases". Your result set should be sorted
    on the alphabetical order of the titles.
    2. Display
    a. the number of books on the subject "DataBases".
    b. the number of book copies on the subject "DataBases".
    3. Display the firstname and lastname of the authors who wrote books on the subject
    "DataBases".
    a. Write your query without using NATURAL JOINs.
    b. Write your query using NATURAL JOINs.
    4. Who translated the book "American Electrician's Handbook"? Display the firstname,
    middlenames, and lastname of the translator.
    Author
    authorID {PK}
    firstname
    middlename
    lastname
    Subject
    subjectID {PK}
    subjecttype
    Publisher
    publisherID {PK}
    publisherfullname
    Book
    bookdescID {PK}
    title
    subtitle
    edition
    voltitle
    volnumber
    language
    place
    year
    isbn
    dewey
    BookCopy
    bookID {PK}
    Borrow
    transactionID {PK}
    borrowdate
    duedate
    returndate
    Person
    personID {PK}
    firstname
    middlename
    lastname
    address
    city
    postcode
    phonenumber
    emailaddress
    studentno
    idcardno
    1..N
    0..N
    1..1 0..N
    1..N
    0..N
    1..1
    0..N
    1..N
    0..N
    0..N
    1..1
    Role
    Role
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 7 of 9
    5. Display the firstname and lastname of the people who returned books late.
    6. Display the firstname and lastname of the people who returned books more than 7 days
    late.
    7. Display the titles of books that haven't been borrowed.
    8. A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF DATABASE
    SYSTEMS", but all of its copies are already borrowed by others. Write two queries to
    display other recommended titles using the following methods.
    a. Using partial matching of the book title -- note that the borrower is interested in a
    "DATABASE" book.
    b. By searching of other books written by the same author (i.e. the author of
    "PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS"
    9. Display the list of publishers who have published books on the subject "DataBases". Your
    query should display publisher's full name, along with "DataBases" book titles they
    published.
    10. List the full names of publishers who have not published books on “Databases".
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 8 of 9
    4 Part C: Relational Database Model (20 Marks)
    This question has been adopted from Fundamentals of Database Systems, Elmasri and Navathe.
    (Question 5.11).
    A relational database schema and an instance of this schema are given below.
    Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee's
    supervisor's SSN (Social Security Number). This example is based on US system, assume it is
    similar to Australian Tax File Number.
    Computer Science and Information Technology
    Document: Assignment 1.docx
    Author: Santha Sumanasekara
    Save Date: 22/03/2017
    Page 9 of 9
    Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of
    Super_SSN, the parent relation is the Employee relation itself (self-referencing).
    1. It was found that the data entry operator had incorrectly entered data for John B. Smith and
    Ramesh K. Narayan. Their SSN were swapped. The error was discovered, and the data
    entry operator was instructed to fix it. (S)he attempted to swap SSN value in each tuple
    using the following SQL statement.
    But, it was not successful. The DBMS returned an error message and two tuples were not
    updated.
    a) Explain why they didn’t work.
    b) Give another way to correct the error.
    c) Write down the correct SQL UPDATE statements to carry out your proposed solution.
    2. James E. Borg gets a pay rise, which increased his salary by 10%. The data entry operator
    has executed the following SQL statement.
    Write down all the integrity constraints violated by the above operation. If the operation
    does not violate any constraints, indicate as 'no violations'.
    3. Due to a workplace dispute, John B. Smith has been moved to a new supervisor. To effect
    this change, the data entry operator has executed the following SQL statement.
    Write down all the integrity constraints violated by the above operation. If the operation
    does not violate any constraints, indicate as 'no violations'.
    4. A new project has been established, but neither a project number nor a department is
    assigned yet. A new record has been entered into the Project relation, as follows.
    Write down all the integrity constraints violated by the above operation. If the operation
    does not violate any constraints, indicate as 'no violations'.
    UPDATE employee
    SET SSN=’666884444’
    WHERE SSN = ‘123456789’;
    UPDATE employee
    SET SSN=’123456789’
    WHERE SSN = ‘666884444’;
    UPDATE employee
    SET salary = salary*110/100
    WHERE SSN = ‘888665555;
    UPDATE employee
    SET super_SSN = '666884444'
    WHERE SSN = ‘123456789';
    INSERT INTO project (pname, plocation)
    VALUES (‘Agent X’, ‘Washington’);
    ISYS1055/1057 Database Concepts assignment 代写