ICT211 Database Design System
ICT211 Database Design System Assignment Help
Introduction to subject
Major concepts, methodologies, tools and techniques that are needed to analyze, design and develop well framed database for the modern organizations will be introduced you in this course. You will be studying data modeling using entity relationship diagram. You will be using a DBMS to earn a reorganization of the concepts and how to apply the database management systems practically. In order to complete the cycle of professional practice SQL need to be covered.
VALUE:- 30% of the total assignment for this course.
Case Study: XYZ Pty Ltd – CRM Database
XYZ Pty Ltd (XYZ) is a computer and information system repair and support company is located in Australia. XYZ provides small businesses an onsite service with their IT needs but they also repairs personal computer.
Requirements for assignment and deliverables
Part A – Submitted as a MS Word Document:
1. Entity Relationship Diagram in Crows Foot Notation
2. Relational Schema – including Primary and Foreign Keys
3. Supplementary design requirements – For Example but not limited to:
4. Information on length of identifiers, postcodes, names.
5. Data attribute information (compulsory, variable length/type, etc.)
Part B – Submitted as a single plain text file with name
<StudentNumber>_crm_sql.txt, containing all your SQL implementation:
CREATE TABLE statement including all integrity constraints,
CREATE TRIGGER statements;
Automatically update the job table – Job Status to ‘Job Invoiced’ when an invoice number is entered into the job,
INSERT INTO statement for populating database:
Some sample of data has been provided from an existing MCS database which you have to enter. Common key are related by the two worksheets of data.
NOTE: not all the sample data attributes are applicable, you choose which data you will include
The data which is provided to you is incomplete, you are expected to generate and enter data which will help you to test the database and produce the required sample report.
Data might be inserted in a particular order to match with integrity constraints,
SELECT statement which will produce the following data:
Sample invoice is generated by a single query (you are required to have the data for the query in the databse) including:
Client name and account number,
Invoice number and the total amount and tax for the invoice,
The job number work required which is included in at least three job invoices and that job amount and tax (the total of the jobs should equal the total invoice amount).
In last month the parts which have been used on jobs will be shown in the inventory report generated in a single query.
A single query which will generate a financial accounting report which shows the outgoing (parts used on jobs ad total time x employee hourly rate), and incoming (total invoices).
Marking Sheet for ATMC ICT211 S2.2018 Task 2
|Marking Criteria||Maximum Marks|
|Part A – Entry Relationship Diagram>||15|
|Part A – Relation Schema>||10|
|Part A – Supplementary Data Requirements>||10|
|Part A – Assumtions>||10|
|Part B – CREATE TABLE statement>||15|
|Part B – CREATE TRIGGER statement>||5|
|Part B – INSERT INTO statement>||15|
|Part B – SELECT statement>||20|
|Total (I) =||100|