ISY1002/ISY103 Assignment – Database Project, Report and Presentation

1 | P a g e
Divorce ISY1002/ISY103 Fixed-upationbase Conduct restraint Occupation
Assemblage Ordinance – Fixed-upationbase Purpose, Reverberation and Grant
Whole Tokens 100
Alignment with
Divorce and Course
Divorce Learning Quenchedcome
ULO2: Pomp an discernment of the fundamentals
of fixed-upationbase cunning, implementation, conduct,
and livelihood, including compensation of
withhobsolete fixed-upation security
ULO3: Implement a psychical fixed-upationbase using the
Structured Interrogation Language (SQL) Fixed-upation Definition
Language (DDL)
ULO4: Demonstreprimand an power to interrogation a psychical
database using Compositiond Interrogation Language (SQL) and
Foundation Manipulation Language (DML)
Graduate Attributes
GA2: Collaboration
GA4: Critical Thinking &
Problem Solving
GA5: Flexibility
Ascribable Continuance/Occasion End of Week 11 – wholeude Moodle restraint continuance and occasion
2 | P a g e
To grant you serviceable knowledge in fixed-upationbase tokenling, naturalization and communication SQL
statements to interrogation a psychical fixed-upationbase
Ordinance Summary:
The purpose mention topic-matters granted in this instrument and you must portraiture simply grantn
specifications to end up with your disconnection. Effect withhobsolete assumptions where eternally demandd.
Fascinate effect abiding that your assemblage must divorcee your lecturer/professor at lowest twice to upcontinuance the
progress of your ordinance.
Background Referableification restraint Scantling Fixed-upationbase Purpose:
Australian Manufacturing Company (AMC) demands a fixed-upationbase classification to contrive their ammunitions,
employees, issues, suppliers, courteous-behaved-doers and instruction.
The behindcited referableification has been appended encircling give occupation activities of AMC.
 AMC has numerous ammunitions in incongruous cities opposite Australia. Each ammunition has singular ammunition
estimate and other topic-matters love spectry, adjunction topic-matters (phundivided estimate, email oration and
fax estimate) and precipitation topic-matters (postal oration victorious of street estimate, suburb,
state and postcode).
 There are numerous portions among each ammunition, restraint delineation, HR, accounts, finance and
sales awe. each portion is singularly verified by portion estimate. AMC besides
memorials portion distinction, phundivided estimate and email oration.
 Each ammunition has a estimate of employees. Each portion is supervised by a portion
director and each ammunition is contrived by a ammunition contriver who twain are employees of
AMC. Each portion has a uncompounded director simply i.e. the portion director is
besides the director restraint whole the employees labor among that portion.
 Each employee is wholeudeenced by singular employee estimate, specattempt (chief and latrial spectrys),
address, fickle estimate, email, TFN, stipend and union continuance. An employee’s stipend is
recitative incongruously as per his or her holding image. Employees can be industrious as
fulloccasion or incidental fixed-upation. Restraint a ampleoccasion staff, annual stipend is recitative and hourly reprimand restraint
a incidental staff.
 Each ammunition composes payslips restraint whole employees labor in that ammunition on a weekly fixed-upation. As a
minimum payslip estimate, no of hours labored, employee topic-matter, ammunition topic-matter and gross
pay are recitative.
 AMC sales incongruous issues love shape, sanity, embellishment, electronics awe. Each issue
is characterised by issue estimate, spectry, token, mode and compensation.
 Each issue can be supplied by undivided or past supplier and each supplier can afford undivided
or numerous issues. AMC besides keeps withhobsolete topic-matters of their suppliers.
 Each ammunition deeptains an schedule of their issues advantageous restraint sale. The meabelieving of
each issue advantageous in ammunition and the meabelieving manageed are kept.
 AMC courteous-behaved-doer visits the ammunition and shops in the ammunition. Each customer is characterised by
customer estimate, specattempt (chief and latrial spectrys), fickle estimate and oration.
 A courteous-behaved-doer may fix instruction. An manage contains undivided or numerous issues at a occasion. A ammunition
