Database System (ITC556) Assignment Help


Assessment item 2


Modelling


Value: 10%

Due date: 27-Aug-2016

Return date: 20-Sep-2016


Submission method options

Alternative submission method




Get Assignment help for database system subject with hndassignmenthelp@gmail.com





Task

The Fermoy House database



The owners of Fermoy House, a Bed and Breakfast guest house in the Blue Mountains of NSW, have approached you to build them a database to help them run their business.

Fermoy House provides overnight accommodation and breakfast to couples and has four guest bedrooms and a detached private cottage in a private part of the garden. Each bedroom, and the cottage, will accommodate a maximum of 2 guests only.
Task
  1. Create an ERD that shows the entities, attributes, relationships, cardinality and optionality that describe the booking of a room by a guest. This ERD is to be labelled ERD 1. (50 marks)


In order to create a point of differentiation from other local bed and breakfast houses, Fermoy House will allow guests to have up to 2 of their dogs stay in the attached kennels. The dog names are normally recorded so that Fermoy House can provide guests with a personalised dog collar for each dog.
Task
  1. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of a dog(s) by a guest to their booking. Add these to ERD 1. This new ERD is to be labelled as ERD 2. (20 marks)


Breakfast is provided for all guests as part of their accommodation, and guests can choose from either a Continental or a cooked Irish breakfast. This information is normally recorded at the time of booking the accommodation.
Task
  1. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of breakfast by a guest to their booking. Add these to ERD 2. This new ERD is to be labelled as ERD 3. (20 marks)


Bookings are normally taken for up to 12 months in advance and guests who have stayed more than 5 times at Fermoy House receive a gift of wine and chocolates on arrival.
Task
  1. Add the entities, attributes, relationships, cardinality and optionality that describe how many times a guest has booked a stay at Fermoy House. Add these to ERD 3. This ERD is to be labelled as ERD 4. (10 marks)




Rationale



This assessment item is designed to test your understanding of the following learning outcomes:
  • Basic database design and modelling concepts,

  • Identification and interpretation of business rules,

  • Creation of an Entity Relationship Diagram from a set of business rules,

  • Application of correct relationships, cardinalities, optionality using the Crows Foot modelling notation.




Marking criteria
































TaskHDDICRPSFL
ERD 1: BookingCreate an ERD that accurately represents all entities, their relationships with both cardinality and optionality correctly described and all Primary Keys and Foreign keys identifiedCreate an ERD that accurately represents all entities, their relationships with most cardinality and optionality correctly described and all Primary Keys and Foreign keys identifiedCreate an ERD that represents all entities, their relationships with most cardinality and optionality correctly described and all Primary Keys and most Foreign keys identifiedCreate an ERD that represents all entities, their relationships with some cardinality and optionality correctly described and all Primary Keys and some Foreign keys identifiedCreate an ERD that represents some entities, their relationships with little or no cardinality and optionality described and few or no Primary Keys and Foreign keys identified
ERD 2: Dog bookingCorrectly add additional entities, relationships, Primary and Foreign keys with all cardinality and optionality correctly described to existing ERD to correctly reflect the additional business ruleCorrectly add additional entities and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleCorrectly add additional entities and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd additional entities and relationships, Primary and some Foreign keys with some cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd some additional entities and relationships, with few or no Primary and some Foreign keys, and with little or no cardinality and optionality described to existing ERD
ERD 3: BreakfastCorrectly add additional entities, relationships, Primary and Foreign keys with all cardinality and optionality correctly described to existing ERD to correctly reflect the additional business ruleCorrectly add additional entities and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleCorrectly add additional entities and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd additional entities and relationships, Primary and some Foreign keys with some cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd some additional entities and relationships, with few or no Primary and some Foreign keys, and with little or no cardinality and optionality described to existing ERD
ERD 4: Number of staysCorrectly add additional entities, relationships, Primary and Foreign keys with all cardinality and optionality correctly described to existing ERD to correctly reflect the additional business ruleCorrectly add additional entities and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleCorrectly add additional entities and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd additional entities and relationships, Primary and some Foreign keys with some cardinality and optionality correctly described to existing ERD to reflect the additional business ruleAdd some additional entities and relationships, with few or no Primary and some Foreign keys, and with little or no cardinality and optionality described to existing ERD

 

Marking Guide
















TaskMarksScoreComments
ERD 1: Booking50
ERD 2: Dog Booking20
ERD 3; Breakfast ordering20
ERD 4: Number of stays10
Total100
Final mark15

 




Presentation



Assignments are to submitted as a single document with all ERD drawings embedded in the document. Assignments that are submitted with separate ERD drawings will not be accepted.

ERD drawings are to be completed using either mySQL or the Draw.io tool.





Assessment item 3


Normalisation


Value: 15%

Due date: 09-Sep-2016

Return date: 05-Oct-2016


Submission method options

Alternative submission method



Task



Home Library
















































































