Database Fundamentals Assignment 1
Ever thought you could do something creative from a few basic instructions? You know what I mean “yeah sure, I can fill in the gaps and make this work”. Well now’s your chance! This assignment is quite different from those in the past because we are giving you the final product and your challenge is to reverse engineer the product and work out the database required to capture all the detail. If your design works respectfully of the requirements then
you’ve Nailed It!
Design Requirements
In order to achieve the desired level of flexibility in data collection, the design will be expected to accommodate the following features:
- Some projects have one or more contacts for the same client (or company). The attached form doesn’t clearly show this so you’ll need to make sure your design accounts for 1, 2, 3 or more contacts for the one client project. All contacts associated with a project are treated equally (i.e. there is no primary contact recorded).
- Staff are assigned to a project to supervise students. More often this is only one person, but some projects have had 2 or more in the past. All staff supervising a project are treated equally.
- Students are assigned to work on a project. This ranges from 1 (solo project groups) to many (up to 7 or more).
- Some text fields require better enforcement to reduce spelling errors and entries with the same meaning. Try to limit the users to pre‐defined selections where possible to help standardise the information collected.
- It is up to you as the designer if you want to use inheritance in the proposed design.
- Your design should include primary keys, unique keys and foreign keys as appropriate.
- Your design will be optimised to reduce the storage of redundant information, this includes people, contact
data and project detail!
- Your design should facilitate the quick searching of
o People by name
o Projects by title
o Projects by methods involved/applied
Your design should be able to reconstruct the original data captured from the form!
Submission Requirements
To complete the assignment you must submit each of the following:
- A Traditional UML diagram using UML standards (no Foreign Keys in classes, use of association classes, weak entities, inheritance etc where appropriate). Each class should contain a list of the applicable attributes that meet the design requirements. The diagram should highlight ALL associations between classes including their multiplicities and include appropriate roles/names to describe the purpose of the association.
- The UML diagram should be drawn up in UMLet or some other UML design tool but not using the Management Studio Diagram tool or DBDesignerFork.
- Multiplicities must be included and can be represented using numerical or crow’s foot notation.
- Any Primary and Candidate Keys should be depicted using (pk), (ck) etc.
- Save the UML as an image to be included in your final word document (make sure it is still readable!)
- If you include foreign key attributes their names will be checked between diagrams so watch out!
- You need to provide any written assumptions that may justify why you chose certain
attributes/association types over another. These should be short descriptive dot points. No need for an essay in this class!
- An Executable UML Diagram drawn using DBDesigner Fork using crows‐foot notation showing:
- Each of the required Table Schemas via the various UML Classes
- Any Foreign Keys with appropriate and consistent naming
- Correct relationship types (identifying vs non‐identifying)
- Appropriate data types
- Save the UML as an image to be included in your final word document and make sure it is still
readable.
Oh! And don’t forget to save frequently because we all know how fun it can be if DBDesigner locks up 🙁
3. At least two table Creation statements for your design that work which together must cover:
- An example of a named Primary Key
- An example of a named Foreign Key
- An example of a named Composite Primary Key OR Composite Foreign Key OR Inherited Key
Hint:
Make sure your two UML diagrams are in agreement as marks are awarded for consistency, this includes association names and multiplicities. Stick to crows‐foot notation in DBDesignerFork otherwise the numbers will be misleading. Make sure you check the SQL that is output by the program as you know it can contain errors and does require some attention!
Due Date
The assignment and all your outstanding work is due Sunday, 16 September 2018 @ 11:55 PM. Oh, and don’t leave it to the last minute or you probably won’t nail it!