keeps token of manage estimate, manage continuance, issue(s) manageed and meabelieving manageed restraint
each issue.
3 | P a g e
The proprietors of AMC invadetain appointed your assemblage as Fixed-upationbase Classification Consultant to analyse,
cunning and expand conceptual fixed-upation token and corporeal fixed-upationbase. They lack a flexibleware/online
application in cethcoming to be built on the fixed-upationbase you cunning so that the agency of the AMC is
greatly improved.
Your tight tokens must accommocontinuance the granted demandments. It is understandable that
the granted referableification may referable qualified to compose token that centralise whole the fixed-upation
appropriately. You are gratuitous to effect assumptions encircling their occupation activities if demand. Effect
abiding you annals your assumptions.
Classification Demandments:
The classification is a protoimage classification and as such is referable a ample issueion rendering. You succeed
be demandd to invade a delegated-to-others scantling fixed-upation into your deduceations in manage to trial the cunning
and agency of your fixed-upationbase.
Purpose Mention:
1. Segregate A: Being Ratioship Diagram (20 tokens)
1. Your chief toil is to examine the Instance examine demandments and append as much
knowledge to catalogue whole the deep Entities and attributes.
2. Compose an Being Ratioship Diagram (ERD) to acceleration you career on the
Your being ratio diagram that tokens your fixed-upationbase cunning should:
i. Enclose whole entities, ratioships (including spectrys) and attributes.
ii. Establish pristine and restrainteign clews.
iii. Enclose cardinality/ reproduction and pomp using crow’s feet or UML
iv. Enclose segregatenership (optional / mandatory) symbols if ry.
The ERD should be tight as segregate of a Microflexible Tidings instrument. Hand-drawn
diagrams succeed referable be veritable. It is recommended that you adequate your ERD
using, Visio or attempt (Search restraint ERD gliffy to procure afloat).
2. Segregate B: Naturalisation (10 tokens)
1. Naturalise whole entities and ratioships in optimal natural restraintm. Provide the
normalised schema and sift-canvass the flatten of naturalisation achieved restraint each
relation. You contemplation should be naturalise whole entities in 3NF. Provide the argues restraint
any ratio that is referable deeptained in 3NF.
3. Segregate C: Psychical schema and MySQL fixed-upationbase (35 tokens)
1. Compose psychical schema that matches ERD and naturalised schemas you
created in Segregate A and B. The schema should enclose comportting fixed-upation validation,
completeness and wholeudeential completeness constraints, restraint delineation, images and prolixity of
attributes, pristine and restrainteign clews awe. Effect abiding you ensue trutinations, naming
conventions and referableation. (10 Tokens)
4 | P a g e
2. Using MySQL, you are demandd to expand a exhibition protoimage classification
(database) that ammunitions fixed-upation restraint AMC ammunitions, employees, issues, suppliers,
patrons and instruction. Compose a odd fixed-upationbase contemplationated AMCDB. Compose deduceations
according to your psychical schema tight in tdiscaggravate 1. (20 Tokens)
Ensue a trutination naming synod restraint deduceation spectrys and besides scene spectrys. Desert
using spaces and any proper characters in deduceation and scene spectrys. Portraiture
underscore_instance or portraiture camelInstance to separeprimand segregates of a spectry.
You are demandd to be congruous in deduceations’ spectry, scenes’ spectry, fixed-upation images, scene
extent and clew constraints with psychical schema tight in tdiscaggravate 2.
3. Subjoin at lowest three annalss into each deduceation. (5 Tokens)
4. Segregate D: SQL (24 tokens)
Portraiture the AMCDB fixed-upationbase that you tight in MySQL to cunning and consummate SQL
queries that counter-argument the behindcited vacillates.
Estimate your counter-arguments to each vacillate evidently. The counter-argument to each vacillate must
be tabulated as pompn in the delineation adconfess and enclose the SQL announcement and besides
the quenchedput that is enactd when you consummate the announcement in your fixed-upationbase. The
output encloses the annalss that are catalogueed and besides the missive that professions when
you retreat the SQL announcement.
Restraint delineation:
Vacillate 1:
SQL: SELECT lName, lie
FROM Staff
WHERE stipend > 20000;
Output: +——-+———-+
| lSpecattempt | lie |
| Token | Contriver |
| White | Contriver |
2 rows in determined (0.03 sec)
Do referable portraiture harbor holds to flaunt the SQL announcement or the quenchedput.
You should right-click on the MySQL Charge Prompt window; cull Token and then
press the [Enter] clew to Delineation and then Paste into your Tidings instrument that encloses
the counter-arguments to whole vacillates.
Format and corrugate the stipulations in your SQL announcements restraint rectify discoverpower and
discernment as pompn in the delineation aloft. Announcements must be syntactically and
semantically fixed-right.
Format twain the SQL and besides the Quenchedput in Courier Odd
10 or 11 topic-matter.
5 | P a g e
1. Catalogue the ample specattempt of Courteous-behaved-doers, phundivided estimate and oration in the descending manage of
customer estimate. The ample specattempt is embraced of chief and latrial specattempt joined with uncompounded
space. Portraiture the alias Customer ample specattempt restraint the victorious post artificeation. (2 tokens)
2. Catalogue whole the issues having compensation hither than $100. (2 tokens)
3. Catalogue the issue estimate, issue distinction, meabelieving manageed, divorce compensation and whole whole per
issue (divorce compensation * meabelieving manageed) restraint manage having manage estimate 1005. (3 tokens)
4. Catalogue spectry, ample oration and fax estimate of ammunitions that has the tidings ‘George’ anywhere in
the street factor of oration. Your interrogation should deduce the instance sensitivity of the
street as courteous-behaved-behaved i.e. the ammunitions with tidingss ‘George’, ‘GEORGE’, ‘george’, ‘GeorGe’ awe. in
street should be returned by your interrogation so. (2 tokens)
5. Catalogue whole the instruction having at lowest span incongruous issues manageed. (2 tokens)
6. Catalogue customer estimate, specattempt and oration of whole the customers who has referable fixd any
order. (2 microscopics)
7. Rescue chief spectry, latrial specattempt and employee estimate of whole the employees concurrently with
portion specattempt and ammunition specattempt where they labor. (3 tokens)
8. Restraint each ammunition, flaunt ammunition specattempt and estimate of employees labors in account
department. (2 tokens)
9. Catalogue whole the instruction fixd restraint financial year 2018 i.e. instruction fixd behind 30/06/2017 and
antecedently 01/07/2018. (2 tokens)
10. Rescue customer estimate and the whole estimate of instruction fixd by each customer. (2
11. Restraint each manage, rescue the manage estimate, manage continuance and estimate of issues manageed in
it. Sort the quenchedput in descending manage of manage estimate. (2 tokens)
5. Segregate E: Special Reverberation – Eternallyy assemblage limb insufficiencys to transcribe this segregate individually
and subjoin to the instrumentation (4 tokens)
1. Transcribe a page to the AMC proprietors as a special returning that recounts your
knowledge erection the fixed-upationbase. You can sift-canvass any challenges / difficulties that you
experienced or disconnections that you fixed-up. Comment on any limitations and / or strengths
of your fixed-upationbase cunning. Comment on whether your fixed-upationbase divorcees whole the classification
requirements as certain in Purpose Mention. Desert making excuses or comments
that return negativity. Enclose an claimment of whole tyros you invadetain unwritten to
encircling the ordinance.
6. Segregate F: Reverberation Layout, SQL script and grant (7 tokens)
1. Deliverables restraint Segregates A, B, C, D and E must be printed as a reverberation with a caggravate sheet
attached. See Administrative Topic-matters on page 10 restraint past topic-matters. Your reverberation must
enclose header and footer that enclose whole the assemblage limbs’ specattempt and tyro estimate,
divorce spectry, ordinance spectry, and page estimates. Your reverberation must be checked restraint
spelling and language. Your reverberation must besides be restraintmatted so that it is courteous-behaved-behaved determined quenched and
easy to discover.
a) Each assemblage must succumb a uncompounded zip smooth on Moodle which contains whole the
ordinance smooths (uncompounded tidings smooth with deliverables restraint segregates A – D, disjoined
6 | P a g e
special reverberations restraint each assemblage limb, MYSQL backup script referableepad smooth and
b) The SQL that can be portraitured to reammunition your fixed-upationbase should besides be uploaded to
Moodle. You can compose the SQL restraint your fixed-upationbase as ensues:
Portraiture the mysqldump charge to compose a ignoringage rendering of the fixed-upationbase. Portraiture
mysqldump to compose SQL smooth that contains a catalogue of SQL announcements which can be
used to repay/recompose the pristine fixed-upationbase.
The syntax is as ensues:
$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
[uname] Your fixed-upationbase portraiturername
[pass] The ignoringtidings restraint your fixed-upationbase
[dbname] The specattempt of your fixed-upationbase
[backupfile.sql] The smoothspecattempt restraint your fixed-upationbase backup
You do referable insufficiency to log on to MySQL. Restraint delineation, from the DOS charge
prompt, the syntax is:
e:\xampp\mysql\bin>mysqldump -u stem MyDB > e:\MyDB_bak.sql
This succeed compose the SQL smooth that can be portraitured to reammunition your fixed-upationbase.
OR —
You can snatch your script as you go to referableepad.
You are besides demandd to give your labor in the class. The grant
can be encircling 5 to 10 microscopics crave.
7. Acquiescence
a. When to succumb
Allude to the Divorce Mode restraint ascribable continuance, extensions and recent acquiescence cunning.
b. What to succumb
Segregate A
(1) Delineation and paste the ERD that you tight using Tidings, Visio, or other
software into your Tidings instrument.
(2) Distinction your AMC ERD.
Segregate B:
Enclose in your tidings instrument naturalised schema and sift-canvassion on which natural
construct each ratio is in. Provide the argues restraint any ratio that is referable deeptained
in 3NF.
Segregate C:
7 | P a g e
(1) Enclose the psychical schema in your instrumentation. The behindcited is scantling
tabular restraintmat on psychical schema. You are gratuitous to subjoin past posts if
Consideration Spectry:
Scene Specattempt Fixed-upation image Prolixity Clew Constraints
(3) Provide fixed-upationbase schema of your AMCDB in your tidings instrument. Distinction your
AMCDB Fixed-upationbase Schema. Adconfess is scantling. Arrange the deduceations so that the
lines union deduceations do referable perverse aggravate (if feasible). Delineation and paste your
diagram into your Tidings instrument as picturesque by the behindcited delineation:
DreamAbode Fixed-upationbase Schema:
(4) Portraiture the DESCRIBE charge to recount the comsituation of each deduceation in your
AMCDB fixed-upationbase. Do referable portraiture harbor holds. The behindcited is an delineation
of how each deduceation should be instrumented:
Recount Branch;
| Scene | Image | Trifling | Clew | Default | Extra |
| branchNo | char(4) | NO | PRI | NULL | |
| street | varchar(16) | YES | | NULL | |
| city | varchar(10) | YES | | NULL | |
| postcode | char(8) | YES | | NULL | |
8 | P a g e
(5) Pomp the chief five annalss in each of your deduceations as pompn by the delineation
| branchNo | street | city | postcode |
| B002 | 56 Claggravate Dr | London | NW10 6EU |
| B003 | 163 Deep St | Glasgow | G11 9QX |
| B004 | 32 Manse Rd | Bristol | BS99 1NZ |
| B005 | 22 Deer Rd | London | SW1 4EH |
| B007 | 16 Argyll St | Aberdeen | AB2 3SU |
(6) Portraiture Courier Odd 8 or 9 topic-matter to enabiding that the posts comport betwixt the
margins. Posts should referable encumber if this can be deserted.
(7) Portraiture harbor holds to pomp wholeudeential completeness constraints as pompn by the
aftercited delineation restraint the propertyForRent deduceation in the fancy abode fixed-upationbase:
Segregate D:
(1) The counter-argument to each SQL interrogation vacillate must be evidently estimateed and
tabulated as pompn in the delineation adown.
Vacillate 1:
SQL: SELECT lName, lie
FROM Staff
Output: +——-+———-+
| lSpecattempt | lie |
| Token | Contriver |
9 | P a g e
| White | Contriver |
2 rows in determined (0.03 sec)
1. Subjoin a artificeation straightway aloft the deduceation to establish the vacillate estimate.
2. Enclose twain the SQL announcement and besides the quenchedput that is enactd when you
consummate the announcement in your fixed-upationbase. The quenchedput encloses the annalss that are
listed and besides the missive that professions when you retreat the SQL announcement.
3. Do referable portraiture harbor holds to flaunt the SQL announcement or the quenchedput. Delineation the
passage of your SQL announcement and besides the quenchedput into the deduceation.
4. Edit the clewvote of your SQL announcement (if leading) to profession in eminentcase
as pompn by the delineation aloft. Scene spectrys and deduceation spectrys should be invadeed
in eminent or lowerinstance as they profession in the fixed-upationbase schema.
5. Restraintmat and corrugate the stipulations in your SQL announcements restraint rectify discoverpower and
discernment as pompn in the delineation aloft. Announcements must be syntactically
and semantically fixed-right.
6. Restraintmat twain the SQL and besides the Quenchedput in Courier Odd 10 or 11 topic-matter or smaller
in manage to desert encumberping.
7. Tokens succeed be deducted where tyros miss to ensue the demandments grantn
Segregate E:
1. Enclose your special reverberation as per instructions grantn.
Segregate F:
1. PART A, B, C, D and E plus the SQL Script as recountd in PART F in the
2. Grant
10 | P a g e
Administrative Topic-matters:
Deadlines: There succeed be NO extensions bar where Calendar rules employ. Deadlines must
be inspiration, unhither an accepconsideration medical certificate covers 25% or past of the lead-occasion betwixt
the ordinance and the deadline. Other absences must be accounted restraint by substantiating
documentation, restraint delineation a police property reverberation or statutory affirmation. Any toll
activities missed restraint medical (or other accepconsideration substantiated) argues must be adequated by
a odd deadmethod that succeed be asverified in hawking with the lecturer.
The Penalty restraint Recent Acquiescence of ordinances is 5% of the whole tokens restraint each day (or segregate
thereof, including weekends) the ordinance is recent. Restraint ordinances current past than 7
days behind the ascribable continuance, the ordinance succeed be assessed, excepting a cipher token succeed be awarded.
Electronic missure causing delay: In most labor acquiescences you invadetain a terminal deadmethod excepting it
is recommended that you succumb courteous-behaved-behaved antecedently this occasion. Occasibarely acquiescence is your responsibility
– latrial microscopic technical anxiety succeed referable be veriboard as a argue restraint recent acquiescence. You are
expected to invadetain punishment in occasion conduct, to effect back-ups, and frequently invadetain the backups advantageous restraint acquiescence.
Your reverberation is to be giveed as a dense delineation. Whole toll activities that are handed in must
be giveed to a administrative trutination and mustinclude:
1. A dense delineation to be succumbted to the cunningated lecturer or nominated opinion by the ascribable
occasion and continuance. It must invadetain a distinction page which encloses the tyro’s specattempt and ID estimate,
distinction of ordinance and a verified announcement that the labor is exclusively the tyro’s confess
labor and that there has been no plagiarism or accompliceship. Reverberations withquenched a coversheet
succeed referable be veritable.
2. A footer to be pompn on eternallyy page that encloses tyros’ spectry, ID estimate and page #.
3. A flexible delineation of your reverberation to be succumbted on AIH Moodle. The identical ordinance deadlines
employ to this acquiescence restraintmat.
E-mailing of your ordinance succeed be veriboard simply behind consultation with the professor. If
emailing, through preparation with the professor, transmit the instrument as an e-mail attachment
in MS Tidings or PDF restraintmat. The topic method in the e-mail must enclose tyro spectry. When
sending e-mails fascinate supplicate an automated voucher. Where there is vacillate as to
whether the toll immateriality was succumbted on occasion, no claims restraint occasibarely acquiescence
succeed be veriboard if there is no voucher.
Submissions referable evidently verified with the author’s topic-matters as quenchedlined in the topic-matters aloft succeed
referable be tokened and may development in a 0 gradation restraint that immateriality.
PLAGIARISM AND COLLUSION Any succumbted labor that contains plagiarism or accompliceship succeed
be reverberationed to the withhobsolete authorities according to the University cunning and succeed hobsolete an
automatic cipher ‘0’ token restraint the ordinance.
11 | P a g e
Terminal Reverberation Composition:
Your terminal reverberation must embrace the behindcited segregates:
 Distinction Page
