of Computer Science and Information Technology
Assignment 2 Part ASemester 3 2019
of your ITF grade (Parts A and B).
knowledge and understanding of relational databases and query design.
December 2019 before 11.55 pm via LMS
Delays caused by
computer downtime cannot be accepted as a valid reason for a late
submission without penalty. Students must plan their work to allow
for both scheduled and unscheduled downtime.
The LMS will be
configured to allow you to submit as many times as you like, the most
recent version will be marked.
submissions will incur a 5% penalty for each day that it is late.
If you change
your submission after the due date it is considered a late submission
and will incur a 5% penalty for each day that it is late
This is an
individual assignment. You are explicitly instructed not to work in
Plagiarism is the
submission of somebody else’s work in a manner that gives the
impression that the work is your own. For individual assignments,
plagiarism includes the case where two or more students work
collaboratively on the assignment. The Department of Computer Science
and IT treats plagiarism very seriously. When it is detected,
penalties are strictly imposed.
Please put your
submission in a MS word document.
The ERD can be
pasted into the word document as an image.
You may use any
tool you like to draw the ERD.(draw.IO)
You are required
to upload your solutions to the LMS.
Your file name
should be formatted like so: ITF_Asssign2A_12345678.docxWhere
12345678 is your student ID.
Table columns are
sometimes also called _____Fields or Attributes_____
Table rows are
sometimes also called ____Records or Tuples________
This is a short
answer question, answers should be at least a few sentences long.
What is a Primary
The primary key
is one or more attributes that must be unique.
does it impose on the table data?
There can never
be two customers with the same customer ID.
The primary key
is enforced by the database itself.
What is a
composite primary key?
A primary key
over multiple columns is called composite primary key.
Search online and
find 6 different SQL Database products, can be open source or closed
source.Single word answers are permitted for this question.
database constraints. What kinds of constraints are available and
what do they do?
NULL constraints prevent null values from being entered into a
ensure that the values in a set of columns are unique and not null
for all rows in the table.
to define relationships between tables.
(also referred to as a table
is a database rule that specifies the values allowed in one or more
columns of every row of a table
is a constraint attribute that can be used by the SQL compiler to
improve the access to data
What is a
database view? How are they useful?
A database view
is a saved query that works just like a normal table, except you
cannot edit the rows.
A database view
is useful if you have a large and complex query.
have a feature known as “transactions”.
a transaction is and what they do.
is unit of work performed within a database
against a database, and treated in a coherent and reliable way
independent of other transactions.
transaction generally represents any change in a database.
Transactions in a database environment have two main purposes:
4 main properties, (ACID) find and describe what they are.
is an indivisible
series of database operations such that either all
occur, or nothing
refers to the requirement that any given database
must change affected data only in allowed ways.
determines how transaction
integrity is visible to other users and systems.
is the ACID
property which guarantees that transactions
that have committed will survive permanently.
What is a
of a database
is its structure described in a formal
supported by the database
(DBMS). The term “schema”
refers to the organization of data as a blueprint of how the database
is constructed (divided into database tables in the case of
The formal definition of a database
schema is a set of formulas (sentences) called integrity
imposed on a database.]These
integrity constraints ensure compatibility between parts of the
schema. All constraints are expressible in the same language. A
database can be considered a structure in realization of the database
The states of a created conceptual
are transformed into an explicit
the database schema. This describes how real-world entities are
in the database.
You have been
contracted to design a simple database system for a car mechanic
When a car is
brought to the shop, we create a “job” for it.The job
has a start time, an end time, and the customer that requested the
Each job consists
of one or more tasks, each task is completed by a staff member. Some
tasks may require multiple staff members to complete.
involved in each task need to be stored along with the number of
hours the staff member spent working on the task so we can calculate
A task may also
require new parts from our inventory which need to be stored in the
Parts will also
need to be catalogued, each part has a name, description, and a
Each car has a
VIN, a make (the brand of the car), a model and a colour. Cars
also have the registration plate number stored in the database, but
many of the cars in the shop don’t have plates yet.
The customers and
staff also need to be stored in the database, both will have a name,
and an address.
We also offer
payment plans, where the customer can pay off the job in monthly
payments.We will need to keep track of each payment and when it
Read the problem
description and create an ER diagram to represent the system.
Many of the
entities described above do not have a suitable primary key
attribute, so you may assign them a numeric ID to be the primary key.
Since many of the cars don’t have number plates, it might be wise to
have them as a separate entity instead of an attribute. This means we
can avoid having NULLs in the database.
Let’s block ads! (Why?)