代写 ICTDBS403 Create basic databases

 

Details of Assessment
Term and Year		Time allowed	-
Assessment No	1 of 2	Assessment Weighting	50%
Assessment Type	Written Project and Practical
Due Date	Week No. 8	Room

 

Details of Subject
Qualification	ICT40115 - Certificate IV in Information Technology
Subject Name	Database Development
Details of Unit(s) of competency
Unit Code (s) and Names	ICTDBS403 Create basic databases
	ICTPRG402 Apply query language

The purpose of this assessment is to assess the student in the following outcomes:	Competent (C)	Not yet Competent (NYC)
Performance Criteria : ICTDBS403 Create basic databases
-
1.1 Determine the information that the database is required to hold
1.2 Develop a written requirement report for the functionality of the database
1.3 Complete the documentation, and submit it to the appropriate person for approval
2. Use data modelling to design the database to suit requirements
2.1 Design an entity-relationship (ER) diagram to model the relationships between the entities and the attributes that the database will hold
2.2 Develop primary and foreign keys to link the entities
2.3 Develop a data dictionary
2.4 Complete the documentation, and submit it to the appropriate person for approval
3. Create a database on a web or database server
3.1 Use the appropriate language on a web or database server to create one or more databases
3.2 Use the appropriate language on a web or database server to create tables
3.3 Populate the database fields
4. Test the database and debug
4.1 Test the database on the web or database server
4.2 Ensure that the information represented matches the requirements
Performance Criteria : ICTPRG402 Apply query language
1. Determine the requirements of developing queries
1.1 Recognise various query-related terminologies
1.2 Identify the type of data source for a chosen query language
1.3 Identify and use the necessary tools, and environment, in building queries
2. Write queries to retrieve and sort values
2.1 Use an expression to retrieve values from a single unit
2.2 Use an expression to combine values from more than one unit
2.3 Use an expression to sort values into a certain order
3. Write queries to selectively retrieve values
3.1 Use an expression to filter a sequence, based on a predicate or a condition
3.2 Use an expression to filter the subset of a sequence, based on a predicate or a condition
3.3 Use an expression to extract a specific value by position
4. Perform a calculation in queries
4.1 Use an expression to perform a calculation on numeric values
4.2 Use an expression to perform an operation on text values
4.3 Use an expression to perform an operation on date and time values
Assessment/evidence gathering conditions
Each assessment component is recorded as either Competent (C) or Not Yet Competent (NYC). A student can only achieve competence when all assessment components listed under “Purpose of the assessment” section are recorded as competent. Your trainer will give you feedback after the completion of each assessment. A student who is assessed as NYC (Not Yet Competent) is eligible for re-assessment.
Resources required for this assessment
Computer with relevant software applications and access to internet Weekly eLearning notes relevant to the tasks/questions
Instructions for Students
Please read the following instructions carefully ·         This assessment has to be completed      In class    At home ·         The assessment is to be completed according to the instructions given by your assessor. ·         Feedback on each task will be provided to enable you to determine how your work could be improved. You will be provided with feedback on your work within two weeks of the assessment due date. All other feedback will be provided by the end of the term. ·         Should you not answer the questions correctly, you will be given feedback on the results and your gaps in knowledge. You will be given another opportunity to demonstrate your knowledge and skills to be deemed competent for this unit of competency. ·         If you are not sure about any aspect of this assessment, please ask for clarification from your assessor. ·         Please refer to the College re-assessment for more information (Student handbook).

代写 ICTDBS403 Create basic databases
 

Assessment marking and observation sheet for trainers

 
Part A: Written/Multiple Choice
Part B: Written/Design Worksheet and Practical/Book Database Codes
 

Marking List	Marks
PART A – Multiple Choice – 10 Marks
Question 1	/2
Question 2	/2
Question 3	/2
Question 4	/2
Question 5	/2
PART B - Design Work Sheet – 20 Marks
Question 1	/2
Question 2	/2
Question 3	/2
Question 4	/2
Question 5	/2
Question 6	/1
Question 7	/2
Question 8	/5
Question 9	/2
SUB TOTAL	/30
PART B - Book Database Codes (Observation of Practical) – 20 Marks
SUB TOTAL	/20
TOTAL	/50

 
 