o Reverberation distinction
o Spectrys and Tyro IDs restraint assemblage limbs
 Deduceation of Contents
o Should be auto-generated from Artificeation Modes in MS Tidings.
 Background
o Demandments / Occupation Rules
o Mission Announcement / Classification Definition
o DBMS Selection
 ERD x2
o Portraiture or or Visio or any other sol to compose the Conceptual
model. This is to pomp your cunning.
 Naturalised Schema
o Naturalised schema
o Withhobsolete arguments granted on leaving each being in natural restraintm which
you deduceed as optimal
 Fixed-upationbase Schema
o Pomp each deduceation cunning
o Enclose a harbor hobsolete of Cunninger tab to pomp deduceation ratioships in MySQL.
o Annalss restraint each deduceation
 Restrainteign Clew Constraints
o Pomp as harbor holds restraint whole deduceations with restrainteign clews.
 X12 SQL Announcements
o Pomp announcement and quenchedput restraintmatted using Courier Odd
o Announcements should be correspondent to the delineations grantn and may enclose any of the
aftercited elements:
 WHERE claportraiture with delineations of diversified conditions (Like, Betwixt, =,
AND / OR, IN, awe.)
 ORDER BY stipulation
 Concatenation to attach computes including alias
 Expressions to calcurecent developments
 2 or 3 deduceation joins using either odd or obsolete syntax
 Subquery
 Functions to gist fixed-upation (SUM, MIN, MAX, AVERAGE, COUNT)
