Database Design Examination

 

INF6050 Database Design Examination

 

 

 

To avoid incurring serious Unfair Means penalties, your submission must be entirely your own work and this exam paper must remain on your computer only. This means:

 

Do not share this exam paper, or any of the questions, with anyone

Do not post this exam paper, or any of the questions online

Do not discuss the exam questions with anyone

Do not share your answers with anyone

Do not look at, nor use, somebody else’s answers

 

This is not an exhaustive list.

 

 

Information School                              3 hours

Spring Semester 2020-2021         Answer all three questions

             

The following paragraph is a description of a relational database required by a consortium of galleries:

 

Art galleries within a UK consortium want a relational database to store and retrieve details of their exhibitions, collections and loans since their opening. They expect this database will grow and capture other information in the future. The galleries all have a name, an address, a phone number and usual opening times each week. The dates when the galleries are closed are also stored. The galleries have always held exhibitions of their collections and some collections may have been exhibited several times. An exhibition only contains one collection of artistic works. All exhibitions have a title, a from-date and a todate. Each collection consists of two or more artistic works. Artistic works are either graphic works

(including paintings, photographs and drawings), collages or sculptures. All artistic works have a width and height, but no depth is recorded for graphic works. Collages include a list of materials used (e.g. paper, steel) and graphical works include a description of the medium (e.g. oil on canvas), but sculptures have only one material recorded (e.g. clay). Each artistic work should be assigned a unique identity number and may also have a title, a short description, a date acquired, a date created and a list of creators. Obviously, creators may create other artistic works. Currently, only creator names (e.g. “Hillclimb” or “Unknown”) are stored. Collections are either being exhibited, loaned to another gallery or stored at one of several named warehouses specialising in storing that particular type of artistic work. Hence, warehouses store collections not exhibited or on loan. When a collection or collections are loaned to another gallery (not necessarily in the consortium), the date & duration of the loan and other gallery details are stored (e.g. the title of their exhibition the collection is being displayed in, its opening details, phone number etc.). Please note, galleries may have borrowed the same collection several times and entire collections are loaned, not parts of collections.

 

Please note: Your answers to the following questions must not be handwritten or hand drawn

 

Q1. Using the information provided in the description, undertake conceptual design (‘data modelling’) and produce a conceptual model (‘data model’) as an Entity-Relationship (‘ER’) diagram in the UML style used in INF6050. This should be readable in a portrait orientation without any need to rotate the page on the screen. The conceptual model should accurately and comprehensively model the information in the description with minimum redundancy. That is, the entities, relationship types, attributes and identifiers are well-chosen, and multiplicities (including existences) are accurate. Furthermore, the conceptual model can inform any future data system and not just a relational database system. * (50 marks)

 

Q2 Using the information provided in the description and the conceptual model produced in Q1, undertake logical design and produce a logical model for a relational database system in the pseudo code style used through-out INF6050. The code submitted must be readable by Turnitin ** (e.g. not an image). The logical model should accurately and comprehensively model the information provided in the conceptual model and description of the data system with minimum redundancy and new information. * (40 marks ***)

 

Q3 Using the information provided in the description, the conceptual model produced in Q1 and the logical model produced in Q2, undertake physical design for an Oracle relational database and produce a single data dictionary, no more. There is no need to consider the performance of the final Oracle relational database. However, the consortium of 9 galleries and 2 warehouses estimate the Oracle relational database will need to accommodate around:

 

  • 3 new exhibitions per year per gallery starting with around 25 exhibitions per gallery
  • 1 new collection per year per gallery starting with around 80 collections per gallery
  • 15 artistic works per collection on average with some collections having around 100 works
  • 50 loans per year per gallery

 

The data dictionary should consist of a table (or tables) containing the headings ‘Table name’, ‘Attribute name’, ‘Any constraint[s]’ and ‘Datatype description’. It should enable a DBA to implement the logical model without reference to the description or the consortium. The data dictionary submitted must be readable by Turnitin (e.g. not an image) and in a portrait orientation without any need to rotate the page on the screen. (10 marks ***)

 

 

* During the conceptual and logical design of any data system, there is a continuous need to ask users of the system and the client questions. For the exam, this will not be possible. Hence, if you need to make any assumptions, please list them underneath the appropriate model. There is no need to explain or justify your answers. There is no need to list your assumptions for the physical model.

 

If you make an assumption, it must be reasonable. Any assumption which the marker feels is unreasonable will be penalised. For example:

 

  • during physical design it would be reasonable to assume all the galleries are within the UK given it is a UK consortium
  • during conceptual design it would be unreasonable to assume collections have a collection number when this is not mentioned in the description.

 

** Guidance on which files can be submitted to Turnitin can be found on the webpage:

https://www.sheffield.ac.uk/apse/digital/turnitin/filetypes

 

*** The marking of the logical model is not dependent on the accuracy of the conceptual model. However, it does assume the conceptual model is well-developed. Students who submit an under-developed conceptual model, may have their logical model marks correspondingly reduced. If this happens, the marking criteria rubric comments may not apply. Likewise, the marking of the physical model is not dependent on the accuracy of the logical model.

 

Submission

Answers to Q1, Q2 and Q3 must be submitted to Turnitin as a single document with your University Registration Number at the top. A PDF document is the safest format to submit to Turnitin. Obviously, you should check your submission before finally submitting. There is no word limit and no requirement to specify a word count.

 

END OF EXAMINATION