ISBNTitleAuthor_LastNameAuthor_FirstNamePublisherDateEditionMedia
369852CosmosSaganCarlRandom House19801Book
741258No SecretsSimonCarlyElektra19721CD
654789Symphony No 3 Dur Eroica Op 55BeethovenLudwig18051CD
789654On the Decay of the Art of LyingTwainMarkProject Gutenberg18801eBook
258963The Adventures of Sherlock HolmesConan DoyleArthurProject Gutenberg1eBook
125896The Divine ComedyAlighieriDanteProject Gutenberg1ebbok
357951The Hitchhikers Guide to the GalaxyAdamsDouglasPan books19791Book
852369The Return of the King, SoundtrackShoreHowardReprise20031CD
831975Unseen AcademicalsPratchettTerryDoubleday20091Book
Tasks:

Using the Home library relation above:
  1. Draw a dependency diagram to show the functional dependencies that exist in this relation.

  2. Decompose the Home Library relation into a set of 3NF relations and draw a dependency diagram for each of the 3NF relations.

  3. Develop the Relational Schema for each of these 3NF relations and show the referential integrity constraints that apply.




Rationale



This assessment item is designed to test your ability to
  • Gather, analyse and model business requirements using Enhanced Entity Relationship Diagrams (EERD),

  • Critically analyse a database design and apply Normalisation Theory and techniques.




Marking criteria



Marking Rubric
























TaskHDDICRPSFL
Draw a dependency diagram to show the functional dependencies in the Home Library relationThe dependency diagram accurately identifies and correctly shows all PKs and all functional, partial, and transitive dependenciesThe dependency diagram accurately identifies and correctly shows all PKs and most functional, partial, and transitive dependenciesThe dependency diagram identifies and shows most PKs and some functional, partial, and transitive dependenciesThe dependency diagram identifies and shows few PKs and few functional, partial, and transitive dependenciesThe dependency diagram identifies and shows few or no PKs and few or no functional, partial, and transitive dependencies
Decompose the Home Library relation into a set of 3NF relationsThe set of relations is in 3NF and accurately identifies all PKs and all the relevant attributesThe set of relations is in 3NF and accurately identifies all PKs and most of the relevant attributesThe set of relations is in 3NF identifies most PKs and some of the relevant attributesThe set of relations is mainly in 3NF and identifies some PKs and some of the relevant attributesThe set of relations is not in 3NF and identifies few or no PKs and few or none of the relevant attributes
Draw a relational schema for each of the 3NF relations and show the referential integrity constraintsThe relational schema accurately describes each entity and correctly shows all of the referential integrity constraintsThe relational schema accurately describes each entity and correctly shows most of the referential integrity constraintsThe relational schema describes each entity and shows some of the referential integrity constraintsThe relational schema describes most entities and shows some of the referential integrity constraintsThe relational schema describes few entities and shows few or none of the referential integrity constraints
Marking Guide







































TaskMarksScore
Task 1: Dependency diagram25
Task 2:
1. All entities identified15
2. PKs identified10
3. Attributes identified for each entity10
Task 3:
1. Relational schema for each entity10
2. Attributes correctly described10
3. PKs correctly described10
4. FKs correctly described10
Total100
Final mark15

 





Assessment item 4


Online Quiz 2


Value: 5%

Due date: 19-Sep-2016

Return date: 14-Oct-2016


Submission method options

Interact2 Test



Task



Complete online quiz 2 in the Interact Test Centre




Rationale



This assignment will assess your knowledge of business requirements, database design and normalisation.




Marking criteria



This assignment is a series of multiple choice questions. Each correct answer will score 0.4 marks.

Marks will not be deducted for incorrect answers.





Assessment item 5


SQL Queries


Value: 15%

Due date: 03-Oct-2016

Return date: 26-Oct-2016


Submission method options

Alternative submission method



Task



Notes:
  • This assignment requires you to use MySQL to complete the tasks listed below.

  • The instructions listed below relate to MySQL.

  • Typing the SQL statements or the results is NOT acceptable and will result in 0 marks for the assessment

Your Submission
You are required to submit:
  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file, and

  2. Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query.

  3. Typing or manually drawing the results is NOT acceptable.

Tasks

Part 1
Open the database prime_minister database (prime_minister.sql ) from the ITC556 Interact Resources Databases folder. Answer the following queries using this database.
  1. Find certain Governors General of Australia.
    1. Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date of appointment. (5 marks)

    2. Now, format the date of appointment as day of the week, day of the month, month and year; eg. Monday, 01 January, 1901. Order the list by ascending date of appointment. (5 Marks)


  2. Find certain Leaders of the Opposition.
    1. Find all Leaders of the Opposition and their date of appointment, who assumed their position after 01/01/1980. (5 marks)

    2. For each Leader of the Opposition listed, add their wife’s name and their date of marriage. (5 Marks)


  3. Find certain Governors General of Australia.
    1. List the title, name, date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960. Order by ascending date of appointment. (5 marks)

    2. Now add to the results of q3a, the list of Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment as Governor General of Australia. Order by ascending date of appointment. (5 marks)


  4. Who are the Opposition Leaders who subsequently became Prime Minister after 1930?
    1. List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister. The dates must be formatted as day of the week, day of the month, month in digits and year in four digits; eg. Monday, 01/01/1901. Order the list in ascending date of appointment as Prime Minister. (15 marks)

    2. Now add their Deputy Prime Minister’s name and the party that they led. Order the list by ascending date of appointment as Opposition Leader. (10 marks)


