DPIT115 Data Management and Security

DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 1 of 6
Instructions
This assignment is related to conceptual modelling of a sample database domain, extension of the given conceptual
schema and logical design. You are also required to prepare study notes on UML and SQL.
The outcomes of this assignment are due Sunday 17th May @ 11:55 pm.
Please read the information listed below very carefully.
This laboratory contributes to 30% of the total evaluation in a subject DPIT115 and consists of 5 tasks.
You may find it beneficial to complete a draft for Tasks 4 and 5 before attempting Tasks 1, 2 and 3.
It is recommended that you attempt to solve the problems before attending the tutorial class to gain the most
from the supervised time.
It is expected that all tasks included in the assignment will be solved individually without any cooperation with the
other students.
If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes.
Deliverables
A single file Assgnment1.docx with your name and student number in the header of the document will be submitted
to a Turnitin box on the Moodle site for this assignment.
Submission of a file with a different name and/or different extension and/or different type will lose marks.
Submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The
compressed files will not be evaluated.
Further information on policy regarding late submissions is included in the subject outline.
Plagiarism will result in a FAIL grade being recorded for the assessment task.
DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 2 of 6
Task 1 (25 marks)
Read and analyse the following specification of a sample database domain.
A transportation company would like to create a database to record some of its operations. The company owns and
uses several busses to implement transportation services between the cities.
The company employs administration staff members, drivers and mechanics. A common description of an employee
consists of a unique employee number, first name last name and date of birth. Additionally, drivers are described by a
unique driving licence number and category of a driving licence. Mechanics are also described by a list of
qualifications acquired in the past together with a date when each qualification has been acquired.
The company supports a number of transportation services. A service starts at a city, passes through a number of
intermediate stops and ends in another or the same city. An origin location, a sequence of intermediate stops, and a
destination location of a service are described by unique addresses. An address consists of city name, street name and
building number. Each intermediate stop has a unique number in a sequence of intermediate stops within a
transportation service.
A transportation service is described by a planned departure time from an origin location, the planned arrival times
and departure times at the intermediate stops and a planned arrival time a destination location. A city of departure,
planned departure time, and a city of destination uniquely identify each transportation service.
The transportation company assigns the drivers and busses to the transportation services. Shorter transportation
services have one bus and one driver assigned. Longer transportation services have a single bus and multiple drivers
assigned. Each driver is assigned to a section of a longer transportation service, A section starts at a location and it
includes a number of successive locations a transportation service passes through.
A description of a bus consists of a registration number, make, model fuel consumption and total number of seats
available.
An objective if this task is to construct a conceptual schema for the specification of a database domain. You are not
allowed to add any artificial identification attributes commonly known as “id” attributes to the specification listed
above.
Use the UMLet tool to create a drawing of a conceptual schema in the UML simplified class diagrams notation
explained to you during the lecture classes in DPIT115. Use CSIT115-815Palette palette!
Add your name and student number to a visible part of the diagram and use an option File->Export as… to export
your diagram into a file solution1.bmp in BMP format.
If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes.
Deliverables
In the file, Assignment1.docx create a heading called Task 1 on a new page. A description of a process showing your
working on the conceptual modelling together with the final design of a conceptual schema should be added to the
document.
DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 3 of 6
Task 2 (15 marks)
Consider the following conceptual schema. The schema represents a database domain where the students submit
the assignments and assignments consist of tasks.
Your task is to extend the schema such after the extension; it would be possible to store the following information
in the database.
(1) We would like to store in the database information about two different types of students: postgraduate
students and undergraduate students. Postgraduate students are additionally described by a title of research
project. Undergraduate students are described by an optional title of software project.
(2) We would like to store in the database information about the titles and topics of all software projects and the
groups of students who participate in each project. Assume that a title uniquely identifies each project.
(3) We would like to add information about the total number of tasks included in each assignment and for each
task we would like to store information about a name of file of each task and a sequence number of a task
within an assignment. Do not forget about an identifier of a class TASK.
Use UMLet and CSIT115-815Palette palette to draw and extend the conceptual schema.
After all extensions save an extended conceptual schema in a file named solution2.uxf.
Next, use an option File->Export as… to export your diagram into a file solution2.bmp in BMP format.
If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes.
Deliverables
In the file, Assignment1.docx create a heading called Task 2 on a new page. Insert the file solution2.bmp with a list
of relational schemas, the primary key for each relational schema, candidate keys (if any) for each relational
schema, foreign keys (if any) for each relational schema.
DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 4 of 6
Task 3 (20 marks)
Consider a conceptual schema given below.
Assume that each driver has a different mobile phone number or does not have a mobile phone at all. Also, assume
that skills can be shared by mechanics.
Your task is to perform logical database design, i.e. to transform a conceptual schema given above into a collection
of relational schemas.
For each relational schema created, list the names of all attributes, primary key, candidate keys (if any), and foreign
keys (if any). Assume that the subset method must be used to implement a generalisation (if required). A way that
the conceptual schema can be transformed into a collection of relational schemas is explained in a presentation 06
Logical Design.
The relational schemas must be listed in a format presented in the slides 44 and 45 in a presentation 06 Logical
Design. Listing of the relational schemas in the other format will score no marks.
If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes.
Deliverables
In the file, Assignment1.docx create a heading called Task 3 on a new page. Insert a list of relational schemas, the
primary key for each relational schema, candidate keys (if any) for each relational schema, foreign keys (if any) for
each relational schema. Submission of a file with a different name and/or different extension and/or different type
scores no marks.
DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 5 of 6
Task 4 (20 marks)
Prepare study notes that explain the use of UML components as outlined in DPIT115. The study notes should include
appropriate definitions, your own drawings of the components that you have used along with an explanation (in
your own words). You should also include your own original examples. You should relate the UML component to
related concepts. Add a references section at the end of the task answer.
If you have any questions regarding this task, ask your tutor.
Deliverables
In the file, Assignment1.docx create a heading called Task 4 on a new page. Insert a listing of all the UML
components covered in this class, including original examples (4 pages maximum).
DPIT115 Data Management and Security
Assignment 1
DPIT115 Autumn Session 2020 Page 6 of 6
Task 5 (20 marks)
Prepare study notes that explain the use of SQL components as outlined in DPIT115. The study notes should include
appropriate definitions, your own examples of how these commands are used
(in your own words). You should also include your own original examples. You should also relate the UML
component to other related concepts. Add a references section at the end of the task answer.
If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes.
Deliverables
In the file, Assignment1.docx create a heading called Task 5 on a new page. Insert A listing of all the UML
components covered in this class, including original examples (4 pages maximum).

We are the Best!

course-preview

275 words per page

You essay will be 275 words per page. Tell your writer how many words you need, or the pages.


12 pt Times New Roman

Unless otherwise stated, we use 12pt Arial/Times New Roman as the font for your paper.


Double line spacing

Your essay will have double spaced text. View our sample essays.


Any citation style

APA, MLA, Chicago/Turabian, Harvard, our writers are experts at formatting.


We Accept

Secure Payment
Image 3