This course will have supervised exams as well as take home assignment.

Exams (including in-class quizzes) are designed to test theoretical concepts and modelling techniques introduced in lectures and tutorials. Exams will address all material from lectures and tutorials that occurred prior to the exam date. All exams will be closed book. You will be required to answer all questions and write in the space provided on the exam paper. Programmable calculators and other computing or communication devices are NOT permitted. Students may request the use of dictionaries, including bilingual dictionaries, supplied by Examinations Section. You will require a HB or 2B pencil and an eraser to complete the exam. Assessment variation is possible for students with a disability (see HUPP 3.30.3 Special Arrangements for Examinations for Students with a Disability). More information on exams will be provided on the exam page on the course web site at the appropriate time.

The assignment is designed to test data analysis and design techniques as well as information system development skills acquired in practical sessions. Assignment specifications will be announced and placed on the course web site at the required time. Please ensure that you download/print your copy as soon as possible on or after the date of issue. Late submission of assessable material will incur a penalty of 20% reduction each date late. Students who believe they have sound reasons of late submission should see the course coordinator as soon as possible. Students are advised to create back-up copies of their assignments. Software can fail (or be lost) for many reasons and it happens quite frequently. Students should also retain paper-based assignments in the event that the original paper version is lost or damaged.

See Course Profile for details on assessment criteria, dates and marks

Quiz 2

Solution

Common Mistakes:

Question 1

•    Confusion with notation (how to represent FDs,)
•    Difference between FD closure and Attribute closure
•    Understanding the basic definitions of normal forms (e.g thinking of transitive dependencies when running 2NF test)

Question 2

•    Not able to identify "have never had", most students answered with WHERE temperatures > 22, which doesn't guarantee the above
•    Using WHERE to evaluate aggregate function e.g. WHERE COUNT(*) > 5, instead of HAVING COUNT(*) > 5
•    Displaying attributes (in SELECT clause) that are not part of GROUP BY clause

Date 14 May 2012
Time: 10.00 - 12.00
Venue: Monday Lecture Room
Marks: 20/100
Duration: 1 Hour 50 Minutes
Material: Modules 6-7

Format: Short question answer: similar to class exercises & tutorials. Where relevent, based on examples from LDBM
Please report to the room at least 10 minutes before the start of exam.
Do not forget your student card !

Preparation
Study the content of modules 6-7 along with the required reading for each lecture from the text books
Class Exercises and tutorials
LDBM
Mock Quiz

Mark Distribution
FDs and Normalization (Module 6)       5 Marks
SQL (Module 7)                                    15 Marks

Assessment
The exam is intended to assess the students' ability to
Reason with the logical foundation of the relational data model and understand the fundamental principles of correct relational database design
Express natural language queries using the SQL language

Quiz 1

Solution

Common Mistakes:

Question 2:

Only small number of students did it wrong e.g. did not read the question carefully, some gave CORRECT examples instead of INCORRECT ones.

Question 3:

Most people could not detect that sports, entertainment and trade are subclasses, and used relationships.
Some thought that the levels in sports(local etc) and genres in entertainment are subclasses or just separate attributes.
Some put the missions as attributes, 1:N relationship or weak entity of trade.
Many missed that there could be more than 1 main competition in sport (multivalued attribute)

Question 4:

In the weak entity, METERREADING, many did not add empid as foreign key
In the weak entity, METERREADING, many used empid as part of primary key
In the weak entity, METERREADING, many used timestamp only as primary key
Did not put propertyid in PROPERTYMETER as foreign key

Question 5:

Explanation given was that key fields cannot be modified (Q5 c)
Explanation for referential integrity was not clear (Q5 c)

Date: 16 April 2012
Time: 10.00 - 11.30

Venue: Monday Morning Lecture Room

Marks: 15/100

Duration: 1.30 Minutes
Material: Modules 1 - 5

Format: Short question answer: similar to class exercises & tutorials. Where relevent, based on examples from the onlne learning tool LDBM
Please report to the room to at lease 10 minutes before the start of exam.
Do not forget your student card !

Preparation
Study the content of modules 1 - 5 along with the required reading for each lecture from the text books
Class Exercises and tutorials
LDBM
Mock Quiz 1 as discussed in class on Wednesday

Mark Distribution
Database fundamentals (Module 1)            2 Marks
DFD (Module 2)                                       3 Marks
ER (Module 3)                                           3 Marks
Relational Model (Module 4)                    4 Marks
Mapping (Module 5)                                 3 Marks

Assessment
The exam is intended to assess the students' ability to
Extract information systems requirements to create basic conceptual models
Map basic conceptual data models to relational database schema
Reason with the logical foundation of the relational data model and understand the fundamental principles of correct relational database design

Assignment

Assignment specifications Due date: 4 May 2012 @ 5PM

Assignment submission template

Competition: As part of the assignment, you have to enter some sample data in your MSAccess implementation to allow us to assess the forms, queries and reports you will create. However, a (near) complete historial record of the entire Olympic games is available from www.databaseolympics.com. Any student or group of students who can enter (copy/paste, import etc) "all" the data into their MSAccess database will get a surprise prize from me. Submission can be made as a .zip file of your MSAccess .accdb file to me via email shazia@itee.uq.edu.au up to 5PM 9th May 2012.

Frequently Asked Question (FAQs) for Assignment as presented in
lecture on Monday 23.04.

Can I introduce an “artificial” key?
Not for your ER. However if you have long composite keys then see attached for some tips on how to deal with them in the MSAccess Implementaion

What if an entity has only one attribute which is its key?
That is perfectly ok for ER as well as relational schema. Often for such entities more attributes are discovered later

Can I have two separate ERs?
No, there will be a single connected ER for any given UoD

How do I define relationship constraints (cardinality, participation) in MSAccess?
See attached

How do I identify FDs in order to conduct normalization for the relational schema I got after mapping my ER?
You can identify the FDs from the given UoD only. Note however, that there is a good chance that you may not have to do any normalization, i.e all the relations (tables) you got after mapping could very well already be in 3NF. However, for each table (say R(A, B, C), first consult the UoD to identify the FDs. (Hint: there will be an obvious FD, i.e A ->B, C). After identifying further the FDs (if any), apply the 2NF and 3NF tests and decompose as needed. Note that in case you do end up doing normalization, you should implement the schema you got after normalization in MSAccess.

How do I document the normalization if all my tables are already in 3NF?
For each table in your schema, do the following:
R(A, B, C)
FDs in R = {A -> B, C}
R is already in 3NF

There are three values for medal: Gold, Silver, Bronze. Does that mean medal is a multi-value attribute?
NO! Above is the result of confusion between an attribute and its domain. A medal is an attribute and its domain (allowable values) consist of 3 values - Gold, Silver, Bronze

How much sample data do I need?
Around 10-20 tuples per table will be sufficient to test your constraints, forms and reports. You are encouraged to cut&paste the sample data from the given websites so you don’t have to make-up any hypothetical data. For example it is not a problem that you don’t have schedule data for games other than London. As a sample, 10-20 tuples in related tables will be sufficient. Copying data from the sources provided is quite easy to do, by first copying the data into an xls spreadsheet – and then rearranging to fit your table design and finally copying (or importing) into MSAccess. Remember that any student/group who manages to copy ALL the historical data (not including the schedule for London games) for the Olympics will get a PRIZE!!

Am I supposed to record data for all athletes, or just medal winners?
Your database design should be capable to record data for all athletes. However, you don’t have to enter the data for all … see above regarding how much sample data is needed. Note that the source of confusion for this question is similar to the one on medals having 3 values – basically a confusion between schema and instance (or design and data).