COMP2350/6350 2020 S2: Database Modelling And Design Assessment One Answer

pages Pages: 4word Words: 890

Question :


COMP2350/6350 2020 S2 – ASSIGNMENT ONE (10%)

Database Modelling & Design

Student Support

Macquarie University provides a range of support services for students. 

The background knowledge for the assignments is given in the textbook(s), lectures, any other components of the unit, in the prerequisite units ISYS114 or COMP1350, and in the readings provided on ilearn. However, some parts of the assignments may not be answered without prior independent research and/or searching for other sources of information.

This assignment concerns database conceptual modelling and logical design. It will be marked out of 100 and will contribute 10% towards your final grade. It consists of developing an enhanced ER (EER) model (Conceptual data model - CDM), and transforming the EER model into a relational data model (logical data model  LDM). The description of the Problem domain is given below.

  • Problem Domain

There are a number of publicly funded libraries in the Greater Sydney region. The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for these libraries. We will refer to this as Sydney Library (SL). The information to be stored include information on different branches of SL (Ryde, Epping, etc), the collections they hold (books, magazines, movies, etc.), staff they employ (librarians, security personnel, etc.), members of the library, events they host (book reading and signing by author-guests). The basic requirements gathered from the stake holders is presented in the following five points. As typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required, and keep a note of the assumptions you made.

  1. Branch Information: The SL System shall keep information on each branch including its name and address, its opening hours (Day, opening and closing time), number of employees who work there, spaces available for holding events and cost of renting such spaces. The system shall also contain information on which days (Mon-Sun) the branch is open, and during which hours. It will also keep information on how staff are rostered to work during those hours.
  2. Collection Information: The system shall contain information on items it holds, their type, cost and provenance (authorship, publisher, producer, editor, prominent actors, etc. as relevant). It is possible that multiple copies of a particular “title” are held at the same branch. Information should also be kept on whether a particular item is available for borrowing, and if not, who has borrowed it, and by which date it should be returned.
  3. Employee Information: The system shall record information on all employees who work at different branches of SL. This will include their roles, type of employment (e.g. permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to.
  4. Membership Information: The system shall record information on members at different branches, including when the membership will expire.
  5. Events Information. The system shall keep information on the events hosted at different branches. A full-time staff manages each event, supported by one or more other staff. The guest would contribute, as mutually agreed, a fixed percent of the selling price of the books they sold at the event. After the event is over, based on the sales, a single invoice for the whole campaign is sent to the guest. When the guest pays, the date

paid is recorded. Furthermore, the actual cost of a event is calculated from a range of information such as: cost of staff time and infrastructure cost.

  • Task Specifications

You will first put all your answers to the tasks below into a file  Assignment1.doc  (carefully following the instructions provided), then generate a .pdf file called  Assignment1.pdf  which you will submit on iLearn.

Task 1 (60 marks)

Construct a conceptual data model in the form of an enhanced ER (EER) model for the above problem domain (see the Appendix for a sample). Identify and justify the use of, if any, generalization /specialisationweak entity types, and attributes on relationships. This model should include entities, attributes, primary keys, the relationships among entities with cardinality and participation constraints. You can make any reasonable assumptions if there is a lack of information on particular aspects and/or you think there is ambiguity. If necessary, you may also introduce additional entities to ensure that every data requirement is captured. Note that you should not show the foreign keys in the EER model.

You can do this task through a data modeling tool such as PowerDesigner or a drawing tool such as . Add/paste the EER model into the  Assignment1.doc  file.

Task 2 (40 marks)

Construct a logical data model (a relational data model) by transforming the conceptual data model of Task 1, showing the relations, their attributes, and foreign and primary keys. The relational data model can be presented either as a relation diagram or a relational schema (see the Appendix for samples).

You can choose to construct the relational data model either through MySQL Workbench or another tool such as PowerDesigner, or using a text editor. Add/paste the model into the  Assignment1.doc  file.

  • Submission

You must submit all the work in a single documents named  Assignment1.pdf  through the electronic submission site on ilearn. If you modify your file(s) after submission, you may re-submit your assignment again before the due date.


  • The  Assignment1.doc  file will be based on the template word file provided on ilearn. Fill out the details provided on the first page of the template. For each task, follow the instructions given and add your answers in.
  • You are not allowed to submit hand drawn images for the models; you should use a modelling or drawing tool as mentioned above. If you happen to submit hand- drawn images, you will NOT receive any marks for it.
  • If your images for any task lack clarity and /or they are not legible, you will not be given any marks. Tutors can zoom in to check the diagrams. But we will not be able to increase the quality of the image. Whatever is submitted is the final submission. So, please make sure your images are legible.
  • PowerDesigner also offers a 30-day free trial period should you wish to install it on your personal computer to use it outside the opening hours of the labs in 9WW Level 1.

Late Submission Policy

No extensions on assignments will be granted without an approved application for Special Consideration.

Late submissions will be accepted but there will be a deduction of 10% of the total available marks made from the total awarded mark for each 24 hour period or part thereof that the submission is late. For example, 25 hours late in submission for an assignment worth 100 marks – 20% penalty or 20 marks deducted from the total.

No submission will be accepted after solutions have been posted.

Show More

Answer :

For solution, connect with online professionals.