IFB105 Database Management
Project – part A
The IFB105 project gives you an opportunity to apply concepts and skills you acquired in the unit to a realistic database design scenario and reflect on the data requirements of an organisation.
The submission is divided into 2 parts due at different times during the semester. These parts will cover:
- Design of a database
- Creation and use of a database
The task for part A
In this part, you will:
- Design an ORM conceptual schema for a business domain based on sample reports required for that domain.
- Map a different ORM schema to a relational database schema.
Part A is worth 15 marks, for 15% of the unit.
This assignment can be done individually or in pairs.
If you choose to work in a pair, only one student should submit the assignment. Please provide the name and student number of the person you worked with on the front page. No consideration will be given to any student who claims to have done more work than the other student in his/her pair, because this assignment can be done individually.
Doing the assignment in undeclared groups, or groups larger than two students, will be treated as plagiarism. Pairs that work together and then split due to difficulties must not submit any of the same work, or it will be treated as plagiarism.
Friday September 4th 11:59 pm (end of week 7).
For this assessment, you should submit a professionally presented Word document containing your solution to each task. For Task 1, ORM schemas should be created using a computer drawing program, such as Lucid Chart or draw.io. For Task 2, the relational schema must be entered using either the notation taught in lectures and tutorials. You should annotate the basic relational schema to ensure all the ORM constraints and subtype definitions are captured. Partial or fully handwritten submissions will not be accepted. Your document needs to be uploaded to the submission link on Blackboard.
Assessment work submitted after the due date will be marked only with an approved extension (MOPP E/6.8.2). Assessment work submitted after the due date without an approved extension or, where an extension has been granted, after the extended due date, will not be marked and a grade of 1 or 0% will be awarded against the assessment item.
Please note: extensions will not be granted for group (2 members) submissions, regardless of the reason.
Part A tasks
Note the following points to prepare your answers:
- Population is significant and no further explanation is required from the domain expert. In other words, carry out your CSDP steps 1 to 6 for the given data only.
- We strongly recommend that you carry out your CSDP steps starting from Step 1 and ending in Step 6 to prepare your final ORM diagram. But you are not required to submit results of these steps individually.
- You are only required to submit your final ORM diagram obtained after performing Step 6.
Task 1 [10 marks]
Following are tables of employee information. Specify an ORM conceptual schema for this UoD.
|123456789||1||32.5||Smith, John B.||ProductX||Bellaire|
|123456789||2||7.5||Smith, John B.||ProductY||Sugarland|
|666884444||3||40.0||Narayan, Ramesh K.||ProductZ||Houston|
|453453453||1||30.0||English, Joyce A.||ProductX||Bellaire|
|453453453||2||20.0||English, Joyce A.||ProductY||Sugarland|
|333445555||2||10.0||Wong, Franklin T.||ProductY||Sugarland|
|333445555||3||10.0||Wong, Franklin T.||ProductZ||Houston|
|333445555||10||10.0||Wong, Franklin T.||Computerization||Stafford|
|333445555||20||10.0||Wong, Franklin T.||Reorganization||Houston|
|999887777||30||30.0||Zekata, Alicia J.||Newbenefits||Stafford|
|999887777||10||10.0||Zekata, Alicia J.||Computerization||Stafford|
|987987987||10||35.0||Jabbar, Ahmad V.||Computerization||Stafford|
|987987987||30||5.0||Jabbar, Ahmad V.||Newbenefits||Stafford|
|987654321||30||20.0||Wallace, Jennifer S.||Newbenefits||Stafford|
|987654321||20||15.0||Wallace, Jennifer S.||Reorganization||Houston|
|888665555||20||Null||Borg, James E.||Reorganization||Houston|
|Smith, John B.||123456789||1965-01-09||731 Fondren, Houston, TX||5||Research||333445555|
|Wong, Franklin T.||333445555||1955-12-08||639 Voss, Houston, TX||5||Research||333445555|
|Zekata, Alicia J.||999887777||1968-07-19||3321 Castle, Spring, TX||4||Administration||987654321|
|Wallace, Jennifer S.||987654321||1941-06-20||291 Berry, Bellaire, TX||4||Administration||987654321|
|Narayan, Ramesh K.||666884444||1962-09-15||975 FireOak, Humble, TX||5||Research||333445555|
|English, Joyce A.||453453453||1972-07-31||5631 Rice, Houston, TX||5||Research||333445555|
|Jabbar, Ahmad V.||987987987||1689-03-29||980 Dallas, Houston, TX||4||Administration||987654321|
|Borg, James E.||888665555||1937-11-10||450 Ston, Houston, TX||1||Headquarters||888665555|
Task 2 [5 marks]
Map the following ORM schema to a relational database schema.