Assessment  - Written/Project

Part A: Multiple Choice (10 Marks)
 

How and what you must submit:

All answers for your multiple choice questions, should include reasoning behind your choice. Submit a Digital copy of this assessment as per guidelines in e-Learning. Hard copy of all pages including assessment coversheet pages submitted to your assessor.

 
Question 1 (2 Marks):

WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 2 (2Marks):

WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 3 (2 Marks):

WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 4 (2 Marks):

WRITE YOUR ANSWER HERE (state with any important notes as required)

Question 5 (2 Marks):

WRITE YOUR ANSWER HERE (state with any important notes as required)
 
Part B: Project (40 Marks)
 
PROBLEM DESCRIPTION
A database is to be made to store information about books. Details to be stored for each book will be the title, year of publication, publisher, author(s), and ISBN. Some books may have an edition number. Each publisher will have a name and an optional address.
Publishers produce many books, but each book will have a single publisher. Each author may write several books, and a book may have one or more authors, and authors will be represented by their last name and initials.
Also associated with the books will be keywords from the title, which will form the basis of a search function.

	Example Books: Title: Cryptonomicon Year: 2000 Publisher: Arrow Author(s): N. Stephenson ISBN: 0099410672		Title: Database Systems Year: 2004 Publisher: Addison-Wesley Author(s): T. Connolly, C. Begg ISBN: 0321210255 Location: Harlow Edition: 4

 
 
 
 
 
 

Books are uniquely identified by their ISBN, authors by the combination of their last name and initials, and publishers by their name.
Deliverables:
There are two main components to the assignment – a design worksheet, and the database program itself. These are both worth 50% of the marks available for the assignment.

How and what you must submit:

Answers to your design worksheet needs to include screenshots from Question 5 onwards as proof of evidence of your designed database. Submit a Digital copy of this assessment and your designed database (export DB file) as per guidelines in e-Learning to submit your assessment via email. Printed hard copy of all pages including assessment coversheet pages submitted to your assessor.

 
1.    Design Worksheet (20 Marks)  
The design worksheet should be provided as a written requirement for the functionality of your database, it consists of a series of exercises for you to work through. The questions start with database design and lead on to writing queries to perform various tasks – such as adding, removing, and searching for books.
You should work through the design worksheet first, as it may help to identify potential problems in your design before you get involved in the programming component.
In particular, if you cannot write the SQL queries required in the worksheet, then you may find it very difficult to do the programming component.
Once you have completed the design worksheet you should set up any tables and other database objects that you need before moving on to the programming component.
 
2.    Book Database Codes (20 Marks)  
Once you have completed the design worksheet, you should go on to do the programming component.
In this part of the assignment you will write the SQL statements written in design worksheet and obtain screenshots of the results/outputs.
 
Screen shots are to be provided to all questions from Question 5 to Questions 9.
 
Design Worksheet
Provide written requirement report for the functionality of the database. Your report needs to include answers to all the below questions.
Question 1 (2 Marks): From the problem description in the assignment, identify the entities that need to be represented in the database, the attributes of each entity, the relationships between the entities, and the cardinality ratios of each relationship. Also include a data dictionary table

WRITE YOUR ANSWER HERE

Question 2 (2 Marks): Draw an Entity-Relationship Diagram illustrating the information you have identified in Question 1.
DRAW YOUR ENTITY-RELATIONSHIP DIAGRAM HERE

Question 3 (2 Marks): Describe the changes (if any) that need to be made to the Entity-Relationship Diagram you have given in Question 2 before it can be implemented as tables in SQL. Provide your final list of entities and their attributes, cardinality rations of each entity-relationalships. Also include your final data dictionary table.
WRITE YOUR ANSWER HERE
 
 代写 ICTDBS403 Create basic databases
 