Part 2

The design of the Prime_Ministers database is now very old. You have been asked to review this design, as shown in the ERD below and advise how it could be updated. You are to complete the following tasks:
  1. Advise how you would improve the ability to query information in this database. For simplicity, use only the tables prime_minister, governor_general, ministry and opposition in your answer.
    1. What new integrity constraints would you use in each of these tables? (5 marks)

    2. Why would you use these integrity constraints? Explain how your constraints would improve queries on the tables. (10 marks)

    3. Write the DDL code that would implement your new integrity constraints for the following tables:
      i. Prime_minister (5 marks)
      ii. Governor General (5 marks)
      iii. Ministry (5 marks)
      iv. Opposition (5 marks)
      (Note: You must keep all of the data attributes currently in these tables. Your implementation should include all existing data attributes and any new integrity constraints)


Prime_Minister_2013 ERD



Rationale



This assessment will test your ability to:
  • be able to implement a database design using Structured Query Language (SQL);

  • be able to query a database using SQL.




Marking criteria



Marking Rubric






























TaskHDDICRPSFL
SQL tasks 1 – 5The SQL statements accurately retrieve and format all the required information using concise and correct SQL syntax.The SQL statements retrieve and format > 75% of the required information using correct SQL syntax.The SQL statements retrieve and format > 60% of the required information using correct SQL syntax.The SQL statements retrieve and format > 50% of the required information using mostly correct SQL syntaxThe SQL statements retrieve and format < 50% of the required information using SQL syntax that contains frequent errors
Define new integrity constraintsAccurate and well-defined explanation of all the integrity constraints to be used and reasons for their use, without errors, is provided with evidence of synthesized application of concepts.Provides correct identification and explanation of all the integrity constraints to be used with most reasons for their use, with evidence of applying the concepts correctly.Good identification and explanation of most of the integrity constraints to be used with some reasons for their use, referencing some links between the concepts and application.Reasonable identification and explanation of most of the integrity constraints to be used with some reasons for their usePoor or inadequate identification and explanation of few or none of the integrity constraints to be used
Explain how integrity constraints improve queriesComprehensive explanation of use of integrity constraints to improve query performance, with evidence of synthesized application of concepts.Provides accurate explanation of use of integrity constraints to improve query performance, with evidence of applying the concepts correctly.Good explanation of use of integrity constraints to improve query performance, referencing some links between the concepts and application.Adequate explanation of use of integrity constraints to improve query performancePoor or inadequate explanation of use of integrity constraints to improve query performance
Write DDL code for 4 new table implementationsThe SQL DDL statements accurately create new tables, with all correct integrity constraints using concise and correct SQL syntax.The SQL DDL statements create new tables, with >75% correct integrity constraints using correct SQL syntax.The SQL DDL statements create new tables, with >60% correct integrity constraints using correct SQL syntax.The SQL DDL statements create new tables, with >50% correct integrity constraints using mostly correct SQL syntaxThe SQL DDL statements do not accurately create new tables, with <50% or no integrity constraints using SQL syntax that contains frequent errors
Marking Guide



















































TaskMarkScore
1 a5
1 b5
2 a5
2 b5
3 a5
3 b5
4 a15
4 b10
5 a15
5 b10
5 c i5
5 c ii5
5 c iii5
5 c iv5
Total100
Final Mark15

 




Presentation



You are required to submit:
  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into a single submission file, and

  2. Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query.

  3. Typing or manually drawing the results is NOT acceptable





Assessment item 6


Final Exam


Value: 50%

Date: To be advised

Duration: 2 hours 10 minutes


Submission method options

N/A - submission not required/applicable



Rationale



Covering all topics, this assessment task has been designed to assess your ability to:
  • apply database theory to the design and implementation of relational databases;

  • analyse and model business database requirements using Entity Relationship Diagrams;

  • analyse a database design and apply Normalisation theory and techniques;

  • implement a database design using Structured Query Language (SQL);

  • query a database using SQL.




Requirements



The examination consists of:
  • Multiple choice questions,

  • short and long answer questions.


The examination is a Closed book examination.

All questions must be answered.




Marking criteria



Question 1 has a similar criteria to Assignment 1 and 4
Question 2 has a similar criteria to Assignment 2.
Question 3 has a similar criteria to Assignment 3.
Question 4 has a similar criteria to Assignment 5.
Question 5: Answer is clear, well-expressed, explains what is required and demonstrates understanding.




Material provided by the University



Answer Booklets (1 X 12 page)
General Purpose Answer Sheet GPAS-200R




Material required by the student



Writing implements, including a 2B pencil and an eraser.
Any calculator allowed, including programmable calculators (hand held, no printer).
iPads and other hand-held computers are not accepted as calculators.



Comments