ICT211 Database Design System Assignment Help
Introduction to subject
Major concepts, methodologies, tools, and techniques that are needed to analyze, design, and develop a well-framed database for modern organizations will be introduced to you in this course. You will be studying data modeling using an 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
Background:-
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 repair personal computers.
Requirements for assignment and deliverables
Part A – Submitted as an 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 the length of identifiers, postcodes, names.
5. Data attribute information (compulsory, variable length/type, etc.)
ICT211 DATABASE DESIGN SYSTEM
Assumptions
Part B – Submitted as a single plain text file with the 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 keys 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 that 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,
A 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 database) including:
Client name and account number,
Invoice number and the total amount and tax for the invoice,
The job number work required 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 the 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 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
Student name:
| 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 |
Visit At :- HI6006 COMPETITIVE STRATEGY