knowledge and understanding of relational databases

La Trobe University Department of Computer Science and Information Technology CSE1ITF Assignment 2 Part ASemester 3 2019 10% of your ITF grade (Parts A and…

La
Trobe University

Department
of Computer Science and Information Technology

CSE1ITF
Assignment 2 Part ASemester 3 2019

10%
of your ITF grade (Parts A and B).

Objective

Demonstrate your
knowledge and understanding of relational databases and query design.

Due
Date

1st
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.

Late
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

Copying,
Plagiarism

This is an
individual assignment. You are explicitly instructed not to work in
groups.

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.

Submission
Guidelines

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
key?

The primary key
is one or more attributes that must be unique.

What constraints
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.

Microsoft Access

PostgreSQL_______

Oracle’s MySQL______

MariaDB_______

Apache Derby_________

Firebird SQL______

Investigate
database constraints. What kinds of constraints are available and
what do they do?

NOT
NULL constraints prevent null values from being entered into a
column.

Unique
constraints
ensure that the values in a set of columns are unique and not null
for all rows in the table.

primary
key
and foreign
key constraints
to define relationships between tables.

heck
constraint
(also referred to as a table
check constraint)
is a database rule that specifies the values allowed in one or more
columns of every row of a table

informational
constraint
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.

SQL databases
have a feature known as “transactions”.

Investigate what
a transaction is and what they do.

A
transaction
is unit of work performed within a database
management system
against a database, and treated in a coherent and reliable way
independent of other transactions.

A
transaction generally represents any change in a database.
Transactions in a database environment have two main purposes:

Transactions have
4 main properties, (ACID) find and describe what they are.

Ctomic:
An atomic
transaction
is an indivisible
and irreducible
series of database operations such that either all
occur, or nothing
occurs.

Consistent:
Consistency
in database
systems
refers to the requirement that any given database
transaction
must change affected data only in allowed ways.

Isolated:
isolation
determines how transaction
integrity is visible to other users and systems.

Durable:
durability
is the ACID
property which guarantees that transactions
that have committed will survive permanently.

What is a
database schema?

The
database
schema
of a database
is its structure described in a formal
language
supported by the database
management system
(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
relational
databases).
The formal definition of a database
schema is a set of formulas (sentences) called integrity
constraints
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
language.[1]
The states of a created conceptual
schema
are transformed into an explicit
mapping,
the database schema. This describes how real-world entities are
modeled
in the database.

You have been
contracted to design a simple database system for a car mechanic
shop.

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
work.

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.

The staff
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
their pay.

A task may also
require new parts from our inventory which need to be stored in the
database.

Parts will also
need to be catalogued, each part has a name, description, and a
price.

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
was received.

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?)

Do you need any assistance with this question?
Send us your paper details now
We’ll find the best professional writer for you!

 



error: Content is protected !!