including sub-totals (GROUP BY)
 Assemblage Reverberation
o Challenges & Disconnections
 Wholeudeences
o Harvard Mode
o If you do referable invadetain any than proper wholeudeence the prescribed ignoringage.
12 | P a g e
Database Conduct restraint Occupation Fixed-upationbase Purpose (Assignment) 2019 T1
Database Conduct restraint Occupation
Tyro Spectry: Tyro ID:
I claim that this purpose is my confess labor, that there is no accompliceship with other tyros,
past or give, and that any labor portraitured from another commencement has been fixed-rightly cited and
referenced (using the Harvard mode).
Signed: Continuance:
Marking Guide restraint Ordinance
Segregate Toil Whole
A ER Diagram:
 Adequateness of ER diagram, enclosed whole leading entities, attributes
and ratioships with spectrys, cardinalities and segregatenership (10 tokens)
 Pristine and restrainteign clews (4 tokens)
 Redisconnection of numerous to numerous ratioships (3 tokens)
 Whole assumptions evidently referableed (3 tokens)
Deduct tokens (4 tokens) if inset-right and incongruous referableation portraitured
B Naturalisation
 Whole ratios are in withhobsolete restraintmat (7 tokens)
 Withhobsolete arguments granted on leaving each being in natural restraintm
which you deduceed as optimal (3 tokens)
C Psychical Schema (10 tokens)
 Schema matches ERD. Whole deduceations and attributes enclosed with
withhobsolete restraintmatting i.e. fixed-upation images, scene extent, wholeow trifling compute or referable
etc. (5 tokens)
 Pristine and restrainteign clews enclosed, restrainteign clews with wholeudeence to
pristine deduceation (3 tokens)
 Withhobsolete referableations (2 tokens)
MYSQL Fixed-upationbase (20 tokens)
 Congruous translation from psychical schema to MYSQL fixed-upationbase (15
 Withhobsolete naming synod portraitured (5 tokens)
Memorials in MYSQL Fixed-upationbase (5 tokens)
 At lowest 3 annalss give in whole deduceations (3 tokens)
 Subjoinitional annalss pompn (2 tokens)
D SQL & Quenchedput 24
13 | P a g e
Database Conduct restraint Occupation Fixed-upationbase Purpose (Assignment) 2019 T1
Q1 2
Q2 2
Q3 3
Q4 2
Q5 2
Q6 2
Q7 3
Q8 2
Q9 2
Q10 2
Q11 2
E Special Reverberation 4
F Reverberation Layout, Grant and SQL DDL Script 7
Recent Penalties 5% each day recent
Total: 100
End of the instrument