Assignment Feedback

1
Assignment Feedback

• Results on course web site www.itee.uq.edu.au/inf
  s1200
• Design part submissions to be returned during
  tutorials this week please collect
• Any uncollected will be made available in an open
  box on level 6, GP South, outside room 632 (Aus
  room) after Friday 10th Oct

2
Assignment Feedback
3
Assignment Feedback
Users enter the system through a registration
process, wherein, they are given a unique login.
After joining, users can create their profiles,
add friends to their network and join
communities. A user profile consists of name
(first name, last name), date of birth, country
of residence, language(s) spoken, hobbies (can be
more than one) and password. Each user has a
contact list. Contact list for a user contains
list of other users and a rating given by the
owner of the list. There are three ratings as
"friend", "neutral" and "block". Users can set up
new communities and/or join existing communities.
Each community has a unique name and a short
description. One user can create/join several
communities. Thus users can be owners and/or
members of communities. A community will have one
owner, but typically many members. Each community
has a message board. A user can post messages on
a community message board. These messages can be
intended for one or many recipients (within that
community). A message board for a given user
contains all messages sent by other users to
him/her. It also displays sender name and the
date when the message was received. Messages are
only displayed where sender rating is friend or
neutral. The system internally maintains a
unique id for each message. The size of a message
is limited to 255 characters. A third party
application has recently been made available for
the system, which allows its members to display
their geospatial coordinates in real time
(connected through mobile devices). The system is
capable to record the coordinates (latitude,
longitude) and timestamp for each user who uses
this application. Note that the timestamp
attribute may not be unique. That is, two users
may record their location (coordinates) under the
same timestamp. However, for a given user, the
timestamp will always be unique (cannot be in two
places at the same time).
User user-id, DOB, lname, fname, pwd,
country Language user-id, language Hobby
user-id, hobby Contact user-id, contact-id,
rating Message message-id, content, date,
sender-id, community-id MessReceive message-id,
user-id Community community-id, description,
owner CommMember community-id,
user-id Locations user-id, timestamp, latitude,
longitude
4
Assignment Feedback
User user-id, DOB, lname, fname, pwd,
country Language user-id, language Hobby
user-id, hobby Contact user-id, contact-id,
rating Message message-id, content, date,
sender-id, community-id MessReceive message-id,
user-id Community community-id, description,
owner CommMember community-id,
user-id Locations user-id, timestamp, latitude,
longitude

• Non-trivial functional dependencies
• user-id ? DOB, lname, fname, pwd, country
• user-id, contact-id ?rating
• message-id ?content, date, sender-id,
  community-id
• community-id ? description, owner
• user-id, timestamp ? latitude, longitude
• ...
• ? All relations resulting from ER to RM mapping
  are in 3NF ?

5
From UoD to Relational Schema
Universal Relation
Decomposition
Emp Cust Order Order_Date DoB

EER Diagram
RDB
UoD
Modeling
Table 2
Table 1
Mapping
Table n

FDEmp ?DoB, Cust ? Order, Corder ?
Order_Date,
Synthesis
Covers, Minimum Covers
6
Assignment Feedback

• Design by Synthesis - A mathematical approach to
  database design
• Identify the set of attributes and FDs
• Compute the Closure of FD (F) in order to find
  all derivable FDs. There may be a number of
  different sets of closures, called covers.
• Delete redundant FDs from a cover to reduce the
  cover into a minimal cover which has the smallest
  number of attributes and the smallest number of
  FDs.
• Create tables and keys based on the minimal
  cover.
• F
• user-id ? DOB, lname, fname, pwd, country
• user-id, contact-id ?rating
• message-id ?content, date, sender-id,
  community-id
• community-id ? description, owner
• user-id, timestamp ? latitude, longitude
• user-id, language? user-id, language
• ....
• Determine F
• Find minimal cover
• Create tables based on minimal cover

Not covered in this course
7
Lesson Learnt for Database Design
?
Universe of Discourse
database design
Relational Database