Question 4 (2 Marks): Draw your revised entity relationship diagram.
DRAW YOUR ENTITY-RELATIONSHIP DIAGRAM HERE

Question 5 (2 Mark): Give the SQL statement(s) used to create the database and tables using the Entity-Relationship Diagram you have given in Question 4.
WRITE YOUR SQL STATEMENT(S) HERE

Question 6 (1 Mark):  Given the SQL table(s) you have created in Question 5, give the SQL statement(s) that would be used to add the following book to the database:
Title: Database Systems
Year: 2004
Publisher: Addison-Wesley
Author(s): T. Connolly, C. Begg
ISBN: 0321210255
Location: Harlow
Edition: 4
WRITE YOUR SQL STATEMENT(S) HERE
 
Question 7 (2 Marks): Give the SQL statement(s) used to populate your database that you created in Question 4, with at least 15 records of different books.
WRITE YOUR SQL STATEMENT(S) HERE
 
Question 8 (5 Marks, 1 Mark each): Given the SQL table(s) you have created in Question 5, give the SQL statements that would be used to conduct the following searches.
a)    Retrieve a list of the ISBNs of all of the books in the database. Sort Ascending
WRITE YOUR SQL STATEMENT(S) HERE
b)    Retrieve a list of the ISBNs of all books published between 1995 and 2000 (inclusive). Sort Descending
WRITE YOUR SQL STATEMENT(S) HERE
c)    Retrieve a list of the ISBNs of all books written by someone whose last name is “Begg” and by someone whose last name is “Connolly”.
WRITE YOUR SQL STATEMENT(S) HERE
d)    Retrieve a list of the ISBNs of all books with a word starting with “data” and a word starting with “system” in the title.
WRITE YOUR SQL STATEMENT(S) HERE
e)    Retrieve a list of the ISBNs of all books with a word starting with “data” in the title, written by an author with last name “Connolly”, published between 1995 and 2000.
WRITE YOUR SQL STATEMENT(S) HERE
f)     Retrieve all of the information, including publisher and author details, of the book with ISBN 0321210255.
WRITE YOUR SQL STATEMENT(S) HERE

 
Question 9 (2 Marks, 1 Mark each): Given the SQL table(s) you have created in Question 5, give the SQL statement(s) you would use to:
a)    Delete the book with ISBN 0321210255 (1 Mark)
WRITE YOUR SQL STATEMENT(S) HERE
b)    Delete all books from the database (1 Mark)
WRITE YOUR SQL STATEMENT(S) HERE
 
PART B - Book Database Codes (Observation of Practical)                               
 

Student name:
Student ID:
Assessor name:
Date of assessment:
Subject:	Database Development
Unit of competency:	ICTDBS403 Create basic databases ICTPRG402 Apply query language
Assessment Name:	Practical Project/Observation
Did the student perform the following skills:				Yes	No	1 Mark each
Determine the information that the database is required to hold
Develop primary and foreign keys to link the entities
Develop a data dictionary
Design an entity-relationship (ER) diagram to model the relationships between the entities and the attributes that the database will hold
Use the appropriate language on a web or database server to create one or more databases
Use the appropriate language on a web or database server to create tables
Populate the database fields
Test the database on the web or database server
Ensure that the information represented matches the requirements
Recognise various query-related terminologies
Identify and use the necessary tools, and environment, in building queries
Use an expression to retrieve values from a single unit
Use an expression to combine values from more than one unit
Use an expression to sort values into a certain order
Use an expression to filter a sequence, based on a predicate or a condition
Use an expression to filter the subset of a sequence, based on a predicate or a condition
Use an expression to extract a specific value by position
Use an expression to perform a calculation on numeric values
Use an expression to perform an operation on text values
Use an expression to perform an operation on date and time values
The student’s performance was:		Competent	Not Competent	Sub Total		/20
Feedback to student
Candidate signature:
Assessor signature:

  代写 ICTDBS403 Create basic databases